FastAdmin 个人记录 - 后台渲染导出 Excel - FastAdmin

# 说明

FastAdmin 后台 Bootstrap Table 自带的数据导出功能，通过前端渲染生成 Excel，对于少量数据导出方便，
但是大量数据导出，会造成卡顿卡死。
考虑利用 php 的 PhpSpreadsheet 封装，后台渲染生成 Excel。

# 参考链接

https://ask.fastadmin.net/article/6055.html
https://ask.fastadmin.net/question/1670.html
https://www.cnblogs.com/xuanjiange/p/14545111.html
https://ask.fastadmin.net/question/14012.html
https://blog.csdn.net/qq_15957557/article/details/113607843

# 具体代码

# 导出按钮

在.html 下

	<div id="toolbar" class="toolbar">
	<a href="javascript:;" class="btn btn-success btn-export" title="{:__('Export')}" id="btn-export-file"><i class="fa fa-download"></i> {:__('Export')}</a>
	</div>

# 具体 js

通过 submitForm 方法，把 bootstrapTable 的搜索条件、排序、字段、searchList，都传递给后台控制器方法

	// 自定义导出
	var submitForm = function (ids, layero) {
	var options = table.bootstrapTable('getOptions');
	//console.log(options);
	var columns = [];
	var searchList = {};
	$.each(options.columns[0], function (i, j) {
	if (j.field && !j.checkbox && j.visible && j.field != 'operate') {
	columns.push(j.field);

	// 保存 searchList 传递给后台处理
	if (j.searchList){
	searchList[j.field] = j.searchList;
	}
	}
	});

	var search = options.queryParams({});
	// 指定具体 form 否则选择条件查询后导出会卡一下
	var form = document.getElementById('export');

	$("input[name=search]", layero).val(options.searchText);
	$("input[name=ids]", layero).val(ids);
	$("input[name=filter]", layero).val(search.filter);
	$("input[name=op]", layero).val(search.op);
	$("input[name=columns]", layero).val(columns.join(','));
	$("input[name=sort]", layero).val(options.sortName);
	$("input[name=order]", layero).val(options.sortOrder);
	$("input[name=searchList]", layero).val(JSON.stringify(searchList));
	//$("form", layero).submit();
	form.submit(ids, layero);
	};

	$(document).on("click", ".btn-export", function () {

	var url = ""; // 导出方法的控制器 url

	var ids = Table.api.selectedids(table);
	var page = table.bootstrapTable('getData');
	var all = table.bootstrapTable('getOptions').totalRows;
	console.log(ids, page, all);

	// 这里有个 form 表单里面的 input 和 submitForm 中对应，想传其他参数，可以继续增加 input
	Layer.confirm("请选择导出的选项<form action='" + Fast.api.fixurl(url) + "' id='export' method='post' target='_blank'><input type='hidden' name='ids' value='' /><input type='hidden' name='filter' ><input type='hidden' name='op'><input type='hidden' name='sort'><input type='hidden' name='order'><input type='hidden' name='search'><input type='hidden' name='columns'><input type='hidden' name='searchList'></form>", {
	title: '导出数据',
	btn: ["选中项(" + ids.length + "条)", "本页(" + page.length + "条)", "全部(" + all + "条)"],
	success: function (layero, index) {
	$(".layui-layer-btn a", layero).addClass("layui-layer-btn0");
	}
	, yes: function (index, layero) {
	submitForm(ids.join(","), layero);
	return false;
	}
	,
	btn2: function (index, layero) {
	var ids = [];
	$.each(page, function (i, j) {
	ids.push(j.id);
	});
	submitForm(ids.join(","), layero);
	return false;
	}
	,
	btn3: function (index, layero) {
	submitForm("all", layero);
	return false;
	}
	})
	});

