How to read .xlsx and .xls files using C# and OleDbConnection?

2
votes

Previously I was using ExcelPackage to read data out of a .xlsx file. That was working fine but then I realized that ExcelPackage doesn't work with the old .xls format. So I upgraded to using OleDbConnection instead of ExcelPackage like this:

var file = HttpContext.Current.Request.Files[0];
DataTable sheetData = new DataTable();
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
    file.FileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"";

using (OleDbConnection conn = new OleDbConnection(connStr))
{
    conn.Open();
    DataTable dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    string sheetName = dtSchema.Rows[0].Field("TABLE_NAME");
    OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
    sheetAdapter.Fill(sheetData);
}

Basically just trying to read the first spreadsheet there. But I get this error in the exception:

Cannot update. Database or object is read-only.

What am I doing wrong? Is there some type of update operation hidden in there?

2
c#exceloledbconnection
Try to close the excel file you are reading in C# – Christian Mark
Do you have Excel open, and by chance this particular file? – Mad Myche
I don't have the file open in excel. Christian, what do you mean close the file? Samething as mad? – Ryan
Where is the excel file located? Is it local? I get error with the posted code, however, There must be something else going on. The error Cannot update seems like an odd error if you are simply trying to READ the file. – JohnG

2 Answers

2
votes

Try this:

OleDbConnection connection;
OleDbCommand command;
OleDbDataReader dr;

        string commandText = "SELECT * FROM [Sheet1$]";
        string oledbConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data Source=" + filename + ";" +
        "Extended Properties=\"Excel 12.0;HDR=YES\";";
        connection = new OleDbConnection(oledbConnectString);
        command = new OleDbCommand(commandText, connection);

        try
        {
            connection.Open();
            dr = command.ExecuteReader();

            while (dr.Read())
            {
                count++;

                for (int i = 1; i < dr.VisibleFieldCount; i++)
                {
                   Console.Writeln(""+dr[i].ToString());
                }
            }

            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("" + ex.Message);
            connection.Close();
        }
0
votes

Below is an example method that returns a DataSet from the given excel file path. The returned DataSet should have each Excel worksheet in the workbook as a DataTable in the DataSet. This appears to work correctly, hope it may help.

private DataSet GetExcelDataSet(string path) {
  string sheetName;
  string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +
                        "; Jet OLEDB:Engine Type = 5; Extended Properties =\"Excel 8.0;\"";
  DataSet ds = new DataSet();
  using (OleDbConnection con = new OleDbConnection(ConnectionString)) {
    using (OleDbCommand cmd = new OleDbCommand()) {
      using (OleDbDataAdapter oda = new OleDbDataAdapter()) {
        cmd.Connection = con;
        con.Open();
        DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        for (int i = 0; i < dtExcelSchema.Rows.Count; i++) {
          sheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
          DataTable dt = new DataTable(sheetName);
          cmd.Connection = con;
          cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
          oda.SelectCommand = cmd;
          oda.Fill(dt);
          dt.TableName = sheetName;
          ds.Tables.Add(dt);
        }
      }
    }
  }
  return ds;
}