1

I have done a little program to parser excel. It works fine only when before to execute it I open Excel file manually (is not it strange?). I.e. first I open excel file, second I execute program and I get good results

enter image description here

If I don't open excel before to execute it I get empty values

enter image description here

My connection string (excel file has extension .XLSX):

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                   "Data Source=" + path + "\\" + f.Name + ";" +
                   "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";

My code to open connection with oleDB:

using (OleDbConnection cnn = new OleDbConnection(connectionString))
{
    cnn.Open();
    ...
    String sql = "SELECT * FROM [" + sheetNames[i] + "]";
    OleDbDataAdapter da = new OleDbDataAdapter(sql, cnn);
    DataTable dt = new DataTable();
    da.Fill(dt); // Now 'dt' should has all data
}

Also, I have installed AccessDatabaseEngine.exe and AccessRuntime.exe

Obviously, my purpose is run the program without having to manually open the file. Any suggestion?

Thanks for your time.

pnuts
  • 58,317
  • 11
  • 87
  • 139
robBerto
  • 196
  • 2
  • 14
  • Same problem as this : http://stackoverflow.com/questions/5700863/c-sharp-read-open-excel-file-through-oledb – PaulF Oct 09 '15 at 08:09
  • I have not found a solution there. – robBerto Oct 09 '15 at 08:21
  • What *else* are you doing? What kind of file are you trying to open? Is it a *real* excel file or a CSV/HTML file with an Excel extension? What does it contain? If your code works the second time, it's because Excel modified the original file. – Panagiotis Kanavos Oct 09 '15 at 08:40
  • @ Panagiotis Kanavos Is it real excel that contains 14 colums with interger and string values. The order does not matter, the program works fine when previously I open manually excel file (First image). – robBerto Oct 09 '15 at 08:55
  • @robBerto: just pointing out there is no easy solution with oleDB which wants exclusive use of the file. – PaulF Oct 09 '15 at 11:27

1 Answers1

2

I found it a real pain when I tried to get OleDb and Excel to play nicely together. Fortunately, I found a much better approach: EPPlus

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

Open source, feature rich and easy to use. If at all possible, use it instead of OleDb.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
  • The suggestion is appreciated, but I'm so close to solving not want to leave this approach. – robBerto Oct 09 '15 at 08:24
  • Finally I have followed your suggestion and I am very satisfied. In half an hour my solution was ready. – robBerto Oct 09 '15 at 10:38
  • I don't normally like to suggest a different solution, but in this case I went though much pain with OleDb and Excel and know that EPPlus is a much better path. Glad it worked for you, – Eric J. Oct 09 '15 at 16:02