Validation Of Excel Data In C#

Introduction

This article demonstrates how to validate an Excel sheet before uploading or storing its data into a database.

I have seen many developers implementing various types of logic to achieve the same. Some developers fetch the Excel data and validate (data type, length of data etc.) each data one by one.

This is a time-consuming process if you have a large amount of data (more than 1000 records) in Excel. This article demonstrates how to validate an Excel data without consuming a lot of time.

Getting Started

Before starting a demonstration let’s understand about this demonstration. This demonstration is useful while validating an Excel sheet before uploading it to the server in both web and Windows-based applications. But here, this demonstration uses Windows-based application and it does not contain Upload coding part, it contains only validation part of coding.

It first,reads an Excel sheet's data using OLDB and stores its data into a Dataset, then validates the Excel sheet and displays validation message in error panel if the error exists.

It does two types of validation - one is table structure or schema and another one is data validation. Validation happens with the help of XmlReaderSettings class which comes under System.Xml namespace.

Validating schema means validating the structure of the table, the number of columns should be same as a schema of the Excel sheet. Let’s talk about schema.

Schema

It is the recommendation of the World Wide Web Consortium (W3C) that specifies how to formally describe the elements in an Extensible Markup Language (XML) document. It can be used by programmers to verify each piece of item content in a document. They can check if it adheres to the description of the element it is placed in.

1. The purpose of an XML Schema is to define the legal building blocks of an XML document:
2. the elements and attributes that can appear in a document
3. the number of (and order of) child elements
4. data types for elements and attributes
5. default and fixed values for elements and attributes

For validating the schema of  Excel sheet, the ValidationType property of class XmlReaderSettings needs to be set ValidationType.Schema. The below code is sample example for validating the schema of Excel sheet.

//XmlSchemaSet schemas;
           schemas = new XmlSchemaSet();
           schemas.Add("http://www.deitel.com/booklist", schema + "\\StudentsSchema.xsd");

           settings = new XmlReaderSettings();
           settings.ProhibitDtd = false;
           settings.CheckCharacters = true;
           settings.ValidationType = ValidationType.DTD;
           settings.Schemas = schemas;
           settings.ValidationEventHandler += new ValidationEventHandler(settings_ValidationEventHandler);
           Uri uri = new Uri(schema + "\\StudentDTD.dtd");
           //settings.XmlResolver.ResolveUri(uri, null);

           XmlReader reader = XmlReader.Create(schema + "\\Students.xml", settings);
           while (reader.Read()) ;
           if (valid)
           {
               writertbox("Document SCHEMA is valid");
           }  // end if
           valid = true;
           reader.Close();

Same as above for validating, you need to set the ValidationType property to ValidationType.DTD. The below code is a sample example for validating the data of Excel sheet. Data validaton means validation of data, such as - type of data and length of data should exist etc.

XmlReaderSettings settings = new XmlReaderSettings();

            //settings.ProhibitDtd = false;
            settings.DtdProcessing = DtdProcessing.Parse;
            settings.ValidationType = ValidationType.DTD;

            settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
            XmlReader reader = XmlReader.Create(Path + "\\Students.xml", settings);



            // Parse the file.

            try
            {

                while (reader.Read()) ;

                if (valid)
                {
                    writertbox("Document contains valid data");
                }  // end if
            }
            catch
            {


            }

Demonstration

Open whatever version  of Visual Studio you have, but the framework should be minimum 3.0 and VS2010 for smoothly running this demonstration.

Select WPF as a project template, then give the name of the project as “Validate Excel”.

Take controls like textbox, textblock, button, and list box like below image. The textbox and button are taken in this demonstration to browse the Excel file and list box whose border color is red for displaying the messages.

Create a function named ‘ReadExcelFile’ and use the below code to read the Excel data.

private DataSet ReadExcelFile(string excelPath) {
    DataSet ds = new DataSet();
    string connectionString = GetConnectionString(excelPath);
    using(OleDbConnection conn = new OleDbConnection(connectionString)) {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;
        // Get all Sheets in Excel File
        // DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        // Loop through all Sheets to get data
        // foreach (DataRow dr in dtSheet.Rows[2])
        // {
        string sheetName = "Student$";
        //if (!sheetName.EndsWith("$"))
        // continue;
        // Get all rows from the Sheet
        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);
        //}
        //string schema = ds.GetXmlSchema();
        cmd = null;
        conn.Close();
    }
    return ds;
}
private string GetConnectionString(string excelPath) {
    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"] = excelPath;
    // 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();
}

