11
1
An Excel XLSX file is a zip archive containing several XML files. I tried to extract all the XML files, and edit xl\connections.xml using an XML editor. That's because I have to change 20+ connections to point to a different server.
When I open the edited archive in Excel, it refuses the changes and repairs the file.
Is there a way to edit the XML files inside an XML archive?
Andomar
Posted 2010-05-26T11:34:29.353
Reputation: 1 221
10
How are you going about doing the editing? I would suggest not fully extracting the file, but instead, just opening the archive in 7-zip. Then, find the file you want to edit, choose "edit", then save it and 7-zip should automatically replace it in the archive. In the past when I've done this, Excel has then opened it up without any trouble, but when actually extracting then recompressing it, I've had issues.
nhinkle
Posted 2010-05-26T11:34:29.353
Reputation: 35 057
2
You definitely should be able to edit the XML in the archive without problems.
I have done that several times with the xl\comments1.xml file and Excel opened it without any problems.
I used 7-zip and Notepad++. Perhaps your editor changes the encoding of the XML or your zipper creates a file that is not exactly compatible with Excel.
Perhaps there is a dependency on another file in the archive that has to be changed also.
You could try changing the connection within Excel and checking which files in the archive changed after saving.
Turismo
Posted 2010-05-26T11:34:29.353
Reputation: 353
3Excel 'zip' file contents are not really compressed (deflated), they're just stored instead. – whitequark – 2010-06-14T15:22:25.027
Ah, that would explain why recompressing as a zip using windows's built-in zipper causes problems. I'm curious now if using 7-zip and specifying the compression level as "store" would work then. – nhinkle – 2010-06-14T20:13:43.243
1@whitequark: that is not correct - the contents are indeed deflated! Excel also reads 'zip' files where the contents are only stored, but if you save the same file again in Excel it gets compressed anyway. @nhinkle: zipping with 7-zip always worked for me regardless if I chose "store" or "normal-deflate" as the compression level. – Turismo – 2010-06-15T10:34:02.357
@Turismo: maybe I missed it with odf or such. Compressing those XML's is, really, a good idea. The Windows zipper is probably brain-damaged. – whitequark – 2010-06-15T10:56:40.960
@whitequark I've tried compressing with the Windows built-in zipper and excel can open it without problem. The most common mistake is that people often right click on the folder and compress, which is not right. You must select all the files inside the folder and compress, since the
[Content_Types].xmlmust be at the root of the ooxml file – phuclv – 2018-12-24T03:43:45.487