Controller层
static final protected List<String> DOWNLOAD_USER_HEADERS = new ArrayList<>(Arrays.asList(new String[]{"用户", "用户ip", "接口信息", "接口地址", "请求参数", "状态", "日志类型", "请求时间"}));
//字段英文对应
static final protected List<String> DOWNLOAD_USER_COL =new ArrayList<>(Arrays.asList(new String[]{"***", "***", "***", "***", "***", "***", "***", "***"}));
@PostMapping("/export")
@ResponseBody
public void export(@RequestBody res){
List logList = new ArrayList<>();
//自定义
ExportExcel<Map<String, Object>> exportExcel = new ExportExcel<>();
exportExcel.doExcel(response, logList, "日志", DOWNLOAD_USER_HEADERS, DOWNLOAD_USER_COL, false);
}
导出类
package net.cnki.urtp.util;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel 导出类
*/
@Slf4j
@Component
public class ExportExcel<T> {
//@Value("${result.export.sheetNum:50000}")
private int exportTotal = 50000;
/**
* 默认日期格式
*/
private static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";
/**
* @param info 需要显示的数据集合
* @param title 表格名称
* @param headers 表格属性列名数组(标题行)
* @param column 需要显示的表格属性列名数组,如果是JavaBean就是字段名,如果是map就是key值
* @param isMerge 是否合并单元格
*/
public void doExcel(HttpServletResponse response, Collection info, String title, List<String> headers, List<String> column, boolean isMerge) {
try {
OutputStream out = response.getOutputStream();
response.reset();
response.setContentType("application/msexcel;charset=UTF-8");
String filenameEncoding = URLEncoder.encode(title + ".xls", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + filenameEncoding);
// HSSFWorkbook workbook = exportExcel(title, headers, column, data, isMerge);
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
int size = exportTotal;
if (info.size() > size && info instanceof List) {
// 如果数据超过50000条分sheet展示
List data = (List) info;
for (int i = 0; i * size < info.size(); i++) {
Collection infoData;
if ((i + 1) * size < data.size()) {
infoData = data.subList(i * size, (i + 1) * size);
} else {
infoData = data.subList(i * size, data.size());
}
// 重新设置sheet标题
String sheetTitle = title + (i + 1);
exportExcel(workbook, sheetTitle, headers, column, infoData, isMerge);
log.info("=====>生成excel开始,deExcelStartTime");
}
} else {
// 如果数量在被限制数量之内,下载到一个sheet里
exportExcel(workbook, title, headers, column, info, isMerge);
log.info("=====>生成excel结束,deExcelStartTime");
}
try {
workbook.write(out);
} catch (Exception e) {
log.error("write excel workbook to http response error", e);
} finally {
out.close();
}
} catch (IOException | NoSuchMethodException | InvocationTargetException | IllegalAccessException e) {
log.error("export excel operate http response error", e);
}
}
/**
* @param sheetName 表格名称
* @param headers 表格属性列名数组(标题行)
* @param column 需要显示的表格属性列名数组,如果是JavaBean就是字段名,如果是map就是key值
* @param dataset 需要显示的数据集合
* @param isMerge 是否合并单元格
*/
private HSSFWorkbook exportExcel(HSSFWorkbook workbook, String sheetName, List<String> headers, List<String> column, Collection<T> dataset,
boolean isMerge) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
// 声明一个工作薄
// HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = createSheet(sheetName, workbook);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.size(); i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(titleCellStyle(workbook));
cell.setCellValue(new HSSFRichTextString(headers.get(i)));
}
// 遍历集合数据,产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
HSSFRow preRow;
Map<String, String> mergeMap = new HashMap();
HSSFCellStyle hssfCellStyle = bodyCellStyle(workbook);
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = it.next();
// 属性数组
List<String> fields = column;
for (short i = 0; i < fields.size(); i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(hssfCellStyle);
String fieldName = fields.get(i);
try {
Object value = "";
Map map = null;
Class tCls = null;
if (t instanceof Map) {
map = (Map) t;
value = map.get(fieldName);
} else {
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
value = getMethod.invoke(t, new Object[]{});
}
if (value == null) {
value = "";
}
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Byte) {
short bValue = (Byte) value;
if ("logType".equals(fieldName)) {
if (1 == bValue) {
textValue = "男";
} else {
textValue = "女";
}
} else {
textValue = Short.toString(bValue);
}
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_DATE_PATTERN);
textValue = sdf.format(date);
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
cell.setCellValue(textValue);
if (isMerge) {
if (index == 1) {
continue;
}
if (i == 0 || i == 1) {
// 获取之前每一行的当前列
for (int j = 1; j < index; j++) {
preRow = sheet.getRow(j);
HSSFCell preCell = preRow.getCell(i);
boolean b1 = cell.getStringCellValue().equals(preCell.getStringCellValue());
boolean b2 = row.getCell(0).getStringCellValue().equals(preRow.getCell(0).getStringCellValue());
if (b1 && b2) {
String str = j + "," + index + "," + i + "," + i;
String key = "";
if (i == 0) {
key = cell.getStringCellValue();
} else {
key = row.getCell(0).getStringCellValue() + cell.getStringCellValue();
}
mergeMap.put(key, str);
break;
}
}
}
}
} catch (NoSuchMethodException | InvocationTargetException | IllegalAccessException e) {
throw new RuntimeException(e);
}
}
}
if (!CollectionUtils.isEmpty(mergeMap) && isMerge) {
for (String key : mergeMap.keySet()) {
String[] obj = mergeMap.get(key).trim().split(",");
int firstRow = Integer.parseInt(obj[0]);
int lastRow = Integer.parseInt(obj[1]);
int firstCol = Integer.parseInt(obj[2]);
int lastCol = Integer.parseInt(obj[3]);
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
}
return workbook;
}
private HSSFSheet createSheet(String sheetName, HSSFWorkbook workbook) {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
return sheet;
}
/**
* 表头样式
*/
private HSSFCellStyle titleCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setWrapText(true);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont titleFont = workbook.createFont();
titleFont.setBold(true);
titleFont.setFontHeightInPoints((short) 14);
titleFont.setFontName("宋体");
titleStyle.setFont(titleFont);
return titleStyle;
}
/**
* 表格样式
*/
private HSSFCellStyle bodyCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle bodyStyle = workbook.createCellStyle();
bodyStyle.setWrapText(true);
bodyStyle.setAlignment(HorizontalAlignment.CENTER);
bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont bodyFont = workbook.createFont();
bodyFont.setFontHeightInPoints((short) 12);
bodyFont.setFontName("宋体");
bodyStyle.setFont(bodyFont);
return bodyStyle;
}
}
前端js代码
function exportExcel() {
fetch('./export',{
method: "POST",
headers: {
"Content-Type": "application/json",
},
body:JSON.stringify(excelParam)
}) // 替换为你的 REST API URL
.then(response => response.blob()) // 将响应体转换为 Blob 对象,以便下载文件时可以正确设置文件名和内容类型
.then(blob => {
const url = window.URL.createObjectURL(blob); // 创建临时 URL,用于下载文件
const link = document.createElement('a'); // 创建一个下载链接元素
link.href = url; // 设置链接的 URL 为临时 URL
link.download = '日志_'+formattedDateTime+'.xlsx'; // 设置下载的文件名为 '日志.xlsx',与后端生成的 Excel 文件名一致
document.body.appendChild(link); // 将下载链接添加到页面上,用户可以点击下载文件
link.click(); // 模拟用户点击下载链接,开始下载文件
})
.catch(error => console.error('Error:', error)); // 处理错误情况,可以在控制台输出错误信息或进行其他处理操作
}