SQL Query against EXCEL Spreedsheet containing formulas

Question

I am writing a component in C# which returns data from an EXCEl spreadsheet using Microsoft.ACE.OLEDB.12.0. The spreadsheet contains cells with formulas and references to other spreadsheets within that workbook. These cells return no data to the DataTable. See example below.

OleDbConnection OleDBconn = new   OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Macro;HDR=Yes;IMEX=1\"",InputFile));
OleDbCommand OleCommand = new OleDbCommand();
OleCommand.Connection = OleDBconn;
OleDBconn.Open();
dtXLS = OleDBconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataTable dt = new DataTable();
OleDbDataAdapter adp = new OleDbDataAdapter(OleCommand);
OleCommand.CommandText = string.Format(@"SELECT [Column] From [Sheet1$]");
adp.SelectCommand = OleCommand;
adp.Fill(dt);

Column contains cells with formulas and references to other worksheets in the workbook. So dt[0][Column] is null when it should have a value. The cell in the spreadsheet contains the below reference

=dd!B2

MethodMan MethodMan · Accepted Answer · 2016-01-25T20:00:51

here is something that you can try in regards to filling and returning the DataTable

//call the method this way
var someDataTable = ExecuteDataSet("SELECT * FROM [Sheet1$]", InputFile);


public static DataTable ExecuteDataSet(string sql, string InputFile)
{
    using (DataSet myDataset = new DataSet())
    using (OleDbConnection OleDBconn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Macro;HDR=Yes;IMEX=1\"",InputFile));
    using (OleDbCommand cmdSelect = new OleDbCommand(sql, OleDBconn))
    {
        try
        {
            OleDBconn.Open();
            dtXLS = OleDBconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//if you need to return this change the method signature to out param for this
            new OleDbDataAdapter(cmdSelect).Fill(myDataset);
        }
        catch (Exception ex)
        {
            //Show a message or log a message on ex.Message
        }
        return myDataset.Tables[0];
    }
}

SQL Query against EXCEL Spreedsheet containing formulas

1 Answers