2

The Excel file format .xlsx is on fact an archive that can be inspected, and have the contents edited, if renamed to .zip.

However, changes to the sharedStrings.xml file in the .xlsx archive does not change the contents as represented when reopened in Excel.

Can anyone explain why and how to make changes in text strings so that the changes appear in Excel?

I have made a few test files, e.g. with a single text string in it in the A1 cell. The text string is present in the sharedStrings.xml file, and I can't find it anywhere else in the .xlsx archive, but alas, changes does not become visible.

bjornte
  • 167

2 Answers2

1

You have to locate the sheet files. Sheet1.xml in this case.

You can find the strings, or more accurately, formulas (<f></f>) and their values (<v></v>) in these files and you can edit them. Excel fights this, you cannot change the files from read-only to not, so you have to save elsewhere and copy over the originals. Further, there will some subtle problems, apparently, aaaaand Excel will carp about opening the file, but it WILL open.

For fun, you can leave the value strings as is and they will show the wrong results for the modified formulas upon opening. The first recalc will fix them, of course, but it shows Excel does NOT recalc on open, just reads the saved material, including formula results. Not sure that could ever be useful, but... it's interesting.

Hmm... one thing I found was that one of the other files in the package contains absolute addressing for certain things. So if your company assigns user names, network access, and so on (login name for Windows, lots of "so on" here), it will BE in the absolute path for most peoples' setups. Who shifts their work to store on their C drive rather than a network share? Even with author/ownership properties cleared, these paths show enough to mark the casual user. If your Windows login is "TMashad", then these absolute paths will contain that and how hard would it be for someone to work with? Though who'd want to? I hope.

Eh... might've wandered from the point a tad. Sheet1.xml is what you want for editing strings and such. My guess was that what you were looking at, which I did not see, so it remains a guess, is OUTPUT, so it doesn't flow away to the strings themselves, but rather the flow is the opposite direction, INTO what you saw. Into only.

Still, interesting. I never really thought about how a lot of actually interesting material was hidden in the XML package...

Jeorje
  • 21
1

Embarrassing, but the solution had nothing to do with Excel. Rather, the error I made was to try to edit the sharedStrings.xml file while it was still inside the zip archive. While the attempted save did not produce an error message, the file was not modified.

Once I copied the xml file out of the archive, edited it (with VS Code), dragged it back in (using the built in File Explorer in Windows), and renamed the zip back to xlsx, the file opened nicely in Excel with the intended changes.

This facilitated my original intent, which was to perform replace operations beyond the scope of Excel's built in Replace dialog.

bjornte
  • 167