Wednesday, April 9, 2014

How to read data from excel (*.xlsx) file using Microsoft.ACE.OLEDB.12.0

In our last post, we have seen how to get column header list from Excel using Open XML SDK 2.5. You can refer below link.

Now, we will see how to read data from excel file Product.xlsx which is shown below and convert it to XML format to send to database.








C#.NET Code
protected void ReadExcel()
{
try
{
string path = Server.MapPath("Product.xlsx");

System.Data.OleDb.OleDbConnection oledbConn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
oledbConn.Open();

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandTimeout = 0;
cmd.Connection = oledbConn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM [Sheet1$]";

System.Data.OleDb.OleDbDataAdapter oleda = new System.Data.OleDb.OleDbDataAdapter();
oleda = new System.Data.OleDb.OleDbDataAdapter(cmd);

System.Data.DataSet ds;
ds = new System.Data.DataSet("ProductList");
oleda.Fill(ds, "Product");

XElement xeProductList = XElement.Parse(ds.GetXml());

Response.Write(xeProductList);
}
catch (Exception ex)
{
throw ex;
}
}

Output XML
<ProductList>
<Product>
<ID>1</ID>
<Name>Asif Ghanchi</Name>
<Department>IT</Department>
<Salary>50000</Salary>
</Product>
<Product>
<ID>2</ID>
<Name>Ghanchi Asif</Name>
<Department>Production Support</Department>
<Salary>60000</Salary>
</Product>
</ProductList>

You can also download sample excel from below location.