calc: Clean up/optimize an ods file?

[newbie-02]

hi @all,

with a document that reproducibly crashed i learned something about the content of my *.ods files.

they accumulate garbage.

besides some links to external files which i couldn’t remove, they contain e.g. hundreds of style definitions like ‘Excel cond format 10.4.1’ and similar, which i’m quite sure not having created or used. and also some hundreds of ‘validity definitions’ which probably result form copying cells from other files where they had meaningful validity checks, but which are not in need in the final file, and where the final file doesn’t have the appropriate ranges with the validity definitions. and probably plenty of other unused stuff collected over the years.

are there any tools / inspectors with which one can:

• inspect a sheet for content / definitions for whatever stuff is in which is not visible on the screen,

• check which of that is in use / referenced by which cells or functions,

• and if superfluous remove it?

• or something which automatically removes all such which is not in use or has broken referencing except predefined things by LO?

i suspect such stuff slowing down the sheet.

[Villeroy]

the formatting part of extension SpecialCells » Libreoffice Extensions dissects the sheet’s used are into ranges of equal formatting attributes. Typical Excel sheets contain hundreds of different formattings in thousands of ranges. Of course, you do keep backups of your important documents, don’t you?
I would remove all formatting, apply cell style “Default” to all the cells and then apply the required cell styles (and page styles if printing is of any importance). Mind that removing all formatting attributes does not modify any of the data nor formula results.

[GrahamLees]

There is an online tool to perform this task called ScanWritr. Link here

[dandv]

That tool didn’t manage to import a 1-row .ods correctly; it only showed one of 3 columns.

[JohnHa]

Copy the file contents to a new file - that strips all garbage.

i suspect such stuff slowing down the sheet.

I doubt that.