1

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

Pratik
  • 11,534
  • 22
  • 69
  • 99
  • See my response on this http://stackoverflow.com/questions/1941083/problems-reading-in-an-excel-file-in-c – Fadrian Sudaman Aug 02 '11 at 14:19
  • @Fadrian : This is what I am using in config : Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0 and also this Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0" Which I guess should be the reason for this Problem ! – Pratik Aug 02 '11 at 14:22
  • My Problem is similar to http://processinginfinity.com/mystery-of-the-missing-cell-value But Still I cannot find a proper solution – Pratik Aug 03 '11 at 10:02

1 Answers1

1

Given all the problems you have I suspect the standard oledb driver just can't read your excel file correctly due to the rows of text prior to the table data.

How about move away and just code it manually using this library http://epplus.codeplex.com/ for reading the xlsx file and create your datatable or db records

Fadrian Sudaman
  • 6,405
  • 21
  • 29
  • Thanks Fadrian, I have also used http://code.google.com/p/excellibrary/ But it gives me out of Memory Exception While loading this Excel into Workbook – Pratik Aug 03 '11 at 16:38
  • Maybe the file contain funny characters that caused loading issue. Try copy and paste as value to a new file and try again – Fadrian Sudaman Aug 04 '11 at 09:41
  • Sorry to bother you again. Firstly Thanks a lot EPPlus Worked for me !! Only thing is that its not supporting MS Office Excel 2003 – Pratik Aug 04 '11 at 14:59
  • You can also look at npoi on codeplex which support xls I think – Fadrian Sudaman Aug 05 '11 at 06:25