Clean empty cells from a worksheet
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
jollytall · 2022-04-22 09:45

I have many unused cells in the worksheet. I do not exactly know where
they come from (probably in a large copy operation created even if in
the source the cell did not exist), but I would like to clean them.
The reason is that I try to use 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 if not
assigned(Worksheet.FindCell(Row, Column)) then logic to skip unused
cells, but sometimes they exist, so I use instead 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 if
Worksheet.ReadAsText(Row, Column) <> '' then but I think it is lower
performance.
Is there an easy way to clean unused cells?

----------------------------------------------------------------------
wp · 2022-04-22 11:14

It is a tiny bit faster when you query the ContentType field of the
cell record:
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  if (cell
<> nil) and (cell^.ContentType = cctEmpty) then...Note that when a
cell contains a formula which results in an empty string (= empty
cell) this instruction will be deleted as well. If you want to keep
this formula you must also check "if not Hasformula(cell)".

In order to delete a cell with all contents, formatting, comments,
hyperlinks, formulas and memory, call Worksheet.DeleteCell(cell).
There is also a Worksheet.RemoveCell(cell) which does the same, but
keeps the memory.

See the attached example. Compare the input file "test.xlsx" and the
result after deletion, "test-emptied.xlsx".

----------------------------------------------------------------------
jollytall · 2022-04-22 11:57

Thanks.
Did you really mean RemoveCell? There is a third version EraseCell and
the description is basically the same, what you write for RemoveCell
(i.e. deletes but keeps memory). Remove cell is in the protected
section, so it cannot even be called directly.
What does it mean that "keeps memory"? If all references removed for a
cell and the memory is still there, isn't it a memory leak? When
should one use that?

Maybe a bit more explanation of the three versions and a how-to when-
to use them would be good.

----------------------------------------------------------------------
wp · 2022-04-22 14:04

Sorry, I meant EraseCell.

The protected RemoveCell is probably a left-over from an ancient
version.

"deletes but keeps memory" means: deletes all the content and
associated data (formatting, formulas, hyperlinks, comments), but
keeps the cell record in the AVLTree. In other words: If you'd do a
worksheet.FindCell, the cell would still be found but it would be
empty.

----------------------------------------------------------------------
jollytall · 2022-04-22 14:27

Thanks, it is clear now.