使用PhpSpreadsheet对Excel文件进行操作

男娘i 2021-12-05 13:07 608阅读 0赞

--------------------

### 环境要求 ###

*  PHP 5.6或更高版本
 *  PHP扩展php\_zip
 *  PHP扩展php\_xml
 *  PHP扩展php\_gd2

--------------------

### 安装 ###

使用composer将PhpSpreadsheet安装到您的项目中：

composer require phpoffice/phpspreadsheet

composer的安装可以参考：[这里][Link 1]

--------------------

### 使用 ###

引入PhpSpreadsheet：

require 'vendor/autoload.php';
    
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

输出一个Hello World工作表：

$spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet -> setCellValue('A1', 'Hello World !');
    
    $writer = new Xlsx($spreadsheet);
    $writer -> save('hello world.xlsx');

读取一个工作表的所有内容：

$inputFileName = '1.xlsx';
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
    $countSheet = $spreadsheet -> getSheetCount();
    for ($i = 0; $i < $countSheet; $i++)
    {
        $currentSheet = $spreadsheet -> getSheet($i);
        $dataArray[$i] = $currentSheet -> toArray();
        
        print_r($dataArray[$i]);
    }

编辑工作表内容：

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); //创建一个新的工作表
    $worksheet = $spreadsheet->getActiveSheet();
    
    //样式设置 - 字体
    $worksheet -> getStyle('A1:G10') -> getFont() 
               -> setBold(true) -> setName('Arial') 
               -> setSize(10); //设置单元格A7:G10的字体样式
               
    $worksheet -> getStyle('A1') -> getFont() 
               -> getColor() -> setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); //设置单元格A1的字体颜色
               
    $spreadsheet -> getDefaultStyle()->getFont()->setName('微软雅黑'); //修改默认字体
    
    //样式设置 - 列宽
    $worksheet -> getColumnDimension('A') -> setWidth(30); //设置A列宽度为30
    $worksheet -> getColumnDimension('B') -> setAutoSize(true); //自动设置B列宽度
    $worksheet -> getDefaultColumnDimension() -> setWidth(12); //设置默认列宽为12
    
    //样式设置 - 行高
    $worksheet -> getRowDimension('10') -> setRowHeight(100); //设置第10行高度为100
    $worksheet -> getDefaultRowDimension() -> setRowHeight(15); //设置默认行高为15
    
    //样式设置 - 水平、垂直居中
    $styleArray = [
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
        ],
    ];
    $worksheet -> getStyle('A1:G10') -> applyFromArray($styleArray);
    
    //样式设置 - 边框
    $styleArray = [
        'borders' => [
            'outline' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                'color' => ['argb' => 'FF0000'],
            ],
            'inside' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            ]
        ],
    ];
    $worksheet -> getStyle('A1:G10') -> applyFromArray($styleArray);
    
    //样式设置 - 单元格背景颜色
    $worksheet -> getStyle('A1:G10') -> getFill() 
               -> setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID) 
               -> getStartColor() -> setARGB('FF9800');
    
    //样式设置 - 合并和拆分
    $worksheet -> mergeCells('C3:G3'); //合并单元格
    $worksheet -> unmergeCells('C3:G3'); //拆分单元格
    
    //内容设置
    $worksheet -> setCellValue('A1', 'xmsb'); //设置A1单元格内容为xmsb
    
    //导出工作表
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer -> save('write.xlsx');

转载于:https://www.cnblogs.com/XiaoMingBlingBling/p/11193887.html

[Link 1]: https://www.cnblogs.com/XiaoMingBlingBling/p/11193197.html