Friday, December 18, 2009

How to export a excel file to a dataset

Scenario
We may have to export excel file data to a dataset, to manipulate data and store in sql server.
Code
public static DataSet ExcelToDataSet(string excelFileName,string sheetName)
{
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\""; OleDbConnection excelCon = new OleDbConnection(excelConnectionString);
string query="select * from [" + sheetName + "$]";
OleDbCommand excelCmd = new OleDbCommand(query, excelCon);
OleDbDataAdapter excelAdp = new OleDbDataAdapter(excelCmd);
DataSet ds = new DataSet();
excelAdp.Fill(ds);
excelCon.Close();
return ds;
}

No comments:

Post a Comment