Recent

Author Topic: Clean empty cells from a worksheet  (Read 472 times)

jollytall

  • Full Member
  • ***
  • Posts: 214
Clean empty cells from a worksheet
« on: April 22, 2022, 11:45:59 am »
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][+][-]
  1. if not assigned(Worksheet.FindCell(Row, Column)) then
logic to skip unused cells, but sometimes they exist, so I use instead
Code: Pascal  [Select][+][-]
  1. if Worksheet.ReadAsText(Row, Column) <> '' then
but I think it is lower performance.
Is there an easy way to clean unused cells?

wp

  • Hero Member
  • *****
  • Posts: 10297
Re: Clean empty cells from a worksheet
« Reply #1 on: April 22, 2022, 01:14:15 pm »
It is a tiny bit faster when you query the ContentType field of the cell record:
Code: Pascal  [Select][+][-]
  1.  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

  • Full Member
  • ***
  • Posts: 214
Re: Clean empty cells from a worksheet
« Reply #2 on: April 22, 2022, 01:57:05 pm »
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

  • Hero Member
  • *****
  • Posts: 10297
Re: Clean empty cells from a worksheet
« Reply #3 on: April 22, 2022, 04:04:36 pm »
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

  • Full Member
  • ***
  • Posts: 214
Re: Clean empty cells from a worksheet
« Reply #4 on: April 22, 2022, 04:27:20 pm »
Thanks, it is clear now.

 

TinyPortal © 2005-2018