PHP + XlsWriter实现百万级数据导入导出

Tinywan

发布于 2024-08-08 12:12:27

1620

发布于 2024-08-08 12:12:27

文章被收录于专栏：开源技术小栈

Excel 你的瓶颈不再是PHP

概述

xlswriter 是一个高性能 PHP C 扩展，可用于读取、写入 Excel 2007+ xlsx 文件，适用于 Linux，FreeBSD，OpenBSD，OS X，Windows。

为什么使用xlswriter

请参考下方对比图；由于内存原因，PHPExcel数据量相对较大的情况下无法正常工作，虽然可以通过修改memory_limit配置来解决内存问题，但完成工作的时间可能会更长

xlswriter是一个 PHP C 扩展，可用于在 Excel 2007+ XLSX 文件中读取数据，插入多个工作表，写入文本、数字、公式、日期、图表、图片和超链接。

特性

写入

100％兼容的Excel XLSX文件
完整的Excel格式
合并单元格
定义工作表名称
过滤器
图表
数据验证和下拉列表
工作表PNG/JPEG图像
用于写入大文件的内存优化模式
适用于Linux，FreeBSD，OpenBSD，OS X，Windows
编译为32位和64位
FreeBSD许可证
唯一的依赖是zlib

读取

完整读取数据
光标读取数据
按数据类型读取

基准测试

测试环境

Macbook Pro 13 inch, Intel Core i5, 16GB 2133MHz LPDDR3 Memory, 128GB SSD Storage

导出

两种内存模式导出100万行数据（单行27列，数据类型均为字符串，单个字符串长度为19）

普通默认模式：耗时 29S，内存只需 2083MB
内存优化模式：耗时 52S，内存仅需 <1MB

导入

100万行数据（单行1列，数据类型为INT）

全量模式：耗时 3S，内存仅 558MB
游标模式：耗时 2.8S，内存仅 <1MB

安装

这里使用PECL安装（推荐）

$ pecl install xlswriter

错误提示

No releases available for package "pecl.php.net/xlswriter"
install failed

以上错误提示，请使用root权限执行安装，即sudo pecl install xlswriter

添加 extension = xlswriter.so 到 php.ini 配置

IDE 助手

composer require viest/php-ext-xlswriter-ide-helper:dev-master

使用

导出文件

注：如果路径下有相同命名的文件，新文件会覆盖老文件

<?php
/**
 * @desc 导出文件
 * @author Tinywan(ShaoBo Wan)
 * @date 2024/8/7 20:20
 */
declare(strict_types=1);

$config = [
    'path' => '/home/www/build' // xlsx文件保存路径
];
$excel  = new \Vtiful\Kernel\Excel($config);

// fileName 会自动创建一个工作表，你可以自定义该工作表名称，工作表名称为可选参数
$filePath = $excel->fileName('tutorial01.xlsx', 'sheet1')
    ->header(['Item', 'Cost'])
    ->data([
        ['Rent', 1000],
        ['Gas',  100],
        ['Food', 300],
        ['Gym',  50],
    ])->output();
var_dump($filePath);

执行输出

string(31) "/home/www/build/tutorial01.xlsx"

导出结果

读取文件

<?php
/**
 * @desc 读取文件
 * @author Tinywan(ShaoBo Wan)
 * @date 2024/8/7 20:29
 */
declare(strict_types=1);

$config = [
    'path' => '/home/www/build' // xlsx文件读取路径
];

$excel = new \Vtiful\Kernel\Excel($config);

// 读取测试文件
$data = $excel->openFile('tutorial01.xlsx')
    ->openSheet()
    ->getSheetData();

var_dump($data);

读取文件输出

array(5) {
  [0]=>
  array(2) {
    [0]=>
    string(4) "Item"
    [1]=>
    string(4) "Cost"
  }
  [1]=>
  array(2) {
    [0]=>
    string(4) "Rent"
    [1]=>
    int(1000)
  }
  [2]=>
  array(2) {
    [0]=>
    string(3) "Gas"
    [1]=>
    int(100)
  }
  [3]=>
  array(2) {
    [0]=
    string(4) "Food"
    [1]=>
    int(300)
  }
  [4]=>
  array(2) {
    [0]=>
    string(3) "Gym"
    [1]=>
    int(50)
  }
}

图表直方图

<?php
/**
 * @desc chart.php 描述信息
 * @author Tinywan(ShaoBo Wan)
 * @date 2024/8/7 20:43
 */
declare(strict_types=1);

$config = [
    'path' => '/home/www/build' // xlsx文件保存路径
];

$fileObject = new \Vtiful\Kernel\Excel($config);

$fileObject = $fileObject->fileName('chart.xlsx');
$fileHandle = $fileObject->getHandle();

$chart = new \Vtiful\Kernel\Chart($fileHandle, \Vtiful\Kernel\Chart::CHART_COLUMN);

$chartResource = $chart->series('Sheet1!$A$1:$A$5')
    ->series('Sheet1!$B$1:$B$5')
    ->series('Sheet1!$C$1:$C$5')
    ->toResource();

$filePath = $fileObject->data([
    [1, 2, 3],
    [2, 4, 6],
    [3, 6, 9],
    [4, 8, 12],
    [5, 10, 15],
])->insertChart(0, 3, $chartResource)->output();