Recent

Author Topic: Problem with xlsx file  (Read 330 times)

kjteng

  • Full Member
  • ***
  • Posts: 190
Problem with xlsx file
« 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:

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

  • Hero Member
  • *****
  • Posts: 7633
Re: Problem with xlsx file
« Reply #1 on: August 11, 2020, 06:31:17 pm »
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.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

kjteng

  • Full Member
  • ***
  • Posts: 190
Re: Problem with xlsx file
« Reply #2 on: August 12, 2020, 12:15:04 am »
Thank you master for the detailed explanation. I will download the fix and try it.

kjteng

  • Full Member
  • ***
  • Posts: 190
Re: Problem with xlsx file
« Reply #3 on: August 12, 2020, 03:33:14 am »
Downloaded r7599. Problem solved. Tqvm.

 

TinyPortal © 2005-2018