PhpSpreadsheet处理Excel 导入、导出并设置样式

PhpSpreadsheet处理Excel

PHPOffice
是一个用来处理办公相关软件的php类库

PhpSpreadsheet
专门处理excle的类库
导出如下：

引入类库

composer require maatwebsite/excel
（"maatwebsite/excel": "^3.1"）

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

public static function excelWriter($fileName, $style = 1, $columnName = null, $data = null, $sheetIndex = 0, $cell = null)
    {
        $spreadsheet = new Spreadsheet();
        //默认为26列，如需要再自定义加
        if (!isset($cell)) {
            $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');
        }
        $sheet = $spreadsheet->getActiveSheet();

        //设置excel的sheet，默认0
        $spreadsheet->setActiveSheetIndex($sheetIndex);

        switch ($style) {
            case 1:
                //常规格式
                self::setExcelStyleOne($sheet, $columnName, $data, $cell);
                break;
            case 2:
                //特别格式
                self::setExcelStyleTwo($sheet, $cell);
                break;
        }

        //下载输出excel页面
//        // Redirect output to a client’s web browser (Excel5)
//        header('Content-Type: application/vnd.ms-excel');
//        header('Content-Disposition: attachment;filename="' . $fileName . '"');
//        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

        // 保存到本地服务器文件
        $filename = date('YmdHis') . '-' . $fileName . '-' . uniqid() . '.xlsx';
        $path = public_path() . '/download/excel/' . date('Ym') . '/';
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
//        File::makeDirectory($path);

        File::makeDirectory($path, $mode = 0777, true, true);
        $objWriter->save($path . $filename);
        $res = array(
            'path' => $path,
            'filename' => $filename
        );
        return $res;
        //$objWriter->save('php://output');
    }

    protected static function setExcelStyleOne($sheet, $columnName, $data, $cell)
    {
        //列表标题
        //$columnName = array('标题1','标题2');
        $count = count($columnName);
        //列表标题=>输入到excel第一行中
        foreach ($columnName as $k => $v) {
            $sheet->setCellValue($cell[$k] . '1', $v);
        }

        //列表标题对应的数据=>输入到excel第二行以下
        foreach ($data as $key => $value) {
            $i = $key + 1;
            for ($j = 0; $j < $count; $j++) {
                $sheet->setCellValue($cell[$j] . ($i + 1), $value[$j]);
            }
        }

    }

    protected static function setExcelStyleTwo($sheet, $cell)
    {
        //样式设置 - 水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ],
        ];
        $sheet->getStyle('A1:H8')->applyFromArray($styleArray);

        //样式设置 - 字体
        $sheet->getStyle('A10:A24')->getFont()->setBold(true)->setName('Arial')->setSize(10);
        $sheet->getStyle('A18:A24')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); //设置单元格A1的字体颜色

        //样式设置 - 边框
        $styleArray = [
            'borders' => [
                'outline' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
                'inside' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ]
            ],
        ];
        $sheet->getStyle('A1:F2')->applyFromArray($styleArray);

        //设置宽度
        foreach ($cell as $k => $v) {
            $sheet->getColumnDimension($v)->setWidth(13);
        }
        $sheet->getColumnDimension('A')->setWidth(20);

        //样式设置 - 合并和拆分
        $sheet->mergeCells('A1:A2'); //合并单元格
        //$sheet -> unmergeCells('C3:G3'); //拆分单元格
        $sheet->setCellValue('A1', '人员概况');

        $column_arr = array('B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M');
        $sheet->setCellValue('A10', '人员对比(正式工+毕业生)');
        foreach ($column_arr as $key => $val) {
            $mon = $key + 1;
            $sheet->setCellValue($column_arr[$key] . '10', $mon . '月');
        }
        $sheet->setCellValue('N10', '合计');
        $sheet->setCellValue('O10', '平均');

        $data_arr = array('B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O');
        $data_row_arr = array('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24');
        $sheet->setCellValue('A11', '2019年人数(不含外包派遣)');
        foreach ($data_row_arr as $kkk => $vvv) {
            foreach ($data_arr as $kk => $vv) {
                $sheet->setCellValue($data_arr[$kk] . $vvv, 0);
            }
        }

    }

    导入如下：

    use PhpOffice\PhpSpreadsheet\IOFactory;

    public function upload(Request $request)
    {
        $file = $request->file('file');
        t_log($file);
        if ($file->isValid()) {
            // 获取文件相关信息
            $originalName = $file->getClientOriginalName(); // 文件原名
            $ext = $file->getClientOriginalExtension();     // 扩展名
            $realPath = $file->getRealPath();   //临时文件的绝对路径
            $type = $file->getClientMimeType();     // image/jpeg
            if (!in_array($ext, ['xls', 'xlsx', 'csv'])) {
                return error('文件类型错误！');
            }
            // 上传文件
            $filename = date('YmdHis') . '-' . uniqid() . '.' . $ext;
            // 使用我们新建的uploads本地存储空间（目录）
            //这里的uploads是配置文件的名称

            //把临时文件移动到指定的位置，并重命名
            $path = public_path() . '/uploads/excel/' . date('Ym') . '/';

            $bool = $file->move($path, $filename);

            if ($bool) {
                $img_path = public_path() . '/uploads/excel/' . date('Ym') . '/' . $filename;
                $res = $this->_readExcel($img_path);
                if ($res === true) {
                    return success('上传存储成功');
                } else {
                    return success($res);
                }
            } else {
                return success('上传文件失败');
            }

        }

        return success('没有获取到文件');
    }

    private function _readExcel($filePath = '')
    {
        $data = array();
        $end_row = null;

        $objPHPExcelReader = IOFactory::load($filePath);
        $sheet = $objPHPExcelReader->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得最大行数
        $highestColumn = $sheet->getHighestColumn(); // 取得最大列数

        $arr = ['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', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'ZZ']; // 目标列标号

        $start_row = 2;//从第二行开始
        $row_arr = [];

        for ($column = 0; $arr[$column] != 'ZZ'; $column++) {
            $vale = $sheet->getCellByColumnAndRow($column, 1)->getValue();
            if (is_null($vale)) {
                continue;
            }
            $temp_keys[] = $vale;
        }
        $value_key = WorkersService::getReportField(WorkersService::MAIN, true);
        //当前操作人的组织id
        $org_id = $this->admin->org_id;
        for ($row = $start_row; $row <= $highestRow; $row++) { // 一次读取一列
            $row_arr_one = $row_arr_other = array();
            for ($column = 0; $arr[$column] != 'ZZ'; $column++) {
                $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                if (is_null($val)) {
                    continue;
                }
                if (isset($value_key[$temp_keys[$column]])) {
                    $keys = $value_key[$temp_keys[$column]];
                    //$row_arr_one判断两个唯一字段是user_id，user_name否表中已存在的数据
                    if($keys=='user_id'){
                        $row_arr_one[$keys] = $val;
                    }
                    if($keys=='user_name'){
                        $row_arr_one[$keys] = $val;
                    }
                    $row_arr_one['org_id'] = $org_id;

                    if(in_array($keys,['entry_date','trial_end','first_time','quit_time'])){
                        $row_arr_other[$keys] =$this->changeTime($val);
                    }else{
                        //有则更新的数组
                        $row_arr_other[$keys]  = $val;
                    }
                    unset($row_arr_other['user_id']);
                    unset($row_arr_other['user_name']);
                }

            }
            DB::connection('kg_report')->table('workers')->updateOrInsert($row_arr_one, $row_arr_other);

        }

        return true;
    }