easyexcel导出

转x33g5p2x  于2022-05-18 转载在 其他  
字(8.5k)|赞(0)|评价(0)|浏览(815)

easyexcel导出

一、依赖

  1. <!--阿里巴巴EasyExcel依赖-->
  2.         <dependency>
  3.             <groupId>com.alibaba</groupId>
  4.             <artifactId>easyexcel</artifactId>
  5.             <version>2.2.10</version>
  6.         </dependency>

参考文档：语雀easyexcel文档

二、导出

普通导出

  1. /**
  2.      * 导出
  3.      *
  4.      * @param response 响应流
  5.      * @param fileName 文件名称
  6.      * @param list     数据
  7.      * @param clazz    class文件
  8.      */
  9.     public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class<?> clazz) {
  10.         if (CollectionUtils.isEmpty(list)) {
  11.             throw new RuntimeException();
  12.         }
  13.         if (StringUtils.isEmpty(fileName)) {
  14.             fileName = new Date().toString();
  15.         }
  17.         try {
  18.             response.setContentType("application/vnd.ms-excel");
  19.             response.setCharacterEncoding("utf-8");
  20.             // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
  21.             try {
  22.                 fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
  23.             } catch (UnsupportedEncodingException e) {
  24.                 e.printStackTrace();
  25.             }
  26.             response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
  28.             EasyExcel.write(response.getOutputStream(), clazz)
  29.                     // 导出文件名
  30.                     .autoCloseStream(Boolean.TRUE).sheet(sheetName)
  31.                     .doWrite(list);
  32.         } catch (Exception e) {
  33.             e.printStackTrace();
  34.         }
  35.     }

实体类

  1. @HeadRowHeight
  2. @HeadStyle(horizontalAlignment = CENTER)
  3. public class ExportAssetModel {
  5.     @ColumnWidth(30)
  6.     @HeadStyle(fillForegroundColor = 40)
  7.     @ContentStyle(wrapped = true)
  8.     @ExcelProperty({"基础属性", "制造商\n(可输入数字0-9字母a-zA-Z,下划线_-以及汉字,最大长字符64位)\n(必填)"})
  9.     private String manufacturer;
  11.     @ColumnWidth(30)
  12.     @HeadStyle(fillForegroundColor = 40)
  13.     @ContentStyle(wrapped = true)
  14.     @ExcelProperty({"基础属性", "型号\n(请输入1-64个字符，可输入中文，数字0-9，字母a-zA-Z，空格，以及字符+._-（）()[]:,/)\n(必填)"})
  15.     private String model;
  17.     @ColumnWidth(30)
  18.     @HeadStyle(fillForegroundColor = 40)
  19.     @ContentStyle(wrapped = true)
  20.     @ExcelProperty({"基础属性", "设备类型\n(请输入1-64个字符，只能输入中文,英文，数字，空格和特殊字符#@_.*-/[]()～′^{}|:;、,‘’)\n(必填)"})
  21.     private String deviceTypeStr;
  23.     @ColumnWidth(30)
  24.     @HeadStyle(fillForegroundColor = 40)
  25.     @ContentStyle(wrapped = true)
  26.     @ExcelProperty({"基础属性", "状态\n(只能输入使用中、未使用)"})
  27.     private String status;
  29.     @ColumnWidth(30)
  30.     @HeadStyle(fillForegroundColor = 40)
  31.     @ContentStyle(wrapped = true)
  32.     @ExcelProperty({"基础属性", "备注\n(请输入1-64个字符，只能输入中文,英文，数字，空格和特殊字符#@_.*-/[]()～′^{}|:;、,‘’)"})
  33.     private String remark;
  35.     @ColumnWidth(30)
  36.     @HeadStyle(fillForegroundColor = 53)
  37.     @ContentStyle(wrapped = true)
  38.     @ExcelProperty({"扩展属性", "高度(U)\n(请输入1-100之间的整数)\n(IT设备、通信设备必填)"})
  39.     private String height;
  41.     @ColumnWidth(30)
  42.     @HeadStyle(fillForegroundColor = 53)
  43.     @ContentStyle(wrapped = true)
  44.     @ExcelProperty({"扩展属性", "重量(Kg)\n(请输入0-9999.99之间的数)"})
  45.     private String weight;
  47.     @ColumnWidth(30)
  48.     @HeadStyle(fillForegroundColor = 53)
  49.     @ContentStyle(wrapped = true)
  50.     @ExcelProperty({"扩展属性", "额定功率(W)\n(请输入0-9999999.99之间的数)\n(IT设备、通信设备必填)"})
  51.     private String ratedPower;
  53.     @ColumnWidth(30)
  54.     @HeadStyle(fillForegroundColor = 53)
  55.     @ContentStyle(wrapped = true)
  56.     @ExcelProperty({"扩展属性", "配电端口数\n(请输入0-2048之间的整数)"})
  57.     private String distributionPort;
  59.     @ColumnWidth(30)
  60.     @HeadStyle(fillForegroundColor = 53)
  61.     @ContentStyle(wrapped = true)
  62.     @ExcelProperty({"扩展属性", "网络端口数\n(请输入0-2048之间的整数)"})
  63.     private String networkPort;
  64. }

