Introduction
Some times in application, it is requiring to upload excel sheet for bulk data update and also requires to validate that excel sheet before upload it.
Getting Started
This blog describes how to validate an excel sheet for the same. It introduces a class (ExcelVlidator) to validate an excel sheet before uploading it into the server or before storing its data into the database server.
Through this class or code, you can check whether your excel sheet matches your criteria like a number of columns, the data type of columns and type data, etc. before uploading it.
This class contains four methods which helps to validate excel sheet. These methods are ValidateExcel(), ValidateScheme(),ValidateData() and ReadExcelFile().
The ReadExcelFile() is for reading excel data, this function reads excel data and store it in a DataSet. Below codes are the details about this function.
private DataSet ReadExcelFile()
{
Console.WriteLine("Readig Excel File : " + ExcelFilePath + " completed");
DataSet ds = new DataSet();
string connectionString = this.GetConnectionString();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
string sheetName = "Student$";
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName.Replace("$", string.Empty);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
ds.Tables.Add(dt);
cmd = null;
conn.Close();
}
Console.WriteLine("Readig Excel File completed");
return ds;
}
This ValidateScheme() as the name suggests is for validating schema (structure of table like a number of columns, type of columns, etc. contained by excel sheet) of excel sheet. To validating schema the function took the help of the XmlSchemaSet class. See the below code on how this XmlSchemaSet class is used in this function.
private void ValidateScheme()
{
Console.WriteLine("Validatingg excel schema");
XmlSchemaSet schemas;
XmlReaderSettings settings;
try
{
//XmlSchemaSet schemas;
schemas = new XmlSchemaSet();
schemas.Add("http://www.deitel.com/booklist", SchemaFilePath);
settings = new XmlReaderSettings();
//settings.ProhibitDtd = false;
settings.DtdProcessing = DtdProcessing.Parse;
settings.CheckCharacters = true;
settings.ValidationType = ValidationType.Auto;
settings.Schemas = schemas;
settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
Uri uri = new Uri(SchemaFilePath);
//settings.XmlResolver.ResolveUri(uri, null);
XmlReader reader = XmlReader.Create(XMLFilePath, settings);
Console.WriteLine("Validating schema File : " + ExcelFilePath);
while (reader.Read())
{
// Console.WriteLine(reader.ReadState.ToString() + " with node elements");
};
if (valid)
{
Console.WriteLine("Document schema is valid");
} // end if
valid = true;
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error : " + ex.Message);
}
finally
{
Console.WriteLine("Excel schema validating ended ");
}
}
For validating data (type of data, length of data etc.), this class has used ValidateData(). It also used the same class (XmlSchemaSet) that the above function used. But only little changes have been made to validate data. See the below codes of function for more details.
private void ValidateData()
{
XmlReaderSettings settings = new XmlReaderSettings();
//settings.ProhibitDtd = false;
settings.DtdProcessing = DtdProcessing.Parse;
settings.ValidationType = ValidationType.DTD;
settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
XmlReader reader = XmlReader.Create(XMLFilePath, settings);
Console.WriteLine("Readig Excel File : " + ExcelFilePath);
// Parse the file.
try
{
while (reader.Read()) ;
if (valid)
{
//writertbox("Document contains valid data");
} // end if
}
catch
{
}
}
The main method which calls all the above-mentioned functions is ValidateExcel() and the code of this method is given below.
public void ValidateExcel()
{
string input = Convert.ToString(Console.ReadLine()).ToUpper();
if (input == "Y")
{
try
{
DataSet ds = this.ReadExcelFile();
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version='1.0' standalone='no'?>");
sb.Append(Environment.NewLine);
sb.Append("<!DOCTYPE NewDataSet SYSTEM '" + DTDFilePath + "'>");
sb.Append(Environment.NewLine);
sb.Append(ds.GetXml());
string ab = sb.ToString();
XmlDocument doc = new XmlDocument();
doc.LoadXml(ab);
doc.Save(XMLFilePath);
Console.WriteLine("New excel file created : " + XMLFilePath);
this.ValidateScheme();
this.ValidateData();
}
catch (Exception ex)
{
Console.WriteLine("Error : " + ex.Message);
}
}
}
Code of class
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Schema;
namespace ValidateExcelSheet
{
public class ExcelVlidator
{
string ExcelFilePath = "E:\\Projects\\ValidateExcel\\Students.xlsx";
string SchemaFilePath = "E:\\Projects\\ValidateExcel\\StudentsSchema.xsd";
string DTDFilePath = "E:\\Projects\\ValidateExcel\\StudentDTD1.dtd";
string XMLFilePath = "E:\\Projects\\ValidateExcel\\Students.xml";
bool valid = true;
public ExcelVlidator()
{
Console.WriteLine("Schema File Path : " + SchemaFilePath);
Console.WriteLine("DTD File Path : " + DTDFilePath);
Console.WriteLine("Enter 'Y' to continue");
}
public void ValidateExcel()
{
string input = Convert.ToString(Console.ReadLine()).ToUpper();
if (input == "Y")
{
try
{
DataSet ds = this.ReadExcelFile();
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version='1.0' standalone='no'?>");
sb.Append(Environment.NewLine);
sb.Append("<!DOCTYPE NewDataSet SYSTEM '" + DTDFilePath + "'>");
sb.Append(Environment.NewLine);
sb.Append(ds.GetXml());
string ab = sb.ToString();
XmlDocument doc = new XmlDocument();
doc.LoadXml(ab);
doc.Save(XMLFilePath);
Console.WriteLine("New excel file created : " + XMLFilePath);
this.ValidateScheme();
this.ValidateData();
}
catch (Exception ex)
{
Console.WriteLine("Error : " + ex.Message);
}
}
}
private void ValidateScheme()
{
Console.WriteLine("Validatingg excel schema");
XmlSchemaSet schemas;
XmlReaderSettings settings;
try
{
//XmlSchemaSet schemas;
schemas = new XmlSchemaSet();
schemas.Add("http://www.deitel.com/booklist", SchemaFilePath);
settings = new XmlReaderSettings();
//settings.ProhibitDtd = false;
settings.DtdProcessing = DtdProcessing.Parse;
settings.CheckCharacters = true;
settings.ValidationType = ValidationType.Auto;
settings.Schemas = schemas;
settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
Uri uri = new Uri(SchemaFilePath);
//settings.XmlResolver.ResolveUri(uri, null);
XmlReader reader = XmlReader.Create(XMLFilePath, settings);
Console.WriteLine("Validating schema File : " + ExcelFilePath);
while (reader.Read())
{
// Console.WriteLine(reader.ReadState.ToString() + " with node elements");
};
if (valid)
{
Console.WriteLine("Document schema is valid");
} // end if
valid = true;
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error : " + ex.Message);
}
finally
{
Console.WriteLine("Excel schema validating ended ");
}
}
private void ValidateData()
{
XmlReaderSettings settings = new XmlReaderSettings();
//settings.ProhibitDtd = false;
settings.DtdProcessing = DtdProcessing.Parse;
settings.ValidationType = ValidationType.DTD;
settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
XmlReader reader = XmlReader.Create(XMLFilePath, settings);
Console.WriteLine("Readig Excel File : " + ExcelFilePath);
// Parse the file.
try
{
while (reader.Read()) ;
if (valid)
{
//writertbox("Document contains valid data");
} // end if
}
catch
{
}
}
private string GetConnectionString()
{
Dictionary<string, string> props = new Dictionary<string, string>();
// XLSX - Excel 2007, 2010, 2012, 2013
props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
props["Extended Properties"] = "Excel 12.0 XML";
props["Data Source"] = ExcelFilePath;
// XLS - Excel 2003 and Older
//props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
//props["Extended Properties"] = "Excel 8.0";
//props["Data Source"] = "C:\\MyExcel.xls";
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
sb.Append(prop.Key);
sb.Append('=');
sb.Append(prop.Value);
sb.Append(';');
}
return sb.ToString();
}
private DataSet ReadExcelFile()
{
Console.WriteLine("Readig Excel File : " + ExcelFilePath + " completed");
DataSet ds = new DataSet();
string connectionString = this.GetConnectionString();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
string sheetName = "Student$";
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName.Replace("$", string.Empty);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
ds.Tables.Add(dt);
cmd = null;
conn.Close();
}
Console.WriteLine("Readig Excel File completed");
return ds;
}
private void ValidationCallBack(Object sender, ValidationEventArgs args)
{
this.valid = false;
Console.WriteLine("Validation error: " + args.Message);
}
}
}
Code for using ExcelVlidator class
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Schema;
namespace ValidateExcelSheet
{
class Program
{
static void Main(string[] args)
{
ExcelVlidator validator = new ExcelVlidator();
validator.ValidateExcel();
Console.ReadLine();
}
}
}
Related Articles
- Export DataTable To Excel in C#
- Export to Excel in MVC
- Export Table Data into XML in SQL
- Export HTML Table to Excel Using JavaScript
- Creating controller for File Upload
- Import Excel in C#
- Import XML into SQL Table
- Reading Excel file in AngularJS
Summary
Thanks for visiting my blog. enjoy and happy coding.
Thanks
