Worksheet.GetLastRowIndex lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- straetch · 2017-01-16 13:37 I recently installed the latest version of fpspreadsheet. When opening a .ods file, it seems that GetLastRowIndex is 1 higher than with older version. I ran a backup version of my program and compared that with the fresh compile. Unfortunately, I cannot exactly define which version of fpspreadsheet was involved in the older program. The discrepancy seems consistent with .ods files. It happens randomly with .xls files (I suppose dependent on which version of Excel). ---------------------------------------------------------------------- wp · 2017-01-16 14:24 If you find one of these Excel files I would appreciate if you could upload it here. But I guess that these files contain formatted empty cells or rows with specific format or row height past the last data cell. Suppose that the last cell containing data is E10. From this, GetLastRowIndex would report the value 9 (0-based). But if you had changed the height of the next row this row would be counted when calculating the last row index even if it does not contain any visual data. Similarly, if cell E11 once had contained some text with special formatting such as bold text, but you had deleted the cell contents, the cell, although empty now, still exists in the internal table because its formatting is retained, and GetLastRowIndex would report 10 even if the corresponding row looks empty. ---------------------------------------------------------------------- straetch · 2017-01-16 16:22 To clarify: I use exactly the same file with older and new version of fpspreadsheet, with different results for GetLastRowIndex. GetLastRowIndex is taken immediately after opening the file. No grid. No content nor format changes. Attached .ods file. The new version of fpspreadsheet yields GetLastRowIndex = 3480. The older version of fpspreadsheet yields GetLastRowIndex = 3479. Visual inspection indicates the older version as correct (3479 in this case). ---------------------------------------------------------------------- wp · 2017-01-16 17:01 I see. This is probably a consequence of the changes that I did some time ago with reading of ods files. ods files are a pain if Calc has created them from Excel files! Look at this excerpt from the end of the table:table node of the contents.xml of your file: After the last data row (I left the last cell with value "A 10,1,1" in the excerpt), there is a empty row containing an empty cell repeated 14 times within the row. And this essentially empty row is repeated more than 1 million times ("table:number-rows-repeated") until the end of the max row specification of ods. fpspreadsheet must not create cells for these filler rows because this would eat up all memory. It took me some effort trying to distinguish these filler rows and columns from blank rows and colums intentionally put after the data range, and it looks that my  way to resolve this left two empty rows after the data rows (that's what I see with spready). To be honest - I don't want to touch this again, it was a pain to get to the current solution... Are the additional rows harmful to you? If I remember correctly they only occur if an Excel file has been read by LibreOffice Calc; original Calc files don't have them. I wonder why LibreOffice does not respect the column and row count available in Excel files to dimension its table accordingly. Code: XML  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i            A 10,1,1                        ---------------------------------------------------------------------- straetch · 2017-01-17 08:23 Thanks for your investigation. I agree, there is a limit to what one can reasonably do, certainly when it involves correcting the mistakes of others - LO in this case. My program does a systematic scan of all rows, so it has to know where the table ends. Does GetLastRowIndex(true) give a better result, idem GetLastOccupiedRowIndex? Fortunately the program can find out the end of the table itself, because it knows what content to expect (e.g. in this case the leftmost cell should contain a 6 digit number - but there may be empty rows). Can I be sure that GetLastRowIndex is always >= the "real" number of rows? If so, I can count backwards until I get a 6 digit number in the first cell. I would be useful  to have a SetLastRowIndex (and SetLastColIndex), so the program can itself define the table size. This would also be useful when saving the spreadsheet. Then only the valid cells would be saved and not the flood of empty cells. I don't know whether this is still compatible with Excel or LO. ---------------------------------------------------------------------- wp · 2017-01-17 09:26 What is the "real" last row?" In terms of fpspreadsheet, there may also be cells which have a cell record without visual content (ContentType = cctEmpty), and there may be also row and column records with special information for the entire row/column even if they do not contain any cell record.. I suppose you mean the row in which you see any cell content. GetLastOccupiedRowIndex returns the index of the row containing the last cell. It may be a cell with ContentType=cctEmpty, though. GetLastRowIndex additionally looks for the row records. If there is a row record with a higher index it will be returned instead. So, none of them will serve your needs. If it's only the remainder of the dummy cells added by LibreOffice at the end you can easily remove them by this code: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure RemoveEmptyCellsAtEnd(AWorksheet: TsWorksheet);var  cell: PCell;begin  cell := AWorksheet.Cells.GetLastCell;  while (cell <> nil) and (cell^.ContentType = cctEmpty) do begin    AWorksheet.DeleteCell(cell);    cell := AWorksheet.Cells.GetLastCell;  end;end; Call this after loading the workbook. Afterwards the method GetLastOccupiedRowIndex will return the index of the row with the last non-empty cell. Fixing the last column index is a bit more complicated because cells are ordered by rows and the longest row can be anywhere. So you must loop through this pseudocode (I don't want to take the time to write this in Pascal because your main interest is in the row count): - Find index of longest row - Check if last cell in this row has ContentType cctEmpty - If it has, delete it. - Repeat until the longest row does not have an empty cell at its end any more. I also don't want to include a SetlastRowIndex and SetLastColIndex method for the general user because it may result in data loss (if there are any important cells beyond these limits). But you can write these procedures easily by yourself, something like this Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure SetLastRowIndex(AWorksheet: TsWorksheet; AIndex: Integer);var  r: Integer;begin  r := AWorksheet.GetLastRowIndex(true);  while AIndex < r do begin    AWorksheet.DeleteRow(r);    dec(r);  end;end; procedure SetlastColIndex(AWorksheet: TsWorksheet; AIndex: Integer);var  c: Integer;begin  c := AWorksheet.GetLastColIndex(true);  while AIndex < c do begin    AWorksheet.DeleteCol(c);    dec(c);  end;end; Please note again that this code is dangerous. If your assumption of the worksheet size is wrong you may chop off valid cells. And if the removed cells are referenced by formulas somewhere else these formulas will not work any more. So, please be careful.