easyExcel动态复杂表头导出(可直接用)
一、前端代码(仅供参考)
- 页面代码
<el-button type="primary" icon="el-icon-upload2" @click="download" v-loading="loading">导出</el-button>
- js代码
download: function () {
axios({
method: "get",
url: urlComme + "/pension/Report/exportTotalList",
params: data,
headers: {
"content-type": "application/x-www-form-urlencoded",
"Access-Token": window.sessionStorage.token
},
timeout: 600000,
withCredentials: true,
responseType: "blob"
}).then(function (response) {
vm_phe.loading = false;
if (response.data.token == "-1") {
vue
.$confirm("未登录或登录过期!", "警告", {
confirmButtonText: "确定",
cancelButtonText: "取消",
type: "error"
})
.then(() => {
if (parent.parent != undefined) {
parent.window.location = "/index.html?v=" + timestamp();
} else if (parent != undefined) {
parent.window.location = "/index.html?v=" + timestamp();
} else {
window.location = "/index.html?v=" + timestamp();
}
});
}else{
let nameData = "统计表.xlsx";
vm_phe.downloadDoc(response.data, nameData);
}
});
}
}
downloadDoc: function (fileData, filename) {
var blob = new Blob([fileData]);
if (window.navigator.msSaveOrOpenBlob) {
navigator.msSaveBlob(blob, filename);
} else {
var a = document.createElement("a");
var url = window.URL.createObjectURL(blob);
if (!url) {
url = window.webkitURL.createObjectURL(blob);
}
a.href = url;
a.download = filename;
if (navigator.userAgent.indexOf("Firefox") > -1) {
let e = document.createEvent("MouseEvents");
e.initEvent("click", true, true);
a.dispatchEvent(e);
} else {
a.click();
}
// a.click()
window.URL.revokeObjectURL(url);
}
},
二、后端代码
- 引入pom
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
- 接口部分
@GetMapping("/exportTotalList")
@ApiOperation(value = "导出统计表", notes = "****")
public void exportTotalList(@RequestParam Map<String, String> map, HttpServletRequest request, HttpServletResponse response) throws IOException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//表头处理
String[] headArray = new String[]{};
List<List<String>> headList = new ArrayList<>();
//表内数据处理
List<Map<Integer, Object>> mapList = new ArrayList<>();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = null;
try {
fileName = URLEncoder.encode("***统计表", "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
String dataName = "***统计表";
//如果第二行有时间范围则为0,否则为1
String type = "0";
//第一行第二行合并列数
int columns = headList.size() - 1;
//如果有时间范围则设置时间范围,否则设置成空(根据需求自行设置)
String timeFrame = sdf.format(new Date());
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
// headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteCellStyle.setWriteFont(headWriteFont);
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 10);
// 内容的策略
WriteCellStyle contentWriteCellStyle = setWriteCellStyle(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
try {
EasyExcel.write(response.getOutputStream())
.registerWriteHandler(new DetectionSheetWriteHandler(dataName, type, columns, timeFrame))
.registerWriteHandler(horizontalCellStyleStrategy)
.head(headList)
.relativeHeadRowIndex(2)
.sheet("***统计表")
.doWrite(firstDetail(mapList));
} catch (IOException e) {
e.printStackTrace();
}
}
relativeHeadRowIndex(2)表示表头从第二行开始处理
- 表头数据处理
//动态表头数据传入
public static List<List<String>> firstHead(String[] header) {
List<List<String>> headTitles = Lists.newArrayList();
//根据具体业务处理
int k = 0;
for (String h : header) {
if (k > 0) {
headTitles.add(Lists.newArrayList(h, h, "人数"));
headTitles.add(Lists.newArrayList(h, h, "工时"));
} else {
headTitles.add(Lists.newArrayList(h));
}
k++;
}
return headTitles;
}
相邻两个表头数据一样,表头会合并
- 表内数据处理
private List<List<Object>> firstDetail(List<Map<Integer, Object>> mapList) {
List<List<Object>> list = new ArrayList<List<Object>>();
for (Map<Integer, Object> map : mapList) {
List<Object> objectList = new ArrayList<>();
for (Integer key : map.keySet()) {
objectList.add(map.get(key));
}
list.add(objectList);
}
return list;
}
- 内容策略
// 内容的策略
private WriteCellStyle setWriteCellStyle(WriteFont font) {
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 背景绿色
// contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setWriteFont(font);
return contentWriteCellStyle;
}
6.自定义处理
public class DetectionSheetWriteHandler implements SheetWriteHandler {
private String titleName;
private String type;
private int columns;
private String timeFrame;
public DetectionSheetWriteHandler() {
}
public DetectionSheetWriteHandler(String titleName, String type, int columns, String timeFrame) {
this.titleName = titleName;
this.type = type;
this.columns = columns;
this.timeFrame = timeFrame;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
Row row1 = sheet.createRow(0);
row1.setHeight((short) 500);
Cell row1Cell1 = row1.createCell(0);
row1Cell1.setCellValue(titleName);
CellStyle row1CellStyle = workbook.createCellStyle();
row1CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
row1CellStyle.setAlignment(HorizontalAlignment.CENTER);
// row1CellStyle.setTopBorderColor((short) 10);
// row1CellStyle.setLeftBorderColor((short) 10);
// row1CellStyle.setBottomBorderColor((short) 10);
// row1CellStyle.setRightBorderColor((short) 10);
// row1CellStyle.setBorderBottom(BorderStyle.THIN);
// row1CellStyle.setBorderLeft(BorderStyle.THIN);
// row1CellStyle.setBorderRight(BorderStyle.THIN);
// row1CellStyle.setBorderTop(BorderStyle.THIN);
row1Cell1.setCellStyle(row1CellStyle);
//合并单元格,起始行,结束行,起始列,结束列
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, columns));
if (type.equals("0")) {//0有时间范围
Row row2 = sheet.createRow(1);
row2.setHeight((short) 500);
Cell row2Cell1 = row2.createCell(0);
row2Cell1.setCellValue("时间范围:" + timeFrame);
CellStyle row2CellStyle = workbook.createCellStyle();
row2CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
row2CellStyle.setAlignment(HorizontalAlignment.RIGHT);
Font row2Font = workbook.createFont();
row2Font.setFontName("宋体");
row2Font.setFontHeightInPoints((short) 10);
row2CellStyle.setFont(row2Font);
row2Cell1.setCellStyle(row2CellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, columns));
}
}
}
- 导出excel样例
白嫖前点个赞,谢谢!