service层方法
//文件路径
private String outputAddr="/home/appdata/files";
//表格表头
private static final List<String> NAME_LIST = Arrays.asList("日志ID","日志类型","警报信息ID","警报日期","警报风险级别","警报类型");
@Override
public List<String> outputALertLog(Map<String,Object> requestQuery) {
requestQuery.remove("updateTime");
Map<String,String> resultMap = new HashMap<>();
//开始从paramMap中复制到resultMap中
Iterator it=requestQuery.entrySet().iterator();
while(it.hasNext()) {
Map.Entry entry=(Map.Entry)it.next();
Object key=entry.getKey();
if(key!=null && requestQuery.get(key)!=null) {
resultMap.put(key.toString(), String.valueOf(requestQuery.get(key)));
}
}
if (resultMap.get("listOwnerType").equals("01")){
resultMap.put("listOwnerType","Cust");
}else if (resultMap.get("listOwnerType").equals("02")){
resultMap.put("listOwnerType","Card");
}
//如果没时间，那么查询最近三个月的数据
if (StringUtils.isBlank(resultMap.get("effectDate")) && StringUtils.isBlank(resultMap.get("expiryDate"))){
String currentDateStr = DateUtil.get13TimeStr();
Date date = DateUtil.addDate(new Date(), Calendar.MONTH, -3);
String nearlyThreeMonths = DateUtil.getDateByType(date, "yyyy-MM-dd HH:mm:ss");
resultMap.put("effectDate",nearlyThreeMonths);//前三个月日期
resultMap.put("expiryDate",currentDateStr);//当前日期
}else {
String effectDate = times(resultMap.get("effectDate"));
String expiryDate = times(resultMap.get("expiryDate"));
resultMap.put("effectDate",effectDate);
resultMap.put("expiryDate",expiryDate);
}
List<RiskAlertInformationLog> alertInformationLogs = riskAlertInformationLogMapper.queryByPageList(resultMap);
//防止文件过大，当日志条数超过200条，每200条创建一个文件
int alertInformationLog_num = 0;
int file_num = 200;
BigDecimal aBig = new BigDecimal(alertInformationLogs.size());
BigDecimal bBig = new BigDecimal(file_num);
if (alertInformationLogs.size() >= file_num){
alertInformationLog_num = (int)Math.ceil(aBig.divide(bBig).doubleValue());
}else {
alertInformationLog_num = 1;
}
List<String> outputFiles = new ArrayList<>();
for (int i = 1; i <= alertInformationLog_num; i++) {
//查询的条数小于200
if (alertInformationLog_num==1){
// 创建一个薄
Workbook workbook = new XSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet("sheet1");
CellStyle style = workbook.createCellStyle();
style.setLocked(true);
//创建表头
Row titleRow = sheet.createRow(0);
Cell cell = null;
for (int j = 0; j < NAME_LIST.size(); j++) {
cell = titleRow.createCell(j);
cell.setCellValue(NAME_LIST.get(j));
cell.setCellStyle(style);
}
//把从数据库中取得的数据一一写入excel文件中
Row row = null;
for (int k = file_num*(i-1); k < alertInformationLogs.size(); k++) {
RiskAlertInformationLog riskAlertInformationLog = alertInformationLogs.get(k);
//创建list.size()行数据
for (int j = 1; j <=alertInformationLogs.size()%file_num ; j++) {
row = sheet.createRow(j);
//把值一一写进单元格里
//设置第一列为自动递增的序号
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
row.createCell(0).setCellValue(riskAlertInformationLog.getLogId());
row.createCell(1).setCellValue(AlertLogTypeEnum.getDesc(riskAlertInformationLog.getLogType()));
row.createCell(2).setCellValue(riskAlertInformationLog.getAlertId());
row.createCell(3).setCellValue(riskAlertInformationLog.getAlertDate());
row.createCell(4).setCellValue(riskAlertInformationLog.getAlertRiskLevel());
row.createCell(5).setCellValue(riskAlertInformationLog.getAlertType());
}
}
//设置单元格宽度自适应，在此基础上把宽度调至1.8倍
for (int n = 0; n < NAME_LIST.size(); n++) {
sheet.autoSizeColumn(n, true);
sheet.setColumnWidth(n, sheet.getColumnWidth(i) * 18/10);
}
//创建文件目录
File folder = new File(outputAddr);
//如果文件夹不存在创建对应的文件夹
if (!folder.exists()) {
folder.mkdirs();
}
String dateTime = DateUtil.getDateByType(new Date(), DateUtil.TIME_YYYYMMDDHHMMSS);
//设置文件名
String fileName = i+"-risk-alert-infomation-log_".concat(dateTime).concat(".xlsx");
String savePath = outputAddr + File.separator + fileName;
InputStream inputStream = null;
OutputStream fileOut = null;
try {
inputStream = new FileInputStream(savePath);
fileOut = new FileOutputStream(savePath);
workbook.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
logger.error("文件下载失败，错误信息$s",e.getMessage());
}finally {
outputFiles.add(fileName);
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}else if (alertInformationLog_num!=1){
//查询结果条数大于200的第一个表格
if (i==1){
// 创建一个薄
Workbook workbook = new XSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet("sheet1");
CellStyle style = workbook.createCellStyle();
style.setLocked(true);
//创建表头
Row titleRow = sheet.createRow(0);
Cell cell = null;
for (int j = 0; j < NAME_LIST.size(); j++) {
cell = titleRow.createCell(j);
cell.setCellValue(NAME_LIST.get(j));
cell.setCellStyle(style);
}
//把从数据库中取得的数据一一写入excel文件中
Row row = null;
for (int k = 0; k < file_num; k++) {
RiskAlertInformationLog riskAlertInformationLog = alertInformationLogs.get(k);
//创建list.size()行数据
row = sheet.createRow(k+1);
//把值一一写进单元格里
//设置第一列为自动递增的序号
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
row.createCell(0).setCellValue(riskAlertInformationLog.getLogId());
row.createCell(1).setCellValue(AlertLogTypeEnum.getDesc(riskAlertInformationLog.getLogType()));
row.createCell(2).setCellValue(riskAlertInformationLog.getAlertId());
row.createCell(3).setCellValue(riskAlertInformationLog.getAlertDate());
row.createCell(4).setCellValue(riskAlertInformationLog.getAlertRiskLevel());
row.createCell(5).setCellValue(riskAlertInformationLog.getAlertType());
}
//设置单元格宽度自适应，在此基础上把宽度调至1.8倍
for (int n = 0; n < NAME_LIST.size(); n++) {
sheet.autoSizeColumn(n, true);
sheet.setColumnWidth(n, sheet.getColumnWidth(i) * 18/10);
}
//创建文件目录
File folder = new File(outputAddr);
//如果文件夹不存在创建对应的文件夹
if (!folder.exists()) {
folder.mkdirs();
}
String dateTime = DateUtil.getDateByType(new Date(), DateUtil.TIME_YYYYMMDDHHMMSS);
//设置文件名
String fileName = i+"-risk-alert-infomation-log_".concat(dateTime).concat(".xlsx");
String savePath = outputAddr + File.separator + fileName;
OutputStream fileOut = null;
try {
fileOut = new FileOutputStream(savePath);
workbook.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
logger.error("文件下载失败，错误信息$s",e.getMessage());
}finally {
outputFiles.add(fileName);
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}else if (i==alertInformationLog_num){
//查询结果条数大于200的最后一个表格
// 创建一个薄
Workbook workbook = new XSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet("sheet1");
CellStyle style = workbook.createCellStyle();
style.setLocked(true);
//创建表头
Row titleRow = sheet.createRow(0);
Cell cell = null;
for (int j = 0; j < NAME_LIST.size(); j++) {
cell = titleRow.createCell(j);
cell.setCellValue(NAME_LIST.get(j));
cell.setCellStyle(style);
}
//把从数据库中取得的数据一一写入excel文件中
Row row = null;
for (int k = file_num*(i-1); k < alertInformationLogs.size(); k++) {
RiskAlertInformationLog riskAlertInformationLog = alertInformationLogs.get(k);
//创建list.size()行数据
for (int j = 1; j <=alertInformationLogs.size()%file_num ; j++) {
row = sheet.createRow(j);
//把值一一写进单元格里
//设置第一列为自动递增的序号
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
row.createCell(0).setCellValue(riskAlertInformationLog.getLogId());
row.createCell(1).setCellValue(AlertLogTypeEnum.getDesc(riskAlertInformationLog.getLogType()));
row.createCell(2).setCellValue(riskAlertInformationLog.getAlertId());
row.createCell(3).setCellValue(riskAlertInformationLog.getAlertDate());
row.createCell(4).setCellValue(riskAlertInformationLog.getAlertRiskLevel());
row.createCell(5).setCellValue(riskAlertInformationLog.getAlertType());
}
}
//设置单元格宽度自适应，在此基础上把宽度调至1.8倍
for (int n = 0; n < NAME_LIST.size(); n++) {
sheet.autoSizeColumn(n, true);
sheet.setColumnWidth(n, sheet.getColumnWidth(i) * 18/10);
}
//创建文件目录
File folder = new File(outputAddr);
//如果文件夹不存在创建对应的文件夹
if (!folder.exists()) {
folder.mkdirs();
}
String dateTime = DateUtil.getDateByType(new Date(), DateUtil.TIME_YYYYMMDDHHMMSS);
//设置文件名
String fileName = i+"-risk-alert-infomation-log_".concat(dateTime).concat(".xlsx");
String savePath = outputAddr + File.separator + fileName;
OutputStream fileOut = null;
try {
fileOut = new FileOutputStream(savePath);
workbook.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
logger.error("文件下载失败，错误信息$s",e.getMessage());
}finally {
outputFiles.add(fileName);
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}else {
查询结果条数大于200的其他表格
// 创建一个薄
Workbook workbook = new XSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet("sheet1");
CellStyle style = workbook.createCellStyle();
style.setLocked(true);
//创建表头
Row titleRow = sheet.createRow(0);
Cell cell = null;
for (int j = 0; j < NAME_LIST.size(); j++) {
cell = titleRow.createCell(j);
cell.setCellValue(NAME_LIST.get(j));
cell.setCellStyle(style);
}
//把从数据库中取得的数据一一写入excel文件中
Row row = null;
for (int k = file_num*(i-1); k < file_num*i; k++) {
RiskAlertInformationLog riskAlertInformationLog = alertInformationLogs.get(k-1);
//创建list.size()行数据
for (int j = 1; j <= file_num; j++) {
row = sheet.createRow(j);
//把值一一写进单元格里
//设置第一列为自动递增的序号
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
row.createCell(0).setCellValue(riskAlertInformationLog.getLogId());
row.createCell(1).setCellValue(AlertLogTypeEnum.getDesc(riskAlertInformationLog.getLogType()));
row.createCell(2).setCellValue(riskAlertInformationLog.getAlertId());
row.createCell(3).setCellValue(riskAlertInformationLog.getAlertDate());
row.createCell(4).setCellValue(riskAlertInformationLog.getAlertRiskLevel());
row.createCell(5).setCellValue(riskAlertInformationLog.getAlertType());
}
}
//设置单元格宽度自适应，在此基础上把宽度调至1.8倍
for (int n = 0; n < NAME_LIST.size(); n++) {
sheet.autoSizeColumn(n, true);
sheet.setColumnWidth(n, sheet.getColumnWidth(i) * 18/10);
}
//创建文件目录
File folder = new File(outputAddr);
//如果文件夹不存在创建对应的文件夹
if (!folder.exists()) {
folder.mkdirs();
}
String dateTime = DateUtil.getDateByType(new Date(), DateUtil.TIME_YYYYMMDDHHMMSS);
//设置文件名
String fileName = i+"-risk-alert-infomation-log_".concat(dateTime).concat(".xlsx");
String savePath = outputAddr + File.separator + fileName;
OutputStream fileOut = null;
try {
fileOut = new FileOutputStream(savePath);
workbook.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
logger.error("文件下载失败，错误信息$s",e.getMessage());
}finally {
outputFiles.add(fileName);
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
//返回文件名称
return outputFiles;
}下载方法获取文件并压缩返回的工具类
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Date;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
public class OutputUtil {
/**
* 批量下载文件
*
* @param filePath 文件路径
* @param list 批量文件名称集合（前端只传id集合，后端去查数据库拿到文件信息）
* @param request request
* @param response response
* @param <T> 实体类 extends BaseEntityPoJo
*/
public static <T> void batchDownloadFile(String filePath,List<String> list, HttpServletRequest request, HttpServletResponse response) {
//设置响应头信息
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
String dateTime = DateUtil.getDateByType(new Date(), DateUtil.TIME_YYYYMMDDHHMMSS);
String downloadName = "risk-alert-infomation-log_".concat(dateTime) + ".zip";
//返回客户端浏览器的版本号、类型
String agent = request.getHeader("USER-AGENT");
try {
//针对IE或者以IE为内核的浏览器：
if (agent.contains("MSIE") || agent.contains("Trident")) {
downloadName = URLEncoder.encode(downloadName, "UTF-8");
} else {
//非IE浏览器的处理：
downloadName = new String(downloadName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
}
} catch (Exception e) {
e.printStackTrace();
}
response.setHeader("Content-Disposition", downloadName + "\"");
//设置压缩流：直接写入response，实现边压缩边下载
ZipOutputStream zipOs = null;
//循环将文件写入压缩流
DataOutputStream os = null;
//文件
File file;
try {
zipOs = new ZipOutputStream(new BufferedOutputStream(response.getOutputStream()));
//设置压缩方法
zipOs.setMethod(ZipOutputStream.DEFLATED);
//遍历文件信息（主要获取文件名/文件路径等）
for (String t : list) {
String name = t;
//文件路径（绝对路径，包含后缀名，如：F:\\test\\测试.pdf）
String path = filePath + File.separator + name;
file = new File(path);
if (!file.exists()) {
throw new RuntimeException("文件不存在");
}
//添加ZipEntry，并将ZipEntry中写入文件流
zipOs.putNextEntry(new ZipEntry(name));
os = new DataOutputStream(zipOs);
FileInputStream fs = new FileInputStream(file);
byte[] b = new byte[100];
int length;
//读入需要下载的文件的内容，打包到zip文件
while ((length = fs.read(b)) != -1) {
os.write(b, 0, length);
}
//关闭流
fs.close();
zipOs.closeEntry();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭流
try {
if (os != null) {
os.flush();
os.close();
}
if (zipOs != null) {
zipOs.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}Controller使用
/**
* 日志导出
*
* @return
*/
@PostMapping("/output")
public CommonResponse importFact(@RequestBody Map<String,Object> requestQuery, HttpServletRequest request, HttpServletResponse response) {
List<String> fullPaths = riskAlertInformationLogService.outputALertLog(requestQuery);
OutputUtil.batchDownloadFile(outputAddr,fullPaths,request,response);
return CommonResponse.success();
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
