If you try to open and write a .xlsm file using PhpSpreadsheet o PHPExcel, you might drop functionality present in the original xlsm file because .xlsm files are not (fully) supported.
So, there is no xlsm library in PHP. In Python you can use openpyxl to read/write data but if you use an exsiting xlsm with Visual Basic macros in it, you can loose that code when writing updated xlms file.
So, usually you create a excel report with your macros in Vb and you do not need to edit macros from PHP or Python, but you need to replace sheet data in XLSM files, without loosing Vb Macro. So you have a xlsm template file that you use to generate a working report version, just replacing the data and getting an updated reports.
If so, the solutiom is jsontoxlsm.jar executed from shell. Find it in : https://github.com/jmarxuach/json2xlsm
Usage :
java -jar json2xlsm.jar <strFileJSON> <strMacroExcelFileIn> <strMacroExcelFile>
Creating JSON file from Pythom and executing json2xlsm
All values must 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')
Creating JSON file from PHP and executing json2xlsm
All values must be in UTF-8.
$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");
That’s all folks !!
Codber 