[PHP知识库]Laravel 使用 PhpSpreadsheet处理Excel

Laravel 使用 PhpSpreadsheet处理Excel

引入类库

composer require phpoffice/phpspreadsheet

创建路由

//创建控制器
php artisan make:controller ExcelController
//填写路由
Route::get('excel','ExcelController@excelRead');
Route::get('excelWrite','ExcelController@excelWrite');

读取excel

public function excelRead() {
        $inputFileType = 'Xls';
        $inputFileName = storage_path() . DIRECTORY_SEPARATOR  .'123.xls';

        # 根据excel文件格式读取文件
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

        // 只读模式
        $reader->setReadDataOnly(true);

        # 加载文件
        $spreadsheet = $reader->load($inputFileName);


        # 获取所有的sheet
        $worksheetData = $reader->listWorksheetInfo($inputFileName);

        //获取指定sheet
        $sheet = $spreadsheet->getSheet(0);

        # 获取当前使用sheet
//        $sheet = $spreadsheet -> getActiveSheet();

        // 取得总行数
        $highestRow = $sheet->getHighestRow();
        // 取得总列数
        $highestColumn = $sheet->getHighestColumn();

        //获取表格所有数据
        $dataInfo = $sheet->toArray();

        //获取指定表格的内容
        $cellValue = $sheet->getCell("A1")->getValue();

        //指定区域内容  默认属性会补全不存在地方返回null
        $sheetData = $sheet->rangeToArray('A1:C5');

        //遍历所有的内容
        $data = array();
        $i = 0;
        foreach ($sheet->getRowIterator() as $row) {
            //键为行1 2 3
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(FALSE); //TRUE只有有值的单元格
            foreach ($cellIterator as $cell) {
                //键为列A B C
                $data[$i][] = $cell->getValue();
            }
            $i++;
        }
        dd($data);

    }

写Excel

public function excelWrite() {
        $arr = [
            [
                'name'  => '张国',
                'sex'   => '男',
                'age'   => '16'
            ],
            [
                'name'  => '杨荣',
                'sex'   => '女',
                'age'   => '17'
            ],
        ];
        $spreadSheet = new Spreadsheet();
        $sheet = $spreadSheet->getActiveSheet();

        //设置指定单元格内容
        $sheet->setCellValue('A1', '姓名');
        $sheet->setCellValue('B1', '性别');
        $sheet->setCellValue('C1', '年龄');

        //设置单元格样式 设置加粗、字体、字体大小
        $sheet->getStyle('B1:C1')->getFont()->setBold(true)->setName('Arial')->setSize(10);

        //设置字体颜色
        $sheet->getStyle('A2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);

        //设置列宽
        $sheet->getColumnDimension('A')->setWidth(30);
        //设置行高
        $sheet->getRowDimension(3)->setRowHeight(100);

        //自动宽度
        $sheet->getColumnDimension('B')->setAutoSize(true);

        //设置默认宽度
        $sheet->getDefaultColumnDimension()->setWidth(12);
        //设置默认行高
        $sheet->getDefaultRowDimension()->setRowHeight(15);

        //使用函数
//        $sheet->setCellValue('B10', '=SUM(B5:C5)');

        //设置超链接
        $sheet->getCell('A2')->getHyperlink()->setUrl('http://www.baidu.com');

        //换行
        $sheet->setCellValue('A5',"换\n行");
        $sheet->getStyle('A5')->getAlignment()->setWrapText(true);

        //设置表格标题
        $sheet->setTitle('花名册');

        //样式
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,//水平居中
            ],
            'borders' => [
                'allBorders' =>[
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                    'color' => ['argb' => 'FFFF0000'],
                ],
            ],
        ];
        $sheet->getStyle('A1:C5')->applyFromArray($styleArray);

        $row = 2;
        foreach ($arr as $key => $value) {
            $column = 1;
            foreach ($value as $k => $v){
                $sheet->setCellValueByColumnAndRow($column, $row, $v);
                $column++;
            }
            $row++;
        }


        $writer = new Xls($spreadSheet);
        $writer->save(storage_path() . DIRECTORY_SEPARATOR . '花名册.xls');

    }