none
Read Excel Spreadsheet using ADO.NET and DataSet RRS feed

  • Question

  •  I Read Excel Spreadsheet using ADO.NET and Dataset. I tested program on Window server 2000 and works fine. When I deployed to Server 2003 R2 x64 platform I founded that Microsoft.Jet.OLEDB.4.0 does not work in x64 platform. I deployed the program to Windows 2000 server and I try to read   Excel from share folder on the Window 2003 but I got error:

    “The Microsoft Jet database engine cannot open the file <file name>. It is already open exclusively by another user or you need permission to view its data.”  I read excel file from window service program. There is the permissions issue, but I do not know how to solve. I assigned to excel file permission to everybody with full access but this still did not help.

     

    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;    Data Source=//apiaddress/sharefolder/Book1.xls;Extended Properties=    ""Excel 8.0;HDR=YES;""";

    Sunday, June 8, 2008 3:34 AM

All replies

  • Are you sure that you've set both the "Permissions" under "Share" and "Security"? You need to grant both full access for the file.

     

    Sunday, June 8, 2008 8:54 AM
  • Hi,

    if you need fully managed (doesn't use Excel Interop) library, I recommend this Excel C# / VB.NET library.

    Here is an Excel C# code how to import Excel to DataSet:

    var ef = new ExcelFile();
    ef.LoadXls("DataSet.xls");
    
    var dataSet = new DataSet();
    
    foreach (ExcelWorksheet ws in ef.Worksheets)
    {
      var dataTable = dataSet.Tables.Add(ws.Name);
    
      // Create columns from first row cells.
      dataTable.Columns.AddRange(ws.Rows[0].AllocatedCells.Cast<ExcelCell>().Select(cell => new DataColumn((string)cell.Value)).ToArray());
    
      // If excel cell value is not string, call ToString() on it.
      ws.ExtractDataEvent += (sender, e) =>
      {
        if (e.ErrorID == ExtractDataError.WrongType)
        {
          e.DataTableValue = e.ExcelValue != null ? e.ExcelValue.ToString() : string.Empty;
          e.Action = ExtractDataEventAction.Continue;
        }
      };
    
      ws.ExtractToDataTable(dataTable, ws.GetUsedCellRange().Height, ExtractDataOptions.None, ws.Rows[1], ws.Columns[0]);
    }
    

    Tuesday, May 10, 2011 8:38 AM
  • using System.Windows.Forms.DataVisualization.Charting;
    using System.Data;
    using System.Data.OleDb;
    ...

    // The Excel file name
    string fileNameString = "data\\ExcelData.xls";

    // Create connection object by using the preceding connection string.
    string sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        fileNameString + ";Extended Properties=\"Excel 8.0;HDR=YES\"";
    OleDbConnection myConnection = new OleDbConnection( sConn );
    myConnection.Open();

    // The code to follow uses a SQL SELECT command to display the data from the worksheet.
    // Create new OleDbCommand to return data from worksheet.
    oleAdapter = new OleDbDataAdapter("Select * From [data1$A1:E25]", myConnection );

    CommandBuilder =

    new OleDbCommandBuilder(GraviDataAdapter);

    Set =

    new DataSet("GraviDataSet");

    oleAdapter.Fill(Set);

    // Populate the chart with data in the file
    dataGridView1.DataSource = Set;

    // close the reader and the connection

    myConnection.Close();

    Regards,

    Narendran Ponpandiyan

    Wednesday, May 11, 2011 7:19 AM