在 PhpSpreadsheet 的评论中添加图片 [英] Add Pictures to comments in PhpSpreadsheet

查看:156
本文介绍了在 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