Laravel 使用 PhpSpreadsheet處理Excel

1. 引入類庫

   composer require phpoffice/phpspreadsheet

2. 使用類庫讀取

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);

    }

           

3.使用類庫寫入

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');

    }