devbox
我家自动化
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

spring boot数据导出excel_springboot workbook fileoutputstream 导出excel文件示例代码

作者：我家自动化 | 2024-05-05 01:43:47

赞

踩

springboot workbook fileoutputstream 导出excel文件示例代码?

Controller:

  1.  @PostMapping(value = "/export",
  2.             produces = {MediaType.APPLICATION_OCTET_STREAM_VALUE, MediaType.APPLICATION_JSON_VALUE})
  3.     public void RApiExport(HttpServletResponse response, @RequestBody SearchListReqDto reqDto) throws Exception {
  4.         rapiSearchConfigService.exportData(response, reqDto);
  5.     }

service

  1.    public void exportData(HttpServletResponse response, SearchListReqDto reqDto) throws IOException, IllegalAccessException {
  2.         //excel对应多sheet实体类
  3.         ExportRApiDataTemplate exportRApiDataTemplate = new ExportRApiDataTemplate();
  4.  
  5.         String statisticalStart = reqDto.getStatisticalStart();
  6.         String statisticalEnd = reqDto.getStatisticalEnd();
  7.         List<String> keywords = getKeywords(reqDto);
  8.  
  9.  
  10.         List<SearchUserInfo> searchUserInfoList = new ArrayList<>();
  11.         List<RapiSearchUser> searchUserList = rapiSearchUserDao.selectByCreateTime(statisticalStart, statisticalEnd, null, null, keywords);
  12.         searchUserList.forEach(r -> {
  13.             SearchUserInfo searchUserInfo = BeanConvertUtils.convertTo(r, SearchUserInfo::new);
  14.             searchUserInfo.setCreateTime(DateUtil.toString(r.getCreateTime(), DateUtil.yyyy_MM_ddHHmmss_format));
  15.             searchUserInfo.setUserHomePage(getUserShareUrl(r.getSearchUserInfo()));
  16.             searchUserInfoList.add(searchUserInfo);
  17.         });
  18.         exportRApiDataTemplate.setSearchUserInfoList(searchUserInfoList);
  19.         String nowDate = DateUtil.toString(new Date(), DateUtil.yyMMddHHmmss_format);
  20.         //调用工具类导出
  21.         EasyPoiUtils.exportMultiSheetWorkbook(nowDate + "RApi数据统计", exportRApiDataTemplate, response);
  22.     }

excel导出模版对应实体类（注：子类不序列化会抛错）

  1. @Data
  2. @AllArgsConstructor
  3. @NoArgsConstructor
  4. @Accessors(chain = true)
  5. public class ExportRApiDataTemplate {
  6.  
  7.     @SheetName(name = "人员信息")
  8.     private List<SearchUserInfo> searchUserInfoList;
  9.  
  10.    /* @SheetName(name = "帖子信息")
  11.     private List<SearchPostsInfo> searchPostsInfoList;
  13.     @SheetName(name = "标签信息")
  14.     private List<SearchHashTagInfo> searchHashTagInfoList;
  15. */
  16. }
  1. @JsonIgnoreProperties(ignoreUnknown = true)
  2. @Data
  3. public class SearchUserInfo implements Serializable {
  4.  
  5.     @Excel(name = "用户id")
  6.     private String uid;
  7.  
  8.     @Excel(name = "用户名称",width = 30)
  9.     private String name;
  10.  
  11.     @Excel(name = "已关注数量")
  12.     private String followingCount;
  13.  
  14.     @Excel(name = "点赞数")
  15.     private String totalFavorited;
  16.  
  17.     @Excel(name = "粉丝数量")
  18.     private String followerCount;
  19.  
  20.     @Excel(name = "insId")
  21.     private String insId;
  22.  
  23.     @Excel(name = "邮箱")
  24.     private String email;
  25.  
  26.     @Excel(name = "用户主页链接")
  27.     private String userHomePage;
  28.  
  29.     @Excel(name = "统计时间")
  30.     private String createTime;
  31.  
  32.     @Excel(name = "数据抓取关键字",width = 50)
  33.     private String dataSource;
  34.  
  35. }

