While looping thru the rows and columns of an excel file to put into a dataset, the FlexCel component appears to be ignoring empty columns in the middle of the file.
This happened in an .xls and .xlsx file. We have a file with 13 columns. Most rows and columns are filled. However, in a file we were importing, we noticed that if column 7 and 8 were blank, on the call to GetCellValueIndexed for column 8 was returning the value for column 9 and the empty column appeared to have been added to the end. On subsequent rows, empty columns aren't even read, it goes from column 6 values to column 9 on the function call.
Downloaded the latest version of 7.20 and still occurring.
Hi,
Yes, this is how GetCellValueIndexed is designed to work. In many cases, you don't care about empty cells, so you can use GetCellValueIndexed, ColFromIndex, etc, to only read the cells that have values and skip the empty cells.
Basically, you have 2 sets of methods to read cells:
All the "Index" methods, which skip over empty cells, and also ColCountInRow. If you have a value in cell A1, and other in E1, then GetCellValueIndexed(1, 1) is the value in cell A1, and GetCellValueIndexed(1,2) is the value in E1. The "colIndex" parameter applies to non-empty cells, and you can convert between a colIndex and a "col" parameter with ColFromIndex and ColToIndex.
All the "normal" methods like "GetCellValue". Here GetCellValue(1,1) is A1, and GetCellValue(1,2) is B1, no matter if B1 is empty.
If you want to loop including empty cells (which in many cases can be what you need), just use GetCellValue and methods that take a "col" parameter, not a "colIndex". If the parameter is a colIndex, then it is not the column (A, B, C...), but the index of the next non-empty cell (A, E in our example where only A and E have values)