redis下载安装（window版本）
参考地址：https://blog.csdn.net/Ci1693840306/article/details/144214215
php安装redis扩展
参考链接：https://blog.csdn.net/jianchenn/article/details/106144313
解决思路：（分批处理，最后合并）
业务逻辑：本项目由于涉及到多张数据表，导出业务逻辑为：先查询主表，查询出数据后通过foreach遍历数据，并在遍历循环中根据与主表关联的字段查询另外几张表对应数据。
解决方案：
后端：
- 先将字典表、需要在循环中查询的所有数据表存储到redis中（如果数据过多，可以将其分为多个方法）
- 将导出数据接口中的数据进行分页，根据页码数导出相应的数据条数，并存放至临时excel文件中。等待全部执行完毕后将这些临时文件合并成一个excel文件，并返回。
前端：
- 首先请求字典等数据表存入redis的接口
- 请求导出数据接口，每次传入当前需导出数据的页码数，在没有全部完成之前页数++，直到完成后执行下载文件操作
PHP-Xlswriter扩展安装：
官网：https://xlswriter-docs.viest.me/zh-cn/an-zhuang/windows
在thinkphp6项目中打开config/cache.php，加上redis配置参数：
<?php
// +----------------------------------------------------------------------
// | 缓存设置
// +----------------------------------------------------------------------
return [
// 默认缓存驱动
'default' => env('cache.driver', 'file'),
// 缓存连接方式配置
'stores' => [
'file' => [
// 驱动方式
'type' => 'File',
// 缓存保存目录
'path' => '',
// 缓存前缀
'prefix' => '',
// 缓存有效期 0表示永久缓存
'expire' => 0,
// 缓存标签前缀
'tag_prefix' => 'tag:',
// 序列化机制 例如 ['serialize', 'unserialize']
'serialize' => [],
],
// 更多的缓存连接
'redis' => [
'type' => 'redis',
// 缓存主机
'host' => '127.0.0.1',
// 缓存端口
'port' => '6379',
// 缓存密码
'password' => '',
// 缓存数据库
'select' => 0,
// 缓存有效期 0表示永久缓存
'timeout' => 0,
// 缓存前缀
'prefix' => ''
]
],
];
后端路由route/app.php
Route::post('export_data/:code/:id', 'Basicinfo/export_data'); // 根据指定条件和字段导出数据
Route::post('export_save_redis/:code/:id', 'Basicinfo/export_save_redis'); // 导出之前将部分数据存入redis
Route::post('export_save_redis2/:code/:id', 'Basicinfo/export_save_redis2'); // 导出之前将部分数据存入redis
控制器 BasicinfoController.php
<?php
namespace app\controller;
use app\service\BasicinfoService;
class BasicinfoController {
public function export_save_redis(){
$data = input('post.');
$service = new BasicinfoService;
$res = $service->export_basicinfo_save_redis($data);
return show(true, '', $res);
}
public function export_save_redis2(){
$data = input('post.');
$service = new BasicinfoService;
$res = $service->export_basicinfo_save_redis_person($data);
return show(true, '', $res);
}
public function export_data(){
$data = input('post.');
$service = new BasicinfoService;
$res = $service->export_data($data);
if($res['state']){
if(file_exists($res['file'])){
return show(true, '', $res['file']);
}else{
return show(false, '文件导出失败');
}
}else{
return show(false, '', $res);
}
}
}
BasicinfoService.php
<?php
namespace app\service;
// 引入其他文件...
class BasicinfoService{
public function export_save_redis($data){
$mapper = new BasicinfoMapper;
$mapper -> export_save_redis($data['search']??[]);
return true;
}
// 由于第此数据表数据量过多，导致保存失败，单独处理
public function export_save_redis2($data){
$mapper = new BasicinfoMapper;
$mapper -> export_save_redis2($data['search']??[]);
return true;
}
public function export_data($data){
$result_data = [];
$data['page'] = isset($data['page']) ? $data['page'] : 1;
// 获取所有要查询的字段和名称
$header_arr = [];
$fields_arr = [];
foreach($data['export_data'] as $key=>$val){
array_push($header_arr, $val['label']);
array_push($fields_arr, $val['field']);
}
// 文件存储目录
$public = app()->getRootPath().'public/';
$path = 'uploads/export_data/';
$file_name = !empty($data['file_name']) ? $data['file_name'] : 'basicinfo_'.rand(99999, 99999999);
$result_data['file_name'] = $file_name;
$fileName = $file_name.'_'.$data['page'].'.xlsx';
if(!file_exists($path)){
mkdir($path, 0777);
}
$excel_config = [
'path' => $public.$path // xlsx文件保存路径
];
$excel = new \Vtiful\Kernel\Excel($excel_config);
$fileObject = $excel->fileName($fileName, 'sheet1');
$mapper = new BasicinfoMapper;
$page_size = 5000; // 每次查询的数据条数
// 在第1页时需要查询总数量，并获取总页数
if($data['page'] == 1){
// 获取总数
$count = $mapper->get_count_basicinfo($data['search']??[]);
$loop_num = ceil($count/$page_size);
$data['total_page'] = $loop_num; // 设置总页数
$result_data['total_page'] = $loop_num; // 返回总页数
}else{
$result_data['total_page'] = $data['total_page'];
}
$loop_page = 2; // 每页执行n次循环
$loop_num = $loop_page;
// 检测当前页数 * 循环数量 >= 总页数后
if(intval($data['page'] * $loop_page) >= intval($data['total_page'])){
if(intval($data['page'] * $loop_page) == intval($data['total_page'])){
$loop_num = 1;
}else{
$loop_num = ($data['page'] * $loop_num) - $data['total_page'];
}
}
// 当前循环完成后最大id
$max_id = !empty($data['max_id']) ? $data['max_id'] : 0;
for($l=0; $l<$loop_num; $l++){
$list = $mapper->export_list_basicinfo($data['search']??[], $fields_arr, $max_id, $page_size);
if($list){
$max_id = $list[count($list)-1]['organ_id']; // 重置最大id
// var_dump($max_id);
$content = [];
$i=0;
foreach($list as &$val){
// 处理转化数据值
// ...
$result = [];
foreach ($fields_arr as $key) {
if (isset($val_arr[$key])) {
$result[$key] = $val[$key];
}else{
$result[$key] = '';
}
}
$content[$i] = array_values($result);
$i++;
unset($val_arr);
unset($val);
unset($result);
}
// 将数据写入xls文件
if(count($content) > 0){
$fileObject->data($content)->output();
unset($content);
}
}
unset($list);
}
$result_data['max_id'] = $max_id;
// 检测如果为最后一页，则将文件合并后返回
if(intval($data['page'] * $loop_page) >= intval($data['total_page'])){
$result_data['state'] = true;
// 处理合并文件
$res = $this->merge_export_file($file_name, $data['page'], $header_arr);
// $file_dir = 'uploads/export_data/'.$fileName;
$result_data['file'] = $res;
}else{
$result_data['state'] = false;
$result_data['file'] = '';
}
return $result_data;
}
// 处理合并文件
public function merge_export_file($fileName, $page, $header_arr){
$public = app()->getRootPath().'public/';
$path = 'uploads/export_data/';
$excel_config = [
'path' => $public.$path // xlsx文件保存路径
];
$excel = new \Vtiful\Kernel\Excel($excel_config);
$res_file = $fileName . '_all.xlsx';
$fileObject = $excel->fileName($res_file, 'sheet1');
// // 设置样式
$fileHandle = $fileObject->getHandle();
$format = new \Vtiful\Kernel\Format($fileHandle);
$alignStyle = $format
->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER_ACROSS)
->toResource();
$boldStyle = $format
// ->bold() // 加粗
// ->wrap() // 文本换行
// ->background(0xFFB6C1) // 设置背景颜色 颜色常量和16进制数
->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER) // 文本居中
->toResource();
// // fileName 会自动创建一个工作表，你可以自定义该工作表名称，工作表名称为可选参数
$fileObject->header($header_arr)
// ->data($content)
->defaultFormat($alignStyle)
->setRow('A1', 30, $boldStyle)
->setRow('A2:A9999', 20) // 行高
// ->setColumn('A:A', '10')
->setColumn('A:CZ', 30)
->output();
$file_arr = [];
for($i=1; $i<=$page; $i++){
$nowFile = $fileName.'_'.$i.'.xlsx';
array_push($file_arr, $nowFile);
if(file_exists($path.$nowFile)){
$data = $excel->openFile($nowFile)
->openSheet()
->getSheetData();
$fileObject->data($data)->output();
}
}
// 回收资源
$fileObject -> close();
// 删除临时文件
foreach($file_arr as $file){
if(file_exists($path.$file)){
unlink($path.$file);
}
}
return $path.$res_file;
}
}
BasicinfoMapper.php
<?php
namespace app\mapper;
use think\facade\Db;
use app\model\Basicinfo;
use think\facade\Cache;
class BasicinfoMapper
{
public function export_save_redis($search){
$where = '';
$out_time = 600; // 缓存时间秒
$batch_size = 2000; // 每批次处理的数量
$redis = Cache::store('redis')->handler();
$redis->flushDb(); // 清空redis缓存
$sql1= 'select main_id,field1,field2,... from table1 where 1=1 '.$where;
$list1 = Db::query($sql1);
if(!empty($list1)){
// 建立管道
$pipe1 = $redis->pipeline();
$batches = array_chunk($list1, $batch_size); // 将数据分批
foreach ($batches as $batch) {
foreach ($batch as $v) {
$pipe1->hMSet('table1_'.$v['main_id'], $v);
$pipe1->expire('table1_'.$v['main_id'], $out_time); // 设置过期时间
}
$pipe1->exec(); // 执行当前批次
$pipe1 = $redis->pipeline(); // 重新初始化管道
}
}
unset($list1);
$sql2= 'select main_id,field1,field2,... from table2 where 1=1 '.$where;
$list2 = Db::query($sql2);
if(!empty($list2)){
$pipe2 = $redis->pipeline();
$batches2 = array_chunk($list2, $batch_size); // 将数据分批
foreach ($batches2 as $batch) {
foreach ($batch as $v) {
$pipe2->hMSet('table2_'.$v['main_id'], $v);
$pipe2->expire('table2_'.$v['main_id'], $out_time); // 设置过期时间
}
$pipe2->exec(); // 执行当前批次
$pipe2 = $redis->pipeline(); // 重新初始化管道
}
}
unset($list2);
}
public function export_save_redis2($search){
$where = '';
$out_time = 600; // 缓存时间秒
$batch_size = 2000; // 每批次处理的数量
$redis = Cache::store('redis')->handler();
// $redis->flushDb(); // 清空redis缓存
// $temp = '';
$sql = 'select main_id,field1,field2,... from table3 where type in (1,3) '.$where;
$list = Db::query($sql);
if(!empty($list)){
$pipe = $redis->pipeline();
$batches = array_chunk($list, $batch_size); // 将数据分批
foreach ($batches as $batch) {
foreach ($batch as $v) {
$key = $v['type'] == 1 ? 'table3_1_'.$v['main_id'] : 'table3_3_'.$v['main_id'];
$pipe->hMSet($key, $v);
$pipe->expire($key, $out_time);
}
$pipe->exec(); // 执行当前批次
$pipe = $redis->pipeline(); // 重新初始化管道
}
}
unset($list);
}
public function export_list_basicinfo($search, $fields=[], $max_id=0, $limit=1000){
$pFields = [];
$dFields = [];
$cFields = [];
$fields = array_reduce($fields, function($carry, $field) use (&$pFields,&$dFields,&$cFields) {
if (substr($field, 0, 6) === 'table1_') {
$dFields[] = $field;
} else if (substr($field, 0, 7) === 'table2_') {
// $carry[] = 'n.' . $field;
$cFields[] = $field;
} else if (substr($field, 0, 6) === 'table3') {
$pFields[] = $field;
} else {
$carry[] = $field;
}
return $carry;
}, []);
$fields = implode(',', $fields);
$where = '1 = 1';
// 其他条件...
$sql = 'select main_id,'.$fields." from basicinfo where ".$where.' and main_id > '.$max_id.' order by main_id limit '.$limit;
$redis = Cache::store('redis')->handler();
$list = Db::query($sql);
if(!$list){ return []; }
foreach ($list as $k => $v){
$fd = [];
if(count($dFields) > 0){
$p_info = $redis->hGetAll('table1_'. $v['main_id']);
if(!empty($p_info)){
$fd = $p_info;
}
}
if(in_array('organ', $dFields)){
$list[$k]['organ'] = !empty($fd) ? $fd['organ']:'';
}
$fc = [];
if(count($cFields) > 0){
if (!empty($redis->hGetAll('table2_'. $v['main_id']))) {
$fc = $redis->hGetAll('table2_'. $v['main_id']);
}
}
if(in_array('party', $cFields)){
$list[$k]['party'] = !empty($fc) ? $fc['party'] : 0;
}
$jbr = [];
$fr = [];
if(count($pFields) > 0){
$table31_info = $redis->hGetAll('table3_1_'. $v['organ_idno']);
$table33_info = $redis->hGetAll('table3_3_'. $v['organ_idno']);
if(!empty($table31_info)){
$jbr = $table31_info;
}
if(!empty($table33_info)){
$fr = $table33_info;
}
}
if(in_array('name', $pFields)){
$list[$k]['name'] = !empty($jbr) ? $jbr['name']:'';
}
}
return $list;
}
}
前端：
<template>
<el-button type="primary" style="margin-top: 30px;" @click="clickExportData">导出</el-button>
</template>
<script>
import downloadFile from '@/plugins/downloadFile';
export default {
data() {
return {
percentage:0, //进度条的占比
progressShow: false, // 是否显示进度条弹出层
dowPage: 1
}
},
methods: {
clickExportData(){
const data = {};
// 获取被选中的字段
let result = [];
data['export_data'] = result;
data['search'] = {};
this.progressShow = true;
this.percentage = 0;
this.$http.post("export_save_redis/"+this.code+'/'+this.user_id, data).then(res1 => {
// console.log(res1);
this.$http.post("export_save_redis2/"+this.code+'/'+this.user_id, data).then(res2 => {
this.exportData(data, times);
})
});
},
// 导出数据
async exportData(data, times){
data.page = this.dowPage;
let url = "export_data/"+this.code+'/'+this.user_id;
const {data:res} = await this.$http.post(url, data);
console.log(res);
if(res.state){
console.log('进行文件下载')
var xls_url = this.$request_url + res.content;
clearInterval(times); // 清除计时器
// 进行下载文件操作
// ...
// downloadFile.getProgress(xls_url, '下载文件_'+new Date().getTime(), this, this.percentage);
}else{
if(res.content){
this.dowPage ++;
data.file_name = res.content.file_name;
data.total_page = res.content.total_page;
data.max_id = res.content.max_id;
this.exportData(data, times);
}else{
this.progressShow = false;
clearInterval(times);
this.$message.error('数据导出失败，请稍后重试！');
}
}
},
}
}
</script>
![nginx: [emerg] bind() to 0.0.0.0:80 failed 端口被占用](https://i-blog.csdnimg.cn/direct/39f9461d3d6a4133aa49748898b63b2c.png)
