millionmile/php-spreadsheet

PHPspreadsheet封装类库，方便导出导入数据

v2.1.2 2020-09-27 09:05 UTC

Requires

• phpoffice/phpspreadsheet: 1.12.0

Suggests

• cache/apcu-adapter: ^1.0.0
• cache/memcache-adapter: ^1.0.0
• cache/redis-adapter: ^1.0.0
• cache/simple-cache-bridge: ^1.0.0

MIT

• 萬里

README

数据导出到excel是日常开发最常见的功能了，如果每一个数据导出都要写一堆难看的代码，那未免太耗时耗力。这里本人封装了一个导出数据到excel的类库，支持大数据导出、多种样式简单实用，写法简单，类库在composer中，这里主要进行使用及文档讲解。

本人导出excel功能是基于PHPSpreadSheet，这里给大家提供下文档地址，中文翻译的不咋地，不过还能勉强看看。官方英文文档就别为难我了。

中文文档（非官方）

英文文档（官方）

为什么要给你们看文档？因为本类库是基于PHPSpreadSheet，那么无疑能够使用PHPSpreadSheet原本的方法来进行你的自定义操作。具体如何自己操作，看下面讲解。

为方便个人使用，自封装类库概览功能如下：

1. 导出方法的实现
2. 设置表头，并定义文字、颜色、行高等多种样式
3. 简单使用缓存，减少内存消耗
4. 第一行写合并的标题行
5. 可以自定义添加要的格式或者处理（使用对象，随时可以再进行二次处理使用 & ）
6. 导出文件的方式：a. 直接传递给浏览器下载；b. 生成自定义名称文件在服务器中

使用示例

这里先给大家展示一下最常见的几种写法

当然，在这之前，需要先composer下本类库：

composer require millionmile/php-spreadsheet

写法一

这是一种最简易方式的写法了。

$header = ['ID','姓名','电话'];
$data=[
    [1,'A','111'],
    [2,'B','222'],
];
$exportObj = new \MillionMile\PHPSpreadsheet\ExportService($header);
$exportObj->appendData($data);
$exportObj->generateFile(__DIR__, 'YourExcelName');

导出excel效果如下： <br/>

写法二

最常用的写法当属它无疑。

$header = [
    'ID'=>'id',
    '姓名'=>'name',
    '电话'=>'phone'
];
$exportObj = new \MillionMile\PHPSpreadsheet\ExportService($header);

for($i=1;$i<10;$i++){
    $data = [
        ['id'=>1,'name'=>'A','phone'=>'111','trash'=>'no export'],
        ['id'=>2,'name'=>'B','phone'=>'222','trash'=>'no export'],
     ];
    $exportObj->appendData($data);
}


$exportObj->generateFile('YourFilePath', 'YourExcelName');

导出excel效果如下：

<br/>

把上面的for改为数据库的分页获取数据，那么用起来就相当顺滑了

从数据库中获取数据，假设有这样一张表（user）：

