kurgaan kurgaan - 1 year ago 71
C# Question

Read Excel using OLEDB, some cell incorrectly read

I'm trying to read an excel file. Some of the cells contain formulas and are read as "#REF!". I just want the cell values for number crunching, I don't care about the formulas. Is there a way to handle formulas? Can I do this in OLEDB or should I switch to interop? Thanks in advance.

var connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""", fileName);
string firstSheetName;
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
DataTable dt = con.GetSchema("Tables");
firstSheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}

var adapter = new OleDbDataAdapter("SELECT * FROM [" + firstSheetName + "]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "anyNameHere");
var data = ds.Tables["anyNameHere"];
string cellValue = (string)data.Rows[i].ItemArray[4];


I tried adding "TypeGuessRows=0;ImportMixedTypes=Text" to the connectionstring.

var connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text""", fileName);


and that did not help.

Answer Source

Try This. Instead of using Get schema, use getoledbschema.

        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName=GettingSheetName(dtExcelSchema);
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download