Thursday, June 02, 2011

How to read Excel XML file from .NET (C#.NET) application using System.Xml.Linq

How to read Excel XML file from .NET (C#.NET) application using System.Xml.Linq
If client machine does not have Excel installed then you have to use Excel XML file for any read/write operation. You can create XML file from excel (save as "XML Spreadsheet 2003") and read from .NET application using System.Xml.Linq library.

Let say you have some Excel XML template on the server location where MS office not installed and you have to read data from Excel XML template then you can do it by using System.Xml.Linq library, actually this is another way to read Excel file
Below source code will give demo to read data from Excel XML by using XDocument
NameSpace
using System.Xml.Linq;
using System.IO;

Source Code
private void button1_Click(object sender, RoutedEventArgs e)
{
  //for silverlight application File operation not allow from client side
  // so we have to give option to select Excel xml file from client
  // you can use file path if it is fixed file from server location
  
  OpenFileDialog dlg = new OpenFileDialog();
  dlg.Multiselect = false;
  dlg.Filter = "Excel XML Files (*.xml)|*.xml";

  //Open saved excel file into XML format (select Save as
  //Type "XML Spreadsheet 2003")
  bool? userClickedOK = dlg.ShowDialog();
  string xml = string.Empty;

  //If user select correct file then procced
  if (userClickedOK == true)
  {
     FileInfo info = dlg.File;
     Stream s = info.OpenRead();
     StreamReader reader = new StreamReader(s);
     xml = reader.ReadToEnd();
     reader.Close();
 }
 else
 {
    //log error or display message for user to select file
    return;
 }

 // load xml into XDocumemt object
 XDocument doc = XDocument.Parse(xml);

 //Query for Sheet1 from selected Worksheet
 var worksheet = doc.Descendants().Where(x => (x.Name.LocalName == "Worksheet" && x.FirstAttribute.Value == "Sheet1"));
 //Query for Filled rows
 var rows = worksheet.Descendants().Where(x => x.Name.LocalName == "Row");

 int rowCount = 0;

 foreach (var row in rows)
 {
    if (String.IsNullOrEmpty(row.Value))
    {
       continue;
    }
   else
   {
     //I am allowing max 100 rows to read from sheet1
      if (rowCount < 100)
      {
         //validating the header row, I am assuming very
       // First row will be the HEADER
       if (rowCount == 0)
       {
        //Read first row first cell value
       if (string.Compare(row.Descendants().Elements().ElementAt(0).Value,
"Emp Number", StringComparison.CurrentCultureIgnoreCase) != 0
                        && string.Compare(row.Descendants().Elements().ElementAt(1).Value,
"Emp Salary", StringComparison.CurrentCultureIgnoreCase) != 0
                        && string.Compare(row.Descendants().Elements().ElementAt(2).Value,
"Date of Birth", StringComparison.CurrentCultureIgnoreCase) != 0)
 {
   //return error for Invalid excel
   break;
 }
}
else
{
    //validating records
    var cell = row.Descendants().Where(y => y.Name.LocalName == "Cell");

//Here you will get all value from excel,you need to check for Empty or null value
//NOTE: If cell is empty then you will not get cell number here like you do not have any value filled on cell 2 then you will not get cell.ElementAt(2), this will be NULL to overcome this you can fill empty string (blank value) into the cell

  string empNo = cell.ElementAt(0).Value; //Result will be 100, 200
  string empSal = cell.ElementAt(1).Value;//Result will be 2000, 3000
  string empDOB = cell.ElementAt(2).Value;//Result will be 10/10/1987, 1/1/1999, convert into desired date    format
}
 rowCount++;
}
else
{
    //return error for Invalid excel
    break;
}
}
} 
}

Assume you have data into Excel XML like below and Saved as "XML Spreadsheet 2003" format "Book1.xml"

Emp Number                     Emp Salary          Date of Birth
100                                         2000                       10/10/1987
200                                         3000                       1/1/1999

1 comment:

Andrei Marcel Balan said...

Hi there,

Thanks for the method, works great, but how I could get with this method only the Sheets of the Excel file.

Thanks.