使用PhpSpreadsheet操作Excle

使用PhpSpreadsheet操作Excle

导出表格

$data = [
                ['开团日期', '厂家', '单位', '产品名称', '报价截图', '备注']
            ];
foreach ($list as $item) {
    $data[] = [
        $item['create_time_text'],//开团日期
        $item['supplier_name'],//厂家
        $item['unit_conversion'],//单位
        $item['product_name'],//产品名称
        $item['remark'],//备注
        $item['count'],//开团次数,仅用于渲染颜色
    ];
}

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

//设置单元格内容
$sheet->setCellValueByColumnAndRow(1, 1, '厂家结算规则');
$row = 3; // 从第二行开始
foreach ($data as $item) {
    $dataCol = 'A';

    //开团次数大于1次时产品名称改变颜色
    $row > 3 && $item[13] > 1 && $sheet->getStyle("D{$row}")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('ffc7ce');

    foreach ($item as $value) {
        // 单元格内容写入
        if ($dataCol === 'L' && $row > 3 && $value) {
            //图片转存至本地
            $res_match = preg_match('/^(.+)\/(.+)$/', $value, $matches);
            if (!$res_match) return view('common@common/wx', ['msg' => "团品{$item[3]}报价截图{$value}格式有误"]);
            $filePath = RUNTIME_PATH . "img/{$matches[2]}";
            $image = file_get_contents($value);
            $res_save = file_put_contents($filePath, $image);
            if (!$res_save) return view('common@common/wx', ['msg' => "团品{$item[3]}报价截图{$value}转存至本地失败"]);

            //图像写入操作
            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $drawing->setWorksheet($sheet);
            $drawing->setPath($filePath);
            $drawing->setWidth(30);//图片宽
            $drawing->setHeight(30);//图片高
            $drawing->setOffsetX(10);//设置图片偏移量
            $drawing->setCoordinates("{$dataCol}{$row}");//将图片放置于单元格
        } else {
            $sheet->setCellValue($dataCol . $row, $value);
        }
        $dataCol++;

        if ($dataCol > 'L') break;
    }
    $row++;
}

$count_line = sizeof($data) + 2;
//设置字体
$sheet->getStyle("A1:M2")->getFont()->setBold(true)->setName('宋体')->setSize(20);
$sheet->getStyle("A3:M3")->getFont()->setBold(true)->setName('宋体')->setSize(11);
$sheet->getStyle("A3:M{$count_line}")->getFont()->setName('宋体')->setSize(11);
//设置颜色
$sheet->getStyle("A1:M2")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('ffff00');
$spreadsheet->getActiveSheet()->mergeCells('A1:M2');//合并表头
$styleArray = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\style\Border::BORDER_THIN //细边框
        ]
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
    ],
];
$sheet->getStyle("A1:M{$count_line}")->applyFromArray($styleArray);//设置边框和居中
$sheet->getStyle("J2:J{$count_line}")->getAlignment()->setWrapText(true);//自动换行
$sheet->getStyle("K2:K{$count_line}")->getAlignment()->setWrapText(true);//自动换行
$sheet->getStyle("M2:M{$count_line}")->getAlignment()->setWrapText(true);//自动换行
$sheet->getDefaultColumnDimension()->setWidth(15);//设置默认列宽
$sheet->getColumnDimension('D')->setWidth(50);//设置列宽
//设置行高
for ($i = 1; $i <= $count_line; $i++) {
    $sheet->getRowDimension($i)->setRowHeight(23);
}

// Redirect output to a client’s web browser (Xlsx)
$time_now = date('Y-m-d-H-i-s');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=团品监控_{$time_now}.xlsx");
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;

• 本文作者： 小蜗牛
• 本文链接： https://vitaminvi.github.io/2021/10/15/使用PhpSpreadsheet操作Excle/
• 版权声明： 本博客所有文章除特别声明外，均采用 MIT 许可协议。转载请注明出处！