Laravel中用phpspreadsheet在Excel两单元格间绘制直线的方法

你之前用Drawing类的方式有误，这个类是用于插入图片的，要绘制直线需使用PhpSpreadsheet内置的Line形状类。以下是具体实现方案：

直接使用PhpSpreadsheet的实现代码 #

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Shape\Line;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

// 初始化Spreadsheet和工作表
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

// 设置单元格内容
$worksheet->setCellValue('A1', 'Cell 1');
$worksheet->setCellValue('D4', 'Cell 2');

// 定义要连接的两个单元格
$startCell = 'B4';
$endCell = 'F9';

// 解析单元格的列和行
[$startCol, $startRow] = Coordinate::coordinateFromString($startCell);
[$endCol, $endRow] = Coordinate::coordinateFromString($endCell);

// 计算起点像素坐标（单元格左上角）
$startX = 0;
for ($col = 'A'; $col < $startCol; $col++) {
    // 默认1单位列宽对应9.5像素
    $startX += $worksheet->getColumnDimension($col)->getWidth() * 9.5;
}
$startY = 0;
for ($row = 1; $row < $startRow; $row++) {
    $startY += $worksheet->getRowDimension($row)->getRowHeight();
}

// 计算终点像素坐标（单元格左上角）
$endX = 0;
for ($col = 'A'; $col < $endCol; $col++) {
    $endX += $worksheet->getColumnDimension($col)->getWidth() * 9.5;
}
$endY = 0;
for ($row = 1; $row < $endRow; $row++) {
    $endY += $worksheet->getRowDimension($row)->getRowHeight();
}

// 如需连接单元格中心，取消下面注释
// $startX += ($worksheet->getColumnDimension($startCol)->getWidth() * 9.5) / 2;
// $startY += $worksheet->getRowDimension($startRow)->getRowHeight() / 2;
// $endX += ($worksheet->getColumnDimension($endCol)->getWidth() * 9.5) / 2;
// $endY += $worksheet->getRowDimension($endRow)->getRowHeight() / 2;

// 创建直线并设置样式
$line = new Line();
$line->setStartPoint($startX, $startY);
$line->setEndPoint($endX, $endY);
// 设置线条颜色为黑色
$line->getStyle()->getLine()->setColor(new Color(Color::COLOR_BLACK));
// 设置线条粗细
$line->getStyle()->getLine()->setWidth(2);

// 将直线添加到工作表
$worksheet->addShape($line);

// 保存Excel文件
$writer = new Xlsx($spreadsheet);
$writer->save('map.xlsx');

推荐的Laravel友好封装包 #

可以使用maatwebsite/laravel-excel（Laravel Excel），它基于PhpSpreadsheet开发，对Laravel做了更友好的封装，适合Laravel项目快速实现导出需求。

示例导出类代码 #

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Shape\Line;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class MapExport implements WithEvents
{
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $worksheet = $event->sheet->getDelegate();

                // 定义要连接的单元格
                $startCell = 'B4';
                $endCell = 'F9';

                // 解析单元格列和行
                [$startCol, $startRow] = Coordinate::coordinateFromString($startCell);
                [$endCol, $endRow] = Coordinate::coordinateFromString($endCell);

                // 计算起点坐标
                $startX = 0;
                for ($col = 'A'; $col < $startCol; $col++) {
                    $startX += $worksheet->getColumnDimension($col)->getWidth() * 9.5;
                }
                $startY = 0;
                for ($row = 1; $row < $startRow; $row++) {
                    $startY += $worksheet->getRowDimension($row)->getRowHeight();
                }

                // 计算终点坐标
                $endX = 0;
                for ($col = 'A'; $col < $endCol; $col++) {
                    $endX += $worksheet->getColumnDimension($col)->getWidth() * 9.5;
                }
                $endY = 0;
                for ($row = 1; $row < $endRow; $row++) {
                    $endY += $worksheet->getRowDimension($row)->getRowHeight();
                }

                // 创建直线
                $line = new Line();
                $line->setStartPoint($startX, $startY);
                $line->setEndPoint($endX, $endY);
                $line->getStyle()->getLine()->setColor(new Color(Color::COLOR_BLACK));
                $line->getStyle()->getLine()->setWidth(2);

                $worksheet->addShape($line);

                // 设置单元格值
                $worksheet->setCellValue('A1', 'Cell 1');
                $worksheet->setCellValue('D4', 'Cell 2');
            },
        ];
    }
}

控制器中调用导出 #

use App\Exports\MapExport;
use Maatwebsite\Excel\Facades\Excel;

public function export()
{
    return Excel::download(new MapExport(), 'map.xlsx');
}

内容的提问来源于stack exchange，提问作者Mina Zakaria