troubleshooting Question

Substituting values in .xlsx files with PHP

Avatar of Terry Woods
Terry WoodsFlag for New Zealand asked on
Microsoft OfficePHPMicrosoft Excel
4 Comments2 Solutions1155 ViewsLast Modified:
I have an xlsx file where some of the cells have values like "${project_description}" and "${estimate}"

I want to use PHP to substitute those values (which are variable names) with variables from my webapp, then save a copy of the file. It's essentially a templating system.

I've tried using PHPExcel (http://phpexcel.codeplex.com/) and it worked for a simple test but seemed to encounter corrupt XML data with the real file that I want to use (which is much more complex).

When I unzip the myfile.xlsx file manually, I see the cell values like "${project_description}" that I want to replace are in the file "xl/sharedStrings.xml" amongst a massively long single line of data that looks like this:
<si><t>Jane</t></si><si><t>UT11550</t></si><si><t>Bloggs</t></si><si><t>WT73383</t></si><si><t>WU33992</t></si><si><t>WG66470</t></si><si><t>${project_description}</t></si><si><t>${approving_manager}</t></si><si><t>${estimate}</t></si>

I'd be happy to run a preg_replace over that file to populate it with my values, but I'm wondering if anyone is aware of any existing code that I could use that handles the unzipping and zipping up side of things? Obviously it's in PHPExcel already, but separating it out could take longer than writing it from scratch.

Also, is it a reliable technique to target the "xl/sharedStrings.xml" file, or might the name of that change under some circumstances? Will all my variable names always be stored in there (and nowhere else)?

Will the technique work on .xlsm files too?
ASKER CERTIFIED SOLUTION
yogi4life

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros