前言
还记得《PHPSpreadsheet自封装类库讲解文档》这篇文章吗?近些日子笔者为了满足公司项目需求,将“PHPSpreadsheet自封装类库2.0”做出来啦,在对导出excel功能进行完善外,着重点放在导入excel(获取excel表格数据上)。
新版本内容中,导出excel功能更新如下:
- 已知bug修复
- 列宽重复设置错误
- 新增功能
- 修改器 formatter
- 二级表头功能
- 批量获取
- 支持多sheet操作
- 大数据导出csv
具体新增功能说明请期待《PHPSpreadsheet自封装类库讲解文档1.1》(晚点再写)
本文着重点:读取excel功能,大致功能如下:
- 读取上传/本地文件
- 数据列名格式化
- 筛选特定格式单元格数据
- 修改器 formatter(含内置转换功能)
- 多种获取数据方法
使用示例
照旧,这里先给大家展示一下最常见的几种写法
照旧,在这之前,需要先composer
下本类库(version>=2.1.2):
composer require millionmile/php-spreadsheet
以下代码使用这个excel数据来示例:
写法一
这是一种最简易方式的写法了。
try {
$filePath = __DIR__ . '/YourExcelName.xlsx';
$headerData = ['id', 'name', 'phone'];
$importObj = new MillionMile\PHPSpreadsheet\ImportService($headerData, $filePath);
$data = $importObj->getTheSheetAllData();
print_r($data);
} catch (Exception $e) {
echo $e->getMessage();
}
获取数据效果如下:
写法二
简单实用的写法,追求自定义排列顺序党可用。
try {
$filePath = __DIR__ . '/YourExcelName.xlsx';
$headerData = [
'name' => 'B',
'id' => 'A',
'phone' => 'C'
];
$importObj = new MillionMile\PHPSpreadsheet\ImportService($headerData, $filePath);
$data = $importObj->getTheSheetAllData();
print_r($data);
} catch (Exception $e) {
echo $e->getMessage();
}
获取数据效果如下:
写法三
追求各种功能的话必用它。
try {
$filePath = __DIR__ . '/YourExcelName.xlsx';
$headerData = [
'id' => [
'col' => 'A',
'filter' => [
'border' => 'all',
'color' => 'red',
'font' => 'bold'
]
],
'name' => [
'col' => 'B',
'formatter' => 'trim'
],
'phone' => [
'formatter' => function ($value) {
return $value === 222 ? 'Y' : $value;
}
]
];
$importObj = new MillionMile\PHPSpreadsheet\ImportService($headerData, $filePath);
$data = $importObj->getTheSheetAllData();
print_r($data);
} catch (Exception $e) {
echo $e->getMessage();
}
获取数据效果如下:
当然,上面的示例只是一种最基本的示例,还有其他更方便更丰富参数和方法,具体看下文。
ImportService类库方法
ImportService
是Import模块中唯一对外操作类。可用方法如下:
1. 实例化
ImportService类构造函数有 3 个参数,其中$headerData
、$fileObj
必填,$cache
选填。
构造函数参数 | 必填 | 示例 |
---|---|---|
$headerData | √ | 读取excel表格的配置。见下文"参数相关文档"的"$headerData参数"章节 |
$fileObj | √ | 上传文件$FILE对象(单文件)或 本地文件路径 |
$cache | × | 是否使用缓存,不填默认不使用,具体写法见下文"参数相关文档"的"$cache参数"章节 |
代码示例:
try {
$headerData = [
'id',
'name',
'phone'
];
$importObj = new MillionMile\PHPSpreadsheet\ImportService($headerData, $_FILES['excel']);
$data = $importObj->getTheSheetAllData();
print_r($data);
} catch (Exception $e) {
echo $e->getMessage();
}
上传示例:
2. 获取数据
本类库中,提供多种导出excel的方法:手动分页获取数据、自动分页获取数据、获取整个工作表数据、获取整个sheet表数据
1) 手动分页获取数据
public function getPageData(int $page = 1, int $pageSize = 100):array|bool
手动分页函数getPageData
有两个参数,一个是读取的页码$page
,用于设置所要读取的数据页是哪一页。另一个是每页条数$pageSize
,设置每一页的数据是多少,划定页的数据范围(注意:当所要读取的页数据超过excel中的数据范围,将会返回false,如要终止,可直接按照此项判断)。
代码示例:
$page = 1;
while (true) {
$data = $importObj->getPageData($page, 1);
if ($data === false) {
break;
}
echo '第', $page++, '页数据:', PHP_EOL;
print_r($data);
}
2) 自动分页获取数据
public function getPageDataCb(callable $cb, int $pageSize = 100): void
自动分页让你省去分页一些冗余重复代码,让你专注于对每页数据的处理。
自动分页函数getPageDataCb
有两个参数:
一个是获取到数据后的用户自定义处理回调函数$cb
,其中会有一个入参$data
-是每页获取到的数据数组。
另一个是每页条数$pageSize
,设置每一页的数据是多少,划定页的数据范围。当整个sheet表的数据读取处理完成后,本函数将退出,继续执行代码。
代码示例:
$page = 1;
$importObj->getPageDataCb(function ($data) use (&$page) {
echo '第', $page++, '页数据:', PHP_EOL;
print_r($data);
}, 1);
echo '处理完成';
3) 获取整个工作表数据
public function getAllSheetData(): array
获取整个工作表数据方法将会返回三维数组的数据,它并没有任何入参,出参也就是所有活动工作表的所有数据,将会形成一个三维数组,一维中的键名为sheet的表名。
代码示例:
$data = $importObj->getAllSheetData();
print_r($data);
效果示例:
4) 获取整个sheet表数据
public function getTheSheetAllData(): array
获取整个工作表数据方法在上文中也反复使用,它并没有任何入参,出参也就是当前活动sheet表的所有数据。
代码示例:
$data = $importObj->getTheSheetAllData();
print_r($data);
4) 获取数据相关方法
上面介绍了获取数据的主要方法,但有时还需要一些方法来进行辅助
- 跳过不获取前N行数据
public function skipFirstRows(int $skip, bool $allSheet = false): void
在上文各种获取数据前使用。如果前N行为标题行或者无用数据,那么即可使用该方法跳过,不进行获取。$skip
参数即是要跳过的行数N,而$allSheet
为bool类型,代表是否作用到所有sheet表上。默认为false。
(注意:当$allSheet=true
时,假如某些表格已经设置了跳过M行,那么这些表格还是使用原本跳过M行的操作,其他没有设置过的,才会去统一使用跳过N行的操作。即特定优先于统一)
代码示例:
$importObj->skipFirstRows(1);
- 切换sheet
public function checkoutSheet($index, array $headerData = [])
一个excel中,不一定只包含一个sheet工作表,我们有时需要切换到其他sheet表去获取数据。本类库提供一个封装方法checkoutSheet
,用于切换sheet表。
入参:本方法支持使用 下标切换(sheet表在excel表中的下标位置,从0开始计数) 和 sheet表名切换(sheet表在excel中的名称),在入参$index
中填入即可。
此外,为让新的sheet表也能够使用到我们内置的读取数据功能,这里也是建议再去重新配置一个新的$headerData
。与实例化函数中的$headerData
编写方法相同。
出参:当切换sheet成功后,将获得该sheet的Worksheet对象
,切换失败的话,获得的将是一个提示报错信息的字符串。
代码示例:
$afterSheet = $importObj->checkoutSheet(1, $headerData);
if (is_string($afterSheet)) {
echo '切换失败:', $afterSheet;
exit;
}
$afterSheet->getHighestDataRow(); //可使用各种PHPspreadsheet官方方法
- 获取工作表中所有sheet的名称,为切换sheet提供一个前置条件
public function getSheetNames(): array
代码示例:
$sheetNames = $importObj->getSheetNames();
print_r($sheetNames);
效果示例:
小知识 :就算某个数据范围下已经没有数据了,但是类库还是读取到某个没有数据的范围。这种时候,请留意你是否操作过了某个不在范围内的单元格/行/列……
参数相关文档
1. $headerData参数
$headerData | 示例 | 说明 |
---|---|---|
一维数组自然键名 | [‘id’,‘name’,‘phone’] | 按照数组内元素顺序读取excel表格中列数据,元素值即为结果数组的键名 |
一维数组自定键名 | [‘id’=>‘A’,‘name’=>‘B’,‘phone’=>‘C’] | 按照数组内元素顺序读取excel表格中对应列数据,其中键名结果数组的键名,而元素值则与是excel表格中对应的列名 |
二维数组 | [‘id’ => [‘col’ => ‘A’, ‘filter’ => [‘border’ => ‘all’, ‘color’ => ‘red’, ‘font’ => ‘bold’]], ‘name’ => [‘col’ => ‘B’, ‘formatter’ => ‘trim’], ‘phone’ => [‘formatter’ => function ($value) {return $value === 222 ? ‘Y’ : $value;}]] | 按照数组内元素顺序读取excel表格中,其中键名是结果数组的键名,而二级数组中的键名col 对应值关联excel所要获取的列名;其他元素还有:filter 和formatter ,详情见下文。 |
$header参数的3种方式可以混合使用,但也会因此存在逻辑混淆。建议每种方式分开使用
下面将分别讲解下$headerData二维数组
方式下的filter
参数和formatter
参数
1) filter参数
为方便针对各单元格的样式来进行筛选数据操作,在$headerData
二维数组方式中使用filter
参数来进行统一方便的样式筛选。具体可筛选的常用样式如下:
筛选键名 | 示例 | 可填参数 | 筛选说明 |
---|---|---|---|
row_range | [ ‘row_range’ => ‘1,2-10,12:15’ ] | 字符串 数字:1 只获取该列第1行的数据 范围:2-10 只获取该列的第2行到第10行的数据 | 使用, 进行分割,效果为“或”。- 与: 效果一样,用于限定范围 |
font | [ ‘font’ => ‘bold’ ] | ‘bold’ | ‘italic’ | ‘underline’ | 筛选文本是否加粗、加斜、加下划线 |
font_name | [ ‘font_name’ => ‘宋体’ ] | 字体字符串 | 筛选文本字体 |
font_size | [ ‘font_size’ => 20 ] | 字符串 数字:20 只获取该列字体大小为20的数据 范围:10-15 只获取该列字体大小在[10,15]内的数据 | 筛选文本大小 |
color | [ ‘color’ => ‘FF0000’ ] | ‘FF0000’的rgb格式(可在前加’#’) ‘FF000000’的argb格式(可在前加’#’) 'rgb(255,0,0)'的rgb格式 ’argb(255,255,0,0)'的argb格式 ’rgba(255,0,0,255)'的rgba格式 ’red’的内置颜色格式 | 筛选文本颜色。支持多种参数形式 |
horizontal | [ ‘horizontal’ => ‘center’ ] | ‘left’ | ‘right’ | ‘center’ | ‘general’ | 筛选单元格水平对齐方式 |
vertical | [ ‘vertical’ => ‘center’ ] | ‘top’ | ‘bottom’ | ‘center’ | ‘justify’ | 筛选单元格垂直对齐方式 |
alignment | [ ‘alignment’ => ‘wrap_text’ ] | ‘wrap_text’ | ‘shrink_to_fit’ | ‘indent’ | 筛选单元格是否’文本自动换行’、‘文本自动适应’、‘存在文本缩进’ |
background | [ ‘background’ => ‘FFFF0000’ ] | ‘FF0000’的rgb格式(可在前加’#’) ‘FF000000’的argb格式(可在前加’#’) 'rgb(255,0,0)'的rgb格式 ’argb(255,255,0,0)'的argb格式 ’rgba(255,0,0,255)'的rgba格式 ’red’等内置颜色格式 | 筛选单元格背景颜色。支持多种参数形式。仅支持纯色背景筛选 |
border | [ ‘border’ => ‘all’ ] | ‘all’ | ‘top’ | ‘left’ | ‘right’ | ‘bottom’ | ‘right’ | 筛选单元格边框,分别筛选是否有’所有边框’、‘顶部边框’、‘左侧边框’、‘右侧边框’、'底部边框’ 注意:如果四周都有边框,那么他同时满足上/下/左/右四种边框样式 |
在样式筛选配置中,采用二维数组的方式来表示和、或之间的关系。什么意思呢?
看下下面这样一个配置示例:
[
[
'font' => 'bold',
'color' => 'red'
],
[
'font' => 'italic',
'color' => 'green'
],
]
上面的示例可以这样解读:一个二维数组中,它的第一层元素中,关系为"或",而元素内的数组,关系为"和",即:
只要满足 “条件A (字体加粗并且为红色)” 或 “条件B (字体加斜并且为绿色)” 其中一个,即可通过筛选,获取到它的值。
小知识 :
-
如果直接把样式元素写在一维数组中,本类库会将这些样式统一处理为“一个套餐”,里面的关系为“和”。
[ 'font' => 'bold', 'color' => 'red' ] //被转化为 [ [ 'font' => 'bold', 'color' => 'red' ] ]
-
样式元素内(
row_range
和font_size
除外)可以用 “|” 和 “&” 分隔开,分别代表 “和”、“或” ,方便编写(加不加空格无所谓)。但是可别两者同时混用,不保证结果逻辑符合你预期[ [ //以下两者需同时满足 'font' => 'bold & italic', //字体要加粗 并且 加斜 'color' => 'red | green' //字体颜色要红色 或者 绿色 ] ]
2) formatter参数
formatter是为了方便对数据进行转换处理,这里提供两种方法进行配置。
-
内置格式化函数
类库中,提供一些常用的数据格式化函数,如下:
date
:将单元格的时间内容处理成’Y-m-d’的日期格式datetime
:将单元格的时间内容处理成’Y-m-d H:i:s’的日期时间格式timestamp
:将单元格的时间内容处理成当前服务器所处时区的时间戳no_warp
:将单元格内的换行内容全部剔除掉trim
:将单元格内容左右两边的空格剔除掉json
:将单元格JSON内容转为数组形式
代码示例:
[ 'formatter' => 'trim | date']
这一些内部函数可以共同使用,使用 ‘|’ 切割开即可(内部方法是按照从左到右的顺序依次格式化数据,请留意)
- 自定义回调函数
使用回调函数进行自己的格式化内容。它有两个入参:$data
和$rowData
。
$data
是单元格的原数据;
$rowData
是单元格所处行的一维数组数据。键名为单元格所处列的名称,格式为
[
'A' => 'A',
'B' => '1',
'C' => '111'
]
代码示例:
$headerData = [
'id' => [
'col' => 'A',
'formatter' => function ($value, $data) {
return $value . ':' . json_encode($data);
}
],
];
try {
$importObj = new MillionMile\PHPSpreadsheet\ImportService($headerData, $filePath);
} catch (Exception $e) {
echo $e->getMessage();
exit;
}
$sheetNames = $importObj->getTheSheetAllData();
print_r($sheetNames);
效果示例:
小知识 :
- 已经被filter筛选掉的数据,在
$data
参数中是不会存在的哦 col
键并非必填项/不可重复,可以使用 formatter自定义回调函数 创建一个"虚拟列",借助其他数据来构造成自己一列的数据
2. $cache参数
为什么要使用缓存?PHPSpreadsheet类库无疑很方便操作,但是其消耗的内存不是一般的小,
官方说明: `PhpSpreadsheet在工作表中平均每个单元格使用约1k,因此大型工作簿可以迅速用尽可用内存。
注意:若要使用APCu缓存、Redis缓存、Memcache缓存等缓存方式,请自行配置调通后,才能正常进行使用。
$cache | 示例 | 说明 |
---|---|---|
无 | null | 不填写cache参数,则代表不使用缓存技术,其导出excel速度无疑是最快的 |
文件缓存 | ‘file’ | 强大网友写的文件缓存方式 能够减少近半内存消耗,导出速度也较快。注意:该缓存方式将导致样式失效 |
APCu缓存 | ‘apcu’ | APCu是php自带的缓存扩展,扩展安装方式可参考php自带的缓存扩展-APCu |
Redis缓存 | 'redis’ Redis对象形式:$cache= new \Redis();$cache->connect(‘127.0.0.1’, 6379); | Redis可以说是最常用的缓存数据库了,减少的内存消耗也较大。如果使用字符串类型’redis’,将自动生成Redis对象,连接到本地Redis,端口为6379; 如果使用Redis对象传参,那么可以自行进行其他配置,更为灵活 |
Memcache缓存 | 字符串形式:'memcache’ Memcache对象形式:$cache= new \Memcache();$cache->connect(‘localhost’, 11211); | 如果使用字符串类型’memcache’,将自动生成Memcache对象,连接到本地Memcache,端口为11211; 如果使用Memcache对象传参,那么可以自行进行其他配置,更为灵活 |
特别注意:使用cache无疑可以减少大量的内存消耗,但如果要导出的数据过多,还是会存在爆内存的情况。设置合理的可用内存跟数据大小有关。在程序中,修改可用内存可参照下面代码:
ini_set('memory_limit','500M'); //设置可用内存为500M
ini_set('memory_limit','-1'); //-1代表不进行内存限制,谨慎操作
总结
本Import类库的原则是:不更改excel内部数据。
本Import类库适用于 “对excel格式有着各种BT要求的数据处理工作” ,尚不适合处理大数据excel表格,请期待后续针对大数据处理的功能开发。