id	name	phone
1	A	111
2	B	222
3	C	333
代码如下：
`php
$servername = "YourServerName";
$username = "YourUserName";
$password = "Yourpassword";
$dbname = "YourDbName";

// 创建连接。原生写法，相信实际使用你不是这样的，这里参考就行 $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) {

die("连接失败: " . $conn->connect_error);

}

$header = [

'ID'=>'id',
'姓名'=>'name',
'电话'=>'phone'

]; $exportObj = new \MillionMile\PHPSpreadsheet\ExportService($header);

$page = 0; $pageSize = 100;

$sql = "SELECT COUNT(*) FROM user"; $result = $conn->query($sql); $a = $result->fetch_assoc(); $total = current($a); $lastId = 0; while (true) {

echo $page . '/' . $totalPage . PHP_EOL;	//看看当前跑到第几页
$offect = $page * $pageSize;

//获取数据库数据。原生写法，相信实际使用你不是这样的，这里参考就行
$sql = "SELECT * FROM user where id > {$lastId} limit {$pageSize}";
$result = $conn->query($sql);
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);

if (empty($arr)) {
    //如果没有数据，那么结束数据的读取
    break;
 }
$exportObj->appendData($data);	//将数据库的数据加入到excel中
$lastId = end($arr)['id'];	   //更新最后读取到的表id
$page++;

}

$exportObj->generateFile('YourFilePath', 'YourExcelName');

<br/>

## 写法三

为了让自己的excel不那么难看，又不想写一堆样式代码怎么办？用它，简简单单样式好看起来

$header = [

'ID'=>[
   'field'=>'id',
   'style'=>[
       'font_bold'=>true
   ]
 ],
'姓名'=>[
	'field'=>'name',
   'style'=>[
       'font_color:2'=>'FF0000',
       'horizontal'=>'center'
   ]
 ],
'电话'=>'phone'

]; $exportObj = new \MillionMile\PHPSpreadsheet\ExportService($header);

for($i=1;$i<10;$i++){ $data = [ ['id'=>1,'name'=>'A','phone'=>'111','trash'=>'no export'],

    ['id'=>2,'name'=>'B','phone'=>'222','trash'=>'no export'],
 ];
$exportObj->appendData($data);

}

$exportObj->generateFile('YourFilePath', 'YourExcelName');

导出excel效果如下：

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200709154903898.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODEyNTA0NQ==,size_16,color_FFFFFF,t_70#pic_center)

<br/>

相信有一定编程经验的同学已经能够看出个所以然来，接下来我们来具体讲解一下，上述例子中参数的具体可填内容。

<br/>

# ExportService类库方法

`ExportService`是唯一对外操作类。可用方法如下：

## 1. 实例化

ExportService类构造函数有两个参数，其中`$header`必填，`$cache`选填。

| 构造函数参数 | 必填 | 示例                                                         |
| ------------ | ---- | ------------------------------------------------------------ |
| $header      | √    | excel表格的标题行。见下文"参数相关文档"的"$header参数"章节   |
| $cache       | ×    | 是否使用缓存，不填默认不使用，具体写法见下文"参数相关文档"的"$cacje参数"章节 |

代码示例：

$header = [

'ID'=>'id',
'姓名'=>'name',
'电话'=>'phone'

]; $exportObj = new \MillionMile\PHPSpreadsheet\ExportService($header,'redis');

<br/>

## 2. 填充数据

`appendData`方法是追加数据到excel表格的常用方法，该方法在实例化ExportService对象可使用，必须在导出excel方法前使用（导出了还能追加啥子数据）。它的参数只有一个，就是下文"参数相关文档"中的`$data`参数，具体写法参照下面即可。

代码示例：

$data = [

    ['id'=>1,'name'=>'A','phone'=>'111','trash'=>'no export'],
    ['id'=>2,'name'=>'B','phone'=>'222','trash'=>'no export'],
 ];

$exportObj->appendData($data);

<br/>

## 3. 设置大标题行

`setBigTitle`方法用于在**首行**设置**合并标题行所占列**的单元格，并填写进相关文本。

代码示例：

$exportObj->setBigTitle('我是长标题');

<br/>

## 4. 自定义操作PHPSpreadsheet

不得不说，`PHPSpreadsheet`有大量的操作，本类库仅对其常用的几种操作进行封装，肯定会存在你想要的某种特殊操作，本类库给不了你。这时候，你可以选择使用`PHPSpreadsheet`自己的操作去完成你的需要。这里提供一个让你能够使用`PHPSpreadsheet`当前活动sheet对象的途径——`getActiveSheet()`方法。

代码示例：

//获取文件操作变量，可自由使用PHPSpreadsheet原生操作 $sheet = &$exportObj->getActiveSheet(); //不加引用也可

$sheet->setCellValue('D4', 'yes'); //设置D4列的文本内容为'yes'

<br/>


## 5. 导出excel方法

本类库中，目前导出excel的方法有两种：生成文件到服务器某位置中、直接返回给浏览器进行下载。

**1) 生成文件到服务器某位置中**

上文示例中也存在，生成文件到服务器某位置中方法即是`generateFile`方法。

public function generateFile( string $fileDir, string $fileName): string

`generateFile()`方法有两个参数，一个是文件生成路径`$fileDir`，用于设置生成的excel文件所在的路径。另一个是文件名称`$fileName`，设置生成的excel文件的文件名（注意：不可加文件后缀名。默认生成文件为xlsx）。

该函数返回的结果是生成的excel文件所在路径（字符串类型）。

代码示例：

$exportObj->generateFile(DIR, 'YourExcelName'); //生成excel文件到执行文件的同一级目录中

<br/>

**2) 直接返回给浏览器进行下载**

`download()`方法是将生成的excel文件保存在output流中，直接传递给浏览器进行下载。其参数只有一个`$fileName`文件名称：设置生成的excel文件的文件名（注意：不可加文件后缀名。默认生成文件为xlsx）。

public function download(string $fileName): void

注意：

1. 运行download方法后，程序变将终止运行，后续操作不再执行。
2. 若文件较大，传输时间过长，浏览器终止访问，其处理方式本类库不提供，请自行参照其他方法进行处理。

代码示例：

$exportObj->download('YourFileName'); //生成自定义文件名给浏览器进行下载

<br/>

## 6. 设置当前工作表标题

`setSheetName()`方法是设置当前活动工作表的简单方法，其参数只有一个`$sheetName`。

代码示例：

$exportObj->setSheetName('YourSheetName'); //设置自定义的sheet名称

<br/>

# 参数相关文档

## 1. $header参数

| $header          | 示例                                                         | 说明                                                         |
| ---------------- | ------------------------------------------------------------ | ------------------------------------------------------------ |
| 一维数组自然键名 | ['ID','姓名','电话']                                         | 按照数组内元素顺序填入excel表格中，与$data没有关联           |
| 一维数组自定键名 | ['ID'=>'id','姓名'=>'name','电话'=>'phone']                  | 按照数组内元素顺序填入excel表格中，其中键名是填入excel表格的内容，而元素值则与关联$data二级元素中相应的键名 |
| 二维数组         | ['ID'=>['field'=>'id','style'=>['font_bold'=>true]],'姓名'=>['field'=>'name','style'=>['font_color:2'=>'FF0000','horizontal'=>'center']],'电话'=>'phone'] | 按照数组内元素顺序填入excel表格中，其中键名是填入excel表格的内容，二级数组中的键名`field`对应值关联$data二级元素中相应的键名；style元素是该列将要使用的样式。 |

$header参数为二维数组时，可与`一维数组自定键名`方式混合使用，当不支持与`一维数组自然键名`方式混合使用。

<br/>

下面具体讲解下`$header二维数组`方式下的style参数

### 1) style参数

为方便针对各列甚至各单元格设置统一的样式，在`$header`二维数组方式中使用`style`参数来进行统一方便的样式设置。具体可设置的常用样式如下：

| 样式键名       | 示例                                                         | 可填参数                                                     | 样式说明                                                     |
| -------------- | ------------------------------------------------------------ | ------------------------------------------------------------ | ------------------------------------------------------------ |
| font_bold      | ['font_bold'=>true]                                          | true\|false                                                  | 是否设置文本加粗                                             |
| font_italic    | ['font_italic'=>true]                                        | true\|false                                                  | 是否设置文本加斜                                             |
| font_underline | ['font_underline'=>true]                                     | true\|false                                                  | 是否设置下划线                                               |
| font           | ['font'=>'宋体']                                             | 字体字符串                                                   | 设置文本字体，可用字体为excel支持字体                        |
| font_size      | ['font_size'=>20]                                            | 数字                                                         | 设置文本大小                                                 |
| font_color     | ['font_color'=>'FF0000']                                     | 'FF0000'的rgb<br />或'FF000000'的rgba十六进制                | 设置文本颜色。可使用rgb十六进制方式（支持rgba`FF000000`，后两位为透明度） |
| horizontal     | ['horizontal'=>'center']                                     | 'left'\|'right'\|'center'                                    | 设置单元格水平对齐方式                                       |
| vertical       | ['vertical'=>'center']                                       | 'top'\|'bottom'\|'center'                                    | 设置单元格垂直对齐方式                                       |
| wrap_text      | ['wrap_text'=>true]                                          | true\|false                                                  | 是否设置文本自动换行。对内容`\n`会进行换行处理               |
| shrink_to_fit  | ['shrink_to_fit'=>true]                                      | true\|false                                                  | 是否自适应大小                                               |
| text_indent    | ['text_indent'=>true]                                        | true\|false                                                  | 是否文本缩进                                                 |
| diagonal       | ['diagonal'=>'down']                                         | 'down'\|'up'\|'cross'                                        | 设置对角线，'down'是从左上到右下；'up'是从左下到右上；'cross'是打交叉线 |
| background     | ['background'=>'FFFF0000']                                   | 'FFFF0000'                                                   | 设置单元格背景色。仅支持rgb\|rgba十六进制格式                |
| border         | ['border'=>'outline']<br />['border'=>['allBorders','thin','FFFF00']] | 字符串形式：<br />'allBorders'<br />'inside'<br />'outline'<br />'horizontal'<br />'top'<br />'left'<br />'vertical'<br />'bottom'<br />'right'<br /><br />数组形式：['allBorders','double','FF0000'] | 设置单元格边框，字符串形式只设置作用的边框，默认边框样式使用`thin`，颜色为`黑色`，数组形式的是[作用边框，边框样式，边框颜色]。相关参数具体内容借鉴`PhpSpreadshht` |
| width          | ['width'=>20]                                                | 'auto'\|宽度数字\|0                                          | 设置列宽，'auto'是根据文本内容自定义列宽；数字是直接设置列的宽度大小，而0是设置列宽为0，也就是隐藏列 |
| height         | ['height'=>20]                                               | 数字                                                         | 直接使用数字设置行高度。为0时，也就是隐藏行。注意：不同列设置行高的话，将会按照最后设置的行高为准，前面的设置将会失效。 |


<br/>

### 2) style参数键名

style参数的键名，原本是写入指定的样式用。这里为方便使用，对其进行扩展，可设置样式作用的范围（键名后面`:`的即为范围）。用法见下表：

| 名称       | 值           | 示例                           | 样式范围                                                     |
| ---------- | ------------ | ------------------------------ | ------------------------------------------------------------ |
| 全局范围   | 无           | ['font_bold'=>true]            | 没有填入范围，默认对所有数据行使用                           |
| 单行范围   | 数字         | ['font_bold:1'=>true]          | 键名`:`后面是1，代表对该列的第1行使用样式                    |
| 连续行范围 | '1-10'       | ['font_bold:1-10'=>true]       | 键名`:`后面是'1-10'，代表对该列的第1行到第10行连续使用样式   |
| 隔行范围   | '1,3,5'      | ['font_bold:1,3,5'=>true]      | 键名`:`后面是'1,3,5'，代表对该列的第1行、第3行、第5行使用样式 |
| 混合范围   | '1,3-5,8,10' | ['font_bold:1,3-5,8,10'=>true] | 键名`:`后面是'1,3-5,8,10'，拆开来分别是：第一行使用样式，第3-5行连续使用样式，第8行使用样式，第10行使用样式 |

<br/>

## 2. $cache参数

为什么要使用缓存？PHPSpreadsheet类库无疑很方便操作，但是其消耗的内存不是一般的小，

官方说明： `PhpSpreadsheet在工作表中平均每个单元格使用约1k，因此大型工作簿可以迅速用尽可用内存。 

