whalephp / phpexcel-helper
PHPExcel Helper
2.0.0
2021-07-17 06:54 UTC
Requires
- php: ^7.2 || ^8.0
- phpoffice/phpspreadsheet: ^1.18
README
项目介绍
PHPExcel-Helper是什么?
PHPExcel辅助开发类,帮助开发者快速创建各类excel。
PHPExcel-Helper存在的意义?
官方phpexcel库功能全面,但其调用有些繁琐,一个简单的表格导出可能需要写上几十行代码,本库将phpexcel中常用的方法配置进行封装,并添加了一些常用的业务支持,通常几行代码即可实现一个导出功能。
在实际开发中很容易的可以将数据库中查询出来的列表配置导出。
安装教程
使用 composer 安装,依赖 phpexcel
$ composer require whalephp/phpexcel-helper
Demo
demo 1:简单表格
<?php include '../vendor/autoload.php'; use whalephp\tool\PHPExcelHelper; $ToolExcel = new PHPExcelHelper(); $list = array( array('id'=>1,'name'=>'a'), array('id'=>2,'name'=>'b'), array('id'=>3,'name'=>'c'), array('id'=>4,'name'=>'c'), ); $key = array( 'id' => '编号', 'name' => '姓名', ); $ToolExcel->exportExcelSimp('简版测试',$key,$list);
demo 2:跨行跨列表格
<?php include '../vendor/autoload.php'; use whalephp\tool\PHPExcelHelper; $ToolExcel = new PHPExcelHelper(); $data = array( 'file_name' => '测试-跨行跨列', 'sheetInfo' => array( 'sheetIndex' => 0, 'sheetTitle' => '订单汇总', ), 'startCell' => array( //开始写入位置 'row'=>1, 'col'=>1 ), 'cellData' => array( array( array( 'val' => 'A1', 'rowspan' => 2, //跨行数 'colspan' => 2, //跨列数 'remarks' => '备注1', ), array( 'val' => 'B1', // 'rowspan' => 2, //跨行数 'colspan' => 2, //跨行数 ), array( 'val' => 'C1', 'colspan' => 2, ), array( 'val' => 'D1', // 'colspan' => 2, ), array( 'val' => 'A1', 'rowspan' => 2, //跨行数 'colspan' => 2, //跨列数 'remarks' => '备注1', ), array( 'val' => 'C1', 'colspan' => 2, ), array( 'val' => 'D1', // 'colspan' => 2, ), ), array( array( 'val' => '1', ), array( 'val' => '2', ), array( 'val' => '3', ), array( 'val' => '4', ), array( 'val' => '5', ), array( 'val' => '6', ), ), array( array( 'val' => 'a', ), array( 'val' => 'b', // ), ), ), ); $ToolExcel->exportExcel($data);
demo 3:指定列宽
<?php include '../vendor/autoload.php'; use whalephp\tool\PHPExcelHelper; $ToolExcel = new PHPExcelHelper(); $list = array( array('id'=>1,'name'=>'a','nickname'=>'aa'), array('id'=>2,'name'=>'b','nickname'=>'bb'), array('id'=>3,'name'=>'c','nickname'=>'cc'), array('id'=>4,'name'=>'d','nickname'=>'dddddddddddddddddddddddddddddddd'), ); $key = array( 'id' => ['编号',10], 'name' => ['姓名',15], 'nickname' => ['昵称',35], ); $ToolExcel->exportExcelSimp('简版测试',$key,$list);
demo 4:指定sheet信息
<?php include '../vendor/autoload.php'; use whalephp\tool\PHPExcelHelper; $ToolExcel = new PHPExcelHelper(); $list = array( array('id'=>1,'name'=>'a','nickname'=>'aa'), array('id'=>2,'name'=>'b','nickname'=>'bb'), array('id'=>3,'name'=>'c','nickname'=>'cc'), array('id'=>4,'name'=>'d','nickname'=>'dddddddddddddddddddddddddddddddd'), ); $key = array( 'id' => ['编号',10], 'name' => ['姓名',15], 'nickname' => ['昵称',35], ); $ToolExcel->exportExcelSimp([ 'file_name' => '简版测试', 'sheetTitle' => '工作区一', ],$key,$list);
demo 5:多sheet
<?php include '../vendor/autoload.php'; use whalephp\tool\PHPExcelHelper; $ToolExcel = new PHPExcelHelper(); // 第一组数据 //-------------------------------------------------------------- $list = array( array('id'=>1,'name'=>'a','nickname'=>'aa'), array('id'=>2,'name'=>'b','nickname'=>'bb'), array('id'=>3,'name'=>'c','nickname'=>'cc'), array('id'=>4,'name'=>'d','nickname'=>'dddddddddddddddddddddddddddddddd'), ); $key = array( 'id' => ['编号',10], 'name' => ['姓名',15], 'nickname' => ['昵称',35], ); // 第二组数据 //-------------------------------------------------------------- $list_02 = array( array('id'=>1,'name'=>'a','nickname'=>'aa2'), array('id'=>2,'name'=>'b','nickname'=>'bb2'), array('id'=>3,'name'=>'c','nickname'=>'cc2'), array('id'=>4,'name'=>'d','nickname'=>'ddddddddddddddddddd2'), ); $key_02 = array( 'id' => ['编号二',10], 'name' => ['姓名二',15], 'nickname' => ['昵称二',25], ); $fileInfo = [ 'file_name'=>'简版测试', 'sheet'=>[ ['sheetIndex'=>0,'sheetTitle'=>'工作区一'], ['sheetIndex'=>1,'sheetTitle'=>'工作区二'], ] ]; $ToolExcel->exportExcelSimp($fileInfo,[$key,$key_02],[$list,$list_02]);
demo 6:综合(更多配置、支持json字符串解析)
<?php include '../vendor/autoload.php'; use whalephp\tool\PHPExcelHelper; $ToolExcel = new PHPExcelHelper(); // 第一组数据 //-------------------------------------------------------------- $list = array( array('id'=>1,'name'=>'a','nickname'=>'aa'), array('id'=>2,'name'=>'b','nickname'=>'bb'), array('id'=>3,'name'=>'c','nickname'=>'cc'), array('id'=>4,'name'=>'d','nickname'=>'dddddddddddddddddddddddddddddddd'), ); $key = array( 'id' => ['编号',10], 'name' => ['姓名',15], 'nickname' => ['昵称',35], ); // 第二组数据 //-------------------------------------------------------------- $list_02 = array( array('id'=>1,'name'=>'a','nickname'=>'aa2', 'data'=>["a"=>"aaa","b"=>"bbb","c"=>"ccc"]), array('id'=>2,'name'=>'b','nickname'=>'bb2', 'data'=>'{"a":"111","b":"222","c":"333"}'), array('id'=>3,'name'=>'c','nickname'=>'ddddddddddddddddddd2', 'data'=>'{"a":"aaa111","b":"bbb222","c":"ccc333"}'), ); $key_02 = array( 'id' => '编号二', 'name' => ['姓名二',15], 'nickname' => '昵称二', 'data.a' => '节点a', 'data.b' => 'data.b', 'data' => [ 'title' => '格式化数据', 'width' => 50, 'parse_json'=> [ 'a' => '节点(a)', 'b' => '节点(b)', 'c' => '节点c', ] ], 'data2' => [ 'title' => '原始数据', 'field' => 'data', // 对应数据中实际的字段键值 'width' => 40 ] ); $fileInfo = [ 'file_name' => '简版测试', 'width' => 20, // 指定默认宽度 'sheet' => [ ['sheetIndex'=>0,'sheetTitle'=>'工作区一'], ['sheetIndex'=>1,'sheetTitle'=>'工作区二'], ] ]; $ToolExcel->exportExcelSimp($fileInfo,[$key_02,$key],[$list_02,$list]);