Get cell types when reading and parsing excel files

Question

I am trying to read and parse and excel and some unclear things come into play as usual for me.

Here is what i have:

while (true)
{
    comVariantCell1 = cells.item(row, 1).value().variantType();
    comVariantCell2 = cells.item(row, 2).value().variantType();

    //if an empty cell is found, processing will stop and user will get an error message in order to solve the inconsistency.
    if (comVariantCell1 != COMVariantType::VT_EMPTY && comVariantCell2 != COMVariantType::VT_EMPTY)
    {
        //both cells have values, check their types.


        importedLine = conNull();

        progress1.setText(strfmt("Importing row %1", row));

        if (cells.item(row, 1).value().variantType() == COMVariantType::VT_BSTR)
        {
           importedLine += cells.item(row, 1).value().bStr();
        }
        else
        {
            importedLine += cells.item(row, 1).value().double();
        }

        importedLine += cells.item(row, 2).value().double();


        importedLinesCollection += [importedLine]; //conIns(importedLinesCollection, row - 1, (importedLine));
        row++;
    }
    else
    {
        info (strFmt("Empty cell found at line %1 - import will not continue and no records were saved.", row));
        break;
    }
}

Excel format:

Item number     Transfer Qty
a100            50.5
a101            10
a102            25

This worked well to check if the cell type is string: COMVariantType::VT_BSTR

but what should i use to check for a real or integer value ?

I am pretty sure in this case, the quantity will be not contain real values but anyway, it could be useful in the future to make the difference between these two types.

I have to mention that, even if i have an int value and I use cells.item(row, 1).value().int() it won't work. I can't see why.

Why do i want to make the difference? Because if it's forbidden to have real values in the quantity column ( at least in my case ), i want to check that and give the user the opportunity to put a correct value in that place and maybe further investigate why that happened to be there.

Jan B. Kjeldsen Jan B. Kjeldsen · Accepted Answer · 2016-01-08T15:18:27

Take a look on how it is done in \Classes\SysDataExcelCOM\readRow. It is basically using switch to test the type. This is really boring!

Also take a look on ExcelIO, a class I made some years ago. It reads Excel and returns each row as a container. This is a more high-level approach.

As a last resort you could save the Excel as a tab separated file. Then use TextIO to read the content. This will be at least 10 times faster than using Excel!

Get cell types when reading and parsing excel files

1 Answers