**注意**：若要使用APCu缓存、Redis缓存、Memcache缓存等缓存方式，请自行配置调通后，才能正常进行使用。

| $cache       | 示例                                                         | 说明                                                         |
| ------------ | ------------------------------------------------------------ | ------------------------------------------------------------ |
| 无           | null                                                         | 不填写cache参数，则代表不使用缓存技术，其导出excel速度无疑是最快的 |
| 文件缓存     | 'file'                                                       | [强大网友写的文件缓存方式](https://blog.csdn.net/x554462/article/details/89102800) 能够减少近半内存消耗，导出速度也较快。**注意：该缓存方式将导致样式失效** |
| APCu缓存     | 'apcu'                                                       | APCu是php自带的缓存扩展，扩展安装方式可参考[php自带的缓存扩展-APCu](http://www.php20.cn/article/117) |
| Redis缓存    | 'redis'<br />Redis对象形式：\$cache= new \Redis();\$cache->connect('127.0.0.1', 6379); | Redis可以说是最常用的缓存数据库了，减少的内存消耗也较大。如果使用字符串类型'redis'，将自动生成Redis对象，连接到本地Redis，端口为6379；<br />如果使用Redis对象传参，那么可以自行进行其他配置，更为灵活 |
| Memcache缓存 | 字符串形式：'memcache'<br />Memcache对象形式：\$cache= new \Memcache();\$cache->connect('localhost', 11211); | 如果使用字符串类型'memcache'，将自动生成Memcache对象，连接到本地Memcache，端口为11211；<br/>如果使用Memcache对象传参，那么可以自行进行其他配置，更为灵活 |

**特别注意：使用cache无疑可以减少大量的内存消耗，但如果要导出的数据过多，还是会存在爆内存的情况。设置合理的可用内存跟数据大小有关。在程序中，修改可用内存可参照下面代码：**

ini_set('memory_limit','500M'); //设置可用内存为500M ini_set('memory_limit','-1'); //-1代表不进行内存限制，谨慎操作

<br/>

## 3. $data参数

$data支持的参数形式都是二维数组。其中一维数组内元素属于自然键名（不是也没关系）按先后顺序填入。二维数组内元素可选择自定键名或自然键名，具体说明见下表。

| $data                   | 示例                                                         | 说明                                                         |
| ----------------------- | ------------------------------------------------------------ | ------------------------------------------------------------ |
| 二维数组自然键名        | [[1,'A','111'],[2,'B','222']]                                | 二维数组内的各元素都是自然键名，按先后顺序填入excel表格中。如使用它，注意保证元素顺序跟$header的元素顺序一致，否则将导致标题与数据列对应不上。 |
| 二维数组关联$header键名 | [['id'=>1,'name'=>'A','phone'=>'111','trash'=>'no export'],['id'=>2,'name'=>'B','phone'=>'222','trash'=>'no export']] | 结合`$header参数的自定键名一维数组或二维数组`进行使用。二维数组内的各元素跟$header自然键名，按先后顺序填入excel表格中。如使用它，可过滤掉某些不填入excel的字段，如示例中的键名`trash`对应的数据。 |

<br/>

# 总结

本类库并不是特别完善，还有些值得再去优化和补充。本人已有计划追加功能：

* 大数据采用csv封装导出 √
* 设置默认单元格、行、列样式
* 使用excel导入数据到数据库中
* 支持多sheet操作 √