如何使用C＃和OLEDBConnection读取.xlsx和.xls文件

Question

以前我使用 ExcelPackage从.xlsx文件中读取数据。这很好，但是后来我意识到ExcelPackage与旧的.xls格式无法使用。因此，我升级到使用OleDbConnection而不是ExcelPackage：

var file = httpcontext.current.request.files [0];DataTable SheetData = new Datatable((;字符串connstr =" provider = microsoft.jet.oledb.4.0; data source ="      file.filename  "; Jet OLEDB：引擎类型= 5;扩展属性= " Excel 8.0; ";使用(OLEDBCONNECTION CONN = NEW OLEDBCONNECTION(CONNSTR(({    conn.open((;    DataTable dtschema = conn.getOledBschematable(oledbschemaguid.tables，new Object [] {null，null，null，null，" table"}(;    字符串表名称= dtschema.Rows [0] .field(" table_name"(;    OLEDBDATAADAPTER SHEEPADAPTER =新的OledBdataAdapter(" select * from [   sheetName  "]"，conn(;    SheetAdapter.fill(SheetData(;} 

基本上只是试图阅读第一个电子表格。但是我在例外情况下遇到了这个错误：

Cannot update. Database or object is read-only.

我在做什么错？是否隐藏了某种类型的更新操作？

Answer 1

尝试以下：

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();
        }

Answer 2

以下是一种示例方法，可从给定的Excel文件路径返回DataSet。返回的DataSet应该在工作簿中的每个Excel工作表作为DataSet中的DataTable。这似乎可以正常工作，希望它可能会有所帮助。

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;
}