C#: Read and Write Excel (*.xls and *.xlsx) Files Content without Excel Automation using ADO.NET ?

This implementation is regarding how to fetch the data from available excel files using System.Data.Oledb name space. Below are two examples which contains fetching the data from .xls file as well as with .xlsx file.

Fetch the data from an excel .xls file -

Let's create an excel file with few rows and save it as Excel 97-2003 Workbook (*.xls) in your system.

For the reference both the excel files will look like with some sample records-

Fetch the records from excel file using C# .Net, Asp.Net -

Step 1 - Let's create an interface which will contain two buttons for Refresh data from .xls and Refresh data from .xlsx respectively.

Step 2 -  Create a gridview in Asp.Net which will present the data fetched from the excel.

Source code for the reference

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

    <asp:Button ID="btnGetData" runat="server" Text="Refresh xls" OnClick="btnGetData_Click" />

    <asp:Button ID="Button1" runat="server" Text="Refresh xlsx" OnClick="Button1_Click" />

    <br />

    <asp:GridView ID="grdExcel" runat="server">

    </asp:GridView>

</asp:Content>


Step 3 - Required name spaces -

using System.Data.OleDb; //To operate the Oledb operations

using System.Data;       //To have the data objects like data tables/sets

Step 3 - Write the logic under click event for both buttons respectively.

For .xls file type

/// <summary>

/// Click event: To fetch data from the excel file of .xls type

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void btnGetData_Click(object sender, EventArgs e)

{

//Specify the excel provider for .xls file type and the file path which contain the excel file

string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/vsachan/Desktop/Aditya/Sample_Data.xls;Extended Properties='Excel 8.0;HDR=Yes;'";

using (OleDbConnection connection = new OleDbConnection(con))

   {

//Open the Oledb connection

connection.Open();

//Specify the command, assume that the sheet name is Sheet1

OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);

//Define the object of data adaper to run the query

OleDbDataAdapter adap = new OleDbDataAdapter(command);

//Define the dataset to hold the records

DataSet ds=new DataSet();

//Fill the data set

adap.Fill(ds);

//Check the condition if dataset contains any table, It should be at least greater than one

if (ds.Tables.Count >= 1)

      {

grdExcel.DataSource = ds.Tables[0];

grdExcel.DataBind();

      }

   }

}

For .xlsx file type

/// <summary>

/// Click event: To fetch data from the excel file of .xlsx type

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void Button1_Click(object sender, EventArgs e)

{

//Specify the excel provider for .xlsx file type and the file path which contain the excel file

string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/vsachan/Desktop/Aditya/Sample_Data.xlsx;Extended Properties=Excel 12.0 Xml";

using (OleDbConnection connection = new OleDbConnection(con))

   {

//Open the Oledb connection

connection.Open();

//Specify the command, assume that the sheet name is Sheet1

OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);

//Define the object of data adaper to run the query

OleDbDataAdapter adap = new OleDbDataAdapter(command);

//Define the dataset to hold the records

DataSet ds=new DataSet();

//Fill the data set

adap.Fill(ds);

//Check the condition if dataset contains any table, It should be at least greater than one

if (ds.Tables.Count >= 1)

      {

grdExcel.DataSource = ds.Tables[0];

grdExcel.DataBind();

      }

   }

}

Difference in logic - In both the logic only difference is in the connection strings which is highlighted in the above explanation else everything remains same.

At the Interface - Once all the source code writing and logic building have been completed then run the application. and click on the respective buttons. The user screen or web site should be look like the below screen shot -

I hope the above explanation make you comfortable with the data handling on excel file types .xls and .xlsx respectively.

Please share your comments for any further information/help.