1.什么是easyExcel

2.如何使用

1.引入pom包

  1.  <dependency>
  2.             <groupId>com.alibaba</groupId>
  3.             <artifactId>easyexcel</artifactId>
  4.             <version>1.1.2-beat1</version>
  5. </dependency>

3.导入使用

1.定义实体类

  1.     @ExcelProperty(index = 0 , value = "时间")
  2.     private String TIME;
  3.     @ExcelProperty(index = 1 , value = "开盘")
  4.     private String OPENING;
  5.     @ExcelProperty(index = 2 , value = "最高")
  6.     private String MAXHIGH;
  7.     @ExcelProperty(index = 3 , value = "最低")
  8.     private String MINLOWEST;
  9.     @ExcelProperty(index = 4 , value = "收盘")
  10.     private String CLOSE_PAN;
  11.     @ExcelProperty(index = 5 , value = "成交量")
  12.     private String VOLUME;
  14.     @ExcelProperty(index = 6 , value = "股票代码")
  15.     private String STOCK_SYMBOL;
  18. //@ExcelProperty(index = 0 , value = "时间")
  19. //index:表示第几列
  20. //value：表示这一列对于的名字

2.添加导入数据监听类

  2. /**
  3.  * 监听类，可以自定义
  4.  *
  5.  * @author liuyi
  6.  * @Created 2019-7-18 18:01:53
  7.  **/
  8. public class ExcelListener extends AnalysisEventListener {
  10.     /**
  11.      * 自定义用于暂时存储data。
  12.      * 可以通过实例获取该值
  13.      */
  14.     private List<Object> datas = new ArrayList<>();
  16.     /**
  17.      * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
  18.      */
  19.     @Override
  20.     public void invoke(Object object, AnalysisContext context) {
  21.         //数据存储到list，供批量处理，或后续自己业务逻辑处理。
  22.         datas.add(object);
  23.         //根据业务自行 do something
  24.         doSomething();
  25.  /*
  26.  如数据过大，可以进行定量分批处理
  27.  if(datas.size()<=100){
  28.  datas.add(object);
  29.  }else {
  30.  doSomething();
  31.  datas = new ArrayList<Object>();
  32.  }
  33.  */
  34.     }
  36.     /**
  37.      * 根据业务自行实现该方法
  38.      */
  39.     private void doSomething() {
  40.     }
  42.     @Override
  43.     public void doAfterAllAnalysed(AnalysisContext context) {
  44.  /*
  45.  datas.clear();
  46.  解析结束销毁不用的资源
  47.  */
  48.     }
  50.     public List<Object> getDatas() {
  51.         return datas;
  52.     }
  54.     public void setDatas(List<Object> datas) {
  55.         this.datas = datas;
  56.     }
  57. }

