如何在不丢失宏的情况下,使用PHPExcel用PHP修改一个带有宏的.xlsm文件?

huangapple go评论57阅读模式
英文:

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

问题

PHP Excel - 修改带有宏的.xlsm文件而不丢失宏

是否有任何 PHP 库或方法可以修改带有宏的.xlsm文件而不丢失宏?我想要在.xlsm模板中导出大量数据,然后该模板导出一个txt文件,该文件将上传到某个地方。

谢谢

英文:

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

答案1

得分: 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.

通常,您在VB中创建带有宏的Excel报告,然后不需要再更改任何参数,但您需要替换xlsm文件中的工作表数据,而不会丢失VB宏。如果这是您要寻找的内容,您可以使用jsontoxlsm.jar,它将从shell中执行。

使用方法如下,您可以在存储库中看到:

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

您可以从Python创建一个JSON文件,然后执行json2xlsm(请记住所有值都必须以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')

如果您想要使用PHP创建JSON文件并执行json2xlsm,可以按照以下步骤操作:

$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");
英文:

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 &lt;strFileJSON&gt; &lt;strMacroExcelFileIn&gt; &lt;strMacroExcelFile&gt;

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 = [
{&#39;field1&#39;: &#39;Value&#39;, &#39;field2&#39;: &#39;Value&#39;, &#39;field3&#39;: &#39;Value&#39;},
{&#39;field1&#39;: &#39;Value&#39;, &#39;field2&#39;: &#39;Value&#39;, &#39;field3&#39;: &#39;Value&#39;},
{&#39;field1&#39;: &#39;Value&#39;, &#39;field2&#39;: &#39;Value&#39;, &#39;field3&#39;: &#39;Value&#39;},
]
 
with open(&#39;jsonFilename.json&#39;, &#39;w&#39;) as fout:
json.dump(data , fout)
 
os.system(&#39;java -jar json2xlsm.jar jsonFilename.json MacroExcelTemplateFile.xlsm MacroExcelFileOut.xlsm&#39;)

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

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

答案2

得分: 0

PHP COM扩展:您可以使用PHP的COM扩展与Microsoft Excel进行交互,通过其COM接口。这种方法要求您在运行PHP的服务器上安装了Excel。您可以编写PHP代码来自动化Excel,打开.xlsm文件,进行必要的修改,并保存它,使宏保持完整。

// 创建一个新的Excel COM对象
$excel = new COM("Excel.Application") or die("无法实例化Excel");

// 禁用警告和可见性
$excel->DisplayAlerts = false;
$excel->Visible = false;

// 打开启用宏的Excel文件
$filePath = __DIR__ . '/input.xlsm';
$workbook = $excel->Workbooks->Open($filePath);

// 访问活动工作表
$sheet = $workbook->ActiveSheet;

// 修改数据或执行其他操作
$sheet->Range("A5")->Value = "Hello, World!";

$tempFilePath = __DIR__ . '/output.xlsm';
// 保存修改后的文件
$workbook->SaveAs($tempFilePath);

// 关闭工作簿并退出Excel
$workbook->Close();
$excel->Quit();
$excel = null;

// 为文件下载设置适当的标头
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="output.xlsm"');
header('Content-Length: ' . filesize($tempFilePath));

// 输出文件内容
readfile($tempFilePath);

// 删除临时文件
unlink($tempFilePath);

请注意,这是PHP与Excel交互的示例代码。如果您有任何其他问题,请随时提出。

英文:

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(&quot;Excel.Application&quot;) or die(&quot;Unable to instantiate Excel&quot;);

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

// Open the macro-enabled Excel file
$filePath = __DIR__ . &#39;/input.xlsm&#39;;
$workbook = $excel-&gt;Workbooks-&gt;Open($filePath);

// Access the active sheet
$sheet = $workbook-&gt;ActiveSheet;

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

$tempFilePath = __DIR__ .&#39;/output.xlsm&#39;;
// Save the modified file
$workbook-&gt;SaveAs($tempFilePath);

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

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

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

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

huangapple
  • 本文由 发表于 2023年5月22日 18:30:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/76305267.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定