Excel to DataTable in C#:
Here is the way to Convert EXCEL Table Data in to Our C# Data Table in button Click:
protected btnsubmit_Click(object sender, EventArgs e)
{
try
{
OleDbConnection oledbConn;
string fpath = Server.MapPath("~/Excelfiles/");
if (filexcel.HasFile)
{
if (Path.GetExtension(filexcel.PostedFile.FileName) == ".xlsx")
{
//Through File Upload
//filexcel.PostedFile.SaveAs(string.Concat(fpath, filexcel.PostedFile.FileName));
//string ftpath = System.IO.Path.GetFullPath(Server.MapPath("~/Excelfiles/New Microsoft Office Excel Worksheet (2)"));
//From web.COnfig
string ftpath = ConfigurationManager.AppSettings["ftpath"].ToString(); //give your excel path here directly
//Or Directly You can assign the Path
var ft = @"D:\manu\New Microsoft Office Excel Worksheet (2).xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ft + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
var adapter = new OleDbDataAdapter(cmd);
var ds = new DataSet();
adapter.Fill(ds);
}
}
}
}
}
catch (Exception ex) { throw ex; }
}
Here is the way to Convert EXCEL Table Data in to Our C# Data Table in button Click:
protected btnsubmit_Click(object sender, EventArgs e)
{
try
{
OleDbConnection oledbConn;
string fpath = Server.MapPath("~/Excelfiles/");
if (filexcel.HasFile)
{
if (Path.GetExtension(filexcel.PostedFile.FileName) == ".xlsx")
{
//Through File Upload
//filexcel.PostedFile.SaveAs(string.Concat(fpath, filexcel.PostedFile.FileName));
//string ftpath = System.IO.Path.GetFullPath(Server.MapPath("~/Excelfiles/New Microsoft Office Excel Worksheet (2)"));
//From web.COnfig
string ftpath = ConfigurationManager.AppSettings["ftpath"].ToString(); //give your excel path here directly
//Or Directly You can assign the Path
var ft = @"D:\manu\New Microsoft Office Excel Worksheet (2).xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ft + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
var adapter = new OleDbDataAdapter(cmd);
var ds = new DataSet();
adapter.Fill(ds);
}
}
}
}
}
catch (Exception ex) { throw ex; }
}
No comments:
Post a Comment