行业知识
java技术：easyexcel同时导出多个excel到web（zip压缩文件）
2026-11-05 03:09:26
来源：互联网转载
目录
环境设置
一、表头设计：
1、利用列表
2、注解形式(推荐)
二、导出zip（可实现任意个excel文件）
三、单元格策略
只要相同的列就合并
合并判断
四、动态标题实现
五、整合（动态标题+单元格合并）
实体类举例
六、感兴趣可看（封装函数说明）
参考：
环境设置
com.alibaba easyexcel 2.2.0-beta2
一、表头设计：
1、利用列表
private static List head() { List list = new ArrayList(); List head0 = new ArrayList(); head0.add("序号"); list.add(head0); Map map = getHeader(); map.forEach((k, v) -> { String deviceCategory = k; List ls = v; ls.forEach(e -> { List head = new ArrayList(); head.add(deviceCategory); head.add(e); list.add(head); });• });• List head1 = new ArrayList();• head1.add("备注");• list.add(head1);• List head2 = new ArrayList();• head2.add("埋深");• list.add(head2);• return list;}private static Map getHeader() { Map map = new HashMap(); List aList = new ArrayList(); List sList = new ArrayList(); List subList = new ArrayList(); String column1 = "X"; aList.add(column1); String column2 = "Y"; aList.add(column2); String column3 = "B"; sList.add(column3); String column4 = "L"; sList.add(column4); String subColumn = "其它"; subList.add(subColumn); subList.add("小计3"); map.put("坐标", aList); map.put("经纬度", sList); return map;} 2、注解形式(推荐)
@Data@ApiModel("角色管理")public class TSRoleVo extends ExcelModel {@ExcelIgnore@ApiModelProperty("id")private String id;• • @ExcelProperty(value = {"角色表列表","导出人:${title}","角色名称"} , index = 0)• @ApiModelProperty(value = "角色名称")• @ColumnWidth(25)• private String roleName;//角色名称• @ExcelProperty(value = {"角色表列表","导出人:${title}","角色编码"} , index = 1)@ApiModelProperty(value = "角色编码")@ColumnWidth(25)private String roleCode;//角色编码@ExcelProperty(value = {"角色表列表","导出人:${title}","部门权限组ID"} , index = 2)@ApiModelProperty(value = "部门权限组ID")@ColumnWidth(25)private String departAgId;//组织机构ID 部门权限组ID@Overridepublic boolean validation(Map validationArgs) { return false;}} ps：宽:@ColumnWidth(25)内容的高:@ContentRowHeight(10)表头的高：@HeadRowHeight(20)
二、导出zip（可实现任意个excel文件）
对于每个excel拥有两个sheet且 每个sheet实体类数据不同
public class ReceiveTaskUtils { public static void addFileToZip(File file, ZipOutputStream zos) throws IOException { InputStream fileInputStream = new FileInputStream(file); //zip中要放文件称为zipEntry ZipEntry zipEntry = new ZipEntry(file.getName()); zos.putNextEntry(zipEntry); byte[] bytes = new byte[1024]; int len; while ((len = fileInputStream.read(bytes)) != -1) { //读的内容会自动放到zip条目中，因此zipentry再输出流读完需要关闭 zos.write(bytes, 0, len); } //要关闭这个 zos.closeEntry(); zos.closeEntry(); fileInputStream.close(); } static void deleteFile(String path) { File file = new File(path); if (file.exists()) { file.delete(); } } /** * 获取Date的月份 */ static int getMonth(Date Time) { LocalDate localDate = Time.toInstant().atZone(ZoneId.systemDefault()).toLocalDate(); int month = localDate.getMonthValue(); return month; } private static String formatToDate(Date receiveCliDate) { SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yy"); String formattedDate = dateFormat.format(receiveCliDate); return formattedDate; } //写到本地{ public static void export(ReceiveExportVo result, HttpServletResponse response, String type) throws Exception {//设置输出流格式 String name = (type.contains("已完成") ? "采购完成" : "领受任务"); Date cliTime = (type.contains("已完成") ? CompleteCliDate : ReceiveCliDate); String month=String.valueOf(getMonth(cliTime)); response.setContentType("application/zip"); response.setCharacterEncoding("UTF-8"); String fileName = name + System.currentTimeMillis() + ".zip"; response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");// 创建excel文件 String fileName1 = ".\单一来源.xlsx"; //合并策略： int[] mergeColumeIndex ={1,2,3,4,5,6,7,8,9,10,11,12}; //从第二行后开始合并 // 调用合并单元格工具类，此工具类是没有根据合并，数据相同就合并了 String titleDate=formatToDate(ReceiveCliDate); ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(2,mergeColumeIndex,titleDate,month); //设置样式 标题和内容 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置 水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置 垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); log.info("创建excel之前"); try (ExcelWriter excelWriter = EasyExcel.write(fileName1).build()) { WriteSheet writeSheet1 = EasyExcel.writerSheet(month + "月单一来源进行中项目统计") .registerWriteHandler(excelFillCellMergeStrategy) .registerWriteHandler(horizontalCellStyleStrategy) .head(SingReceiveExport1Vo.class).build(); List singReceiveExport1VoList = result.getSingReceiveExport1VoList(); excelWriter.write(singReceiveExport1VoList, writeSheet1); WriteSheet writeSheet2 = EasyExcel.writerSheet(month + "月已安排审价项目统计") .registerWriteHandler(horizontalCellStyleStrategy) .registerWriteHandler(excelFillCellMergeStrategy) .head(SingReceiveExport2Vo.class).build(); List singReceiveExport2VoList = result.getSingReceiveExport2VoList(); excelWriter.write(singReceiveExport2VoList, writeSheet2); } String fileName2 = ".\竞争型.xlsx"; try (ExcelWriter excelWriter = EasyExcel.write(fileName2).build()) { WriteSheet writeSheet3 = EasyExcel.writerSheet(month + "月正在进行中竞争性采购项目") .registerWriteHandler(horizontalCellStyleStrategy) .registerWriteHandler(excelFillCellMergeStrategy) .head(CompeteReceiveExport1Vo.class).build(); List competeReceiveExport1VoList = result.getCompeteReceiveExport1VoList(); excelWriter.write(competeReceiveExport1VoList, writeSheet3); WriteSheet writeSheet4 = EasyExcel.writerSheet(month + "月已开标项目") .registerWriteHandler(excelFillCellMergeStrategy) .registerWriteHandler(horizontalCellStyleStrategy) .head(CompeteReceiveExport2Vo.class).build(); List competeReceiveExport2VoList = result.getCompeteReceiveExport2VoList(); excelWriter.write(competeReceiveExport2VoList, writeSheet4); } //压缩路径// String zipFilePath =".\"+".zip"; // 创建输出流 FileOutputStream fos = new FileOutputStream(fileName); ZipOutputStream zos = new ZipOutputStream(fos); // 压缩文件 File file1 = new File(fileName1); File file2 = new File(fileName2); addFileToZip(file1, zos); addFileToZip(file2, zos); zos.close(); fos.close(); //将压缩文件输入流传给response输出流 InputStream fileInputStream = new FileInputStream(fileName); OutputStream outputStream = response.getOutputStream(); byte[] bytes = new byte[1024 * 8]; int len; while((len=fileInputStream.read(bytes))!=-1) { outputStream.write(bytes,0,len); } fileInputStream.close(); outputStream.close();// 删除文件 deleteFile(fileName1); deleteFile(fileName2); deleteFile(fileName); }}
三、单元格策略
只要相同的列就合并（图片仅供参考）
import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import lombok.Data;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;@Datapublic class ExcelFillCellMergeStrategy implements CellWriteHandler { private int[] mergeColumnIndex; private int mergeRowIndex;public ExcelFillCellMergeStrategy() {}public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex();• if (curRowIndex > mergeRowIndex) {• for (int i = 0; i 合并判断（图片仅供参考）
package com.ph.rfwg.util;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;public class ExcelMergeUtil implements CellWriteHandler {private int[] mergeColumnIndex;private int mergeRowIndex;public ExcelMergeUtil() {}public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex) { for (int i = 0; i 四、动态标题实现
public class CustomerTitleHandler implements CellWriteHandler { private String title; PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}"); public CustomerTitleHandler(String title) { this.title = title; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { if (head！=null) { //获取一个标题列表 List headNameList = head.getHeadNameList(); if (CollectionUtils.isNotEmpty(headNameList)) { Properties properties = new Properties(); //需要多个动态变量直接在后面加，properties相当于map会自动映射 properties.setProperty("title", title); headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties)); } } } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) {}} 五、整合（动态标题+单元格合并）
实体类举例
/**
*需求单位联系人/电话
*/
@ColumnWidth(25)
@ExcelProperty(value={"${titleDate}","需求单位联系人/电话"})
// @ExcelProperty(value={"","需求单位联系人/电话"})
private String demandUnitPeople;
/**
*单一来源承制单位
* plan
*/
@ColumnWidth(40)
@ExcelProperty(value={"单一来源正在进行中审价项目统计","单一来源承制单位"})
private String singleSourceProvider;
/**
*承制单位联系人及电话
*/
@ColumnWidth(40)
@ExcelProperty(value={"单一来源正在进行中审价项目统计","承制单位联系人及电话"})
private String manufacturerPhone;
package com.example.procurementmanagement.util;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.metadata.data.CellData;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import org.springframework.util.PropertyPlaceholderHelper;import java.util.List;import java.util.Properties;/** * author:lanjie * 单元格合并策略 * describe：合并相同字段的列，但需要判断首列是否合并（条件合并） * ps:可复用 */@Slf4jpublic class ExcelMergeUtil implements CellWriteHandler { //需要合并的列数组 private int[] mergeColumnIndex; //合并起始行 private int mergeRowIndex; private String titleDate; private String titleMonth; PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}"); public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex,String titleDate,String titleMonth) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; this.titleDate=titleDate; this.titleMonth=titleMonth; } public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { List headNameList = head.getHeadNameList(); if (CollectionUtils.isNotEmpty(headNameList)){ Properties properties = new Properties(); properties.setProperty("titleDate", titleDate); properties.setProperty("titleMonth", titleMonth); headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties)); log.info("headNameList:{}",headNameList); } } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { log.info("定义单元格以后！"); } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List
