jh-admin/extend/Excel.php

<?php

namespace extend;

use PHPExcel_IOFactory;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\OLE\PPS\Root;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;

class Excel
{

//    public function test()
//    {
//        $data = [
//            ['title1' => '111', 'title2' => '111'],
//            ['title1' => '222', 'title2' => '222'],
//            ['title1' => '333', 'title2' => '333']
//        ];
//        $mergeCells = [
//            ['A1:B1'=>'第一行标题','C1:F1'=>'第一111行标题'],
//            ['A2:B2'=>'第一行标题','C2:E2'=>'第一222行标题'],
//        ];
//        $tableHeader = [
//            ['第一行标题','第一行标题'],
//            ['第二行标题', '第二行标题']
//        ];
//        $fileName = "8888.xlsx";
//
//        $data = Excel::saveFile($data, $fileName, $tableHeader,$mergeCells);
//
//        var_dump($data);
//        exit;
//    }

    private static $config = [
        // 默认行高
        'row_height' => 24,
        // 默认列宽
        'column_width' => 18,
        // 字体
        'font_family' => '微软雅黑',
        // 字体大小
        'font_size' => 11,
        // 字体颜色
        'color' => '000000',
        // 垂直对齐方式
        'visibility_align' => Alignment::VERTICAL_CENTER,
        // 水平对齐方式
        'text_align' => Alignment::HORIZONTAL_CENTER,
        // 边框样式
        'border_style' => Border::BORDER_THIN,
        // 边框颜色
        'border_color' => '000000',
        // 分割线填充样式
        'cut_fill_style' => Fill::FILL_SOLID,
        // 分割线填充颜色
        'cut_fill_color' => 'CCCCCC',
        // 是否自动换行
        'is_auto_wrap' => true,
        // 行开始位置
        'row_start_num' => 0,
        // 表头是否冻结
        'header_freeze' => false,
        // 是否筛选
        'auto_filter' => true,
        //是否加粗
        'is_bold' => false,
    ];
    public static $excelCol = ['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',
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];
    public static $setBold = false;
    public static $setName = '宋体';
    public static $setSize = '12';
    public static $setBgRGB = 'FFFFFFFF';
    public static $setFontRGB = 'FF000000';
    public static $styleArray = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['argb' => '000000'],
            ],
        ],
    ];

    public function __construct(array $config = [])
    {
        $this->init($config);
    }

    /**
     * 初始化
     * @param array $config
     */
    private function init(array $config = [])
    {
        self::$config = array_merge(self::$config, $config);
    }

    /**
     * 读取excel
     * @param $filePath
     * @param int $pageIndex
     * @param int $readRow
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public static function read($filePath, $pageIndex = 0, $readRow = 0)
    {
        //加载文件
        $spreadSheet = IOFactory::load($filePath);
        //获取文件内容
        $workSheet = $spreadSheet->getSheet($pageIndex)->toArray('', true, true, false);
        //删除表头几行
        if ($readRow > 0) {
            for ($i = 0; $i < $readRow; $i++) {
                array_shift($workSheet);
            }
        }
        return $workSheet;
    }

    /**
     * 下载文件
     * @param $data
     * @param $fileName
     * @param array $tableHeader
     * @param array $mergeCells
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function download($data, $fileName, $tableHeader = [], $mergeCells = [], $suffix = 'xlsx')
    {
        $spreadsheet = self::write($data, $tableHeader, $mergeCells);
        // 将输出重定向到客户端的网络浏览器(Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '"');//文件名
        header('Cache-Control: max-age=0');
        // 如果你服务于IE 9，那么以下可能是需要的
        header('Cache-Control: max-age=1');
        // 如果您通过SSL为工业工程服务，那么可能需要以下内容
        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, ucwords($suffix));
        $writer->save('php://output');

    }

    /**
     * 保存文件
     * @param $data
     * @param $fileName
     * @param array $tableHeader
     * @param array $mergeCells
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function saveFile($data, $fileName, $tableHeader = [], $mergeCells = [], $suffix = 'xlsx')
    {
        $spreadsheet = self::write($data, $tableHeader, $mergeCells);

        $writer = IOFactory::createWriter($spreadsheet, ucwords($suffix));

//        var_dump(app()->getRootPath().'/'.$fileName);die;
        $writer->save(app()->getRootPath() . $fileName, true);
        return $fileName;
    }

    /**
     * 写入数据
     * @param $data
     * @param $tableHeader
     * @param $mergeCells
     * @return Spreadsheet
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public static function write($data, $tableHeader, $mergeCells)
    {
        // 创建excel对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $totalCol = 0;
        //设置表头合并单元格
        foreach ($mergeCells as $row => $rows) {
            $head_title = array_merge([
                'name' => '默认标题',
                // 是否加粗
                'is_bold' => true,
                // 字体大小
                'font_size' => 20,
                // 字体颜色
                'color' => self::$config['color'],
                // 水平对齐方式
                'text_align' => self::$config['text_align']
            ], $rows);
            $i = 0;
            foreach ($rows as $col => $colValue) {
                //合并单元格
                $sheet->mergeCells($head_title['value']);
                //设置样式
                self::setStyle($sheet, $i, $totalCol, $head_title);
                //单元格内容写入
                $sheet->setCellValue(substr($head_title['value'], 0, strpos($head_title['value'], ":")), $head_title['name']);
                $i++;
            }
        }
        $totalCol += count($mergeCells);
        //设置表头
        foreach ($tableHeader as $row => $rows) {
            $cell_v = array_merge([
                // 字段
                'field' => '',
                // 名称
                'name' => '',
                // 横向合并
                'colspan' => 1,
                // 列宽
                'width' => self::$config['column_width'],
                // 水平对齐方式
                'text_align' => self::$config['text_align'],
            ], $rows);
            $tableHeader[$row] = $cell_v;
            self::setStyle($sheet, $row, $totalCol, $cell_v);
            //单元格内容写入
            $sheet->setCellValue(self::$excelCol[$row] . ($totalCol + 1), $rows['name']);
        }
        $totalCol += 1;
        foreach ($data as $row => $rows) {
            foreach ($tableHeader as $col => $colValue) {
                self::setStyle($sheet, $col, $totalCol + $row, $colValue);
                // 单元格内容写入
                $sheet->setCellValue(self::$excelCol[$col] . ($totalCol + $row + 1), $rows[$colValue['field']]);
            }
        }
        return $spreadsheet;
    }

    /**
     * 设置单元格样式
     * @param $sheet
     * @param $col
     * @param $totalCol
     * @param $row
     */
    public static function setStyle($sheet, $col, $totalCol, $cell_v = [])
    {
        $config = array_merge(self::$config, $cell_v);
        //设置单元格居中
        $sheet->getStyle(self::$excelCol[$col] . ($totalCol + 1))->applyFromArray(self::$styleArray);
        //设置单元格
        $sheet->getStyle(self::$excelCol[$col] . ($totalCol + 1))
            ->getFill()
            ->setFillType(self::$config['cut_fill_style'])
            ->getStartColor()
            ->setRGB(self::$setBgRGB); //背景色

        if (isset($config['width'])) {
            //设置行间距
            $sheet->getColumnDimension(self::$excelCol[$col])
                ->setWidth($config['width']);
        }
        if (isset($config['row_height'])) {
            //设置行高度
            $sheet->getRowDimension(($totalCol + 1))->setRowHeight($config['row_height']);
        }
        //设置单元格字体样式、字体、字体大小
        $sheet->getStyle(self::$excelCol[$col] . ($totalCol + 1))
            ->getFont()
            ->setBold($config['is_bold'])
            ->setName(self::$setName)
            ->setSize($config['font_size']);
        //设置字体颜色
        $sheet->getStyle(self::$excelCol[$col] . ($totalCol + 1))
            ->getFont()
            ->getColor()->setRGB($config['color']);
    }
}