none
how to Read excel from DOt net COde RRS feed

  • Question

  •  

    hi .

     

    i am using windows server 2003 with 64 bit.

     

    my task is to upload a excel sheet and reading data by dot net and send it to SQL server as per application.

    and i used the code as.

     

    "Provider=Microsoft.Jet.OLEDB.4.0;" +

    "Data Source= ".xls;" +

    "Extended Properties=Excel 12.0; HDR=Yes;IMEX=1";

    OleDbConnection conn = new OleDbConnection();

    conn.ConnectionString = strConn;

    try

    (

    conn.Open();

    }

    catch (Exception Ex)

    {

    // here error Comes

    }

     

     

    The error comes "'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine" on site level

    but on preview level there is no error.

     if i have not registered 'Microsoft.Jet.OLEDB.4.0' then how can it run for local server as preview.?

     

    please tell me is there any other method to read excel data from .net. rather then OLEDB ??

     

    Thanks

     

    Ankit  jain

     

    Tuesday, November 11, 2008 6:15 AM

Answers

  • Thanks Val Mazur

     

     

     i will try your code but currently i work c# .net and i wrote code .

     

    Excel.Application ExcelObj = new Excel.Application();

     

    if (ExcelObj == null)

    {

    errMsg = "Excel object not found";

    }

    string filead =  "any.xls";

    Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filead, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",

    true, false, 0, true, false, false);

     

    Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

    string[] strArray;

    string cntexcel = worksheet.UsedRange.Rows.Count.ToString(); // .Rows.Count.ToString();

    for (int h = 2; h <= cnyexcelint; h++)

    {

    Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + h.ToString(), "N" + h.ToString());

    System.Array myvalues = (System.Array)range1.Cells.Value2;

    strArray = ConvertToStringArray(myvalues);

    }

     

    and this work fine with 64 bit site.

    Tuesday, November 11, 2008 1:42 PM

All replies

  • This may help you.

     

    Code Snippet

    Imports Microsoft.Office.Interop

     

    Dim app As New Microsoft.Office.Interop.Excel.Application

    Dim ebook As Microsoft.Office.Interop.Excel.Workbook

    Dim esheet As Microsoft.Office.Interop.Excel.Worksheet

     

     

    These codes may help you read a excel file and also manipulate everything in it.

     

    The variable "app" has the property that you may need which are "Workbooks.open" ro read and ".add" to create.

     

    These three are link to each other to fully navigate in excel via .NET which is like this.

     

    Write

    Code Snippet

    ebook = app.Workbooks.Add ' Create the excel file

    esheet = exbook.Sheets(1) ' Read Sheet1 of the created excel file

     

    'ends should be

    ebook.SaveAs(FileName, FileFormat:=-4143)'FileFormat:=-4143 is to set it at .xls format

    ebook.Close()

    app.Quit()

     

     

    to read just replace the "app.Workbooks.Add" with "app.Workbooks.Open" and remove the SaveAs part since your only reading.

    Tuesday, November 11, 2008 7:19 AM
  • Please read my reply on your other post about 64 bit installation. If Jet is not working for you, you could try .NET managed reader for Excel from my web site www.xporttools.net. It is 100% .NET.

     

    Tuesday, November 11, 2008 11:25 AM
    Moderator
  • Thanks Val Mazur

     

     

     i will try your code but currently i work c# .net and i wrote code .

     

    Excel.Application ExcelObj = new Excel.Application();

     

    if (ExcelObj == null)

    {

    errMsg = "Excel object not found";

    }

    string filead =  "any.xls";

    Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filead, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",

    true, false, 0, true, false, false);

     

    Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

    string[] strArray;

    string cntexcel = worksheet.UsedRange.Rows.Count.ToString(); // .Rows.Count.ToString();

    for (int h = 2; h <= cnyexcelint; h++)

    {

    Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + h.ToString(), "N" + h.ToString());

    System.Array myvalues = (System.Array)range1.Cells.Value2;

    strArray = ConvertToStringArray(myvalues);

    }

     

    and this work fine with 64 bit site.

    Tuesday, November 11, 2008 1:42 PM
  • This is very good alternative 
    Monday, January 4, 2010 1:33 PM