导出效果

自定义表头导出

  1. /**
  2.      * 自定义表头导出
  3.      *
  4.      * @param response
  5.      * @param fileName
  6.      * @param sheetName
  7.      * @param list
  8.      * @param head
  9.      */
  10.     public static void customHeadExportExcel(HttpServletResponse response, String fileName, String sheetName, List<List<Object>> list, List<List<String>> head) {
  11.         if (CollectionUtils.isEmpty(list)) {
  12.             throw new RuntimeException();
  13.         }
  15.         if (CollectionUtils.isEmpty(head)) {
  16.             throw new RuntimeException();
  17.         }
  19.         if (StringUtils.isEmpty(fileName)) {
  20.             fileName = new Date().toString();
  21.         }
  23.         try {
  24.             response.setContentType("application/vnd.ms-excel");
  25.             response.setCharacterEncoding("utf-8");
  26.             // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
  27.             try {
  28.                 fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
  29.             } catch (UnsupportedEncodingException e) {
  30.                 e.printStackTrace();
  31.             }
  32.             response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
  34.             EasyExcel.write(response.getOutputStream())
  35.                     .head(head)
  36.                     //默认策略
  37. //                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
  38.                     .registerWriteHandler(myHorizontalCellStyleStrategy())
  39.                     .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))//简单的列宽策略，列宽20
  40. //                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)25))//简单的行高策略
  41.                     // 导出文件名
  42.                     .autoCloseStream(Boolean.TRUE)
  43.                     .sheet(sheetName)
  44.                     .doWrite(list);
  45.         } catch (Exception e) {
  46.             e.printStackTrace();
  47.         }
  48.     }
  50. 	private static HorizontalCellStyleStrategy myHorizontalCellStyleStrategy() {
  51.         //1 表头样式策略
  52.         WriteCellStyle headWriteCellStyle = new WriteCellStyle();
  53.         //设置表头居中对齐
  54.         headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
  55.         //表头前景设置淡蓝色
  56.         headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
  57.         WriteFont headWriteFont = new WriteFont();
  58.         headWriteFont.setBold(true);
  59.         headWriteFont.setFontName("宋体");
  60.         headWriteFont.setFontHeightInPoints((short) 12);
  61.         headWriteCellStyle.setWriteFont(headWriteFont);
  63.         //内容样式  多个样式则隔行换色
  64.         List<WriteCellStyle> listCntWritCellSty = new ArrayList<>();
  66.         //2 内容样式策略  样式一
  67.         WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
  68.         WriteFont contentWriteFont = new WriteFont();
  69.         //内容字体大小
  70.         contentWriteFont.setFontName("宋体");
  71.         contentWriteFont.setFontHeightInPoints((short) 11);
  72.         contentWriteCellStyle.setWriteFont(contentWriteFont);
  73.         //设置自动换行
  74.         contentWriteCellStyle.setWrapped(true);
  75.         //设置垂直居中
  76.         contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  77.         // 头默认了 FillPatternType所以可以不指定。
  78.         contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
  79.         //设置背景黄色
  80.         contentWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
  81.         //设置水平靠左
  82.         contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
  83.         //设置边框样式
  84.         setBorderStyle(contentWriteCellStyle);
  85.         //内容风格可以定义多个。
  86.         listCntWritCellSty.add(contentWriteCellStyle);
  88.         //2 内容样式策略  样式二
  89.         WriteCellStyle contentWriteCellStyle2 = new WriteCellStyle();
  90.         // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色。
  91.         // 头默认了 FillPatternType所以可以不指定。
  92.         contentWriteCellStyle2.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
  93.         // 背景绿色
  94.         contentWriteCellStyle2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
  95.         //设置垂直居中
  96.         contentWriteCellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
  97.         contentWriteCellStyle2.setHorizontalAlignment(HorizontalAlignment.LEFT);
  98.         //设置边框样式
  99.         setBorderStyle(contentWriteCellStyle2);
  100.         listCntWritCellSty.add(contentWriteCellStyle2);
  101.         // 水平单元格风格综合策略(表头 + 内容)
  102.         // return  new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
  103.         return new HorizontalCellStyleStrategy(headWriteCellStyle, listCntWritCellSty);
  104.     }
  106.     /**
  107.      * 设置边框样式
  108.      *
  109.      * @param contentWriteCellStyle
  110.      */
  111.     private static void setBorderStyle(WriteCellStyle contentWriteCellStyle) {
  112.         //设置边框样式
  113.         contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
  114.         // contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex()); //颜色
  115.         contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
  116.         contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
  117.         contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
  118.     }

