phpspreadsheet 导出类

华疯子

已于 2022-11-24 16:05:28 修改

阅读量155

点赞数

分类专栏： php常用操作文章标签： php

于 2022-11-24 15:30:58 首次发布

本文链接：https://blog.csdn.net/weixin_43247563/article/details/128019578

版权

php常用操作专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文介绍了如何使用PHP的PhpSpreadsheet库创建和导出Excel表格，包括设置表头、写入数据、合并单元格，以及自定义样式。通过实例展示了如何根据给定的数据和合并规则生成Excel文件并下载。

摘要由CSDN通过智能技术生成

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class PHPReadSheet
{
    protected $cell = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');


    /**
     * Notes:导出
     * Date: 2022/11/23 16:18
     *
     * @param string $fileName  文件名
     * @param array  $title     文件表头 [[10,'zzz']]
     * @param array  $data      数据   [[10,'zzz']]
     * @param array  $merge     合并数组 ['1-1:1-2','2-1:3-1']
     *                          '1-1:1-2': A1:A2 行合并
     *                          '2-1:3-1': B1:C1 列合并
     * @param int    $deep      数据层级
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function export(string $fileName, array $title, array $data, array $merge=[], $deep = 2)
    {
        $spreadSheet = new  Spreadsheet();
        $spreadSheet->setActiveSheetIndex(0);
        $sheet      = $spreadSheet->getActiveSheet();
        $titleCount = count($title[0]);
        //获取标头cellNmae
        $cellName = $this->getCellName($titleCount);
        //行下表
        $index = 1;
        sort($title);
        //表头写入
        foreach ($title as $tk => $tv) {
            foreach (array_values($tv) as $ttk => $ttv) {
                $sheet->setCellValue($cellName[$ttk] . $index, $ttv);
            }
            $index++;
        }
        //写入数据
        $dindex = $index;
        if ($deep == 2) {
            foreach ($data as $k => $v) {
                foreach (array_values($v) as $vk => $item) {
                    $sheet->setCellValue($cellName[$vk] . $dindex, $item);
                }
                $dindex += 1;
            }
        } else {
            foreach ($data as $v) {
                foreach ($v as $item) {
                    foreach (array_values($item) as $k => $value) {
                        $sheet->setCellValue($cellName[$k] . $dindex, $value);
                    }
                    $dindex += 1;
                }
            }
        }

        if ($merge) {
            //获取合并信息
            foreach ($merge as $mv) {
                //切分数据
                $mArr     = explode(':', $mv);
                $mergeStr = '';
                foreach ($mArr as $mmv) {
                    $cell = explode('-', $mmv);
                    //合并字符串
                    $mergeStr .= $cellName[($cell[0] - 1)] . $cell[1] . ':';
                }
                $mergeStr = trim($mergeStr, ':');
                $sheet->mergeCells($mergeStr);
            }
        }

		// 字体    
        $spreadSheet->getDefaultStyle()->getFont()->setName('宋体'); 
        // 字体大小                               
        $spreadSheet->getDefaultStyle()->getFont()->setSize(12);
         // 自动换行                               
        $spreadSheet->getDefaultStyle()->getAlignment()->setWrapText(true);                        
        // 设置垂直居中 style中范围'A1:Z12'
        $spreadSheet->getActiveSheet()->getStyle("A1:" . ($cellName[$titleCount - 1]) . $dindex)
            ->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

        // 设置水平居中
        $spreadSheet->getActiveSheet()->getStyle("A1:" . ($cellName[$titleCount - 1]) . $dindex)
            ->getAlignment()->setHorizontal(Alignment::VERTICAL_CENTER);
        //按日期命名
        $fileName = $fileName . date('Y-m-d');
        //写入数据
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadSheet, 'Xlsx');
        $writer->save('php://output');
    }

    /**
     * Notes:获取数据的列
     * Date: 2022/11/23 10:09
     *
     * @param int $titleCount 标题长度
     *
     * @return array
     */
    protected function getCellName(int $titleCount): array
    {
        $cellName = [];
        for ($i = 0; $i < $titleCount; $i++) {
            if ($i < 26) {
                $cellName[] = $this->cell[$i];
            } else {
                $firstNum   = floor($i / 25);
                $secondNum  = $i % 25;
                $cellName[] = $this->cell[$firstNum - 1] . $this->cell[$secondNum - 1];

            }
        }
        return $cellName;
    }

}