Question 174182 in PHP Question posted in
Official manual can be found here.

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

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    // 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);

  2. 0

    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 =&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");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search