声明:本站文章均为作者个人原创,图片均为实际截图。如有需要请收藏网站,禁止转载,谢谢配合!!!

PhpSpreadsheet是一个纯PHP编写的组件库,它使用现代PHP写法,代码质量和性能比PHPExcel高不少,完全可以替代PHPExcel(PHPExcel已不再维护)。使用PhpSpreadsheet可以轻松读取和写入Excel文档,支持Excel的所有操作。

PhpSpreadsheet特性

  • 支持读取.xls,.xlsx,.html,.csv等格式文件
  • 支持写入导出.xls,.xlsx,.html,.csv,.pdf格式文件。
  • 提供丰富的API,提供单元格样式设置、Excel表格属性设置、图表设置等等诸多功能
  • 使用PhpSpreadsheet完全可以生成一个外观结构都满足你的Excel表格文件。
  • 卓越的性能,尤其在PHP7上表现优异,比PHPExcel强大很多。

1、安装PhpOffice\PhpSpreadsheet

composer require phpoffice/phpspreadsheet

2、引入

use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

3、使用(导出)

    set_time_limit(0);

    $spreadsheet = new Spreadsheet();

    //合并单元格
    $spreadsheet->getActiveSheet()->mergeCells('A1:E1');
    //设置自动高度
    $spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    //设置宽度
    $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(100);
    //设置字体为粗体
    $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    //设置表头
    $spreadsheet->getActiveSheet()->getCell('A1')->setValue('销售汇总单(' . $date . ')');
    //设置自动换行
    $spreadsheet->getActiveSheet()->getStyle('A3:I20')->getAlignment()->setWrapText(true);
    //设置水平、垂直居中
    $styleArray = [
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
        ],
    ];

    $worksheet = $spreadsheet->getActiveSheet();

    $worksheet->getStyle('A1:E100')->applyFromArray($styleArray);

    $title = ['序号', '商品名称', '规格'];
    foreach ($title as $index => $item) {
        $worksheet->setCellValueByColumnAndRow($index + 1, 2, $item);
    }

    $data = [
        ['no' => 1, 'product_name' => '苹果', 'package' => '1斤'],
        ['no' => 2, 'product_name' => '香蕉', 'package' => '2斤']
    ]

    $row = 2; // 从第二行开始
    foreach ($data as $item) {
    $column = 1;
    foreach ($item as $value) {
    // 单元格内容写入
        $worksheet->setCellValueByColumnAndRow($column, $row, $value);
        $column++;
    }
        $row++;
    }

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="销售汇总单('. date('Y-m-d')  .')'.'.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');

4、进价使用:合并复杂单元格(导出)

有时候经常会遇到合并单元格的情况,比如一个人有多个订单,则需要把此人用户信息合并,合并思路:
1、找到行最小粒度(单个订单)
2、先写入用户信息(1,1),然后循环该用户的订单,比如有3个订单,则设置单元格
记录订单开始行startRow = 1
(1,2) (1,3) (1,4) 分别为 第1个订单的订单号,下单时间,收货地址
(2,2) (2,3) (2,4) 分别为 第2个订单的订单号,下单时间,收货地址
(3,2) (3,3) (3,4) 分别为 第3个订单的订单号,下单时间,收货地址
记录订单开始行endRow = 3
3、合并(1,1)(2,1)(3,1),填充内容为用户信息
代码如下

$data = [
    [
    'id' => 1, 
    orders => 
        ['order_sn' => 101, 'order_time' => '20211019', 'order_address' => '***'],
        ['order_sn' => 102, 'order_time' => '20211019', 'order_address' => '***'],
        ['order_sn' => 103, 'order_time' => '20211019', 'order_address' => '***']
    ]
]
$row = 1;
foreach ($data as $k => $v) {
    //先写入用户信息
    $worksheet->setCellValueByColumnAndRow(1, $row, $v['user_id']);
    $rowStart = $row;
    //循环输出订单
    foreach ($vvv['orders'] as $kk => $vv) {
        $worksheet->setCellValueByColumnAndRow(2, $row, $vvvv['order_sn']);
        $worksheet->setCellValueByColumnAndRow(3, $row, $vvvv['contactor']);
        $worksheet->setCellValueByColumnAndRow(4, $row, $vvvv['contactor']);
        //也可以使用下面
        /*
        $col = 2;
        foreach ($vv as $kkk => $vvv) {
            $worksheet->setCellValueByColumnAndRow($row, $col, $vvv);
            $col++;
        }
        $col = 2;
        */
        $worksheet->setCellValueByColumnAndRow(3, $row, $vvvv['contactor']);
        $worksheet->setCellValueByColumnAndRow(4, $row, $vvvv['contactor']);
        $row++;
    }
    //合并用户信息
    $spreadsheet->getActiveSheet()->mergeCells('A' . $rowStart . ':A' . ($row - 1));
    }
}

5、使用(导入)

 $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
 $reader->setReadDataOnly(TRUE);
 $spreadsheet = $reader->load('students.xlsx'); //载入excel表格

$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数

$data = [];

for ($row = 1; $row <= $highestRow; ++$row) { //从第一行开始

    $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //姓名
    $age = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //年龄

    $data[] = [
        'age' => $age,
        'name' => $name,
    ];
}



点赞(2) 打赏

Comment list 共有 0 条评论

暂无评论