I have exported the data from database using HttpContext with formatting of table, tr and td. I want to read the same file and convert into datatable.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='HTML Import;HDR={1};IMEX=1'" />
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'" />
private DataTable GetTableFromExcel()
{
DataTable dt = new DataTable();
try
{
if (exclFileUpload.HasFile)
{
string FileName = Path.GetFileName(exclFileUpload.PostedFile.FileName);
string Extension = Path.GetExtension(exclFileUpload.PostedFile.FileName);
string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
//string NewFileName = string.Format("{0}_{1}", DateTime.Now.ToString().Replace("/", "").Replace(" ", "").Replace(":", ""), FileName);
string FilePath = Path.Combine(string.Format("{0}/{1}", FolderPath, FileName));
exclFileUpload.SaveAs(FilePath);
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, true);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
File.Delete(FilePath);
}
}
catch (Exception ex)
{
}
return dt;
}
When using the second connection string I am getting error "External table is not in the expected format on connection.Open()." But when using the first, I am getting error on reading the sheet name.
Please tell me how to read the sheet or, directly, data from Excel.
Excel files can be exported as html files and or you can EMBED the excel file into a html file in a number of ways. You could create a live excel file using office online Once the excel file is created , go to to the File menu, and choose Share. Embed using generated HTML.
In Excel, select "File" and then "Open...". Navigate to the directory/folder where you saved the data. Change the "Files of type:" to "HTML Documents". Select the file you saved and click "Open".
I think This Third party dll-(ExcellDataReader) may help solve your problem.
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With