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>