thinkphp5+phpspreadsheet实现多表多sheet导出excel

接上一篇导出excel，本文涉及接口类和抽象类，看似没关系，实际就是没什么关系，直接使用即可，下面是代码：

需要掌握的技能：使用composer引入phpspreadsheet代码

实现一个Base基本抽象类

1
2
3
4
5
6
7
8
9
10
11
12

<?php
declare (strict_types = 1);
namespace app\index\stract;

/**
* 实现一个Base基本抽象类
*/
abstract class QueryBase
{
public $public_url = 'http://www.xxxxxxxxx.cn';

}

继承公共抽象类实现导出抽象类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

<?php
declare (strict_types = 1);
namespace app\index\stract;

/**
* 导入composer引入的phpspreadsheet类
*/
use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**
* 继承公共抽象类实现导出抽象类
*/
abstract class Export extends QueryBase
{
public $spreadsheet;

abstract function loadExportClass(): object;

}

实现一个excel导出接口类，写出继承类需要实现的几个方法

1
2
3
4
5
6
7
8
9
10
11
12

<?php
declare (strict_types = 1);
namespace app\index\face;

/**
* 实现一个excel导出接口类，写出继承类需要实现的几个方法
*/
interface Export
{
function exportSheet(): bool;

}

实现最终的导出类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

<?php
declare (strict_types = 1);
namespace app\index\controller;
use app\index\face\Export as ExportInterface;
use app\index\model\Business;
use app\index\model\Merchant;
use app\index\model\Store;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use app\index\stract\Export as ExportStract;

/**
* 最终实现的导出类
*/
class Export extends ExportStract implements ExportInterface
{
//定义文件名称
public $file_name = '';

//构造方法
public function __construct()
{
self::loadExportClass();
}

//实现抽象类的方法
public function loadExportClass(): object
{
$this->spreadsheet = new Spreadsheet();
return $this->spreadsheet;
}

//实际导出方法
public function exportSheet(): bool
{
$Business = Business::all()->toArray();
$Merchant = Merchant::all()->toArray();
$Store = Store::all()->toArray();
$data_array = [
['title'=>'表名1', 'rows'=>$Business],
['title'=>'表名2', 'rows'=>$Merchant],
['title'=>'表名3', 'rows'=>$Store],
];
foreach ($data_array as $key => $data) {
$this->handleSheet($this->spreadsheet, $key, $data);
}
$this->file_name = '导出信息表';
return true;
}

public function handleSheet(Spreadsheet $spreadsheet, int $n, array $data): bool
{
$spreadsheet->createSheet();//创建sheet
$sheet = $spreadsheet->getActiveSheet($n);
$objActSheet = $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet
$sheet = $spreadsheet->getActiveSheet($n)->setTitle($data['title']);//设置sheet的名称
$column = [];
switch($n){
case 0:
$column = ['id', 'name', 'age'];
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', '姓名');
$sheet->setCellValue('C1', '年龄');
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(15);
$sheet->getColumnDimension('C')->setWidth(50);
break;
case 1:
$column = ['id', 'name', 'age', 'sex', 'address'];
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', '姓名');
$sheet->setCellValue('C1', '年龄');
$sheet->setCellValue('D1', '性别');
$sheet->setCellValue('E1', '地址');
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(15);
$sheet->getColumnDimension('C')->setWidth(50);
$sheet->getColumnDimension('D')->setWidth(20);
$sheet->getColumnDimension('E')->setWidth(20);
break;
case 2:
$column = ['id', 'name', 'age', 'business_name'];
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', '姓名');
$sheet->setCellValue('C1', '年龄');
$sheet->setCellValue('D1', '公司名称');
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(15);
$sheet->getColumnDimension('C')->setWidth(15);
$sheet->getColumnDimension('D')->setWidth(50);
break;
default:
break;
}
$count = count($column);
foreach ($data['rows'] as $key => $item)
{
//循环设置单元格：
//$key+$infoStart,因为第一行是表头，所以写到表格时从第数据行开始写
for ($i = 65; $i < $count + 65; $i++)
{
//数字转字母从65开始：
$sheet->setCellValue(strtoupper(chr($i)) . ($key+2), $item[$column[$i - 65]]);
}
}
return true;
}

//析构方法，所有方法结束之后都执行
public function __destruct()
{
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$this->file_name.'.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($this->spreadsheet);
$writer->save('php://output');
//删除清空
$this->spreadsheet->disconnectWorksheets();
unset($this->spreadsheet);
exit;
}

}

最后直接运行exportSheet方法即可导出数据

发送评论 编辑评论

推荐文章

发送评论编辑评论