none
Oledb reading Excel problem RRS feed

  • Question

  • Hello everyone,

    I have a function to read a Excel file and save all the data into a DataTable at server side.

    and this function is about to read some Excel file simultaneously. the problem comes:

    1, When there is only one Excel file need to read, i can open the Excel file and modify it, and read the new data into the DataTable, no problem.

    2, When two or more Excel file need to read, the first instance Excel will auto load the second Excel file in ReadOnly mode, and the Oledb will read from this ReadOnly file,

    3, I have done a test, before read from a Excel file, i opened a Excel instance without load any Excel into it, and i use the Oledb function to read a Excel file, the blank Excel instance auto load the Excel file about to read by Oledb, in ReadOnly mode...

    I dont understand this behavior.

    This is my codes: (i have tried both Jet and ACE driver, same behavior)

     

    string BddExTest = Server.MapPath("../spreadsheets/NPclient/" + siretId + ".xls");//TextBox1.Text

     

    //Microsoft.ACE.OLEDB.12.0//Excel 12.0 2007

     

    //Microsoft.Jet.OLEDB.4.0//Excel 8.0 2003

     

    string cnnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=NO;IMEX=1;'", BddExTest);

     

    string sql = string.Format("Select * From [{0}]", "Historique$");

     

    OleDbConnection SQLConn = new OleDbConnection(cnnString);

    SQLConn.Open();//This is the moment when Excel instance auto load the file in ReadOnly mode

     

    OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();

     

    OleDbCommand selectCMD = new OleDbCommand(sql, SQLConn);

    SQLAdapter.SelectCommand = selectCMD;

     

    DataTable dt = new DataTable();

    SQLAdapter.Fill(dt);

    SQLConn.Close();

    SQLConn.Dispose();

    Any suggestions?

    Thanks very much for your time.

    Regards,

    Thursday, August 19, 2010 7:51 AM

All replies

  • Maybe my question wasn't clear enough.

    I need to read a file Excel, extract data from it, and when read the file, the file Excel can be open, and i can do modification to it, and read the new data calculated.

    as far as my test goes, the Oledb provider is not working. maybe some other thrid party lib can do it?

    Any suggestion?

    Thanks very much~

    Regards,

     

    Monday, August 23, 2010 1:42 PM
  • Hello,

    Please try to install the components here and see the result,  hope it could help:

    http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

    By the way, if you paste the error messages, that would be more helpfule to diagnose the issue.

    regards

    Tuesday, August 24, 2010 9:33 AM
  • Hello,

    there is no error messages, and the utilisation of oledb have no problem, Oledb driver can read Excel file, but when u read 2 or more Excel files at one time, it will open the second Excel file 2 times and in readonly mode,

    sorry for my English expressions, and looking forward your reponse:

    Regards,

    Tuesday, August 24, 2010 3:47 PM
  • Are you trying to create two connections to a single Excel Workbook? I only see code for opening a single instance of the file.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, August 24, 2010 5:20 PM
  • Hello paul,

    Not exactly, i need read two Excel Workbooks at the same time when they are open, and i need change the value in the workbook at the sametime.

    Wednesday, August 25, 2010 8:32 AM
  • Hello Achilles,

    we are also facing the same problem did u get any solution for this error.

     

    Friday, February 25, 2011 1:01 PM