I am trying to get data from Excel File to DataTable.
Here's my code-snippet :
FilePath = WebConfig.SavePath + "Book2.xls";
// Create the connection object
OleDbConnection oledbConn = new OleDbConnection(WebConfig.ExcelConnection(FilePath));
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1 //WebConfig.SheetNameFirstExcel
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + "Sheet1" + "$]", oledbConn);
// Create new OleDbDataAdapter
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.
DataTable dt = new DataTable();
// Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(dt);
Problem with this is that, data of some cells is exported to data-table while some other is NOT.
format of excel is something like :
1st Row Heading
2nd Some text
3rd Row blank
4th onwards a table
of 10 columns & 298 rows.
What is missing in above code, or any suggestion for extracting such excel(.xlsx) to datatable in asp.net 3.5
