电话
13363039260
后台列表导出excel示例
2024-01-26 14:22 小编
如图所示在后台列表中增加导出按钮，用于导出当前搜索结果的全部数据到excel
实现需求需要使用教程：https://www.xunruicms.com/doc/1026.html
例子我们以news模块为例，在列表中增加导出功能
1、新建news的模板文件，采用方法：
https://www.xunruicms.com/doc/720.html
2、打开news列表的模板文件，增加按钮代码，注意按钮的url一定要正确
<label><a href="{dr_url('news/api/excel', ['sq' => $list_query, 'tb' => $list_table])}" class="btn green btn-sm onloading" > {dr_lang('导出')}</a></label>
sq：表示table类生成的sql语句别名
tb：表示当前查询的表名称
3、新建控制器文件：dayrui/App/News/Controllers/Admin/Api.php
<?php namespace Phpcmf\Controllers\Admin;
/**
* 二次开发时可以修改本文件，不影响升级覆盖
*/
class Api extends \Phpcmf\Admin\Module
{
protected function _Admin_List() {
$this->fix_table_list = true;
parent::_Admin_List();
}
// 导出excel
public function excel() {
$sql = dr_safe_replace(\Phpcmf\Service::L('input')->get('sq'));
$sql = dr_authcode($sql, 'DECODE');
if (!$sql) {
$this->_admin_msg(0, 'SQL语句解析失败');
}
// 去掉limit语句
$arr = explode(' LIMIT ', $sql);
$sql = $arr[0];
if (!$sql) {
$this->_admin_msg(0, 'SQL语句解析失败');
}
// 查询结果
$list = \Phpcmf\Service::M()->db->query($sql)->getResultArray();
if (!$list) {
$this->_admin_msg(0, '查询结果为空');
}
$data = [];
$title = ['标题', '录入时间', '文章链接']; // 导出的标题格式
foreach ($list as $t) {
$data[] = [
$t['title'],
dr_date($t['inputtime']),
dr_url_prefix($t['url']),
];
}
// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 方法一，使用 setCellValueByColumnAndRow
//表头
//设置单元格内容
foreach ($title as $key => $value) {
// 单元格内容写入
$sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
}
$row = 2; // 从第二行开始
foreach ($data as $item) {
$column = 1;
foreach ($item as $value) {
// 单元格内容写入
$sheet->setCellValueByColumnAndRow($column, $row, $value);
$column++;
}
$row++;
}
$name = dr_date(SYS_TIME, 'YmdHis');
// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.urlencode($name).'.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, 'Xlsx');
$writer->save('php://output');
exit;
}
}
4、然后点击（1）中的导出按钮即可生成excel文件
如果遇到系统故障等问题时，需要引入excel文件类：https://www.xunruicms.com/doc/1026.html
// 导出excel
public function excel() {
$sq = $sql = dr_safe_replace(\Phpcmf\Service::L('input')->get('sq'));
$sql = dr_authcode($sql, 'DECODE');
if (!$sql) {
$this->_admin_msg(0, 'SQL语句解析失败');
}
// 去掉limit语句
$arr = explode(' LIMIT ', $sql);
$sql = $arr[0];
if (!$sql) {
$this->_admin_msg(0, 'SQL语句解析失败');
}
$page = max(1, (int)\Phpcmf\Service::L('input')->get('page'));
$path = WRITEPATH.'temp/excel-'.$sq.'/';
if ($page == 1) {
dr_dir_delete($path);
dr_mkdirs($path);
}
if ($page == 999) {
if (is_file($path.'1.xlsx')) {
// code...
$cname = date('Y-m-d-H-i-s').'.zip';
$zipfile = $path.$cname;
$rt = \Phpcmf\Service::L('file')->zip($zipfile, $path);
if ($rt) {
$this->_admin_msg(0, $rt);
}
set_time_limit(0);
$handle = fopen($zipfile,"rb");
if (FALSE === $handle) {
$this->_admin_msg(0, dr_lang('文件已经损坏'));
}
$filesize = filesize($zipfile);
header('Content-Type: application/octet-stream');
header("Accept-Ranges:bytes");
header("Accept-Length:".$filesize);
header("Content-Disposition: attachment; filename=".$cname);
while (!feof($handle)) {
$contents = fread($handle, 4096);
echo $contents;
ob_flush(); //把数据从PHP的缓冲中释放出来
flush(); //把被释放出来的数据发送到浏览器
}
fclose($handle);
ob_end_clean();
exit;
} else {
$this->_admin_msg(0, dr_lang('文件丢失'));
}
}
$psize = 10000;
$a = $psize * ($page - 1);
$sql.= ' LIMIT '.$a.','.$psize;
// 查询结果
$list = \Phpcmf\Service::M()->db->query($sql)->getResultArray();
if (!$list) {
// 查询完毕
if (is_file($path.'1.xlsx')) {
// code...
$this->_admin_msg(1, '导出完毕', dr_url(APP_DIR.'/home/excel', [
'sq' => $sq,
'page' => 999,
]));
}
$this->_admin_msg(0, dr_lang('内容查询为空'));
}
$data = [];
$title = ['产品', '防伪码', '查询链接（二维码内容）']; // 导出的标题格式
foreach ($list as $t) {
$data[] = [
$t['title'],
$t['fangweima'],
'',
];
}
// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 方法一，使用 setCellValueByColumnAndRow
//表头
//设置单元格内容
foreach ($title as $key => $value) {
// 单元格内容写入
$sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
}
$row = 2; // 从第二行开始
foreach ($data as $item) {
$column = 1;
foreach ($item as $value) {
// 单元格内容写入
$sheet->setCellValueByColumnAndRow($column, $row, $value);
$column++;
}
$row++;
}
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$file = $path.$page.'.xlsx';
$writer->save($file);
$this->_admin_msg(1, '正在导出中（'.$page.'）...', dr_url(APP_DIR.'/home/excel', [
'sq' => $sq,
'page' => $page+1,
]));
}