Opening an Excel (created with EPPLUS) with OleDB

Question

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?

i am facing the same problem. Were you able to get around this? — icedek

Pankaj Pankaj · Accepted Answer · 2014-08-11T08:09:41

I guess, it should be like this, rather than hardcoding you should use OleDbConnectionStringBuilder class

OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder();
connectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
connectionStringBuilder.DataSource = excelPath; // This is your Excel File Full Path
connectionStringBuilder.Add("Mode", "Read");

const string extendedProperties = "Excel 12.0;IMEX=1;HDR=YES";
connectionStringBuilder.Add("Extended Properties", extendedProperties);

String connectionString = connectionStringBuilder.ToString();
// Create connection object by using the preceding connection string.
using (var objConn = new OleDbConnection(connectionString))
{
     // Open connection with the database.
      objConn.Open();

   // Do operations with your File here
}

Opening an Excel (created with EPPLUS) with OleDB

2 Answers