Cannot use OLEDB to access files created by ExcelPackage

Topics: Developer Forum
Jan 19, 2007 at 7:20 PM
I created a VERY simple excel file using ExcelPackage:

FileInfo newFile = new FileInfo(@"C:\excel.xlsx");

using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
worksheet.Cell(1, 1).Value = "Product";
worksheet.Cell(4, 1).Value = "Peas";
worksheet.Cell(5, 1).Value = "Total";
worksheet.Cell(1, 2).Value = "Tins Sold";
xlPackage.Save();
}

After creating the file, I attempted to read the file using Microsoft.ACE.OLEDB.12 provider and was informed that it was an invalid format:

string yo = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;data source={0};Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;\"", textBox1.Text);

System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(yo);

cn.Open(); <-- errors on this line "External table is not in the expected format."

I openned the file ExcelPackage created in Excel 2007 and then saved it -- the file size changed from 4kb to 9kb and then the code above was able to open the file correctly.

It seems that a chunk of data is being excluded from being written to the file and it isn't correct enough for OLE DB to be able to read successfully.

I am going to tinker with it today to see if I can solve the problem, but I thought if anybody else encountered the same issue they could post the resolution.

Thank you!

Coordinator
Jan 25, 2007 at 12:11 AM
Yes, I can see why this would occurr. ExcelPackage is doing the minimum to create a file which Excel 2007 will open. So some optional elements are not created in the structure.

For example, when a cell has a formula, ExcelPackage removes the value in order to force Excel into re-calculating the cell. However, the OLEDB reader does not have a calculation engine, so will fail to read the value.

There are no plans to add a calculation engine to ExcelPackage as there is little point - Excel Services does the job for you.