Link to home
Avatar of Terry Woods
Terry WoodsFlag 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?
SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Terry Woods

ASKER

Thanks for your help!
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