fastadmin使用phpoffice实现服务端自定义导出功能

admin · 发表于 2024-5-31 20:08:01

第一步，要在列表对应的js文件内增加一段代码：

$(document).on("click", ".btn-export", function () {
console.log('eeee')
var ids = Table.api.selectedids(table);
var page = table.bootstrapTable('getData');
var all = table.bootstrapTable('getOptions').totalRows;
console.log(ids, page, all);
Layer.confirm("请选择导出的选项<form action='" + Fast.api.fixurl("map/shortlink/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='search'><input type='hidden' name='columns'></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;
}
})
});
var submitForm = function (ids, layero) {
var options = table.bootstrapTable('getOptions');
console.log(options);
var columns = [];
$.each(options.columns[0], function (i, j) {
if (j.field && !j.checkbox && j.visible && j.field != 'operate') {
columns.push(j.field);
}
});
var search = options.queryParams({});
$("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(','));
$("form", layero).submit();
};

复制代码

添加在：

// 为表格绑定事件
Table.api.bindevent(table);

复制代码

这个代码下面。
第二步，需要在对应的控制器文件内新增导出方法，代码如下：

/**
* 导出订单数据
*/
public function export()
{
$ids = $this->request->post("ids");
$filter = $this->request->post("filter");
$op = $this->request->post("op");
$search = $this->request->post("search");
// $columns = $this->request->post("columns");
$columns = ['id','shopname','realname','mobile','address','linkurl'];
// 解析搜索条件
$where = [];
if ($filter) {
$filter = (array)json_decode($filter, true);
$op = (array)json_decode($op, true);
foreach ($filter as $key => $val) {
if (isset($op[$key])) {
switch ($op[$key]) {
case 'LIKE':
$where[] = [$key, 'like', "%{$val}%"];
break;
case '=':
$where[] = [$key, '=', $val];
break;
case 'IN':
$where[] = [$key, 'in', $val];
break;
}
}
}
// 获取数据
$list = $this->model->where($where)->field($columns)->select();
$list = collection($list)->toArray();
}
// 如果选择了全部导出，则忽略ids
if ($ids !== 'all') {
$ids = explode(',', $ids);
// 获取数据
$list = $this->model->where('id', 'in', $ids)->field($columns)->select();
$list = collection($list)->toArray();
}
// 获取字段注释
$table = $this->model->getTable();
$fieldComments = $this->getFieldComments($table, $columns);
// 创建一个新的 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 设置表头
$colIndex = 1;
foreach ($columns as $column) {
$sheet->setCellValueByColumnAndRow($colIndex, 1, isset($fieldComments[$column]) ? $fieldComments[$column] : $column);
$colIndex++;
}
// 设置数据
$rowIndex = 2;
foreach ($list as $row) {
$this->model->where('id', $row['id'])->update(['status'=>1]);
$colIndex = 1;
foreach ($columns as $column) {
$sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $row[$column]);
$colIndex++;
}
$rowIndex++;
}
// 导出 Excel 文件
$filename = '短链数据导出_' . date('Y-m-d_H_i_s') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
}
/**
* 获取字段注释
* @param string $table
* @param array $columns
* @return array
*/
private function getFieldComments($table, $columns)
{
$fieldComments = [];
$result = Db::query("SHOW FULL COLUMNS FROM `{$table}`");
foreach ($result as $column) {
if (in_array($column['Field'], $columns)) {
$fieldComments[$column['Field']] = $column['Comment'];
}
}
return $fieldComments;
}

复制代码

需要再控制器文件增加phpoffice功能文件的引用，代码如下：

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

复制代码

这样导出功能就完成了，亲测可用，有偿操作可联系QQ2513533699