Merged cells cause problems when extracting the data

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Open and try to get data out of an excel document that contains merged cells
2.
3.

What is the expected output? What do you see instead?

DataTable with missing columns, sometimes a datatable with nothing in it is
returned; if the excel document contains merged cells.

What version of the product are you using? On what operating system?
Latest build from this site.

Please provide any additional information below.
Row / column enumeration must have some issue with merged cells.

Original issue reported on code.google.com by ihsan...@gmail.com on 26 Nov 2009 at 2:36

[GoogleCodeExporter]

The only solution I could come up with is opening the Excel document prior to
everything and clearing the "cellformats". But I have to rely on Excel interop 
to do
this. It is not "messy" per se, but it is slow, prone to crashes and 
bothersome. It
has a lot of COM baggage attached to it and when dealing with hundreds of 
files, it
will be a performance killer by all aspects.

If somebody can implement the equivalent of 
ExcelWorkSheet.Columns.ClearFormats()
then this "merged cells" issue will be solved.

Until that time, and if you have Office 2007 you can use the below function to
clearup the formatting, then you can use the ExcelLibrary to extract data from 
the
worksheet.

Cheers

private void clearExcelCellFormat(string fullPath)
{
    Excel.Application excelApp = new Excel.Application();
    excelApp.Visible = false;
    try
    {
        Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(fullPath,
                0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);

        Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[1];
        excelWorkSheet.Columns.ClearFormats();

        excelWorkBook.Close(true, fullPath + "_icy", false);

        System.IO.FileInfo fi = new FileInfo(fullPath);
        fi.Delete();
        fi = new FileInfo(fullPath + "_icy");
        fi.MoveTo(fullPath);

        fi = null;

        excelApp.Quit();
    }
    catch (Exception ex)
    { }
    finally
    {
        // Make sure we release the reference to the underlying COM object
        Marshal.ReleaseComObject(excelApp);
        excelApp = null;

        //final check for COM created Excel application cleanup
        System.Diagnostics.Process[] PROC =
System.Diagnostics.Process.GetProcessesByName("EXCEL");
        foreach (System.Diagnostics.Process PK in PROC)
        {
            if (PK.MainWindowTitle.Length == 0)
                PK.Kill();
        }
    }
}

Original comment by ihsan...@gmail.com on 26 Nov 2009 at 3:23