Incorrect RowCount value

FLX TMS FlexCel for VCL & FMX
1

Hello,


I've a problem with an Excel file where RowCount isn't correctly calculated.
The Excel file does have 205 rows and Flexcel reports 206 rows.

Do you know how to solves this ? Do you need this Excel file.

Regards,

St?phane Wierzbicki
2

Hi,
RowCount is a really core functionality, and I can be reasonably sure that if FlexCel reports 206 rows, then the file has 206 rows. But note that there are many ways in which you can define a "rowcount".

FlexCel rowcount is the number of rows that have anything, not necessarily data. So for example if row 206 is empty, but is painted red, then it would be included. If it has a different numeric format, it will be included (but when you look at the file you might not realize there is anything special in row 206). In short, in xls or xlsx files we store only the rows that have any kind of data, and RowCount reports the maximum row references in the file. As said, this is a very core functionality and it is likely to be ok.

Now, you might want a different kind of rowcount. For example, internally we have a RowCountWithOnlyData which doesn't include formatted rows, only rows with actual data. Or to print, we have a "PrintingRowCount" that calculates the rowcount of rows that need to be printed. So for example a row formatted in red, even if it has no data, is included in printingrowcount, because the red row is visible and will be printed. But a row with a different height or with a different numeric format is not, because the difference with an empty row is not visible when printing.

So it depends on what you need. And while FlexCel has a "ColCountOnlyData", it doesn't expose a public "RowCountOnlyData" If you need the "maximum row with data" instead of the "maximum row with anything" in the file, you can use something like this:


function RowCountOnlyData(const xls:TExcelFile): integer;
begin
  Result := xls.RowCount;
  while (Result > 0) and (xls.ColCountInRow(Result) = 0) do dec(Result);

end;



But as said, this is just one interpretation of what "RowCount" can be. This particular implementation will skip rows with format but no data, but you might also have rows with empty cells (say for example a cell formatted in red or with a different numeric format). If that is the case, you could also check in the code if any cell has data. For example:


function RowCountOnlyData(const xls:TExcelFile): integer;
var
  colIndex: integer;
  XF: integer;
begin
  Result := xls.RowCount;
  while (Result > 0) do
  begin
   for colIndex:= 1 to xls.ColCountInRow(Result) do
   begin
     if not xls.GetCellValueIndexed(Result, colIndex, XF).IsEmpty then exit;
   end;
   dec(Result);
  end;

end;



As said, there are many ways in which you might want to interpret what an empty row is. We can't include all the variations, but normally it is simple to code the one you need.

3

Hi Adrian,


Thank you very much for these explanations. Your RowCountOnlyData exmple fits my needs.
4

Hi,

That's curious, I have the very same problem. Is it possible that opening the file generated by Flexcel, with Excel just to enlarge a column, have added an empty row, btw if Excel had formatted the last line to help add new datas ? 
Aside do you think using RowCountOnlyData is to avoid absolutely ? (my program doesn't work because of this last line, as it expects data to put them in an array of records).
Best regards,
Milos
5

Excel shouldn't add a row on its own, but it depends on what you do. There might be some macro adding or formatting an extra row.


The RowCountOnlyData will just tell you the last cell that is not empty in the file. It might or might not be the last cell you need to read.
6

How about the most obvious meaning of RowCount? I want to know what the row index of the last populated row in the sheet is. Count ALL the blanks, every row whether anything is there or not.

I have a problem because (despite using this library for years) I had assumed the RowCount would take me to the end of the sheet, but if I try

  value := FXls.GetCellValue(FXls.RowCount, 1);

I don't get the value of the last cell, in column A, because the Row parameter is unrelated to RowCount.

Every blank row makes the RowCount less than the row I want to target.

This came to a head today when I discovered that:

  for var i := 10 to FXls.RowCount do
  begin
   {code}
  end;

wasn't looping at all, because, despite there being data on Row 10, RowCount = 8

I really need to get this resolved quick. How do I get the row index of the last row that has data?

7

Hi,
If I understood correctly, RowCount does exactly what you say. It gives you the last row that has something. And again, this is very basic functionality, and you can be 100% sure it works. Basically, RowCount just gives you List<Rows>.Count.

If you are getting a RowCount of 8, then FlexCel read 8 rows, and there is nothing at row 10. There are 2 reasons I can think you might be getting this error:

  1. You are reading the wrong sheet. Maybe you want to read sheet 2, but ActiveSheet is at row 1, which has 8 rows.

  2. Some corrupt files could make FlexCel read only 8 rows even if Excel can read 10. This is very rare, and I haven't seen it in decades, but could be the case. If this is the problem, you can check it by opening and saving the file in Excel and then trying again.

If it isn't one of those, please send me the file that is giving you trouble (either post it here or send it to adrian@tmssoftware.com if it has any confidential data) and we will look at it.

8

Thanks Adrian,

I did find it was reading RowCount of another sheet, and I have solved that now. Most of the reading is done specifying the sheet explicitly, but RowCount was using whatever sheet was active.

9

Good to know! Just for your information, you can use GetRowCount (TXlsFile.GetRowCount Method | FlexCel Studio for VCL and FireMonkey documentation ) if you want to specify the sheet explicitly.

1 Like