# 后台控制器方法

	public function export()
	{
	if ($this->request->isPost()) {
	set_time_limit(0);
	$search = $this->request->post('search');
	$ids = $this->request->post('ids');
	$filter = $this->request->post('filter');
	$op = $this->request->post('op');
	$sort = $this->request->post('sort');
	$order = $this->request->post('order');
	$columns = $this->request->post('columns');
	$searchList = $this->request->post('searchList');
	$searchList = json_decode($searchList,true);

	$spreadsheet = new Spreadsheet();
	$spreadsheet->getProperties()
	->setCreator("FastAdmin")
	->setLastModifiedBy("FastAdmin")
	->setTitle("标题")
	->setSubject("Subject");
	$spreadsheet->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
	$spreadsheet->getDefaultStyle()->getFont()->setSize(10);
	// 单元格格式文本
	$spreadsheet->getDefaultStyle()->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);

	$worksheet = $spreadsheet->setActiveSheetIndex(0);
	$whereIds = $ids == 'all' ? '1=1' : ['id' => ['in', explode(',', $ids)]];
	$this->request->get(['search' => $search, 'ids' => $ids, 'filter' => $filter, 'op' => $op, 'sort' => $sort, 'op' => $order]);
	list($where, $sort, $order, $offset, $limit) = $this->buildparams();

	//$columns 是得到的字段，可以在这里写上自己的逻辑，比如删除或添加其他要写的字段
	$columns_arr = explode(',',$columns);

	$line = 1;
	$list = [];
	$this->model
	->field($columns)
	->where($where)
	->where($whereIds)
	->chunk(100, function ($items) use (&$list, &$line, &$worksheet,&$columns_arr,&$searchList) {
	$list = $items = collection($items)->toArray();
	foreach ($items as $index => $item) {
	$line++;
	$col = 1;
	foreach ($item as $field => $value) {
	// 只导出传递的字段过滤 createtime_text 等 modeld 中的附加字段
	if (!in_array($field,$columns_arr)) continue;

	// 根据前端传递的 $searchList 处理状态等
	if (isset($searchList[$field])){
	$value = $searchList[$field][$value] ?? $value;
	}

	if (strlen($value) < 10){
	$worksheet->setCellValueByColumnAndRow($col, $line, $value);
	}else{
	// 防止长数字科学计数
	$worksheet->setCellValueExplicitByColumnAndRow($col, $line, $value,\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
	}
	$col++;
	}
	}
	},$sort,$order);
	$first = array_keys($list[0]);
	foreach ($first as $index => $item) {
	// 只导出传递的字段
	if (!in_array($item,$columns_arr)) continue;
	$worksheet->setCellValueByColumnAndRow($index + 1, 1, __($item));
	// 单元格自适应宽度
	$spreadsheet->getActiveSheet()->getColumnDimensionByColumn($index + 1)->setAutoSize(true);
	// 首行加粗
	$spreadsheet->getActiveSheet()->getStyleByColumnAndRow($index + 1, 1)->getFont()->setBold(true);
	// 水平居中
	$styleArray = [
	'alignment' => [
	'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
	],
	];
	$spreadsheet->getActiveSheet()->getStyleByColumnAndRow($index + 1, 1)->applyFromArray($styleArray);
	}

	$spreadsheet->createSheet();
	// Redirect output to a client’s web browser (Excel2007)
	$title = date("YmdHis");
	//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	//header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
	//header('Cache-Control: max-age=0');
	// If you're serving to IE 9, then the following may be needed
	//header('Cache-Control: max-age=1');

	// If you're serving to IE over SSL, then the following may be needed
	//header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	//header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
	//header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	//header('Pragma: public'); // HTTP/1.0

	$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
	// 下载文档
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
	header('Cache-Control: max-age=0');
	$writer = new Xlsx($spreadsheet);
	$writer->save('php://output');

	return;
	}
	}

# 不足之处

大数据导出还是会内存溢出

有个轻量的 Excel 封装，能解决内存溢出问题 https://github.com/mk-j/PHP_XLSXWriter

# PHP_XLSXWriter 参考链接

https://blog.csdn.net/qq_36025814/article/details/117136435
https://blog.csdn.net/qq_21193711/article/details/105285623
https://github.com/mk-j/PHP_XLSXWriter

# PHP_XLSXWriter 使用

# copy 用法

把 xlsxwriter.class.php 文件放在目录根目录 \application\common\libs 下
修改文件名 xlsxwriter.class.php 为 XLSXWriter.php
在文件中引入命名空间 namespace app\common\library;
把压缩包类名：（1）、ZipArchive () 改成 \ZipArchive ()；（2）、ZipArchive::CREATE 改成 \ZipArchive::CREATE
扩展：可把 XLSXWriter_BuffererWriter 抽出来，改成一个单独的类 XLSXWriterBufferWriter。在 XLSXWriter.php 中调用的地方修改一下。

	<?php
	namespace app\common\library;

	class XLSXWriter
	{
	}