EasyPoiUtils 工具类

  1. package com.seezoon.admin.modules.sys.utils;
  2.  
  3.  
  4. import cn.afterturn.easypoi.excel.ExcelExportUtil;
  5. import cn.afterturn.easypoi.excel.ExcelImportUtil;
  6. import cn.afterturn.easypoi.excel.entity.ExportParams;
  7. import cn.afterturn.easypoi.excel.entity.ImportParams;
  8. import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
  9. import cn.afterturn.easypoi.exception.excel.ExcelExportException;
  10. import cn.afterturn.easypoi.exception.excel.ExcelImportException;
  11. import com.google.common.collect.Lists;
  12. import com.google.common.collect.Maps;
  13. import com.seezoon.admin.modules.sys.dto.excel.kol.SheetName;
  14. import org.apache.commons.lang3.StringUtils;
  15. import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
  16. import org.apache.poi.hssf.usermodel.HSSFRichTextString;
  17. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  18. import org.apache.poi.ss.usermodel.*;
  19. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
  20. import org.apache.poi.xssf.usermodel.XSSFRichTextString;
  21. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  22. import org.slf4j.Logger;
  23. import org.slf4j.LoggerFactory;
  24. import org.springframework.web.multipart.MultipartFile;
  25. import javax.servlet.http.HttpServletResponse;
  26. import java.io.BufferedOutputStream;
  27. import java.io.File;
  28. import java.io.IOException;
  29. import java.io.OutputStream;
  30. import java.lang.reflect.Field;
  31. import java.lang.reflect.ParameterizedType;
  32. import java.lang.reflect.Type;
  33. import java.net.URLEncoder;
  34. import java.util.*;
  35.  
  36. import java.util.zip.ZipEntry;
  37. import java.util.zip.ZipOutputStream;
  38.  
  39.  
  40. /**
  41.  * @Classname EasyPoiUtils
  42.  * @Description
  43.  * @Date 2023/8/31 11:59
  44.  * @Created by jyl
  45.  */
  46. public class EasyPoiUtils {
  47.         static final Logger logger = LoggerFactory.getLogger(EasyPoiUtils.class);
  48.         private static final String XLS = "xls";
  49.         private static final String XLSX = "xlsx";
  50.         private static final String SPLIT = ".";
  51.  
  52.         public static void newExportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
  53.             newExport(list, fileName, response);
  54.         }
  55.  
  56.  
  57.         private static void newDownLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
  58.             try {
  59.                 response.setCharacterEncoding("UTF-8");
  60.                 response.setHeader("content-Type", "application/vnd.ms-excel");
  61.                 response.setHeader("Content-Disposition",
  62.                         "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
  63.                 workbook.write(response.getOutputStream());
  64.             } catch (IOException e) {
  65.                 throw new RuntimeException(e.getMessage());
  66.             }
  67.         }
  68.         private static void newExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
  69.             Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
  70.             if (workbook != null);
  71.             newDownLoadExcel(fileName, response, workbook);
  72.         }
  73.  
  74.  
  75.  
  76.         public static <T> List<T> newImportExcelMore(MultipartFile file, Class<T> pojoClass, ImportParams params){
  77.             if (file == null){
  78.                 return null;
  79.             }
  80.  
  81.             List<T> list = null;
  82.             try {
  83.                 list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
  84.             }catch (NoSuchElementException e){
  85.                 throw new RuntimeException("excel文件不能为空");
  86.             } catch (Exception e) {
  87.                 throw new RuntimeException(e.getMessage());
  88.             }
  89.             return list;
  90.         }
  91.  
  92.         public static Workbook newGetWorkbook(MultipartFile file) {
  93.             Workbook workbook=null;
  94.             try {
  95.                 // 获取Excel后缀名
  96.                 String fileName = file.getOriginalFilename();
  97.                 if (StringUtils.isEmpty(fileName) || fileName.lastIndexOf(SPLIT) < 0) {
  98.                     logger.warn("解析Excel失败，因为获取到的Excel文件名非法！");
  99.                     return null;
  100.                 }
  101.                 String fileType = fileName.substring(fileName.lastIndexOf(SPLIT) + 1, fileName.length());
  102.                 // 获取Excel工作簿
  103.                 if (fileType.equalsIgnoreCase(XLS)) {
  104.                     workbook = new HSSFWorkbook(file.getInputStream());
  105.                 } else if (fileType.equalsIgnoreCase(XLSX)) {
  106.                     workbook = new XSSFWorkbook(file.getInputStream());
  107.                 }
  108.             } catch (IOException e) {
  109.                 e.printStackTrace();
  110.             }
  111.             return workbook;
  112.         }
  113.  
  114.  
  115.     public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
  116.         ExportParams exportParams = new ExportParams(title, sheetName);
  117.         exportParams.setCreateHeadRows(isCreateHeader);
  118.         defaultExport(list, pojoClass, fileName, response, exportParams);
  119.  
  120.     }
  121.  
  122.     /**
  123.      * 导出excel
  124.      *
  125.      * @param list      数据list
  126.      * @param title     标题
  127.      * @param sheetName sheet名称
  128.      * @param pojoClass 实体class
  129.      * @param fileName  文件名
  130.      * @param response  响应
  131.      */
  132.     public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
  133.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GBK");
  134.         try {
  135.             response.setHeader("content-disposition",
  136.                     "attachment;filename=" + java.net.URLEncoder.encode(fileName, "GBK")
  137.                             + ";filename*=GBK''" + java.net.URLEncoder.encode(fileName, "GBK"));
  138.         } catch (Exception e) {
  139.             // ...
  140.         }
  141.         defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
  142.     }
  143.  
  144.     /**
  145.      * 导出excel
  146.      *
  147.      * @param list     多个Map key title 对应表格Title key entity 对应表格对应实体 key data
  148.      * @param fileName 标题
  149.      * @param response 响应
  150.      */
  151.     public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
  152.         defaultExport(list, fileName, response);
  153.     }
  154.  
  155.     /**
  156.      * 导出多个excel
  157.      *
  158.      * @param workbooks 多个excel文件 通过ExcelExportUtil.exportExcel往workbooks内放入excel
  159.      * @param fileNames 文件名 每个excel文件的名称顺序必须一致且名称请务必保证不重复
  160.      * @param fileName  压缩包文件名
  161.      * @param response  标题
  162.      */
  163.     public static void exportExcels(List<Workbook> workbooks, List<String> fileNames, String fileName, HttpServletResponse response) {
  164.         try {
  165.             response.setHeader("Content-Disposition",
  166.                     "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".zip");
  167.             OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
  168.             ZipOutputStream zipOut = new ZipOutputStream(toClient);
  169.             for (int i = 0; i < workbooks.size(); i++) {
  170.                 ZipEntry entry = new ZipEntry(fileNames.get(i) + ".xls");
  171.                 zipOut.putNextEntry(entry);
  172.                 workbooks.get(i).write(zipOut);
  173.             }
  174.             zipOut.flush();
  175.             zipOut.close();
  176.         } catch (IOException e) {
  177.             throw new ExcelExportException(e.getMessage());
  178.         }
  179.     }
  180.  
  181.     /**
  182.      * 导出excel
  183.      *
  184.      * @param list      数据list
  185.      * @param pojoClass 实体class
  186.      * @param fileName  文件名
  187.      * @param response  响应
  188.      */
  189.     private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
  190.         Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
  191.         downLoadExcel(fileName, response, workbook);
  192.     }
  193.  
  194.     private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
  195.         try {
  196.             response.setCharacterEncoding("UTF-8");
  197.             response.setHeader("content-Type", "application/vnd.ms-excel");
  198.             response.setHeader("Content-Disposition",
  199.                     "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
  200.             workbook.write(response.getOutputStream());
  201.             workbook.close();
  202.         } catch (IOException e) {
  203.             throw new ExcelImportException(e.getMessage());
  204.         }
  205.     }
  206.  
  207.     private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
  208.         Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
  209.         downLoadExcel(fileName, response, workbook);
  210.     }
  211.  
  212.     public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
  213.         if (StringUtils.isBlank(filePath)) {
  214.             return null;
  215.         }
  216.         ImportParams params = new ImportParams();
  217.         params.setTitleRows(titleRows);
  218.         params.setHeadRows(headerRows);
  219.         List<T> list = null;
  220.         try {
  221.             list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
  222.         } catch (NoSuchElementException e) {
  223.             throw new ExcelImportException("模板不能为空");
  224.         } catch (Exception e) {
  225.             e.printStackTrace();
  226.             throw new ExcelImportException(e.getMessage());
  227.         }
  228.         return list;
  229.     }
  230.  
  231.     public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
  232.         if (file == null) {
  233.             return null;
  234.         }
  235.         ImportParams params = new ImportParams();
  236.         params.setTitleRows(titleRows);//标题占几行,从哪行开始读取
  237.         params.setHeadRows(headerRows);//header占几行
  238.         params.setSheetNum(1);
  239.         List<T> list = null;
  240.         try {
  241.             list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
  242.         } catch (NoSuchElementException e) {
  243.             throw new ExcelImportException("excel文件不能为空");
  244.         } catch (Exception e) {
  245.             throw new ExcelImportException(e.getMessage());
  246.         }
  247.         return list;
  248.     }
  249.  
  250.  
  251.     /**
  252.      * 多Sheet导出，不需要批注
  253.      * @param fileName 文件名
  254.      * @param exportDataSet 导出的类对象
  255.      * @param response
  256.      * @throws IllegalAccessException
  257.      * @throws IOException
  258.      */
  259.     public static <T> void exportMultiSheetWorkbook(String fileName,T exportDataSet, HttpServletResponse response) throws IOException, IllegalAccessException {
  260.         exportMultiSheetWorkbook(fileName,exportDataSet,response,null);
  261.     }
  262.  
  263.     /**
  264.      * 多Sheet导出，需要批注
  265.      * @param fileName 文件名
  266.      * @param exportDataSet 导出的类对象
  267.      * @param response
  268.      * @throws IllegalAccessException
  269.      * @throws IOException
  270.      */
  271.     public static <T> void exportMultiSheetWorkbook(String fileName,T exportDataSet, HttpServletResponse response,String remake) throws IllegalAccessException, IOException {
  272.         // 多个sheet配置参数
  273.         final List<Map<String, Object>> sheetsList = Lists.newArrayList();
  274.  
  275.         Class<?> aClass = exportDataSet.getClass();
  276.         Field[] declaredFields = aClass.getDeclaredFields();
  277.         for (Field field : declaredFields) {
  278.             field.setAccessible(true);
  279.             SheetName annotation = field.getAnnotation(SheetName.class);
  280.             final String sheetName = annotation.name();
  281.             Map<String, Object> exportMap = Maps.newHashMap();
  282.             final ExportParams exportParams = new ExportParams(null, sheetName);
  283.  
  284.             // 以下3个参数为API中写死的参数名 分别是sheet配置/导出类（注解定义）/数据集
  285.             exportMap.put("title", exportParams);
  286.             // 获取list<?>中?的泛型
  287.             if (field.getType().isAssignableFrom(List.class)){
  288.                 Type fc = field.getGenericType(); //如果是List类型，得到其Generic的类型
  289.                 if(fc instanceof ParameterizedType){
  290.                     ParameterizedType pt = (ParameterizedType) fc;
  291.                     //得到泛型里的class类型对象。
  292.                     Class genericClazz = (Class)pt.getActualTypeArguments()[0];
  293.                     exportMap.put("entity", genericClazz);
  294.                 }
  295.                 exportMap.put("data", field.get(exportDataSet));
  296.             }else {
  297.                 exportMap.put("entity", field.getType());
  298.                 exportMap.put("data", new ArrayList<>(Collections.singletonList(field.get(exportDataSet))));
  299.             }
  300.             // 加入多sheet配置列表
  301.             sheetsList.add(exportMap);
  302.         }
  303.  
  304.         // 导出文件
  305.  
  306.         // 核心方法：导出含多个sheet的excel文件 【注意，该方法第二个参数必须与上述的ExportParams对象指定的导出类型一致，默认ExcelType.HSSF格式，否则执行此方法时会报错!!!】
  307.         final Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
  308.  
  309.         // 添加批注格式 ：0#姓名不能为空__1#学生性别 1:男 2:女__2#出生日期：yyyy-MM-dd__3#图片不能为空
  310.         if(StringUtils.isNotBlank(remake)){
  311.             buildComment(workbook,0,remake);
  312.         }
  313.         response.setCharacterEncoding("UTF-8");
  314.         response.setHeader("content-Type", "application/vnd.ms-excel");
  315.         response.setHeader("Content-Disposition",
  316.                 "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
  317.  
  318.         workbook.write(response.getOutputStream());
  319.         // 写完数据关闭流
  320.         workbook.close();
  321.     }
  322.  
  323.     public static void buildComment(Workbook workbook, int titleRowsIndex, String commentStr) {
  324.         Sheet sheet = workbook.getSheetAt(0);
  325.         //创建一个图画工具
  326.         Drawing<?> drawing = sheet.createDrawingPatriarch();
  327.         Row row = sheet.getRow(titleRowsIndex);
  328.         if (StringUtils.isNotBlank(commentStr)) {
  329.             //解析批注，并传换成map
  330.             Map<Integer, String> commentMap = getCommentMap(commentStr);
  331.             for (Map.Entry<Integer, String> entry : commentMap.entrySet()) {
  332.                 Cell cell = row.getCell(entry.getKey());
  333.                 //创建批注
  334.                 Comment comment = drawing.createCellComment(newClientAnchor(workbook));
  335.                 //输入批注信息
  336.                 comment.setString(newRichTextString(workbook, entry.getValue()));
  337.                 //将批注添加到单元格对象中
  338.                 cell.setCellComment(comment);
  339.  
  340.                 if (entry.getValue().contains("必填项")){
  341.                     //设置单元格背景颜色
  342.                     CellStyle cellStyle = workbook.createCellStyle();
  343.                     //通过workbook获得文字处理类
  344.                     Font font = workbook.createFont();
  345.                     font.setColor(Font.COLOR_RED);
  346.                     //水平居中
  347.                     cellStyle.setAlignment(HorizontalAlignment.CENTER);
  348.                     //垂直居中
  349.                     cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  350.                     cellStyle.setFont(font);
  351.                     cell.setCellStyle(cellStyle);
  352.                 }
  353.             }
  354.         }
  355.     }
  356.     /**
  357.      * 批注信息，默认解析：批注#列索引，比如用户名不允许重复#0。可覆盖此方法，解析自定义的批注格式
  358.      *
  359.      * @param commentStr 当前行的所有批注信息
  360.      * @return key：列索引，value：对应列的所有批注信息
  361.      */
  362.     protected static  Map<Integer, String> getCommentMap(String commentStr) {
  363.         //每行的所有单元格的批注都在commentStr里，并用”__”分隔
  364.         String[] split = commentStr.split("__");
  365.         Map<Integer, String> commentMap = new HashMap<>();
  366.         for (String msg : split) {
  367.             String[] cellMsg = msg.split("#");
  368.             //如果当前列没有批注，会将该列的索引作为key存到map里；已有批注，以“,“分隔继续拼接
  369.             int cellIndex = Integer.parseInt(cellMsg[0]);
  370.             if (commentMap.get(cellIndex) == null) {
  371.                 commentMap.put(cellIndex, cellMsg[1]);
  372.             } else {
  373.                 commentMap.replace(cellIndex, commentMap.get(cellIndex) + "," + cellMsg[1]);
  374.             }
  375.         }
  376.         return commentMap;
  377.     }
  378.     private static  ClientAnchor newClientAnchor(Workbook workbook) {
  379.         //xls
  380.         if (workbook instanceof HSSFWorkbook) {
  381.             return new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
  382.         }
  383.         //xlsx
  384.         else {
  385.             return new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
  386.         }
  387.     }
  388.     private static  RichTextString newRichTextString(Workbook workbook, String msg) {
  389.         //xls
  390.         if (workbook instanceof HSSFWorkbook) {
  391.             return new HSSFRichTextString(msg);
  392.         }
  393.         //xlsx
  394.         else {
  395.             return new XSSFRichTextString(msg);
  396.         }
  397.     }
  398.  
  399.  
  400. }

若需要合并单元格可使用@ExcelCollection注解 

注:标记该注解的类型必须是list,且list类型里的属性须有@Excel注解标记

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/536773
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号