3.编写导入导出工具类

  2. /**
  3.  * Excel工具类
  4.  *
  5.  * @author liuyi
  6.  * @Created 2019-7-18 18:01:53
  7.  **/
  8. public class ExcelUtil {
  9.     /**
  10.      * 读取 Excel(多个 sheet)
  11.      *
  12.      * @param excel 文件
  13.      * @param rowModel 实体类映射，继承 BaseRowModel 类
  14.      * @return Excel 数据 list
  15.      */
  16.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws ExcelException {
  17.         ExcelListener excelListener = new ExcelListener();
  18.         ExcelReader reader = getReader(excel, excelListener);
  20.         if (reader == null) {
  21.             return null;
  22.         }
  24.         for (Sheet sheet : reader.getSheets()) {
  25.             if (rowModel != null) {
  26.                 sheet.setClazz(rowModel.getClass());
  27.             }
  28.             reader.read(sheet);
  29.         }
  31.         return excelListener.getDatas();
  32.     }
  34.     /**
  35.      * 读取某个 sheet 的 Excel
  36.      *
  37.      * @param excel 文件
  38.      * @param rowModel 实体类映射，继承 BaseRowModel 类
  39.      * @param sheetNo sheet 的序号 从1开始
  40.      * @return Excel 数据 list
  41.      */
  42.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws ExcelException {
  43.         return readExcel(excel, rowModel, sheetNo, 1);
  44.     }
  46.     /**
  47.      * 读取某个 sheet 的 Excel
  48.      *
  49.      * @param excel 文件
  50.      * @param rowModel 实体类映射，继承 BaseRowModel 类
  51.      * @param sheetNo sheet 的序号 从1开始
  52.      * @param headLineNum 表头行数，默认为1
  53.      * @return Excel 数据 list
  54.      */
  55.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws ExcelException {
  56.         ExcelListener excelListener = new ExcelListener();
  57.         ExcelReader reader = getReader(excel, excelListener);
  59.         if (reader == null) {
  60.             return null;
  61.         }
  63.         reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
  65.         return excelListener.getDatas();
  66.     }
  68.     /**
  69.      * 导出 Excel ：一个 sheet，带表头
  70.      *
  71.      * @param response HttpServletResponse
  72.      * @param list 数据 list，每个元素为一个 BaseRowModel
  73.      * @param fileName 导出的文件名
  74.      * @param sheetName 导入文件的 sheet 名
  75.      * @param object 映射实体类，Excel 模型
  76.      */
  77.     public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName,
  78.                                   String sheetName, BaseRowModel object) throws ExcelException {
  79.         ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
  80.         Sheet sheet = new Sheet(1, 0, object.getClass());
  81.         sheet.setSheetName(sheetName);
  83.         TableStyle tableStyle = new TableStyle();
  84.         tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
  85.         Font font = new Font();
  86.         font.setFontHeightInPoints((short) 9);
  87.         tableStyle.setTableHeadFont(font);
  88.         tableStyle.setTableContentFont(font);
  89.         sheet.setTableStyle(tableStyle);
  91.         writer.write(list, sheet);
  92.         writer.finish();
  93.     }
  95.     /**
  96.      * 导出 Excel ：多个 sheet，带表头
  97.      *
  98.      * @param response HttpServletResponse
  99.      * @param list 数据 list，每个元素为一个 BaseRowModel
  100.      * @param fileName 导出的文件名
  101.      * @param sheetName 导入文件的 sheet 名
  102.      * @param object 映射实体类，Excel 模型
  103.      */
  104.     public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response,
  105.                                                           List<? extends BaseRowModel> list, String fileName,
  106.                                                           String sheetName, BaseRowModel object) throws ExcelException {
  107.         ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
  108.         Sheet sheet = new Sheet(1, 0, object.getClass());
  109.         sheet.setSheetName(sheetName);
  110.         sheet.setTableStyle(getTableStyle());
  111.         writer.write(list, sheet);
  113.         return writer;
  114.     }
  116.     /**
  117.      * 导出融资还款情况表
  118.      *
  119.      * @param response
  120.      * @param list
  121.      * @param fileName
  122.      * @param sheetName
  123.      * @param object
  124.      */
  125.     public static void writeFinanceRepayment(HttpServletResponse response, List<? extends BaseRowModel> list,
  126.                                              String fileName, String sheetName, BaseRowModel object) throws ExcelException {
  127.         ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
  128.         Sheet sheet = new Sheet(1, 0, object.getClass());
  129.         sheet.setSheetName(sheetName);
  130.         sheet.setTableStyle(getTableStyle());
  131.         writer.write(list, sheet);
  133.         for (int i = 1; i <= list.size(); i += 4) {
  134.             writer.merge(i, i + 3, 0, 0);
  135.             writer.merge(i, i + 3, 1, 1);
  136.         }
  138.         writer.finish();
  139.     }
  141.     /**
  142.      * 导出文件时为Writer生成OutputStream
  143.      */
  144.     private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws ExcelException {
  145.         //创建本地文件
  146.         fileName = fileName + ".xls";
  148.         try {
  149.             fileName = new String(fileName.getBytes(), "ISO-8859-1");
  150.             response.addHeader("Content-Disposition", "filename=" + fileName);
  152.             return response.getOutputStream();
  153.         } catch (Exception e) {
  155.             throw new ExcelException("导出异常！");
  156.         }
  157.     }
  159.     /**
  160.      * 返回 ExcelReader
  161.      *
  162.      * @param excel 需要解析的 Excel 文件
  163.      * @param excelListener new ExcelListener()
  164.      */
  165.     private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws ExcelException {
  166.         String filename = excel.getOriginalFilename();
  168.         if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
  169.             throw new ExcelException("文件格式错误！");
  170.         }
  171.         InputStream inputStream;
  173.         try {
  174.             inputStream = new BufferedInputStream(excel.getInputStream());
  176.             return new ExcelReader(inputStream, null, excelListener, false);
  177.         } catch (IOException e) {
  178.             e.printStackTrace();
  179.         }
  181.         return null;
  182.     }
  184.     /**
  185.      * 资金收支导出 Excel ：一个 sheet，带表头
  186.      *
  187.      * @param response HttpServletResponse
  188.      * @param list 数据 list，每个元素为一个 BaseRowModel
  189.      * @param fileName 导出的文件名
  190.      * @param sheetName 导入文件的 sheet 名
  191.      * @param object 映射实体类，Excel 模型
  192.      */
  193.     public static void exportFundBudgetExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
  194.                                              String fileName, String sheetName, BaseRowModel object) throws IOException, ExcelException {
  195.         ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
  196.         Sheet sheet = new Sheet(1, 0, object.getClass());
  197.         sheet.setSheetName(sheetName);
  198.         sheet.setTableStyle(getTableStyle());
  200.         writer.write(list, sheet);
  201.         writer.merge(2, 3, 0, 0);
  202.         writer.merge(4, 13, 0, 0);
  203.         writer.merge(14, 14, 0, 1);
  204.         writer.finish();
  205.     }
  207.     /**
  208.      * 读取Excel表格数据，封装成实体
  209.      *
  210.      * @param inputStream
  211.      * @param clazz
  212.      * @param sheetNo
  213.      * @param headLineMun
  214.      * @return
  215.      */
  216.     public static Object readExcel(InputStream inputStream, Class<? extends BaseRowModel> clazz, Integer sheetNo,
  217.                                    Integer headLineMun) throws ExcelException {
  218.         if (null == inputStream) {
  220.             throw new NullPointerException("the inputStream is null!");
  221.         }
  223.         ExcelListener listener = new ExcelListener();
  224.         ExcelReader reader = new ExcelReader(inputStream, valueOf(inputStream), null, listener);
  225.         reader.read(new Sheet(sheetNo, headLineMun, clazz));
  227.         return listener.getDatas();
  228.     }
  230.     /**
  231.      * 根据输入流，判断为xls还是xlsx，该方法原本存在于easyexcel 1.1.0 的ExcelTypeEnum中。
  232.      */
  233.     public static ExcelTypeEnum valueOf(InputStream inputStream) throws ExcelException {
  234.         try {
  235.             FileMagic fileMagic = FileMagic.valueOf(inputStream);
  237.             if (FileMagic.OLE2.equals(fileMagic)) {
  238.                 return ExcelTypeEnum.XLS;
  239.             }
  241.             if (FileMagic.OOXML.equals(fileMagic)) {
  242.                 return ExcelTypeEnum.XLSX;
  243.             }
  245.             throw new ExcelException("excelTypeEnum can not null");
  247.         } catch (IOException e) {
  248.             throw new RuntimeException(e);
  249.         }
  250. //        return null;
  251.     }
  253.     /**
  254.      * 设置全局样式
  255.      *
  256.      * @return
  257.      */
  258.     private static TableStyle getTableStyle() {
  259.         TableStyle tableStyle = new TableStyle();
  261.         tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
  262.         Font font = new Font();
  263.         font.setBold(true);
  264.         font.setFontHeightInPoints((short) 9);
  265.         tableStyle.setTableHeadFont(font);
  266.         Font fontContent = new Font();
  267.         fontContent.setFontHeightInPoints((short) 9);
  268.         tableStyle.setTableContentFont(fontContent);
  270.         return tableStyle;
  271.     }
  272. }

