Recent

Author Topic: FPSpreadsheet - TWorkSheet.Rows.Count problem  (Read 238 times)

Zoran

  • Hero Member
  • *****
  • Posts: 1456
    • http://wiki.lazarus.freepascal.org/User:Zoran
FPSpreadsheet - TWorkSheet.Rows.Count problem
« on: June 28, 2019, 12:31:13 am »
My application needs to read and process data from xlsx files, which are made according to a specified format (which is out of my control).

The first row is empty, the second row has column names, and data start on the third row.
So, after loading the WorkBook and getting the Worksheet I try to iterate through data:
Code: Pascal  [Select]
  1. for I := 2 to WorkSheet.Rows.Count - 1 do // the data start on the third row.
  2. begin
  3.   // read cells and process data
  4. end
  5.  
However, it fails -- no data are processed.
I found that WorkSheet.Rows.Count returns 1, so the loop is never executed.
However, when I open the file in Excell or Libre Office, I see that there are many more rows.

The attached zip file contains the simple test application and simple Test.xlsx file (saved in the application folder).

Please try the application -- the WorkSheet.Rows.Count returns 1, but reading cells with bigger row indexes gives correct values!

Lazarus 2.0.3-fixes, FPC 3.0.4, FPSpreadSheet trunk rev. 7018, Windows 7 32-bit

wp

  • Hero Member
  • *****
  • Posts: 6003
Re: FPSpreadsheet - TWorkSheet.Rows.Count problem
« Reply #1 on: June 28, 2019, 01:15:21 am »
No, Worksheet.Rows has a bit different meaning here. It does not refer to the total row count of the worksheet, but is a container for the row records. A row has an individual record, when it carries a global format for all cells in that row, or has a non-default height.

In order to get the "true" row count you call "Worksheet.GetLastRowIndex" or "Worksheet.GetlastOccupiedRowIndex"  (and add 1, because index counting begins at 0). The difference between the two is that the latter one counts only up to the last row with cell content, while the former one considers also rows after the last occupied row when some cells only are formatted, but do not contain content.

Code: Pascal  [Select]
  1.       Memo1.Lines.Append('Row count = ' + IntToStr(WSheet.GetLastRowIndex+1));  
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Zoran

  • Hero Member
  • *****
  • Posts: 1456
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: FPSpreadsheet - TWorkSheet.Rows.Count problem
« Reply #2 on: June 28, 2019, 10:05:54 am »
I understand, thank you.