Substituting values in .xlsx files with PHP

Terry Woods
Terry Woods used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016
Commented:
No points for this since it does not really answer the essential part of the question, but PHP has some ZIP functionality.  I have used it successfully on smaller projects.  Here's a demo script.
<?php // RAY_zip_archive.php
error_reporting(E_ALL);
date_default_timezone_set('America/New_York');

// THE URL ARGUMENT IS THE NAME OF THE DIRECTORY TO ZIP
$dir = (!empty($_GET["dir"])) ? $_GET["dir"] : NULL;
if (!$dir) die("PLEASE PROVIDE dir= IN THE URL");

// A GOOD PATH ON MY SERVER
$path
= getcwd()
. DIRECTORY_SEPARATOR
. $dir
;
if (!is_dir($path)) die("FAIL: NOT VALID PATH $path");

// THIS IS THE LIST OF scandir() RESPONSES THAT WE DO NOT WANT
$unwanted
= array
( '.'
, '..'
, 'teste2.txt'
)
;

// INSTANTIATE THE OBJECT
$zip = new ZipArchive();

// CREATE A DATE-TIME ARCHIVE NAME
$archive
= date('Ymd\THis')
. '_archive_'
. $dir
. '.zip'
;

// TRY TO CREATE THE ARCHIVE
if ($zip->open($archive, ZIPARCHIVE::CREATE)!==TRUE) die("FAIL: ZIP->OPEN <$archive>");

// GET THE LIST OF FILES
if (!$files = scandir($path))   die("FAIL: scandir() $path");
foreach ($files as $file)
{
    // SKIP THE UNWANTED AND ADD THE OTHERS TO THE ARCHIVE
    if ( in_array($file, $unwanted) )                    continue;
    if ( !is_file($path . DIRECTORY_SEPARATOR . $file) ) continue;
    $zip->addFile($path . DIRECTORY_SEPARATOR . $file );
}

if (!$zip->close()) die("FAIL: ZIP->CLOSE");

$fs  = filesize($archive);
$fsn = number_format($fs);


// PREPARE A LINK
$link
= '<a target="_blank" href="'
. $archive
. '">'
. "DOWNLOAD $archive $fsn BYTES"
. '</a>'
;
// echo $link;

// SEND THE ZIP ARCHIVE
header("Content-Type: archive/zip");
header("Content-Disposition: attachment; filename=$dir".".zip");
header("Content-Length: $fs");
readfile($archive);

Open in new window

Best of luck with it, ~Ray
1) For zip and unzip you can use this ZIP-libary for PHP:

        http://www.php.net/manual/en/book.zip.php

    Notice the requirements for PHP 4 and PHP 5

2) xl/sharedString.xml: The name will not change for Excel 2007 and Excel 2010. The file might not exist if there are no shared strings

3) All your "variable names" (strings) will be stored there yes....

4) Yes - it will work for an .xlsm file too
Terry WoodsIT Guru
Most Valuable Expert 2011

Author

Commented:
Thanks for your help!
Terry WoodsIT Guru
Most Valuable Expert 2011

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial