EasyExcel
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址:https://github.com/alibaba/easyexcel
导出示例
快速开始
在pom中引入easyExcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
导出的实体model
设置导出头,样式,行高等
@ColumnWidth(30)
@ContentRowHeight(25)
@HeadRowHeight(25)
@HeadFontStyle(fontHeightInPoints = 11)
public class OrderExportDTO {
@ExcelProperty(value = "Order No.", index = 0)
private String orderNo;
@ExcelProperty(value = "Report No.", index = 1)
private String reportNo;
@ExcelProperty(value = "Boss No.", index = 2)
private String bossOrderNo;
@ExcelProperty(value = "Quotation No", index = 3)
private String quotationNo;
@ExcelProperty(value = "Tracing Order", index = 4)
private String tracingOrder;
@ExcelProperty(value = "Applicant Name", index = 5)
private String applicantNameEn;
}
@ColumnWidth(30) 设置列宽
@ContentRowHeight(25) 设置行高
@HeadRowHeight(25) 设置首行行高
@HeadFontStyle(fontHeightInPoints = 11) 设置首行字体大小
@ExcelProperty(value = “Applicant Name”, index = 5) 首行显示的title,及顺序
EasyExcelUtil 工具类
设置文件名及response
动态隐藏列
设置单元格样式等,自行扩展
public class EasyExcelUtil {
private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);
public static void buildResponse(HttpServletResponse response, String fileName) {
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
response.addHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
}
public static void export(HttpServletResponse response, ExcelModel excelModel) {
try {
if (excelModel.isHiddenCol()) {
EasyExcel.write(response.getOutputStream(), excelModel.getClazz())
.registerWriteHandler(new ExcelCellWriteHandler(excelModel.getHiddenColIndexList()))
.autoCloseStream(Boolean.FALSE).sheet(excelModel.getSheetName()).doWrite(excelModel.getData());
} else {
EasyExcel.write(response.getOutputStream(), excelModel.getClazz())
.autoCloseStream(Boolean.FALSE).sheet(excelModel.getSheetName()).doWrite(excelModel.getData());
}
} catch (IOException e) {
e.printStackTrace();
logger.error("{} export failure!{}", excelModel.getFileName(), e);
}
}
}
ExcelModel封装了导出的一些属性,sheetName,导出的数据,隐藏列,fileName,excel数据模板等
public class ExcelModel<T> {
private Class clazz;
private String sheetName;
private String fileName;
private List<T> data;
private boolean isHiddenCol;
private Set<Integer> hiddenColIndexList;
public ExcelModel() {}
public ExcelModel(Class clazz, String fileName, String sheetName, List<T> data, Boolean isHiddenCol, Integer[] hiddenColIndex) {
this.clazz = clazz;
this.sheetName = sheetName;
this.fileName = fileName;
this.data = data;
this.isHiddenCol = isHiddenCol;
if (isHiddenCol) {
this.hiddenColIndexList = CollUtil.newHashSet(hiddenColIndex);
}
}
public static <T> ExcelModel<T> buildExcelModel(Class clazz, String fileName, String sheetName, List<T> data, Boolean isHiddenCol, Integer... hiddenColIndex) {
return new ExcelModel(clazz, fileName, sheetName, data, isHiddenCol, hiddenColIndex);
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public Class getClazz() {
return clazz;
}
public void setClazz(Class clazz) {
this.clazz = clazz;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = data;
}
public boolean isHiddenCol() {
return isHiddenCol;
}
public void setHiddenCol(boolean hiddenCol) {
isHiddenCol = hiddenCol;
}
public Set<Integer> getHiddenColIndexList() {
return hiddenColIndexList;
}
public void setHiddenColIndexList(Set<Integer> hiddenColIndexList) {
this.hiddenColIndexList = hiddenColIndexList;
}
}
导出示例
EasyExcelUtil.buildResponse(response, OdcConstant.REPORT_FOR_ORDER_FILE_NAME);
ExcelModel excelModel = ExcelModel.buildExcelModel(OrderExportDTO.class, OdcConstant.REPORT_FOR_ORDER_FILE_NAME,
OdcConstant.REPORT_SHEET_NAME, orderExportDTOS, Boolean.FALSE);
// 需要设置隐藏列的方式
ExcelModel excelModel = ExcelModel.buildExcelModel(OrderExportDTO.class, OdcConstant.REPORT_FOR_ORDER_FILE_NAME,
OdcConstant.REPORT_SHEET_NAME, orderExportDTOS, Boolean.TRUE, 29, 30);
EasyExcelUtil.export(response, excelModel);