How to import data from Excel sheet to data Table in c#?
18,307
Use the following code:
public static DataTable exceldata(string filePath)
{
DataTable dtexcel = new DataTable();
bool hasHeaders = false;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//Looping Total Sheet of Xl File
/*foreach (DataRow schemaRow in schemaTable.Rows)
{
}*/
//Looping a first Sheet of Xl File
DataRow schemaRow = schemaTable.Rows[0];
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
string query = "SELECT * FROM [" + sheet3 + "]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
dtexcel.Locale = CultureInfo.CurrentCulture;
daexcel.Fill(dtexcel);
}
conn.Close();
return dtexcel;
}
Source: http://www.codeproject.com/Questions/445400/Read-Excel-Sheet-Data-into-DataTable
You may also refer the following question: Importing Excel into a DataTable Quickly if you wish to import faster.
Author by
Thinksright
Updated on July 29, 2022Comments
-
Thinksright almost 2 years
I am using Asp.net with C#. I need to import data from an Excel sheet to a DataTable. The sheet has 100,000 records with four columns: Firstname, LastName, Email,Phone no.
How can I do this?
-
Joel Coehoorn almost 3 yearsThis can work, but it requires MS Office to be installed on the web server and will literal open an instance of excel behind the scenes for every http request where this is used.