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

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

yogi4life

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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