How to replace placeholders from excel file in php using str_replace() function?

Question

Following PHP code is perfectly working for word documents (specificly .docx) but for excel (.xlsx) it is not changing placeholders. As I understand, this code converts word file into document.xml file and which inside the word folder of zip file and finds {{placeholder}} and changes it into text what we want.

    if(isset($_POST["e_submit"]))
    {
        $name=(string) $_POST["e_name"];
        $email=(string) $_POST["e_email"];
        $source='TemplateSimpleText.docx';
        $temp='template'.'.docx';

        copy($source,$temp);

        $zip = new ZipArchive;

        $fileXml='word/document.xml';
        if($zip->open($temp) === TRUE)
        {
            $old=$zip->getFromName($fileXml);

            $new=str_replace('{{Name}}',$name,$old);
            $new=str_replace('{{Email}}',$email,$new);

            $zip->deleteName($fileXml);
            $zip->addFromString($fileXml,$new);
            $zip->close();

            if (ob_get_level()) {
                ob_end_clean();
            }

            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename=' . basename($temp));
            header('Content-Transfer-Encoding: binary');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($temp));


            readfile($temp);
            unlink($temp);

           exit();
        }
    }

Answer 1

For Excel documents you have to edit couple of different files. Namely:

• xl/sharedStrings.xml for strings
• xl/worksheets/sheet1.xml (and other sheet#.xml) for number values
• xl/workbook.xml to force recalculations if needed

Working code:

    $zip =  new ZipArchive;

    if ($zip->open($tmp_file) === true) {
        $xml = 'xl/sharedStrings.xml';
        $old_contents = $zip->getFromName($xml);
        $new_contents = strtr($old_contents, [ 'old' => 'new' ]);
        $zip->deleteName($xml);
        $zip->addFromString($xml, $new_contents);

            $xml = 'xl/worksheets/sheet1.xml';
            $old_contents = $zip->getFromName($xml);
            $new_contents = strtr($old_contents, [ '123' => '456' ]);
            $zip->deleteName($xml);
            $zip->addFromString($xml, $new_contents);

            $xml = 'xl/workbook.xml';
            $old_contents = $zip->getFromName($xml);
            $new_contents = strtr($old_contents, [
                '<calcPr' => '<calcPr fullCalcOnLoad="1" ',
            ]);
            $zip->deleteName($xml);
            $zip->addFromString($xml, $new_contents);

      $zip->close();
            
    /* ... do something with your zip file ... */
  }

Forced recalculations work only in spreadsheet editors. They do not work in xlsx viewers.