在 PhpSpreadsheet 的评论中添加图片 [英] Add Pictures to comments in PhpSpreadsheet
问题描述
我在 PhpSpreadsheet 中有一条评论,我想为其添加背景图片.评论是这样创建的:
$spreadsheet->getActiveSheet()->getComment($cell)->getText()->createTextRun('Test text');
但是文档中没有关于如何向其添加背景图片的内容.
我能够通过(不好但有效的)变通方法解决我的问题.PHPSpreadsheet 似乎还不能(还)为评论创建背景图片,也许 PHPSpreadsheet 将来可以做到.
解决方法:PHPSpreadsheet 无法创建带有背景图像的评论,但 Powershell 可以.使用以下 Powershell 脚本,您将向工作表 1 中 C2 行的注释添加背景图像.
$excel = new-object -comobject excel.application$workbook = $excel.workbooks.open("C:\MyExcel.xlsx")$worksheet = $workbook.worksheets.item(1)$image = "C:\MyPicture.png"$worksheet.Range("C2").AddComment(" ")$worksheet.Range("C2").Comment.Shape.Fill.UserPicture($image)$workbook.save()$workbook.close()停止进程 -processname powershell*
有了这些知识,您就可以在 PHP 中创建一个动态的 Powershell 脚本:
$ps_script=Array();$ps_script[]='$excel = new-object -comobject excel.application';$ps_script[]='$workbook = $excel.workbooks.open("'.$excel_output_path.'")';$ps_script[]='$worksheet = $workbook.worksheets.item('.($sheetindex+1).')';$ps_script[]='$image = "'.$img_path.'"';$ps_script[]='$worksheet.Range("'.$cell.'").AddComment(" ")';$ps_script[]='$worksheet.Range("'.$cell.'").Comment.Shape.Fill.UserPicture($image)';$ps_script[]='$workbook.save()';$ps_script[]='$workbook.close()';$ps_script[]='Stop-Process -processname powershell*';$ps_script = implode("\r\n", $ps_script);file_put_contents($powershell_output, $ps_script);
现在我们创建一个 CMD 脚本,调用那个 Powershell 脚本:
$call_powershell_script=Array();$call_powershell_script[]="TASKKILL/F/IM powershell.exe";$call_powershell_script[]="TASKKILL/F/IM EXCEL.exe";$call_powershell_script[]=$powershell_path.'powershell.exe "'.$powershell_output.'"';$call_powershell_script[]="TASKKILL/F/IM powershell.exe";$call_powershell_script[]="TASKKILL/F/IM EXCEL.exe";$call_powershell_script = implode("\r\n", $call_powershell_script);file_put_contents($call_powershell_script_path, $call_powershell_script);
最后我们在 PHP 中调用这个 CMD 脚本:
$output = shell_exec($cpowershell_script_path." 2>&1");echo "
";回声$输出;echo "
";
注意:通常 Powershell 脚本不需要这一行:
Stop-Process -processname powershell*
但没有它,PHP 脚本将永远运行.
注意 2:我在 Powershell 中收到一个错误,当脚本由 PHP 运行时,我的 Excel 文件无法访问:
使用1"参数调用Open"的异常:Microsoft Office Excel 无法访问文件‘C:\MyExcel.xlsx’.有几个可能的原因:文件名或路径不存在.文件正被另一个程序使用.您尝试保存的工作簿与当前打开的工作簿同名.
找到这个错误的解决方法此处.>
I have a comment in PhpSpreadsheet and I want to add a backgroundpicture to it. The comment is created like this:
$spreadsheet->getActiveSheet()->getComment($cell)->getText()->createTextRun('Test text');
But there is nothing in the documentation about how to add a backgroundpicture to it.
I was able to solve my issue with a (bad, but working) workaround. It seems that PHPSpreadsheet is not able (yet) to create a backgroundpicture to a comment, maybe PHPSpreadsheet can do it in the future.
Workaround: PHPSpreadsheet is not able to create a comment with backgroundimage, but Powershell is. With the following Powershell script you are adding a background image to a comment in row C2, in worksheet 1.
$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("C:\MyExcel.xlsx")
$worksheet = $workbook.worksheets.item(1)
$image = "C:\MyPicture.png"
$worksheet.Range("C2").AddComment(" ")
$worksheet.Range("C2").Comment.Shape.Fill.UserPicture($image)
$workbook.save()
$workbook.close()
Stop-Process -processname powershell*
With that knowledge, you are able to create a dynamically Powershell script inside PHP:
$ps_script=Array();
$ps_script[]='$excel = new-object -comobject excel.application';
$ps_script[]='$workbook = $excel.workbooks.open("'.$excel_output_path.'")';
$ps_script[]='$worksheet = $workbook.worksheets.item('.($sheetindex+1).')';
$ps_script[]='$image = "'.$img_path.'"';
$ps_script[]='$worksheet.Range("'.$cell.'").AddComment(" ")';
$ps_script[]='$worksheet.Range("'.$cell.'").Comment.Shape.Fill.UserPicture($image)';
$ps_script[]='$workbook.save()';
$ps_script[]='$workbook.close()';
$ps_script[]='Stop-Process -processname powershell*';
$ps_script = implode("\r\n", $ps_script);
file_put_contents($powershell_output, $ps_script);
Now we create a CMD script, calling that Powershell script:
$call_powershell_script=Array();
$call_powershell_script[]="TASKKILL /F /IM powershell.exe";
$call_powershell_script[]="TASKKILL /F /IM EXCEL.exe";
$call_powershell_script[]=$powershell_path.'powershell.exe "'.$powershell_output.'"';
$call_powershell_script[]="TASKKILL /F /IM powershell.exe";
$call_powershell_script[]="TASKKILL /F /IM EXCEL.exe";
$call_powershell_script = implode("\r\n", $call_powershell_script);
file_put_contents($call_powershell_script_path, $call_powershell_script);
And finally we call this CMD script inside PHP:
$output = shell_exec($cpowershell_script_path." 2>&1");
echo "<br>";
echo $output;
echo "<br>";
Note: Normally the Powershell script doesn't need this line:
Stop-Process -processname powershell*
But without it, the PHP script takes forever.
Note2: I received an error in Powershell, when the script was run by PHP, that my Excel file was not accessable:
Exception calling "Open" with "1" argument(s): "Microsoft Office Excel cannot access the file 'C:\MyExcel.xlsx'. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook.
The solution for this error was found here.
这篇关于在 PhpSpreadsheet 的评论中添加图片的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!