java技术：easyexcel同时导出多个excel到web（zip压缩文件）
目录
环境设置
一、表头设计：
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
@Override
public 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;
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public 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;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public 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) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public 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:可复用
*/
@Slf4j
public 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
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理!
部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理!
图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!
