Sample to read Excel file through C#

Hey,

Today there was a requirement to project a graph from a excel sheet provided by a client. So i wrote a sample code in C# to read the data from the excel sheet. Thought let me post a sample code which will be helpful for readers...

The Class used here is System.Data.OleDb.OleDbConnection

Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class ExcelFromC : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

string strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Fauzi\Excelfile\Sample.xls;Extended Properties=Excel 5.0";

//Creating the OleDb Connection

System.Data.OleDb.OleDbConnection objOleDbConnection = new System.Data.OleDb.OleDbConnection(strConnectionString);

//creating query for Sheet#1

//Here A is the text in A[0][0] ~= A[Row #1][Column #1] So the first row is considered like a Column of a table

string strExcelQuery = "Select A,C from [Sheet1$]";

//strExcelQuery = "Select * from [Sheet1$]";

//create the OleDb Command

System.Data.OleDb.OleDbCommand objOleDbCommand = new System.Data.OleDb.OleDbCommand(strExcelQuery, objOleDbConnection);

objOleDbConnection.Open();

//Read Section:

//-------------

//Create OleDb DataReader

System.Data.OleDb.OleDbDataReader objOleDbDataReader = objOleDbCommand.ExecuteReader();

//Iterating for every records

Response.Write("Reading Data from Excel file");

Response.Write("</br>");

Response.Write(" A - B - C ");

while (objOleDbDataReader.Read())

{

Response.Write("</br>");

string strValue = "";

for (int i = 0; i < objOleDbDataReader.FieldCount; i++)

{

strValue = strValue + " - " + (objOleDbDataReader.GetValue(i)).ToString();

}

Response.Write(strValue);

}

objOleDbConnection.Close();

//Update Section:

//---------------

strExcelQuery = "Update [Sheet1$] set C = C+2"; // from Sheet1";

//Create the command to be executed

objOleDbCommand = new System.Data.OleDb.OleDbCommand(strExcelQuery, objOleDbConnection);

//Open the connection to the file

objOleDbConnection.Open();

//Execute update

objOleDbCommand.ExecuteNonQuery();

//Close connection

objOleDbConnection.Close();

Response.Write("</br>");

Response.Write("</br>");

Response.Write("Reading Data AFTER(C = C+2) Excel file was updated through code");

Response.Write("</br>");

Response.Write(" A - B - C ");

strExcelQuery = "Select A,C from [Sheet1$]";

//strExcelQuery = "Select * from [Sheet1$]";

//create the OleDb Command

objOleDbCommand = new System.Data.OleDb.OleDbCommand(strExcelQuery, objOleDbConnection);

objOleDbConnection.Open();

//Create OleDb DataReader

objOleDbDataReader = objOleDbCommand.ExecuteReader();

//Iterating for every records

while (objOleDbDataReader.Read())

{

Response.Write("</br>");

string strValue = "";

for (int i = 0; i < objOleDbDataReader.FieldCount; i++)

{

strValue = strValue + " - " + (objOleDbDataReader.GetValue(i)).ToString();

}

Response.Write(strValue);

}

objOleDbConnection.Close();

}

}

Excel File: http://www.checkthiz.com/publicfiles/sample.xls

Exception:While reading the excel file the whole sheet is considered as a table & the first row texts are considered to be the column names. So when you give a select (string strExcelQuery = "Select Col from [Sheet1$]";) query in run time the compiler will look for a column 'Col' in the first row and if the particular row is missing, the following exception will be raised.

Syntax error (missing operator) in query expression 'Col'
System.Data.OleDb.Exception -> System.Data.OleDb.OleDbErrorCollection
how to read excel file through C#
Hope it helps :)

Regards
Fauzi