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