EasyExcel的简单使用
EasyExcel的简单使用
EasyExcel 的简介
Java领域解析，生成Excel比较有名的框架有Apache poi,jxl等，但他们都存在一个严重的问题就是非常的耗内存，如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc.
EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节省内存著称，EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）。
EasyExcel 简单使用
1. 导入 jar 包，主要是依赖于 poi，poi 和 poi-ooxml 分别对应 excel2003 和 excel2007 两个版本(版本怎么对应的不知道)
com.alibaba
easyexcel
2.2.7
org.apache.poi
poi
3.17
org.apache.poi
poi-ooxml
3.17
2. 设置文件的相对项目的路径，并且起个文件名
String filePath = new File("").getCanonicalPath();
path = filePath + File.separator + "excels" + File.separator;
exportFileName = fileId + "_" + now.getYear() + now.getMonthValue() + now.getDayOfMonth() + now.getHour() + now.getMinute() + now.getSecond() + ".xlsx";
File file = new File(path + exportFileName);
3. 设置表头部分
/*sheet 头部*/
private List> head(Map map){
List> headTitles = Lists.newArrayList();
//一共 7 列，7 行的数据
//第一列
headTitles.add( Lists.newArrayList( sheetTitle, sheetTitle, projectName, itemProject, nationalProject, nationalDetails, index ) );
//第二列
headTitles.add( Lists.newArrayList( sheetTitle, sheetTitle, projectName, itemProject, nationalProject, nationalDetails, inspectName ) );
//第三列
headTitles.add( Lists.newArrayList( sheetTitle, sheetTitle, map.get("engineeringtName").toString(), map.get("secondItem").toString(), map.get("projectName").toString(), nationalDetails ,inspectName) );
//第四列
headTitles.add( Lists.newArrayList( sheetTitle, sheetTitle, map.get("engineeringtName").toString(), unitProject, projectNum, nationalDetails,inspectName ) );
//第五列
headTitles.add( Lists.newArrayList( sheetTitle, sheetTitle, map.get("engineeringtName").toString(), map.get("thirdMajor").toString(), map.get("projectCode").toString(), nationalDetails, memberNo ) );
//第六列
headTitles.add( Lists.newArrayList( sheetTitle, sheetTitle, projectNo, subProject, nationalOperate, nationalDetails,memberName ));
//第七列
headTitles.add( Lists.newArrayList( sheetTitle, sheetTitle, map.get("meteringNumber").toString(), map.get("classificationName").toString(), map.get("measureUnit").toString(), nationalDetails,modelNational ) );
return headTitles;
}
4. 单元格合并策略,可以针对这个 excel 表格，不过在头部合并单元格已经处理，所以只要合并数据部分就好
/*合并单元格策略内部类*/
private class MyMergeStrategy extends AbstractMergeStrategy {
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if(cell.getRowIndex() > rowNum - 1 && cell.getColumnIndex() == 1){
CellRangeAddress item = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), 1, 3);
sheet.addMergedRegionUnsafe(item);
}
if(flag){
if( cell.getRowIndex() == rowNum + detailNum - 1 && cell.getColumnIndex() == 0){
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowNum + detailNum - 1 ,rowNum + detailNum - 1,0 ,5));
}
}
}
}
5. 设置单元格样式
/*样式*/
private HorizontalCellStyleStrategy getHorizontalCellStyleStrategy(){
// 头的策略
WriteCellStyle cellStyle = new WriteCellStyle();
// 背景色
WriteFont headWriteFont = new WriteFont(); //字体
headWriteFont.setFontHeightInPoints((short) 10);
cellStyle.setWriteFont(headWriteFont);
cellStyle.setWrapped(true); //自动换行
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(cellStyle, cellStyle);
return horizontalCellStyleStrategy;
}
6. 公式之类的计算
/*单元格操作内部类*/
private class CustomCellWriteHandler implements CellWriteHandler {
@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) {
if(flag && cell.getRowIndex() == rowNum + detailNum - 1 && cell.getColumnIndex() == colNum - 1){
cell.setCellFormula("SUM(G"+ rowNum +":G"+ (rowNum + detailNum - 1) +")");
}
}
}
7. 单元格宽度策略
/*单元格宽度策略*/
private class MyCellWidthStrategy extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List cellDataList, Cell cell, Head head,
Integer relativeRowIndex, Boolean isHead) {
// 简单设置
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 3000);
}
}
8. 处理数据
/*sheet 数据*/
private List> contentData(Map map) {
//用来返回的list
List> contentList = Lists.newArrayList();
//接口获取的list
List> list = map.get("list")==null?Lists.newArrayList():(List)map.get("list");
//需要把接口获取的 list 转换成 contentList 需要的格式
for(Map detailMap : list ){
contentList.add( Lists.newArrayList( ++indexNum, detailMap.get("fourthCollection"), null,null,detailMap.get("SF36Unicode"), detailMap.get("componentName"), detailMap.get("quantity") ) );
}
if(flag){
contentList.add( Lists.newArrayList( merge,null,null,null,null,null, null) );
}
return contentList;
}
9. 写入 excel
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
.head(head(map))
.registerWriteHandler(myMergeStrategy)
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(new CustomCellWriteHandler( ))
.registerWriteHandler(new MyCellWidthStrategy())
.build();
if(excelWriter == null){
excelWriter = EasyExcel.write(out).build();
}
excelWriter.write(dataList,writeSheet);
writeSheet.finish();
10. excel 展示
EasyExcel 填充方式，用起来比较方便
1. 设置一个魔板
模板中 {name} 中是需要填充的单个变量， {.name} 表示列表。
2. 代码实现
package com.demo.excel.service;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.stereotype.Service;
import java.io.File;
import java.io.IOException;
import java.util.*;
/*
* 填充方式
* */
@Service
public class EasyExcelFillService {
int rowNum = 8;
int detailNum;
public void fillExcel() throws IOException {
//魔板路径
String templatePath = new File("").getCanonicalPath() + File.separator + "excels/template.xlsx";
System.out.println( templatePath );
//填充后的文件
String fileName = new File("").getCanonicalPath() + File.separator + "excels/" + "7aa9d995-8b43-44a5-9aff-bf73c71b0c98" + ".xlsx";
System.out.println( fileName );
//合并策略, 如果不使用这个合并策略，检验批合并单元格那个列表，会出现格式错误，原因未知
MyMergeStrategy myMergeStrategy = new MyMergeStrategy();
//模拟列表数据，就是 {.name} 这种的
List> list = list();
Double num = list.stream().map(e -> (Double) e.get("num")).reduce(Double::sum).get();
detailNum = list.size();
//模拟非列表的数据，就是 {name} 这种的
Map map = data(num);
//两个 sheet 页，这个 sheet 的名称不会起左右，原因未知
WriteSheet writeSheet = EasyExcel.writerSheet(0,"第一个sheet").build();
WriteSheet writeSheet1 = EasyExcel.writerSheet(1,"第二个sheet").build();
//获取 excel 模板输出流
ExcelWriter writer = EasyExcel.write(fileName).withTemplate(templatePath).registerWriteHandler(myMergeStrategy).build();
//填充 {name} 数据
writer.fill(map, writeSheet);
writer.fill(map, writeSheet1);
//填充 {.name} 数据，这个 config 是在列表不是最后一行的时候，必须加这个配置，比较消耗内存
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
writer.fill(list, fillConfig, writeSheet);
writer.fill(list, fillConfig, writeSheet1);
writer.finish();
}
private Map data(Double num){
Map map = new HashMap<>();
map.put( "unitProjectName", "熊二的工程名称" );
map.put( "unitNo", "熊二的编号" );
map.put( "itemProject", "熊二的单项工程名称" );
map.put( "unitProject", "熊二的单位工程名称" );
map.put( "subProject", "熊二的分部名称" );
map.put( "quantityProject", "熊二的工程量清单项目名称" );
map.put( "projectNo", "熊二的项目编码" );
map.put( "unit", "熊二的工程量提取方式" );
map.put( "count", num );
return map;
}
private List> list(){
List> list = new ArrayList<>();
for(int i = 0;i < 20;i++){
Map map = new HashMap<>();
map.put("index",i+1);
map.put("inspectName","检验批名称");
map.put("dbNo","构件编码");
map.put("dbName","构件名称");
map.put("num",Math.random()*10);
list.add(map);
}
return list;
}
//合并单元格策略内部类
private class MyMergeStrategy extends AbstractMergeStrategy {
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if(cell.getRowIndex() > rowNum - 1 && cell.getColumnIndex() == 1){
// CellRangeAddress item = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), 1, 3);
// sheet.addMergedRegionUnsafe(item);
if(relativeRowIndex==null ||relativeRowIndex==0){
return;
}
int rowIndex = cell.getRowIndex();
int colIndex = cell.getColumnIndex();
sheet=cell.getSheet();
Row preRow = sheet.getRow(rowIndex - 1);
Cell preCell = preRow.getCell(colIndex);//获取上一行的该格
List list = sheet.getMergedRegions();
CellStyle cs = cell.getCellStyle();
cell.setCellStyle(cs);
for (int i = 0; i < list.size(); i++) {
CellRangeAddress cellRangeAddress = list.get(i);
if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
int lastColIndex = cellRangeAddress.getLastColumn();
int firstColIndex = cellRangeAddress.getFirstColumn();
CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
return;
}
}
}
if( cell.getRowIndex() == rowNum + detailNum - 1 && cell.getColumnIndex() == 0){
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowNum + detailNum - 1 ,rowNum + detailNum - 1,0 ,5));
}
}
}
}
复制模板 sheet
模板 sheet 复制，easyExcel 貌似没有这个功能，但是我们可以使用 poi 来复制，同时可以给每个 sheet 起名，代码实现如下：
@Override
public void downloadExcel( List> excelDataList, HttpServletResponse response) throws IOException {
//设置响应头，返回的是 excel 文件流
String excelName = "test.xlsx";
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(excelName.getBytes(), "ISO-8859-1" ));
//魔板路径
File file = ResourceUtils.getFile("classpath:file/excel/sense/" + excelName);
ExcelWriter writer;
/**
* EasyExcel 未找到相关复制 sheet 方法，所以使用 poi 的复制
*/
try(FileInputStream templateInputStream = new FileInputStream(file); ByteArrayOutputStream copyOutputStream = new ByteArrayOutputStream()){
//先读取魔板，复制 sheet
XSSFWorkbook workbook = new XSSFWorkbook(templateInputStream);
for(int i = 1;i < excelDataList.size();i++){
workbook.cloneSheet(0);
}
//设置 sheet 名称
for(int i = 0;i < excelDataList.size();i++){
Map excelData = excelDataList.get(i);
Set keys = excelData.keySet();
StringBuilder sheetName = new StringBuilder();
for(String key : keys){
if( !"data".equals(key) ){
sheetName.append( excelData.get(key) ).append(";");
}
}
if(sheetName.length() <= 0){
sheetName = new StringBuilder( excelName.replace(".xlsx", "") );
}
sheetName.append(i);
workbook.setSheetName(i, sheetName.toString());
}
//将复制后的 sheet 写入到的输出流中，此时的 copyOutputStream 使我们复制好的表格，根据这个输出流去写 writer
workbook.write(copyOutputStream);
writer = EasyExcel.write( response.getOutputStream() ).withTemplate(new ByteArrayInputStream(copyOutputStream.toByteArray())).build();
}
// 填充数据
for(int i = 0;i < excelDataList.size();i++){
Map excelData = excelDataList.get(i);
WriteSheet writeSheet = EasyExcel.writerSheet( i ).build();
writer.fill(excelData.get("data"), writeSheet);
}
// 记得关闭输出流
writer.finish();
}
参考文档
https://github.com/alibaba/easyexcel/blob/master/docs/API.md
https://www.yuque.com/easyexcel/doc/easyexcel
https://blog.csdn.net/sinat_32366329/article/details/103109058