openpyxl changes the format of the saved file

[Sheldon Cooper]

I create a calc file using libreoffice whose structure is as follows:

@mosby:/temp/aa/xl# ls -R

.:

•  [Content_Types].xml docProps _rels xl
•  ./docProps: app.xml core.xml custom.xml 
•  ./_rels: 
•  ./xl: _rels sharedStrings.xml styles.xml workbook.xml worksheets 
•  ./xl/_rels: workbook.xml.rels 
•  ./xl/worksheets: sheet1.xml sheet3.xml sheet5.xml sheet7.xml sheet2.xml sheet4.xml sheet6.xml sheet8.xml

Then through python and openpyxl I work on that file after which I save it

wb = load_workbook(file_name) [...] wb.save(file_name)

The problem is that the sharedStrings.xml file is gone while I need it for other applications that don't use openpyxl. Is there a switch that forces openpyxl to use the original structure in the save?

[Charlie Clark]

On 18 Jul 2021, at 18:14, Sheldon Cooper wrote:

> The problem is that the sharedStrings.xml file is gone while I need it for
> other applications that don't use openpyxl. Is there a switch that
> forces openpyxl to use the original structure in the save?

sharedStrings.xml is entirely optional according to the specification so no application should require it, no data is missing without it. If you want it, you'll have to use another library or a much older version of openpyxl.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Sheldon Cooper]

Thank you sir for your answer even if I dont agree it. I will try an older version (if I find it :) )

[Charlie Clark]

On 19 Jul 2021, at 13:29, Sheldon Cooper wrote:

> Thank you sir for your answer even if I dont agree it. I will try an older
>
> version (if I find it :) )

I'm not sure what you disagree with: shared strings are entirely optional and an artefact of a previous optimisation that probably made more sense when worksheets were limited to 256 columns and 65,000 rows. It becomes a bit of a memory and resource hog on larger worksheets with lots of text. Any library that follows the specification should have no problem with inline strings; if it does then it's likely to have more problems than just that.

[Sheldon Cooper]

The same work  I do with python (manipulating  xlsx files and DB) is performed in another office via PHP and that tool, which has been working perfectly for at least 5 years, expects to find sharedStrings. 

What I meant is that openpyxl should take into account whether or not that file is present. If it exists it restores it, otherwise it doesn't use it. Anyway, thanks to your suggestion I have installed version 2.5 and everything is OK 

[Charlie Clark]

On 19 Jul 2021, at 18:10, Sheldon Cooper wrote:

> The same work I do with python (manipulating xlsx files and DB) is
> performed in another office via PHP and that tool, which has been working
> perfectly for at least 5 years, expects to find sharedStrings.

Which means it doesn't follow the specification which clearly makes it optional. We've worked very hard with the library to implement the specification. Openpyxl reads sharedStrings.xml but doesn't write it.

> What I meant is that openpyxl should take into account whether or not that
> file is present. If it exists it restores it, otherwise it doesn't use it.

Nonsense, there is no point in slavishly preserving the package structure. Particularly when streaming large reports, sharedStrings can be a significant overhead.

> Anyway, thanks to your suggestion I have installed version 2.5 and

> everything is OK.

Great, as long as you can live without the subsequent additions and improvements.