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

Solution

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);