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.

http://www.sqlscientist.com/2014/04/get-column-header-list-from-excel-using.html

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.

1. Product.xlsx