Filehelpers ExcelStorage.ExtractRecords fails when first cell is empty

Question

Hello,

When the first cell of an excel sheet to import using ExcelStorage.ExtractRecords is empty, the process fail. Ie. If the data starts at col 1, row 2, if the cell (2,1) has an empty value, the method fails.

Does anybody know how to work-around this? I've tried adding a FieldNullValue attribute to the mapping class with no luck.

Here is a sample project that show the code with problems

Hope somebody can help me or point in some direction.

Thank you!

Answer 1

I haven't really used this, just looked at the library some time ago: can FieldOptional attribute help?

Answer 2

Thanks to the help of Tuzo, I could figure out a way of working this around. I added a method to ExcelStorage class to change the while end condition. Instead of looking at the first cell for empty value, I look at all cells in the current row to be empty. If that's the case, return false to the while. This is the change to the while part of ExtractRecords:

while (!IsEof(cRow, mStartColumn, RecordFieldCount))

instead of

while (CellAsString(cRow, mStartColumn) != String.Empty)

IsEof is a method to check the whole row to be empty:

    private bool IsEof(int row, int startCol, int numberOfCols)
    {
        bool isEmpty = true;
        string cellValue = string.Empty;

        for (int i = startCol; i <= numberOfCols; i++)
        {
            cellValue = CellAsString(row, i);
            if (cellValue != string.Empty)
            {
                isEmpty = false;
                break;
            }
        }

        return isEmpty;
    }

Of course if the user leaves an empty row between two data rows the rows after that one will not be processed, but I think is a good thing to keep working on this.

Thanks