Use xlsm macro excel from Python or PHP

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 =&gt; array("field1" =&gt; "Value", "field2" =&gt; "Value"),
1 =&gt; array("field1" =&gt; "Value", "field2" =&gt; "Value"),
2 =&gt; array("field1" =&gt; "Value", "field2" =&gt; "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 !!

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s