Avatar of Terry Woods
Terry Woods
Flag for New Zealand asked on

Substituting values in .xlsx files with PHP

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>

Open in new window


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?
PHPMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Terry Woods

8/22/2022 - Mon
SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
yogi4life

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Terry Woods

ASKER
Thanks for your help!
Terry Woods

ASKER
In case it's useful to anyone, my prototype version (with inadequate error checking etc) is as follows. It replaces the values "${project_description}", "${estimate}", "${start_date}" in the Excel file with new values:

<?php
$zip = new ZipArchive;
$res = $zip->open('testfile.xlsm');
$tempFolder = "unziptemp"; #this gets overwritten each time the script is run
if ($res === TRUE) {
  $zip->extractTo("$tempFolder/");
  $zip->close();
  echo "File contents extracted...\n";
} else {
  echo "Error: Could not unzip file!\n";
  exit;
}

$targetFile = "$tempFolder/xl/sharedStrings.xml";

print "Reading file...\n";
$data = file_get_contents($targetFile);
print "...done\n";
$test_values = array(
  "project_description" => "This is the project descr",
  "estimate" => '$1,000,000',
  "start_date" => "1/6/2012"
);
foreach ($test_values as $field=>$value) {
  print "Populating $field field with value '$value'\n";
  $data = preg_replace("#\\$\\{".preg_quote($field,"#")."\\}#",preg_replace("/\\$/","\\\\\\$",xmlentities($value)),$data);
}
print "Writing back to file...\n";
file_put_contents($targetFile,$data);
print "...done\n";

#To do: check if $tempFolder folder exists?
#To do: check if myzip.zip already exists?
print "Rezipping...\n";
Zip("$tempFolder","New File.xlsm");
print "...done\n";
#To do: rmdir unzip
#To do: replace original file


#===============================================================================
function xmlentities($string) {
    return str_replace(array("&", "<", ">", "\"", "'"),
        array("&amp;", "&lt;", "&gt;", "&quot;", "&apos;"), $string);
}

#===============================================================================
function Zip($source, $destination)
{
    if (!extension_loaded('zip') || !file_exists($source)) {
        return false;
    }

    $zip = new ZipArchive();
    if (!$zip->open($destination, ZIPARCHIVE::CREATE)) {
        return false;
    }

    $source = str_replace('\\', '/', realpath($source));

    if (is_dir($source) === true)
    {
        $files = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($source), RecursiveIteratorIterator::SELF_FIRST);

        foreach ($files as $file)
        {
            $file = str_replace('\\', '/', realpath($file));

            if (is_dir($file) === true)
            {
                $zip->addEmptyDir(str_replace($source . '/', '', $file . '/'));
            }
            else if (is_file($file) === true)
            {
                $zip->addFromString(str_replace($source . '/', '', $file), file_get_contents($file));
            }
        }
    }
    else if (is_file($source) === true)
    {
        $zip->addFromString(basename($source), file_get_contents($source));
    }

    return $zip->close();
}

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes