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();
}
}
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
