none
Problems updating Excel 2007 Worksheet with Microsoft.ACE.OleDB.12 provider RRS feed

  • Question

  • Modified an existing function, previously working fine with Excel 97/2003 and Microsoft.Jet.OLEDB.4.0 provider, to work with Excel 2007 and the Microsoft.ACE.OLEDB.12.0 provider.

     

    The function is used to import information from Excel and then it uses the information to create orders in an ERP-system. The excel files do not contains any headers and the extended property HDR is set to NO. If the import is successful one of the columns for the row is updated from 0 to 1.

     

    Nothing else changed except for the provider, the only row that is changed now is row number one in the worksheet regardless of the update query used in the code below (marked as red). Anyone who knows why? Bug?

     

     

    The code (simplified):

     

    OleDbConnectionStringBuilder csExcel = new OleDbConnectionStringBuilder();

    csExcel.DataSource = "c:\\file.xlsx";

    csExcel.Provider = "Microsoft.ACE.OLEDB.12.0";

    csExcel.Add("Extended Properties", "Excel 12.0;HDR=NO");

     

    OleDbConnection excelConn = new OleDbConnection(csExcel.ToString());

     

    excelConn.Open();

     

    OleDbCommand readExcelCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", excelConn);

    OleDbCommand setInvoiceStatus = excelConn.CreateCommand();

     

    using (OleDbDataReader drExcel = readExcelCommand.ExecuteReader())

    {

    int row = 0;

    while (drExcel.Read())

    {

    row++;

    if (Int32.Parse(drExcel[0].ToString()) == 0)

    {

    OleDbTransaction setValue = excelConn.BeginTransaction();

    setInvoiceStatus.CommandText = "UPDATE [Sheet1$A" + row + ":A" + row + "] SET F1=1";

    setInvoiceStatus.Transaction = setValue;

    int rowsUpdated = setInvoiceStatus.ExecuteNonQuery();

    setValue.Commit();

    }

    }

    }

     

     

     

     

     

     

     

     

     

     

    Tuesday, April 29, 2008 11:41 AM