Convert EXCEL to DataTable in the asp.net c#
Step 1:- Get the
absolute path for the Excel sheet .I used “Sample.xlsx”
string data = MapPath("sample.xlsx").ToString();
Step2:- Create the
connection string for the OLEDB connection
String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data
Source=" + data + ";"
+
"Extended
Properties=Excel 12.0 Xml";
Step3:- Get the list
of sheets names in the excel sheet in to list
List<string> listSheet = new
List<string>();
using (OleDbConnection conn = new
OleDbConnection(strConn.ToString()))
{
conn.Open();
System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow drSheet in
dtSheet.Rows)
{
if
(drSheet["TABLE_NAME"].ToString().Contains("$"))
{
listSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
}
Step4:- By using that
sheet name get the table in the sheets and passed to the data table.
OleDbDataAdapter da = new OleDbDataAdapter
("SELECT
* FROM [" + listSheet[0]+"]",
strConn);
da.Fill(ds);
The Complete Code is here
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
using
Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;
namespace SampleTestingCodes
{
public partial
class ExcelToDataTable
: System.Web.UI.Page
{
protected
void Page_Load(object
sender, EventArgs e)
{
string
data = MapPath("sample.xlsx").ToString();
String
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
+
"Data
Source=" + data + ";"
+
"Extended
Properties=Excel 12.0 Xml";
DataSet
ds = new DataSet();
List<string> listSheet = new
List<string>();
using
(OleDbConnection conn = new OleDbConnection(strConn.ToString()))
{
conn.Open();
System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach
(DataRow drSheet in
dtSheet.Rows)
{
if
(drSheet["TABLE_NAME"].ToString().Contains("$"))
{
listSheet.Add(drSheet["TABLE_NAME"].ToString());
}
}
}
OleDbDataAdapter
da = new OleDbDataAdapter
("SELECT
* FROM [" + listSheet[0]+"]",
strConn);
da.Fill(ds);
}
}
}
Thanks & Regards
LOKESH