Read a Excel File with Sheet wise into DataSet using C#

T.saravanan
Posted by T.saravanan under C# category on | Points: 40 | Views : 33681
Hi All,

Here i am given a Code Snippet for How to read a Excel file data with Sheet wise using C#

Using System.Data.OleDb;  // Namespace

private DataSet ReadExcelData(string sFilePath)
{
string sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + sFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection dbCon = new OleDbConnection(sConnection);
dbCon.Open();

// Get All Sheets Name
DataTable dtSheetName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

// Retrive the Data by Sheetwise
DataSet dsOutput = new DataSet();
for (int nCount = 0; nCount < dtSheetName.Rows.Count; nCount++)
{
string sSheetName = dtSheetName.Rows[nCount]["TABLE_NAME"].ToString();
string sQuery = "Select * From [" + sSheetName + "]";
OleDbCommand dbCmd = new OleDbCommand(sQuery, dbCon);
OleDbDataAdapter dbDa = new OleDbDataAdapter(dbCmd);
DataTable dtData = new DataTable();
dbDa.Fill(dtData);
dsOutput.Tables.Add(dtData);
}
dbCon.Close();
return dsOutput;
}


I hope its helpful.

Cheers :)

Comments or Responses

Posted by: Raja on: 9/16/2010 Level:Starter | Status: [Member] | Points: 10
Hi T.Saravanan,

Thanks for the code snippet, I tried to copy-paste your code and it doesn't even compile when copy-pasting. So I have modified it a bit and pasting it here.


using System.Data;
using System.Data.OleDb;
private DataSet ReadExcelData(string sFilePath)
{

string sConnection="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+sFilePath+";Extended Properties=Excel 8.0;";
OleDbConnection dbCon=new OleDbConnection(sConnection);
dbCon.Open();

// Get All Sheets Name
DataTable dtSheetName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
// Retrive the Data by Sheetwise

DataSet dsOutput = new DataSet();
for(int nCount=0;nCount<dtSheetName .Rows.Count;nCount++)
{
string sSheetName=dtSheetName.Rows[nCount]["TABLE_NAME"].ToString();
string sQuery = "Select * From ["+sSheetName+"]";
OleDbCommand dbCmd=new OleDbCommand(sQuery,dbCon);
OleDbDataAdapter dbDa=new OleDbDataAdapter(dbCmd);
DataTable dtData=new DataTable();
dbDa.Fill(dtData);
dsOutput.Tables.Add(dtData);
}
dbCon.Close();
return dsOutput;
}


Thanks

Login to post response