Create another function with name ‘CreateXML’ to convert the Excel data into XML file for validating the data. Use the below code to create an XML file.

private void CreateXML(string path,DataSet ds)
        {
              StringBuilder sb = new StringBuilder();
                sb.Append("<?xml version='1.0' standalone='no'?>");
                sb.Append(Environment.NewLine);
                sb.Append("<!DOCTYPE NewDataSet SYSTEM '"+path+"\\StudentDTD1.dtd'>");
                sb.Append(Environment.NewLine);
                sb.Append(ds.GetXml());
              string ab=sb.ToString();
              XmlDocument doc = new XmlDocument();
              doc.LoadXml(ab);
              doc.Save(path + "\\Students.xml");
        }

The above functions are related to the Excel sheet. Now, we will create an XML schema file. In this demonstration, I have created a schema file with the help of DataSet’s WriteXMLSchema function. You can generate in the same way or you can create manually.

Below schema is for validation structure of demonstrational Excel sheet.

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Student">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="RollNo" type="xs:double" minOccurs="0" />
              <xs:element name="StudentName" type="xs:string" minOccurs="0" />
              <xs:element name="DOB" type="xs:dateTime" minOccurs="0" />
              <xs:element name="Class" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

Given below is the DTD for validating the data of Excel sheet.

<!ELEMENT NewDataSet (Student)*>
<!ELEMENT Student (RollNo,StudentName, DOB, Class)>
<!ELEMENT RollNo (#PCDATA)>
<!ELEMENT StudentName (#PCDATA)>
<!ELEMENT DOB (#PCDATA)>
<!ELEMENT Class (#PCDATA)>

Create two more functions with the name ‘ValidateExcelSchema’ and ‘ValidateExcelData’ for writing the codes for validating schema and data of the Excel sheet. I have already written in the upper part of this article for validating the schema and data. Use the above code for the same or see the below code.

private bool ValidateExcelSchema(string schema)
       {

           //XmlSchemaSet schemas;
           schemas = new XmlSchemaSet();
           schemas.Add("http://www.deitel.com/booklist", schema + "\\StudentsSchema.xsd");

           settings = new XmlReaderSettings();
           settings.ProhibitDtd = false;
           settings.CheckCharacters = true;
           settings.ValidationType = ValidationType.DTD;
           settings.Schemas = schemas;
           settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
           Uri uri = new Uri(schema + "\\StudentDTD.dtd");
           //settings.XmlResolver.ResolveUri(uri, null);

           XmlReader reader = XmlReader.Create(schema + "\\Students.xml", settings);
           while (reader.Read()) ;
           if (valid)
           {
               writertbox("Document SCHEMA is valid");
           }  // end if
           valid = true;
           reader.Close();

           return false;
       }
       private void ValidateExcelData(string Path)
       {
           XmlReaderSettings settings = new XmlReaderSettings();

           //settings.ProhibitDtd = false;
           settings.DtdProcessing = DtdProcessing.Parse;
           settings.ValidationType = ValidationType.DTD;

           settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
           XmlReader reader = XmlReader.Create(Path + "\\Students.xml", settings);



           // Parse the file.

           try
           {

               while (reader.Read()) ;

               if (valid)
               {
                   writertbox("Document contains valid data");
               }  // end if
           }
           catch
           {


           }
       }
       private void ValidationCallBack(Object sender, ValidationEventArgs args)
       {
           //Display the validation error.  This is only called on error
           //bool m_Success = false; //Validation failed
           valid = false; // validation failed
           writertbox("Validation error: " + args.Message);
       }

I have attached the Excel sheet which is taken in this demonstration and the above details demonstrated how to create a project in VS for validating the Excel sheet. Now, we will discuss the steps to validate an Excel sheet.

First, we will validate the schema or table structure of Excel, remove or add one table of attached Excel sheet, and browse in your demo application.

You will get an error message regarding the schema like on the below image.

 

For validation of the data set of the number of columns of an Excel sheet according to the schema give alphabet or string values in the cell of the of roll number column and browse the Excel sheet.

 

If now there is an error in the Excel sheet, you will get a positive message like the below image.