This project has moved and is read-only. For the latest updates, please go here.

Opening an Excel (created with EPPLUS) with OleDB

Aug 11, 2014 at 11:05 AM
I have code from a colleague, this code creates a few excel sheets with Epplus. With my code I would like to add an database extract 10k+/- lines. Because of the large amount of data it takes too long with Epplus, because you need to write each cell. With OleDB it only takes a few seconds. But I can't open a previously created excel by Epplus with OleDB. Even with different connection strings.

This my code works perfect if you separate the two code blocks.
var excelPath = "C:\\test_" + DateTime.Today.ToString("yyyyMMdd_") + DateTime.Now.ToString("hh") + DateTime.Now.Minute.ToString() + ".xlsx";
using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(excelPath)))
{
    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Schedule V");
    worksheet.Cell(1, 1).Value = "test";
    xlPackage.Save();
    xlPackage.Dispose();
}
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0 Xml';";
using (OleDbConnection conn = new OleDbConnection(strCn))
{
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
    cmd.ExecuteNonQuery();
    conn.Close();
}
I tried the following connection strings but they all give the same error:

OleDbException was unhandled, External table is not in the expected
format.

My diffrent connection string I tried:
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0';";
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0 Xml';";
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=YES';";
var strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
What am I doing wrong here?
Oct 8, 2015 at 8:48 AM
Hi,

I have experienced this also, the only solution I could find was to revert to version 3.1. Not ideal really.
Nov 16, 2015 at 1:36 PM
Hi,

I am also facing same problem as mentioned above.

need help
Nov 16, 2015 at 1:40 PM
Hi all,

I am also facing same problem with epplus 4.0. if have solutions plz share.

Thanks
Dec 3, 2015 at 4:20 PM
Hello all,

I had the same problem and I think I have a solution.

It appears the Microsoft.ACE.OLEDB.12.0 OleDb provider does not like an EPPlus 4.0.4 generated Excel package because it's missing an numFmtId attribute in the xf element of xl\styles.xml file.

Bad, as generated by EPPlus 4.0.4:
                  <cellXfs count="1">
                    <xf fontId="0" applyFont="1" applyBorder="0" xfId="0"  />
                  </cellXfs>
Good:
                  <cellXfs count="1">
                    <xf fontId="0" applyFont="1" applyBorder="0" xfId="0" numFmtId="0" />
                  </cellXfs>
To fix this download the EPPLus sources, find the file ExcelXfsXml.cs and change the '>' on line 818 to a '>=':
//            if (_numFmtId > 0)  
            if (_numFmtId >= 0)
            {
                SetXmlNodeString("@numFmtId", _numFmtId.ToString());
                if(doSetXfId) SetXmlNodeString("@applyNumberFormat", "1");
            }
This fixes the OleDb problem but I did not test it extensively, so I can't be sure it doesn't break anything else. I don't think so though.

Hope this helps..
Dec 11, 2015 at 12:23 AM
Thanks for the excellent find, that did the trick - at least for my simple scenario of dumping a csv into an xlsx and reading data back out with LinqToExcel!

I'm a little concerned about the LGPL license and modifying the code, but I'm not publically distributing the resulting software and the binary remains separate so I guess it's all good. If I knew Mercurial or had time to get familiar with it I'd submit a pull request.
Dec 27, 2015 at 9:40 PM
Thanks, we'll add this fix in the next version.

/Mats