easyExcel动态复杂表头导出

easyExcel动态复杂表头导出(可直接用)

一、前端代码(仅供参考)

1. 页面代码

<el-button type="primary" icon="el-icon-upload2" @click="download" v-loading="loading">导出</el-button>

2. 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);
			}
		},

二、后端代码

1. 引入pom

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

2. 接口部分

    @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)表示表头从第二行开始处理

3. 表头数据处理

    //动态表头数据传入
    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;
    }

相邻两个表头数据一样，表头会合并

4. 表内数据处理

    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;
    }

5. 内容策略

    // 内容的策略
    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));
        }
    }
}

7. 导出excel样例
   白嫖前点个赞，谢谢！