多sheet导出

  1. public static void exportMoreExcel(HttpServletResponse response, String fileName,
  2.                                        String sheetNameOne,String sheetNameTwo,String sheetNameThree,
  3.                                        List<List<Object>> list, List<List<String>> head,
  4.                                        List<?> listTwo,Class<?> clazzTwo, List<?> listThree,Class<?> clazzThree) {
  5.         if (CollectionUtils.isEmpty(list)) {
  6.             throw new RuntimeException();
  7.         }
  9.         if (CollectionUtils.isEmpty(head)) {
  10.             throw new RuntimeException();
  11.         }
  13.         if (StringUtils.isEmpty(fileName)) {
  14.             fileName = new Date().toString();
  15.         }
  17.         try {
  18.             response.setContentType("application/vnd.ms-excel");
  19.             response.setCharacterEncoding("utf-8");
  20.             // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
  21.             try {
  22.                 fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
  23.             } catch (UnsupportedEncodingException e) {
  24.                 e.printStackTrace();
  25.             }
  26.             response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
  28.             ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
  29.             WriteSheet writeSheet = EasyExcel.writerSheet(1, sheetNameOne)
  30.                     .head(head)
  31.                     .registerWriteHandler(myHorizontalCellStyleStrategy())
  32.                     .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))//简单的列宽策略，列宽20
  33.                     .build();
  34.             excelWriter.write(list, writeSheet);
  36.             writeSheet = EasyExcel.writerSheet(2, sheetNameTwo).head(clazzTwo).build();
  37.             excelWriter.write(listTwo, writeSheet);
  39.             writeSheet = EasyExcel.writerSheet(3, sheetNameThree).head(clazzThree).build();
  40.             excelWriter.write(listThree, writeSheet);
  41.             // 千万别忘记finish 会帮忙关闭流
  42.             excelWriter.finish();
  43.         } catch (Exception e) {
  44.             e.printStackTrace();
  45.         }
  46.     }

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/weixin_43296313/article/details/124816503

内容来源于网络，如有侵权，请联系作者删除！

Javapoi开发语言

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com