4.编写异常扩展类

  2. public class ExcelException extends Exception  {
  4.     public ExcelException() {
  5.         // TODO Auto-generated constructor stub
  6.     }
  8.     public ExcelException(String message) {
  9.         super(message);
  10.         // TODO Auto-generated constructor stub
  11.     }
  13.     public ExcelException(Throwable cause) {
  14.         super(cause);
  15.         // TODO Auto-generated constructor stub
  16.     }
  18.     public ExcelException(String message, Throwable cause) {
  19.         super(message, cause);
  20.         // TODO Auto-generated constructor stub
  21.     }
  22. }

5.调用方法

  1. // excel：MultipartFile
  2. // ExcelEntity：对应的实体类
  3. // d调用导入方法后吧objLiest强转为List<ExcelEntity> ，此对象存储了所有的数据
  4. Object objList = readExcel(excel, new ExcelEntity(),3);
  5. List<ExcelEntity> orderList = (List<ExcelEntity>) objList;

4.easyExcel导出字典值的映射转换

主要通过制定converter来实现
image.png

  2.     /**
  3.      * easyexcel关于导出的字典值转换
  4.      */
  5.     public class ExcelDictConverter implements Converter {
  7.         private String[] dictCodIsYesNoArr ={"afterSalesStatus", ""};
  9.       /**
  10.        * 返回Java中的对象类型
  11.        * @return Support for Java class
  12.        */
  13.         @Override
  14.         public Class supportJavaTypeKey() {
  15.             return Integer.class;
  16.         }
  18.       /**
  19.        * 返回excel中的对象枚举
  20.        * @return Support for {@link CellDataTypeEnum}
  21.        */
  22.         @Override
  23.         public CellDataTypeEnum supportExcelTypeKey() {
  24.             return CellDataTypeEnum.STRING;
  25.         }
  27.       /**
  28.        * 将Excel对象转换为Java对象
  29.        * @param cellData            Excel单元格数据。
  30.        * @param contentProperty     内容属性。可为空。
  31.        * @param globalConfiguration 全局配置。
  32.        * @return 放入Java对象的数据
  33.        * @throws Exception Exception.
  34.        */
  35.         @Override
  36.         public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
  37.             // 1、获取解析的某个excel字符内容
  38.          //  String value = cellData.getStringValue();
  39.             //return DataTranferEnum.getValue(value);
  40.             return null;
  41.         }
  43.       /**
  44.        * 将Java对象转换为Excel对象
  45.        * @param value               Java Data.NotNull。
  46.        * @param contentProperty     内容属性。可为空。
  47.        * @param globalConfiguration 全局配置。
  48.        * @return 数据放入Excel
  49.        * @throws Exception Exception.
  50.        */
  51.         @Override
  52.         public CellData convertToExcelData(Integer dictKey, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
  54.               // 1、获取属性名
  55.           //  String attributeName = contentProperty.getField().getName();
  56.             // 2、通过属性名和java属性值获取对应的Excel字符值
  57.           //  String excelValue = DataTranferEnum.getExcelValueByProperName(attributeName,value); DataTranferEnum  自定义类
  59.            // if(StringUtils.isNotBlank(excelValue)){
  60.            //    return new CellData(excelValue);
  61.            //  }
  63.            // return new CellData(value);
  65. // 类里需要转换的字段
  66.             Field field = excelContentProperty.getField();
  67.             String fieldName = field.getName();
  68.             String excelValue;
  69. // 字典值为yes_no的字段
  70.             if (StringUtils.equalsAnyIgnoreCase(fieldName, dictCodIsYesNoArr)) {
  71.                 excelValue = DictCache.getValue(DictEnum.YES_NO, dictKey);
  72.             }else {
  73. // 将字段名转换成下划线连接（也就是字典表里的code
  74.                 String dictCode = camelToUnderline(fieldName);
  75. // 当前字段对应的字典值
  76.                 excelValue = DictCache.getValue(dictCode, dictKey);
  77.             }
  78.             return new CellData(StringUtil.isBlank(excelValue) ? dictKey.toString() : excelValue);
  79.         }
  81.         /**
  82.          * 驼峰转成下划线
  83.          *
  84.          * @param sourStr 需要转换的字符串，如：orderStatus
  85.          * @return 转换后的字符串，如：order_status
  86.          */
  87.         public String camelToUnderline(String sourStr) {
  88.             if (sourStr == null || "".equals(sourStr.trim())) {
  89.                 return "";
  90.             }
  91.             int len = sourStr.length();
  92.             StringBuilder sb = new StringBuilder(len);
  93.             for (int i = 0; i < len; i++) {
  94.                 char c = sourStr.charAt(i);
  95.                 if (Character.isUpperCase(c)) {
  96.                     sb.append('_');
  97.                 }
  98.                 sb.append(Character.toLowerCase(c));
  99.             }
  100.             return sb.toString();
  101.         }
  103.     }