locked
Excel Automation using jet provider 4.0 RRS feed

  • Question

  • User-661590535 posted

    hi Friends,

    I am trying to automate excel using "jet provider 4.0". iam facing a strange problem, please find my approach and the problem below.

    1) i have written the following code to read data from the excelsheet. iam passing source file name (srcFileName ) and worksheetname as parameters to this mehtod.

    DataTable dataTable = null;

    OleDbConnection objConn = null;

    OleDbCommand ObjCommand = null;

    OleDbDataAdapter objAdp = null;

    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + srcFileName +

    ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";

    objConn = new OleDbConnection(connectionString);

    objConn.Open();

    ObjCommand =
    new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", objConn);objAdp = new OleDbDataAdapter();

    objAdp.SelectCommand = ObjCommand;

    dataTable =
    new DataTable();

    objAdp.Fill(dataTable);

    objConn.Close();

     i have used IMEX=1 because my excel sheet contains text inbetween rows, so i was unable to read some of the data and one of the forum suggested in using IMEX=1 to read data in string format.

    2) Please find below screenshot of the excel file along with some data. As can bee seen there are values like  6,5 and 6 (1st row) etc., ..but actually  when we see these values in the excel formulae bar the values will be 5.923, 5.432, 6.456 respectively for the first row and similarly for the remaining rows. They have applied some format for the excel cells to display in that manner. When i execute the above code and read excel data into datatable, i sometimes get the values as 6,5,6 and some times i get the same cells as 5.923, 5.432, 6.456. even without changing any code iam getting this behaviour. Actually i need to read the values as 6,5 and 6 respectively.

    Do i need to change anything in my code or do i need to completely remove my code and use interop dlls to read the data in our own format. But my primary concern is i need to read arount 30 excel worksheets with data in the range 0f 100 rows and 10 columns. jet provider is lot quicker compared to using interop.

    6 5 6
    16 15 12
    22 20 18
    86% 78% 70%
         
    1 0 1
    0 1 0
    24 10 27
    25 28 33
    25 26 21

     

    Thanks in advance,

    Bhargav

    Monday, December 8, 2008 1:21 PM

All replies

  • User1350100793 posted

    hi i have just finished some coding like this only, please keep me in the loop if any of you can achieve this.

    Tuesday, December 9, 2008 5:10 AM