当前位置： 首页 > article >正文

SpringBoot中EasyExcel使用实践总结

article 2024/10/27 9:41:01

【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);

http://www.kler.cn/news/367093.html

相关文章：

  • uniapp圆形波浪进度效果
  • 241026-RHEL如何以root身份卸载Docker
  • STM32 从0开始系统学习 1
  • 10.25学习
  • 使用Python来下一场深夜雪
  • 《Python游戏编程入门》注-第2章2
  • 【Redis 设计与实现】String 的数据结构如何实现的？
  • RN安卓应用打包指南
  • 帝国CMS 内容页调用上一篇下一篇的方法（精华汇总）
  • 零一万物新模型Yi-Lightning：超越GPT-4o
  • C#实现简单的文件夹对比程序（续）
  • 《使用Gin框架构建分布式应用》阅读笔记：p208-p211
  • 函数连续性导论
  • 姿态传感器（学习笔记上）
  • 【Django】继承框架中用户模型基类AbstractUser扩展系统用户表字段
  • AMD平台，5600X+6650XT，虚拟机安装macOS 15 Sequoia 15.0.1 (2024.10)
  • Vite React 项目绝对路径配置
  • Java 项目 Dockerfile 示例：从基础镜像选择到环境变量配置的详细指南
  • 【经典论文阅读11】ESMM模型——基于贝叶斯公式的CVR预估
  • pytorch + d2l环境配置
  • 自定义类型：联合和枚举【上】
  • [实时计算flink]Flink JAR作业快速入门
  • 香橙派5(RK3588)使用npu加速yolov5推理的部署过程
  • Unsupervised Domain Adaptation in SemanticSegmentation: A Review——论文笔记
  • NSS刷题
  • Linux DEADLINE调度算法详解