How can I modify an .xlsm file with macros using PHPExcel without losing macros?

Question

PHP Excel - modify .xlsm file with macros without loosing macros

Is there any php library or approach to modify xlsm file which has macros without loosing macros. I want to export large data in xlsm template. the xlsm template then export txt file which will be uploaded somewhere

Thanks

Answer 1

PHP COM extension: You can use PHP's COM extension to interact with Microsoft Excel via its COM interface. This method requires you to have Excel installed on the server running PHP. You can write PHP code to automate Excel, open the .xlsm file, make the necessary modifications, and save it back with the macros intact.

// Create a new COM object for Excel
$excel = new COM("Excel.Application") or die("Unable to instantiate Excel");

// Disable alerts and visibility
$excel->DisplayAlerts = false;
$excel->Visible = false;

// Open the macro-enabled Excel file
$filePath = __DIR__ . '/input.xlsm';
$workbook = $excel->Workbooks->Open($filePath);

// Access the active sheet
$sheet = $workbook->ActiveSheet;

// Modify the data or perform other operations
$sheet->Range("A5")->Value = "Hello, World!";

$tempFilePath = __DIR__ .'/output.xlsm';
// Save the modified file
$workbook->SaveAs($tempFilePath);

// Close the workbook and quit Excel
$workbook->Close();
$excel->Quit();
$excel = null;

// Set the appropriate headers for file download
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="output.xlsm"');
header('Content-Length: ' . filesize($tempFilePath));

// Output the file content
readfile($tempFilePath);

// Delete the temporary file
unlink($tempFilePath);

Answer 2

If you are using PhpSpreadsheet for it, you might notice that it sometimes working and sometimes not. The reason is, that .xlsm files are not fully supported.

Usually you create an excel report with macro in VB and afterwards you dont need to change any parameters anymore, but you need to replace the sheet data in the xlsm files without losing VB Macro. If that´s what you are searching for you can go with jsontoxlsm.jar which will be executed from shell.

The Usage is the following as you can see in the Repo:

java -jar json2xlsm.jar <strFileJSON> <strMacroExcelFileIn> <strMacroExcelFile>

You can create a JSON file from Python and then execute json2xlsm (keep in mind all Values habe to be in UTF-8):

import json
import os
 
data = [
{'field1': 'Value', 'field2': 'Value', 'field3': 'Value'},
{'field1': 'Value', 'field2': 'Value', 'field3': 'Value'},
{'field1': 'Value', 'field2': 'Value', 'field3': 'Value'},
]
 
with open('jsonFilename.json', 'w') as fout:
json.dump(data , fout)
 
os.system('java -jar json2xlsm.jar jsonFilename.json MacroExcelTemplateFile.xlsm MacroExcelFileOut.xlsm')

If you wanna use PHP to create the JSON file and execute json2xlsm, you can do the following:

    $array = array(
0 => array("field1" => "Value", "field2" => "Value"),
1 => array("field1" => "Value", "field2" => "Value"),
2 => array("field1" => "Value", "field2" => "Value"),
);
 
$jsonString = json_encode($array);
 
file_put_contents("jsonFilename.json", $jsonString);
 
shell_exec("java -jar json2xlsm.jar jsonFilename.json MacroExcelTemplateFile.xlsm MacroExcelFileOut.xlsm");