PhpSpreadsheet常用操作

向右看齐 2023-10-09 11:17 16阅读 0赞

php用PhpSpreadsheet对Excel进行读取、写入、修改十分便捷，下面将我在工作中用到过的操作进行总结。文档参考百度搜索和原始文档：[https://phpspreadsheet.readthedocs.io/en/latest/][https_phpspreadsheet.readthedocs.io_en_latest]（只有英文版）  
首先要引入php模块: **composer require phpoffice/phpspreadsheet**  
1、读取Excel：见文章[PhpSpreadsheet读取单元格内容的坑][PhpSpreadsheet]  
2、修改Excel：示例代码（只保留了基础代码）：

$this->view->title = '填写Excel';
    $abs_excel_path = $_SERVER['DOCUMENT_ROOT'] . '/data_be_writen.xlsx';
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($abs_excel_path);
    $count = 0;
    // 三个sheet都进行修改
    for ($sheetIndex=0; $sheetIndex<3; $sheetIndex++) { 
        $worksheet = $spreadsheet->getSheet($sheetIndex);
        $highestRow = $worksheet->getHighestRow(); // 总行数1, 2，3
        for ($rowIndex = 1; $rowIndex <= $highestRow; $rowIndex++) { 
        	$count++;
        	// 写入的数据只是个示例，具体项目中根据需求获取数值写入
            $worksheet->getCellByColumnAndRow(1, $rowIndex)->setValue($rowIndex);
            $worksheet->getCellByColumnAndRow(2, $rowIndex)->setValue($rowIndex * 10);
        }
    }
    echo "填写了${count}条数据";
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save($abs_excel_path);

3、写入Excel，下面的代码提现了一个导出账号的过程，是基于ThinkPHP5.0的：

$head = ['部门', '姓名', '职位', '手机号', '邮箱'];
    $count = count($head);
    
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
    
    $worksheet->getColumnDimension('A')->setWidth(20);
    $worksheet->getColumnDimension('B')->setWidth(12);
    $worksheet->getColumnDimension('C')->setWidth(12);
    $worksheet->getColumnDimension('D')->setWidth(18);
    $worksheet->getColumnDimension('E')->setWidth(20);
    
    $rowIndex = 1;
    foreach ($head as $colIndex => $title) { 
        $worksheet->getCellByColumnAndRow($colIndex + 1, $rowIndex)->setValue($title);
    }
    
    $admins = $this->order('id asc')->select();
    foreach ($admins as $admin) { 
        $rowIndex++;
        $worksheet->getCellByColumnAndRow(1, $rowIndex)->setValue(model('Department')->get_name_by_id($admin['department_id']));
        $worksheet->getCellByColumnAndRow(2, $rowIndex)->setValue($admin['username']);
        $worksheet->getCellByColumnAndRow(3, $rowIndex)->setValue($admin['position']);
        $worksheet->getCellByColumnAndRow(4, $rowIndex)->setValue($admin['phone']);
        $worksheet->getCellByColumnAndRow(5, $rowIndex)->setValue($admin['email']);
    }
    
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="账号.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    
    //删除清空：
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
    exit;

4、修改单元格样式的，代码进行了精简，具体要标记哪个，根据项目实际判断：

$this->view->title = '填写Excel';
    $abs_excel_path = $_SERVER['DOCUMENT_ROOT'] . '/2.xlsx';
    $styleArray = [
        'borders' => [
            'outline' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                'color' => ['argb' => 'FFFF0000'],
            ],
        ],
    ];
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($abs_excel_path);
    $count = 0;
    for ($sheetIndex=0; $sheetIndex<3; $sheetIndex++) { 
        $worksheet = $spreadsheet->getSheet($sheetIndex);
        $highestRow = $worksheet->getHighestRow(); // 总行数1, 2，3
        $excel_data = new \app\admin\model\ExcelData2;
        for ($rowIndex = 2; $rowIndex <= $highestRow; $rowIndex++) { 
            $worksheet->getStyle("B{ $rowIndex}")->applyFromArray($styleArray);
        }
    }
    echo "发现了${count}条数据";
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save($_SERVER['DOCUMENT_ROOT'] . '/2标记.xlsx');

注意：对比2、4可以发现：读取了Excel以后，如果要改写，只需要两行代码，在save的时候填写的绝对路径跟读取的一样，则为改写，如果不同，则为新建。

[https_phpspreadsheet.readthedocs.io_en_latest]: https://phpspreadsheet.readthedocs.io/en/latest/
[PhpSpreadsheet]: https://blog.csdn.net/canlynetsky/article/details/101344773