phpSpreadsheet 使用

一、 安装

安装直接使用 composer 安装即可

composer require phpoffice/phpspreadsheet

二、 使用

2.1 基本使用

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

2.2 使用

2.2.1 设置列宽

可以使用以下代码设置列的宽度：

<?php
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);

如果希望phpSpreadsheet执行自动宽度计算，请使用以下代码。

<?php
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

2.2.2 设置格式

设置字符串格式

<?php
$sheet->getCell('A'.$i)->setValueExplicit($item['line_number'], DataType::TYPE_STRING);

三、其它

3.1 运行错误

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes)

需要设置运行内存

<?php
ini_set('memory_limit','512M');

四、例子

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

date_default_timezone_set("Asia/Shanghai");
ini_set('memory_limit','512M');


$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getColumnDimension('A')->setAutoSize(true);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
$sheet->getColumnDimension('D')->setAutoSize(true);
$sheet->getColumnDimension('E')->setAutoSize(true);
$sheet->getColumnDimension('F')->setAutoSize(true);

$sheet->setCellValue('A1', '线路号');
$sheet->setCellValue('B1', '车辆号');
$sheet->setCellValue('C1', '卡号');
$sheet->setCellValue('D1', '卡类型');
$sheet->setCellValue('E1', '乘车时间');
$sheet->setCellValue('F1', '卡类');


$i = 2;
foreach ($record as $item) {
    $sheet->getCell('A'.$i)->setValueExplicit($item['line_number'], DataType::TYPE_STRING);
    $sheet->getCell('B'.$i)->setValueExplicit($item['bus_number'], DataType::TYPE_STRING);
    $sheet->getCell('C'.$i)->setValueExplicit($item['card_number'], DataType::TYPE_STRING);
    $sheet->getCell('D'.$i)->setValueExplicit($item['card_type'], DataType::TYPE_STRING);
    $sheet->setCellValue('E'.$i, $item['time']);
    $sheet->getCell('F'.$i)->setValueExplicit($item['card_class'], DataType::TYPE_STRING);
    $i++;
}

$distDir = `fk_dir` ."/dist/". $date. "/";
if(!is_dir($distDir)){
    mkdir($distDir, 0777, true);
}
$fileName = $distDir. $date."-".date('H').".xlsx";

$writer = new Xlsx($spreadsheet);
$writer->save($fileName);

五、参考文档

https://www.cnblogs.com/zx-admin/p/11653863.html

https://phpspreadsheet.readthedocs.io/en/latest/#getting-started