# 替换之前的 export 方法

	public function export()
	{
	if ($this->request->isPost()) {
	set_time_limit(0);
	ini_set("memory_limit", "256M");
	$search = $this->request->post('search');
	$ids = $this->request->post('ids');
	$filter = $this->request->post('filter');
	$op = $this->request->post('op');
	$sort = $this->request->post('sort');
	$order = $this->request->post('order');
	$columns = $this->request->post('columns');
	$searchList = $this->request->post('searchList');
	$searchList = json_decode($searchList,true);

	$whereIds = $ids == 'all' ? '1=1' : ['id' => ['in', explode(',', $ids)]];
	$this->request->get(['search' => $search, 'ids' => $ids, 'filter' => urldecode($filter), 'op' => urldecode($op), 'sort' => $sort, 'order' => $order]);
	list($where, $sort, $order, $offset, $limit) = $this->buildparams();

	//$columns 是得到的字段，可以在这里写上自己的逻辑，比如删除或添加其他要写的字段
	$columns_arr = $new_columns_arr = explode(',',$columns);
	$key = array_search('serviceFee',$columns_arr);
	if ($key){
	$new_columns_arr[$key] = "(platformFee + agentFee)/100 AS serviceFee";
	$columns = implode(',',$new_columns_arr);
	}

	//todo
	$userInfo = $this->auth->getUserInfo();
	$map = [];

	// 代理
	if ($userInfo['level'] == \Pc::Agent_Level){
	$info = model('Agent')->where([
	'accname' => $userInfo['username'],
	])->find();
	if (!$info) $this->error(__('Permission denied'));
	$map['agentID'] = $info['id'];
	}

	// 限制商户显示
	if ($userInfo['level'] == \Pc::Merchant_Level){
	$info = model('Merchant')->where([
	'accname' => $userInfo['username'],
	])->find();
	if (!$info) $this->error(__('Permission denied'));
	$map['mid'] = $info['mid'];
	}

	$count = $this->model
	->where($where)
	->where($whereIds)
	->where($map)
	->count();

	if ($count > 50000){
	$this->error('数据量过大,建议分批导出','');
	}

	$title = date("YmdHis");
	$fileName = $title . '.xlsx';
	$writer = new \app\common\library\XLSXWriter();
	$sheet = 'Sheet1';

	// 处理标题数据，都设置为 string 类型
	$header = [];
	foreach ($columns_arr as $value) {
	$header[__($value)] = 'string'; // 把表头的数据全部设置为 string 类型
	}
	$writer->writeSheetHeader($sheet, $header);

	$this->model
	->field($columns)
	->where($where)
	->where($whereIds)
	->where($map)
	->chunk(1000, function ($items) use (&$list, &$writer,&$columns_arr,&$searchList) {
	$list = $items = collection($items)->toArray();
	foreach ($items as $index => $item) {
	// 根据标题数据 title，按 title 的字段顺序把数据一条条加到 excel 中
	$sheet = 'Sheet1';
	$row = [];

	foreach ($item as $field => $value) {
	// 只导出传递的字段过滤 createtime_text 等 modeld 中的附加字段
	if (!in_array($field,$columns_arr)) continue;

	// 根据前端传递的 $searchList 处理状态等
	if (isset($searchList[$field])){
	$value = $searchList[$field][$value] ?? $value;
	}
	$row[$field] = $value;

	}

	$writer->writeSheetRow($sheet, $row);
	}
	},$sort,$order);



	// 设置 header，用于浏览器下载
	header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($fileName).'"');
	header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
	header('Content-Transfer-Encoding: binary');
	header('Cache-Control: must-revalidate');
	header('Pragma: public');
	$writer->writeToStdOut();
	exit(0);
	}
	}

# 基本使用

	<?php

	use app\common\libs\XLSXWriter;

	class Test {
	// 入口方法
	public function test() {
	$title = self::getTitle();
	$data = self::getData();

	// 下载直接保存文件
	// self::downloadExcel($title, $data, 'download_by_file.xlsx');

	// 浏览器弹框下载
	self::downloadExcel($title, $data, 'downloadByBrowser.xlsx', 2);
	}

	/**
	* 把数据下载为文件
	* @param $title 标题数据
	* @param $data 内容数据
	* @param $fileName 文件名（可包含路径）
	* @param int $type Excel 下载类型：1 - 下载文件；2 - 浏览器弹框下载
	* @param string $sheet Excel 的工作表
	*/
	public function downloadExcel($title, $data, $fileName, $type = 1, $sheet = 'Sheet1')
	{
	$writer = new XLSXWriter();

	if ($type == 2) {
	// 设置 header，用于浏览器下载
	header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($fileName).'"');
	header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
	header('Content-Transfer-Encoding: binary');
	header('Cache-Control: must-revalidate');
	header('Pragma: public');
	}

	// 处理标题数据，都设置为 string 类型
	$header = [];
	foreach ($title as $value) {
	$header[$value] = 'string'; // 把表头的数据全部设置为 string 类型
	}
	$writer->writeSheetHeader($sheet, $header);

	// 根据标题数据 title，按 title 的字段顺序把数据一条条加到 excel 中
	foreach ($data as $key => $value) {
	$row = [];
	foreach ($title as $k => $val) {
	$row[] = $value[$k];
	}
	$writer->writeSheetRow($sheet, $row);
	}

	if ($type == 1) { // 直接保存文件
	$writer->writeToFile($fileName);
	} else if ($type == 2) { // 浏览器下载文件
	$writer->writeToStdOut();
	// echo $writer->writeToString();
	exit(0);
	} else {
	die('文件下载方式错误~');
	}
	}

	public function getData()
	{
	$data = [];
	for ($i = 0; $i < 10; $i ++) {
	$data[] = ['name' => "姓名{$i}", 'hobby' => "爱好{$i}"];
	}
	return $data;
	}

	public function getTitle()
	{
	return [
	'name' => '姓名',
	'hobby' => '爱好'
	];
	}
	}

教程 FastAdmin

# 说明

# 参考链接

# 具体代码

# 导出按钮

# 具体 js

# 后台控制器方法

# 不足之处

# PHP_XLSXWriter 参考链接

# PHP_XLSXWriter 使用

# copy 用法

# 替换之前的 export 方法

# 基本使用

nazurin搭建-基于 Telegram, Heroku, MEGA 及 Firebase 的图片收藏自动工作流

oneDrive + oneManager + heroku 部署个人网盘