Read and write XLSM file in PHP

Question

I want to read and write XLSM type file using PHP. I tried using PHPSpreadsheet for this but it doesn't support XLSM extension.

One possible solution could be to use EasyXLS (https://www.easyxls.com/manual/basics/import-from-xlsx-file-format.html)

// Create an instance of the class that imports XLSM files
$workbook = new COM("EasyXLS.ExcelDocument");

// Import XLSM file
$workbook->easy_LoadXLSXFile("C:\\Samples\\Excel to import.xlsm");

// Get the table of the second worksheet
$xlsSecondTable = $workbook->easy_getSheet("Second tab")->easy_getExcelTable();

// Add more data to the second sheet
$xlsSecondTable->easy_getCell_2("A1")->setValue("Data added by Tutorial37");
for ($column=0; $column<5; $column++)
{
    $xlsSecondTable->easy_getCell(1, $column)->setValue("Data " . ($column + 1));
}

// Generate the XLSM file
$workbook->easy_WriteXLSXFile("C:\Samples\Excel with macro.xlsm");

But I was unable to find any Library for this.

Does anyone have any possible solution for this or some other way for this?

I am using **Ubuntu** (Linux). Also, I found out that **COM** is a _window only extension_ and won't work in my case. — Amarjeet Pati, Nov 30 '19 at 07:04

score 0 · Answer 1 · answered May 25 '20 at 17:28

function excel($excelfile, $sheet = false){//from stores.blade.php
    $tempfile                   = resource_path("uploads/excel.xlsm");//it just needs a place to store the XML file temporarily, this function only works in Laravel, replace with a filename
    if($sheet){//load XML file
        //$XML = file_get_contents($tempfile);
        $XML                    = simplexml_load_file($excelfile);
        $XML                    = json_decode(json_encode((array)$XML), TRUE);
        $excelfile              = pathinfo($sheet)['basename'];
        if($excelfile == "workbook.xml"){
            $RET = [];
            foreach($XML["sheets"]["sheet"] as $data){
                $RET[ $data["@attributes"]["sheetId"] ] = $data["@attributes"]["name"];
            }
            return [
                'Filename'      => $excelfile,
                'SheetName'     => "sys_workbook",
                'SheetData'     => $RET,
            ];
        } else if($excelfile == "sharedStrings.xml"){
            foreach($XML["si"] as $index => $value){
                if(isset($value["t"])){
                    $value      = $value["t"];
                } else {
                    foreach($value["r"] as $index2 => $value2){
                        if(is_array($value2["t"])){
                            $value2["t"] = $value2["t"][0];
                        }
                        $value["r"][$index2] = $value2["t"];
                    }
                    $value = implode("", $value["r"]);
                }
                if(is_array($value)){
                    $value = $value[0];
                }
                $XML["si"][$index] = $value;
            }
            return [
                'Filename'      => $excelfile,
                'SheetName'     => "sys_strings",
                'SheetData'     => $XML["si"],
            ];
        } else if(isset($XML["sheetPr"])){
            return [
                'Filename'          => $excelfile,
                'SheetName'         => $XML["sheetPr"]["@attributes"]["codeName"],
                'SheetData'         => $XML["sheetData"]["row"],
            ];
        }
        return false;
    } else {//load ZIPped XLSM file
        $files                  = [];
        $zip                    = new ZipArchive;
        if ($zip->open($excelfile) === TRUE) {
            for($i = 0; $i < $zip->numFiles; $i++) {
                $filename       = $zip->getNameIndex($i);
                if(startswith($filename, "xl/worksheets/") || $filename == "xl/workbook.xml" || $filename = "xl/sharedStrings.xml"){
                    copy("zip://" . $excelfile . "#" . $filename, $tempfile);
                    $XML        = excel($tempfile, $filename);
                    if($XML !== false){
                        $files[ $XML["SheetName"] ] = $XML["SheetData"];
                    }
                }
            }
            @unlink($tempfile);
            $zip->close();
        }
        var_dump($files);
        die();
    }
}

I started work on this, I got this far. The problem is, I don't know how the shared strings (sys_strings) are referenced, and you'd need an equation evaluator to handle the functions. I HOPE, the sheets are in order, so Sheet1 becomes the first array value in sys_workbook.

score 0 · Answer 2 · answered Jul 07 '22 at 06:38

0

For anyone still looking for a solution to this problem, the following XLSX reader can parse XLSM without a problem. Also saves the headache in interpreting Excel timestamps and data formats. Tried and tested.

I did not test the Writer though, yet!

https://github.com/shuchkin/simplexlsx

answered Jul 07 '22 at 06:38

Oliver M Grech

3,071
1
21
36

Did you test the writer? It works? – JuanFernandoz Sep 16 '22 at 21:59

Read and write XLSM file in PHP

2 Answers2