Lazarus
Programming => Packages and Libraries => FPSpreadsheet => Topic started by: kjteng on August 11, 2020, 06:06:14 pm
-
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:
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 ?
.
-
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 file
- Rename the xlsx file to get extension .zip
- Use an unzip utility to extract the contents of the file to a folder
- Among others this creates a folder "docProps" which contains the custom.xml file
- If you did not write any meta data to the file you can simply delete this file. It is not needed in this case
- If 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.
-
Thank you master for the detailed explanation. I will download the fix and try it.
-
Downloaded r7599. Problem solved. Tqvm.