SpringBoot中EasyExcel使用实践总结
【1】导出数据返回ResponseEntity
版本是3.1
@RequestMapping(value = "/export",method = {RequestMethod.POST, RequestMethod.GET})
public ResponseEntity<byte[]> export(HttpServletRequest request) throws UnsupportedEncodingException {
List<FmInfo> allDataList = dmInfoService.list();
ByteArrayOutputStream outputStream=new ByteArrayOutputStream();
EasyExcel.write(outputStream, FmInfo.class)
.registerWriteHandler(new CustomCellWriteWidthConfig())
.sheet("告警数据")
.doWrite(allDataList);
//添加响应头
HttpHeaders headers = new HttpHeaders();
headers.add("Content-Disposition", "attachment;filename="+ FileUtils.setFileDownloadHeader(request, "告警数据.xlsx"));
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
HttpStatus statusCode = HttpStatus.OK;
return new ResponseEntity<byte[]>(outputStream.toByteArray(), headers, statusCode);
}
【2】导出数据并设置表头字体颜色
版本是3.1
@Override
public void downLoad(HttpServletResponse response) {
try {
List<FmInfo> allDataList = dmInfoService.list();
String fileName = "告警数据.xlsx";
response.setCharacterEncoding("UTF-8");
response.setHeader(HttpHeaders.CONTENT_TYPE, "application/vnd.ms-excel");
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
//设置导出的头样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();
// 导出数据
EasyExcel.write(response.getOutputStream(), FmInfo.class)
.registerWriteHandler(horizontalCellStyleStrategy)
.sheet("Sheet1")
.doWrite(allDataList);
} catch (Exception e) {
log.error("下载校验结果失败!",e);
}
}
private static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为黄色
headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)15);
headWriteCellStyle.setWriteFont(headWriteFont);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy();
horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle);
return horizontalCellStyleStrategy;
}
【3】导出时将数据写入两个sheet
版本3.1
@Override
public void downLoad(HttpServletResponse response) {
try {
List<FmInfo> allDataList1 = dmInfoService.list();
List<FmInfo> allDataList2 = dmInfoService.list();
String fileName = "告警数据.xlsx";
response.setCharacterEncoding("UTF-8");
response.setHeader(HttpHeaders.CONTENT_TYPE, "application/vnd.ms-excel");
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
//设置导出的头样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), FmInfo.class).build();
{
WriteSheet writeSheet = EasyExcel.writerSheet(0,"sheet1")
.registerWriteHandler(horizontalCellStyleStrategy)
.build();
excelWriter.write(allDataList1 , writeSheet);
WriteSheet writeSheetTwo = EasyExcel.writerSheet(1,"sheet2")
.registerWriteHandler(horizontalCellStyleStrategy)
.build();
excelWriter.write(allDataList2 , writeSheetTwo);
}
excelWriter.finish();
} catch (Exception e) {
log.error("下载校验结果失败!",e);
}
}
【4】导出数据时自定义表头列
版本3.1
@Override
public void downLoad( HttpServletResponse response) {
try {
List<FmInfo> allDataList1 = dmInfoService.list();
List<FmInfo> allDataList2 = dmInfoService.list();
String fileName = "告警数据.xlsx";
response.setCharacterEncoding("UTF-8");
response.setHeader(HttpHeaders.CONTENT_TYPE, "application/vnd.ms-excel");
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
//设置导出的头样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
{
// 表头内容
WriteSheet writeSheet = EasyExcel.writerSheet(0,"sheet1")
.registerWriteHandler(horizontalCellStyleStrategy)
.head(getHeaders("header3"))
.build();
excelWriter.write(allDataList1 , writeSheet);
WriteSheet writeSheetTwo = EasyExcel.writerSheet(1,"sheet2")
.registerWriteHandler(horizontalCellStyleStrategy)
.head(getHeaders("header4"))
.build();
excelWriter.write(allDataList2 , writeSheetTwo);
}
excelWriter.finish();
} catch (Exception e) {
log.error(e.getMessage(), e);
throw new CommonException("导出数据异常!");
}
}
private List<List<String>> getHeaders(String cellType) {
List<List<String>> headers = new ArrayList<>();
headers.add(Collections.singletonList("header1"));
headers.add(Collections.singletonList("header2"));
headers.add(Collections.singletonList(cellType));
return headers;
}
【5】读取模板写入数据并导出,导出时不采用映射类的title
读取classpath下的模板,写入数据并且导出。导出时采用模板的header,不用映射类的title。
@RequestMapping(value = "/export",method = {RequestMethod.POST, RequestMethod.GET})
public ResponseEntity<byte[]> export(HttpServletRequest request,@RequestParam(value = "idList",required = false) List<Long> idList) throws Exception {
ClassPathResource classPathResource = new ClassPathResource("订单导入模板.xlsx");
InputStream inputStream = classPathResource.getInputStream();
List<SysOrder> allDataList = orderService.list(new QueryWrapper<SysOrder>().in(idList!=null&&!idList.isEmpty(),"id",idList));
//存储导出的数据--这里自己构造
List<OrderExportVO> wholeList = new ArrayList<>();
ByteArrayOutputStream outputStream=new ByteArrayOutputStream();
EasyExcel.write(outputStream,OrderExportVO.class)
.withTemplate(inputStream)
.needHead(false)//导出时不写入头部,使用模板的头部
.sheet()
.doWrite(wholeList);
//添加响应头
HttpHeaders headers = new HttpHeaders();
headers.add("Content-Disposition", "attachment;filename="+ FileUtils.setFileDownloadHeader(request, "订单导入模板.xlsx"));
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
//设置响应参数
// response.setContentType("application/vnd.ms-excel;charset=utf-8");
HttpStatus statusCode = HttpStatus.OK;
return new ResponseEntity<byte[]>(outputStream.toByteArray(), headers, statusCode);
}
.needHead(false)
导出时不写入头部,使用模板的头部。否则会追加一行header,title为映射类中标明的。
【6】自定义列合并行
源数据为每一行的数据,合并时将数值重复的单元格且索引在指定范围内的进行合并
package com.empowerment.common.excel;
import com.alibaba.excel.metadata.Head;
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 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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
import java.util.Objects;
/**
* Created by Janusat 2024/10/26.
* 实现单元格跨行合并
*/
public class CellWriteHandlerImpl implements CellWriteHandler {
private static final Logger log= LoggerFactory.getLogger(CellWriteHandlerImpl.class);
private List <Integer> unMergeColumnIndexList;// 不合并的列
public CellWriteHandlerImpl(List<Integer> unMergeColumnIndexList) {
this.unMergeColumnIndexList = unMergeColumnIndexList;
}
/**
* 在 Cell 写入后处理
* @param writeSheetHolder
* @param writeTableHolder
* @param cellDataList
* @param cell 当前 Cell
* @param head
* @param relativeRowIndex 表格内容行索引,从除表头的第一行开始,索引为0
* @param isHead 是否是表头,true表头,false非表头
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//判断当前为表头,不执行操作
if (isHead) {
return;
}
Integer mergeIndex = head.getColumnIndex();
if(unMergeColumnIndexList.contains(mergeIndex)){//不需要合并则跳过
return;
}
//当前 Sheet
Sheet sheet = cell.getSheet();
//当前 Cell 所在行索引
int rowIndexCurr = cell.getRowIndex();
//当前 Cell 所在行的上一行索引
int rowIndexPrev = rowIndexCurr - 1;
//当前 Cell 所在行的 Row 对象
Row rowCurr = cell.getRow();
//当前 Cell 所在行的上一行 Row 对象
Row rowPrev = sheet.getRow(rowIndexPrev);
if(rowPrev==null){
return;
}
//当前单元格的上一行同列单元格
int cellColumnIndex = cell.getColumnIndex();
Cell cellPrev = rowPrev.getCell(cellColumnIndex);
//当前单元格的值
Object cellValueCurr = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
//上面单元格的值
Object cellValuePrev = cellPrev.getCellType() == CellType.STRING ? cellPrev.getStringCellValue() : cellPrev.getNumericCellValue();
//判断当前单元格与上面单元格是否相等,不相等不执行操作
if (!Objects.equals(cellValueCurr, cellValuePrev)) {
return;
}
//从 Sheet 中,获取所有合并区域
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
//是否合并过
boolean merged = false;
//遍历合并区域集合
for (int i = 0; i < mergedRegions.size(); i++) {
CellRangeAddress cellAddresses = mergedRegions.get(i);
//判断 cellAddress 的范围是否是从 rowIndexPrev 到 cell.getColumnIndex()
if (cellAddresses.isInRange(rowIndexPrev, cellColumnIndex)) {
//从集合中移除
sheet.removeMergedRegion(i);
//设置范围最后一行,为当前行
cellAddresses.setLastRow(rowIndexCurr);
//重新添加到 Sheet 中
sheet.addMergedRegion(cellAddresses);
//已完成合并
merged = true;
break;
}
}
//merged=false,表示当前单元格为第一次合并
if (!merged) {
CellRangeAddress cellAddresses = new CellRangeAddress(rowIndexPrev, rowIndexCurr, cellColumnIndex, cellColumnIndex);
sheet.addMergedRegion(cellAddresses);
}
}
}
controller代码如下:
// 0-19合并 28-40合并 20-27不合并
List <Integer> unMergeColumnIndexList= Arrays.asList(20,21,22,23,24,25,26,27);// 不合并的列
EasyExcel.write(outputStream,OrderExportVO.class)
.withTemplate(inputStream)
.needHead(false)//导出时不写入头部,使用模板的头部
.registerWriteHandler(new CellWriteHandlerImpl(unMergeColumnIndexList))
.sheet()
.relativeHeadRowIndex(2)//从模板第三行开始写入
.doWrite(wholeList);