Problem with xlsx file
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
kjteng · 2020-08-11 16:06

If I repeatedly open certain worksheet (xlsx) for two times, any xlsx
file subsequently saved (in the same apps) would not be readable by
Excell (unless I let Excel to recover/repair it). Example codes:

Quote       wbSource1.Workbook.ReadFromFile('test.xlsx');
       wbSource1.Workbook.Clear;
       wbSource1.Workbook.ReadFromFile('test.xlsx');
       wbSource1.SaveToSpreadsheetFile('test1.xlsx');

//   files attached:  test.xlsx can be opened in Excel.  test1.xlsx
need to be
//   repaired before Excel open it.
After few hours of testing, I found that::-
I) within test.xlsx there is a file called custom.xml which contains
some custom properties. These properties are duplicated twice (eg. 4
items would became 8 items) in test1.xls. Hence test1.xlsx cannot be
read by Excel (Libreoffice  has no issue with test1.xlsx - I guess
Libreoffice would ignore/automatically remove duplicate properties).
II) I can manually delete custom.xml and solve the above issue i.e. by
opening the excel file (as archive) using 7zip.

My questions:
How can I prevent the above issue?
How do I remove the duplicate properties (or remove all this unwanted
properties) programatically in fpSpread ?
.

----------------------------------------------------------------------
wp · 2020-08-11 16:31

Ah... Workbook.Clear did not clear the metadata. Should be fixed now.

In order to restore readability of the damaged files you must fix them
manually:
Make a backup copy of the xlsx fileRename the xlsx file to get
extension .zipUse an unzip utility to extract the contents of the file
to a folderAmong others this creates a folder "docProps" which
contains the custom.xml fileIf you did not write any meta data to the
file you can simply delete this file. It is not needed in this caseIf
you did write meta data open the custom.xml file in a text (or xml)
editor and remove the duplicate items. Note that this will be hard
work in a text editor because the xml text is unformatted without line
breaks.Pack all files back to a zip so that the original folder
structure and file names are retained.Rename the zip back to xlsx
extension.

----------------------------------------------------------------------
kjteng · 2020-08-11 22:15

Thank you master for the detailed explanation. I will download the fix
and try it.

----------------------------------------------------------------------
kjteng · 2020-08-12 01:33

Downloaded r7599. Problem solved. Tqvm.