Create DataTable from Excel 2007

See more:

C#

MS-Excel

I am converting a Windows Forms application from Windows XP/Office 2003 to Windows 7/Office 2007. We have a method (GetDataTableFromExcel) that returns an Excel DataTable that is used throughout our application.

In the XP/Office 2003 version, any column value (in the Excel file) that does not contain a value is returned as a null value ({}, as depicted in the ItemArray property of the returned excelDataTable).

In the Windows 7/Office 2007 version, any column value that does not contain a value is returned as an empty string ("", as depicted in the ItemArray property of the returned excelDataTable).

We call this method all over the place and then operate on the returned excelDataTable, but our application is throwing errors when it attempts to validate an empty string in an excelDataTable column when a double data-type is expected.

I would like to try and correct this in our Windows7/Office 2007 GetDataTableFromExcel method, so that an empty Excel column value will be returned as a null value, but I am falling short of accomplishing this. I believe that our issue may exist in the "Extended Properties" in our connectionString, but I cannot get it resolved.

Any help is greatly appreciated!


Here is a snippet of code from my Windows XP/Office 2003 implementation:

public static DataTable GetDataTableFromExcel(String filePath)
        {
            String connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + @";Extended Properties=""Excel 8.0;HDR=No;IMEX=1""";
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
            try
            {
                connection.Open();
                DataTable excelSheetsDataTable = null;
                excelSheetsDataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                // TAKE THE FIRST SHEET AS THE SHEET CONTAINING THE REQUIRED DATA.
                String excelSheetName = excelSheetsDataTable.Rows[0]["TABLE_NAME"].ToString();
                OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + excelSheetName + "]", connection);
                dataAdapter.SelectCommand = cmdSelect;
                DataTable excelDataTable = new DataTable();
                dataAdapter.Fill(excelDataTable);foreach (DataColumn excelColumn in excelDataTable.Columns)
                {
                    excelColumn.ColumnName = excelDataTable.Rows[0][excelColumn.ColumnName].ToString().ToUpper();
                    foreach (DataRow excelRow in excelDataTable.Rows)
                    {
                        if (!Convert.IsDBNull(excelRow[excelColumn.ColumnName.ToString()]))
                            excelRow[excelColumn.ColumnName.ToString()] = excelRow[excelColumn.ColumnName.ToString()].ToString().ToUpper().Replace("'", "");
                    }
                }
                excelDataTable.Rows.RemoveAt(0);
                return excelDataTable;

Here is a snippet of code from my Windows 7/Office 2007 implementation:

public static DataTable GetDataTableFromExcel(String filePath)
        {
            String connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1""";
                                                                                                                    
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
            try
            {
                connection.Open();
                DataTable excelSheetsDataTable = null;
                excelSheetsDataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
                // TAKE THE FIRST SHEET AS THE SHEET CONTAINING THE REQUIRED DATA.
                String excelSheetName = excelSheetsDataTable.Rows[0]["TABLE_NAME"].ToString();
                OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + excelSheetName + "]", connection);
                dataAdapter.SelectCommand = cmdSelect;
                DataTable excelDataTable = new DataTable();
                dataAdapter.Fill(excelDataTable);
foreach (DataColumn excelColumn in excelDataTable.Columns)
                {
                    excelColumn.ColumnName = excelDataTable.Rows[0][excelColumn.ColumnName].ToString().ToUpper();
                    foreach (DataRow excelRow in excelDataTable.Rows)
                    {
                        if (!Convert.IsDBNull(excelRow[excelColumn.ColumnName.ToString()]))
                            excelRow[excelColumn.ColumnName.ToString()] = excelRow[excelColumn.ColumnName.ToString()].ToString().ToUpper().Replace("'", "");
                    }
                }
                excelDataTable.Rows.RemoveAt(0);
                return excelDataTable;