devbox
小小林熬夜学编程
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

若依框架自定义导出Excel多sheet页+合并单元格(Poi)_若依导出excel合并单元格

作者：小小林熬夜学编程 | 2024-05-19 23:14:35

赞

踩

若依导出excel合并单元格

先看效果：

 

 

在用若依框架是发现自带的导出功能中并不能导出多个sheet和合并单元格，所以我在这里做了修改希望可以帮到你，用到的点个赞呗！

我们先一步步来

整个程序的思路为先返回下载地址，然后根据下载地址去下载excel

一  ： 返回下载地址

首先是我们需要excel的下载地址，这里我们直接跳到exportExcel方法，以下是代码部分

其中list，listyw为两个sheet页的数据

  1. @GetMapping("/export")
  2.     public AjaxResult export(BizGuideFileDto bizGuideFile) {
  3.         List<BizGuideFileDto> list=iBizGuideFileService.selectBizGuideFile(bizGuideFile);
  4.         List<BizGuideFileDto> listyw=iBizGuideFileService.selectBizGuideFile2(bizGuideFile);        ExcelUtilMerge<BizGuideFileDto> bizDSubjectExcelUtil = new ExcelUtilMerge<BizGuideFileDto>(BizGuideFileDto.class);
  5.  
  6.         return AjaxResult.success(bizDSubjectExcelUtil.exportExcel
  7.                 (list,listyw, "sheet1","sheet2","标题据",list.get(0).getFileTitle()));
  8.     }
  9.  
  10.  
  11. public AjaxResult exportExcel(List<T> list,List<T> listyw, String sheetName,String sheetName2, String title,String title2)
  12.     {
  13.         this.init(list,listyw, sheetName,sheetName2, title, title2,Type.EXPORT, Excelyw.Type.EXPORT);
  14.         return exportExcel();
  15.     }

1、怎么建立多个sheet页

下面有我附上的工具类可以看到在方法init里面和源码相比多了几条尾号为2的，就是第二个sheet，在建工作簿、获取注解时、获取数据导出时都有相关的2，具体情况可以看代码/

具体逻辑可以从init里面一步步往下看

2、如何合并单元格

exportExcel() --->   
writeSheet() --->  
fillExcelData(index, row) --->
this.addCell(excel, row, vo, field, column++);
先根据这个思路看ExcelUtil<T>

合并的只有三行代码，这里我做了一个判断，因为在这个方法外面是一个for循环，不做判断的话回报多重合并的错误，虽然结果是对的，但是有强迫症的我看着不舒服

这里合并的逻辑为从第三行开始，第listyw.size()+1行结束，第0列开始，第0列结束

  1. if (heBing==true){
  2.                     CellRangeAddress region = new CellRangeAddress(2, (short)listyw.size()+1, 0, (short)0);
  3.                     sheet2.addMergedRegion(region);
  4.                     this.heBing=false;
  5.                 }

3、自定义导出字段

因为若依导出是可以自定义的选择导出的字段的，所以只需要给需要导出的字段加上注解就可以，@Excel为若依源码的，是获取第一个sheet的导出字段，@Excelyw是我自己加的获取另一个sheet页导出字段的注解，具体逻辑可以复制Excel的

@Excel：

  1. package com.gyjn.common.annotation;
  2.  
  3. import com.gyjn.common.utils.poi.ExcelHandlerAdapter;
  4. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  5. import org.apache.poi.ss.usermodel.IndexedColors;
  6.  
  7. import java.lang.annotation.ElementType;
  8. import java.lang.annotation.Retention;
  9. import java.lang.annotation.RetentionPolicy;
  10. import java.lang.annotation.Target;
  11. import java.math.BigDecimal;
  12.  
  13. /**
  14.  * 自定义导出Excel数据注解
  15.  *
  16.  * @author ruoyi
  17.  */
  18. @Retention(RetentionPolicy.RUNTIME)
  19. @Target(ElementType.FIELD)
  20. public @interface Excel
  21. {
  22.     /**
  23.      * 导出时在excel中排序
  24.      */
  25.     public int sort() default Integer.MAX_VALUE;
  26.  
  27.     /**
  28.      * 导出到Excel中的名字.
  29.      */
  30.     public String name() default "";
  31.  
  32.     /**
  33.      * 日期格式, 如: yyyy-MM-dd
  34.      */
  35.     public String dateFormat() default "";
  36.  
  37.     /**
  38.      * 如果是字典类型，请设置字典的type值 (如: sys_user_sex)
  39.      */
  40.     public String dictType() default "";
  41.  
  42.     /**
  43.      * 读取内容转表达式 (如: 0=男,1=女,2=未知)
  44.      */
  45.     public String readConverterExp() default "";
  46.  
  47.     /**
  48.      * 分隔符，读取字符串组内容
  49.      */
  50.     public String separator() default ",";
  51.  
  52.     /**
  53.      * BigDecimal 精度 默认:-1(默认不开启BigDecimal格式化)
  54.      */
  55.     public int scale() default -1;
  56.  
  57.     /**
  58.      * BigDecimal 舍入规则 默认:BigDecimal.ROUND_HALF_EVEN
  59.      */
  60.     public int roundingMode() default BigDecimal.ROUND_HALF_EVEN;
  61.  
  62.     /**
  63.      * 导出时在excel中每个列的高度 单位为字符
  64.      */
  65.     public double height() default 14;
  66.  
  67.     /**
  68.      * 导出时在excel中每个列的宽 单位为字符
  69.      */
  70.     public double width() default 16;
  71.  
  72.     /**
  73.      * 文字后缀,如% 90 变成90%
  74.      */
  75.     public String suffix() default "";
  76.  
  77.     /**
  78.      * 当值为空时,字段的默认值
  79.      */
  80.     public String defaultValue() default "";
  81.  
  82.     /**
  83.      * 提示信息
  84.      */
  85.     public String prompt() default "";
  86.  
  87.     /**
  88.      * 设置只能选择不能输入的列内容.
  89.      */
  90.     public String[] combo() default {};
  91.  
  92.     /**
  93.      * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
  94.      */
  95.     public boolean isExport() default true;
  96.  
  97.     /**
  98.      * 另一个类中的属性名称,支持多级获取,以小数点隔开
  99.      */
  100.     public String targetAttr() default "";
  101.  
  102.     public String mergeLine() default "";
  103.  
  104.     /**
  105.      * 是否自动统计数据,在最后追加一行统计数据总和
  106.      */
  107.     public boolean isStatistics() default false;
  108.  
  109.     /**
  110.      * 导出类型（0数字 1字符串）
  111.      */
  112.     public ColumnType cellType() default ColumnType.STRING;
  113.  
  114.     /**
  115.      * 导出字体颜色
  116.      */
  117.     public IndexedColors color() default IndexedColors.BLACK;
  118.  
  119.     /**
  120.      * 导出字段对齐方式
  121.      */
  122.     public HorizontalAlignment align() default HorizontalAlignment.CENTER;
  123.  
  124.     /**
  125.      * 自定义数据处理器
  126.      */
  127.     public Class<?> handler() default ExcelHandlerAdapter.class;
  128.  
  129.     /**
  130.      * 自定义数据处理器参数
  131.      */
  132.     public String[] args() default {};
  133.  
  134.     public enum Align
  135.     {
  136.         AUTO(0), LEFT(1), CENTER(2), RIGHT(3);
  137.         private final int value;
  138.  
  139.         Align(int value)
  140.         {
  141.             this.value = value;
  142.         }
  143.  
  144.         public int value()
  145.         {
  146.             return this.value;
  147.         }
  148.     }
  149.  
  150.     /**
  151.      * 字段类型（0：导出导入；1：仅导出；2：仅导入）
  152.      */
  153.     Type type() default Type.ALL;
  154.  
  155.     public enum Type
  156.     {
  157.         ALL(0), EXPORT(1), IMPORT(2);
  158.         private final int value;
  159.  
  160.         Type(int value)
  161.         {
  162.             this.value = value;
  163.         }
  164.  
  165.         public int value()
  166.         {
  167.             return this.value;
  168.         }
  169.     }
  170.  
  171.     public enum ColumnType
  172.     {
  173.         NUMERIC(0), STRING(1), IMAGE(2);
  174.         private final int value;
  175.  
  176.         ColumnType(int value)
  177.         {
  178.             this.value = value;
  179.         }
  180.  
  181.         public int value()
  182.         {
  183.             return this.value;
  184.         }
  185.     }
  186. }

@Excelyw

  1. package com.gyjn.common.annotation;
  2.  
  3. import com.gyjn.common.utils.poi.ExcelHandlerAdapter;
  4. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  5. import org.apache.poi.ss.usermodel.IndexedColors;
  6.  
  7. import java.lang.annotation.ElementType;
  8. import java.lang.annotation.Retention;
  9. import java.lang.annotation.RetentionPolicy;
  10. import java.lang.annotation.Target;
  11. import java.math.BigDecimal;
  12.  
  13. /**
  14.  * 自定义导出Excel数据注解
  15.  *
  16.  * @author ruoyi
  17.  */
  18. @Retention(RetentionPolicy.RUNTIME)
  19. @Target(ElementType.FIELD)
  20. public @interface Excelyw
  21. {
  22.     /**
  23.      * 导出时在excel中排序
  24.      */
  25.     public int sort() default Integer.MAX_VALUE;
  26.  
  27.     /**
  28.      * 导出到Excel中的名字.
  29.      */
  30.     public String name() default "";
  31.  
  32.     /**
  33.      * 日期格式, 如: yyyy-MM-dd
  34.      */
  35.     public String dateFormat() default "";
  36.  
  37.     /**
  38.      * 如果是字典类型，请设置字典的type值 (如: sys_user_sex)
  39.      */
  40.     public String dictType() default "";
  41.  
  42.     /**
  43.      * 读取内容转表达式 (如: 0=男,1=女,2=未知)
  44.      */
  45.     public String readConverterExp() default "";
  46.  
  47.     /**
  48.      * 分隔符，读取字符串组内容
  49.      */
  50.     public String separator() default ",";
  51.  
  52.     /**
  53.      * BigDecimal 精度 默认:-1(默认不开启BigDecimal格式化)
  54.      */
  55.     public int scale() default -1;
  56.  
  57.     /**
  58.      * BigDecimal 舍入规则 默认:BigDecimal.ROUND_HALF_EVEN
  59.      */
  60.     public int roundingMode() default BigDecimal.ROUND_HALF_EVEN;
  61.  
  62.     /**
  63.      * 导出时在excel中每个列的高度 单位为字符
  64.      */
  65.     public double height() default 14;
  66.  
  67.     /**
  68.      * 导出时在excel中每个列的宽 单位为字符
  69.      */
  70.     public double width() default 16;
  71.  
  72.     /**
  73.      * 文字后缀,如% 90 变成90%
  74.      */
  75.     public String suffix() default "";
  76.  
  77.     /**
  78.      * 当值为空时,字段的默认值
  79.      */
  80.     public String defaultValue() default "";
  81.  
  82.     /**
  83.      * 提示信息
  84.      */
  85.     public String prompt() default "";
  86.  
  87.     /**
  88.      * 设置只能选择不能输入的列内容.
  89.      */
  90.     public String[] combo() default {};
  91.  
  92.     /**
  93.      * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
  94.      */
  95.     public boolean isExport() default true;
  96.  
  97.     /**
  98.      * 另一个类中的属性名称,支持多级获取,以小数点隔开
  99.      */
  100.     public String targetAttr() default "";
  101.  
  102.     public String mergeLine() default "";
  103.  
  104.     /**
  105.      * 是否自动统计数据,在最后追加一行统计数据总和
  106.      */
  107.     public boolean isStatistics() default false;
  108.  
  109.     /**
  110.      * 导出类型（0数字 1字符串）
  111.      */
  112.     public ColumnType cellType() default ColumnType.STRING;
  113.  
  114.     /**
  115.      * 导出字体颜色
  116.      */
  117.     public IndexedColors color() default IndexedColors.BLACK;
  118.  
  119.     /**
  120.      * 导出字段对齐方式
  121.      */
  122.     public HorizontalAlignment align() default HorizontalAlignment.CENTER;
  123.  
  124.     /**
  125.      * 自定义数据处理器
  126.      */
  127.     public Class<?> handler() default ExcelHandlerAdapter.class;
  128.  
  129.     /**
  130.      * 自定义数据处理器参数
  131.      */
  132.     public String[] args() default {};
  133.  
  134.     public enum Align
  135.     {
  136.         AUTO(0), LEFT(1), CENTER(2), RIGHT(3);
  137.         private final int value;
  138.  
  139.         Align(int value)
  140.         {
  141.             this.value = value;
  142.         }
  143.  
  144.         public int value()
  145.         {
  146.             return this.value;
  147.         }
  148.     }
  149.  
  150.     /**
  151.      * 字段类型（0：导出导入；1：仅导出；2：仅导入）
  152.      */
  153.     Type type() default Type.ALL;
  154.  
  155.     public enum Type
  156.     {
  157.         ALL(0), EXPORT(1), IMPORT(2);
  158.         private final int value;
  159.  
  160.         Type(int value)
  161.         {
  162.             this.value = value;
  163.         }
  164.  
  165.         public int value()
  166.         {
  167.             return this.value;
  168.         }
  169.     }
  170.  
  171.     public enum ColumnType
  172.     {
  173.         NUMERIC(0), STRING(1), IMAGE(2);
  174.         private final int value;
  175.  
  176.         ColumnType(int value)
  177.         {
  178.             this.value = value;
  179.         }
  180.  
  181.         public int value()
  182.         {
  183.             return this.value;
  184.         }
  185.     }
  186. }

二 ：根据下载地址下载excel

  1. @GetMapping("/download")
  2.     public void fileDownload(String fileName, String excelName, HttpServletResponse response, HttpServletRequest request) {
  3.         try {
  4.             if (!FileUtils.checkAllowDownload(fileName)) {
  5.                 throw new Exception(StringUtils.format("文件名称({})非法，不允许下载。 ", fileName));
  6.             }
  7.             String realFileName = excelName + fileName.substring(fileName.indexOf("_"));
  8.             String filePath = RuoYiConfig.getDownloadPath() + fileName;
  9.             response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
  10.             // 下载文件名重新编码
  11.             FileUtils.setAttachmentResponseHeader(response, realFileName);
  12.             FileUtils.writeBytes(filePath, response.getOutputStream());
  13.         } catch (Exception e) {
  14.             e.printStackTrace();
  15.         }
  16.     }

最后

给你附上exportExcel所在的工具类，具体看相关代码，整个工具类复制就可以用了

  1. import com.gyjn.common.annotation.Excel;
  2. import com.gyjn.common.annotation.Excel.ColumnType;
  3. import com.gyjn.common.annotation.Excel.Type;
  4. import com.gyjn.common.annotation.Excels;
  5. import com.gyjn.common.annotation.Excelyw;
  6. import com.gyjn.common.annotation.Excelyws;
  7. import com.gyjn.common.config.RuoYiConfig;
  8. import com.gyjn.common.core.domain.AjaxResult;
  9. import com.gyjn.common.core.text.Convert;
  10. import com.gyjn.common.exception.UtilException;
  11. import com.gyjn.common.utils.DateUtils;
  12. import com.gyjn.common.utils.DictUtils;
  13. import com.gyjn.common.utils.StringUtils;
  14. import com.gyjn.common.utils.file.FileTypeUtils;
  15. import com.gyjn.common.utils.file.FileUtils;
  16. import com.gyjn.common.utils.file.ImageUtils;
  17. import com.gyjn.common.utils.reflect.ReflectUtils;
  18. import org.apache.commons.lang3.RegExUtils;
  19. import org.apache.poi.hssf.usermodel.*;
  20. import org.apache.poi.ooxml.POIXMLDocumentPart;
  21. import org.apache.poi.ss.usermodel.*;
  22. import org.apache.poi.ss.util.CellRangeAddress;
  23. import org.apache.poi.ss.util.CellRangeAddressList;
  24. import org.apache.poi.util.IOUtils;
  25. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  26. import org.apache.poi.xssf.usermodel.*;
  27. import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
  28. import org.slf4j.Logger;
  29. import org.slf4j.LoggerFactory;
  30.  
  31. import javax.servlet.http.HttpServletResponse;
  32. import java.io.*;
  33. import java.lang.reflect.Field;
  34. import java.lang.reflect.Method;
  35. import java.math.BigDecimal;
  36. import java.text.DecimalFormat;
  37. import java.time.LocalDate;
  38. import java.time.LocalDateTime;
  39. import java.util.*;
  40. import java.util.stream.Collectors;
  41.  
  42. /**
  43.  * Excel相关处理
  44.  *
  45.  * @author cyf
  46.  */
  47. public class ExcelUtil<T>
  48. {
  49.     private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
  50.  
  51.     public static final String FORMULA_REGEX_STR = "=|-|\\+|@";
  52.  
  53.     public static final String[] FORMULA_STR = { "=", "-", "+", "@" };
  54.  
  55.     /**
  56.      * Excel sheet最大行数，默认65536
  57.      */
  58.     public static final int sheetSize = 65536;
  59.  
  60.     /**
  61.      * 工作表名称
  62.      */
  63.     private String sheetName;
  64.     private String sheetName2;
  65.  
  66.     /**
  67.      * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
  68.      */
  69.     private Type type;
  70.     private Excelyw.Type type2;
  71.  
  72.     /**
  73.      * 工作薄对象
  74.      */
  75.     private Workbook wb;
  76.     private Workbook wb2;
  77.  
  78.     /**
  79.      * 工作表对象
  80.      */
  81.     private Sheet sheet;
  82.     private Sheet sheet2;
  83.  
  84.     /**
  85.      * 样式列表
  86.      */
  87.     private Map<String, CellStyle> styles;
  88.     private Map<String, CellStyle> styles2;
  89.  
  90.     /**
  91.      * 导入导出数据列表
  92.      */
  93.     private List<T> list;
  94.     private List<T> listyw;
  95.  
  96.     /**
  97.      * 注解列表
  98.      */
  99.     private List<Object[]> fields;
  100.     private List<Object[]> fields2;
  101.  
  102.     /**
  103.      * 当前行号
  104.      */
  105.     private int rownum;
  106.  
  107.     /**
  108.      * 标题
  109.      */
  110.     private String title;
  111.     private String title2;
  112.  
  113.     /**
  114.      * 最大高度
  115.      */
  116.     private short maxHeight;
  117.     private short maxHeight2;
  118.  
  119.     /**
  120.      * 统计列表
  121.      */
  122.     private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
  123.     private Map<Integer, Double> statistics2 = new HashMap<Integer, Double>();
  124.  
  125.     /**
  126.      * 数字格式
  127.      */
  128.     private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
  129.  
  130.     /**
  131.      * 实体对象
  132.      */
  133.     public Class<T> clazz;
  134.     private boolean heBing=true;
  135.     public ExcelUtilMerge(Class<T> clazz)
  136.     {
  137.         this.clazz = clazz;
  138.     }
  139.  
  140.     public void init(List<T> list,List<T> listyw, String sheetName, String sheetName2, String title, String title2, Type type, Excelyw.Type type2)
  141.     {
  142.         if (list == null)
  143.         {
  144.             list = new ArrayList<T>();
  145.         }
  146.         this.list = list;
  147.         this.listyw = listyw;
  148.         this.sheetName = sheetName;
  149.         this.sheetName2 = sheetName2;
  150.         this.type = type;
  151.         this.type2 = type2;
  152.         this.title = title;
  153.         this.title2 = title2;
  154.         createExcelField();
  155.         createExcelField2();
  156.         createWorkbook();
  157.         createTitle();
  158.         createTitle2();
  159.     }
  160.  
  161.     /**
  162.      * 创建excel第一行标题
  163.      */
  164.     public void createTitle()
  165.     {
  166.         if (StringUtils.isNotEmpty(title))
  167.         {
  168.             Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
  169.             titleRow.setHeightInPoints(30);
  170.             Cell titleCell = titleRow.createCell(0);
  171.             titleCell.setCellStyle(styles.get("title"));
  172.             titleCell.setCellValue(title);
  173.             sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
  174.                     this.fields.size() - 1));
  175.         }
  176.     }
  177.     public void createTitle2()
  178.     {
  179.         if (StringUtils.isNotEmpty(title2))
  180.         {
  181.             Row titleRow = sheet2.createRow(rownum == 0 ? rownum++ : 0);
  182.             titleRow.setHeightInPoints(30);
  183.             Cell titleCell = titleRow.createCell(0);
  184.             titleCell.setCellStyle(styles2.get("title"));
  185.             titleCell.setCellValue(title2);
  186.             sheet2.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
  187.                     this.fields2.size() - 1));
  188.         }
  189.     }
  190.     /**
  191.      * 对excel表单默认第一个索引名转换成list
  192.      *
  193.      * @param is 输入流
  194.      * @return 转换后集合
  195.      */
  196.     public List<T> importExcel(InputStream is) throws Exception
  197.     {
  198.         return importExcel(is, 0);
  199.     }
  200.  
  201.     /**
  202.      * 对excel表单默认第一个索引名转换成list
  203.      *
  204.      * @param is 输入流
  205.      * @param titleNum 标题占用行数
  206.      * @return 转换后集合
  207.      */
  208.     public List<T> importExcel(InputStream is, int titleNum) throws Exception
  209.     {
  210.         return importExcel(StringUtils.EMPTY, is, titleNum);
  211.     }
  212.  
  213.     /**
  214.      * 对excel表单指定表格索引名转换成list
  215.      *
  216.      * @param sheetName 表格索引名
  217.      * @param titleNum 标题占用行数
  218.      * @param is 输入流
  219.      * @return 转换后集合
  220.      */
  221.     public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception
  222.     {
  223.         this.type = Type.IMPORT;
  224.         this.wb = WorkbookFactory.create(is);
  225.         List<T> list = new ArrayList<T>();
  226.         // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
  227.         Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
  228.         if (sheet == null)
  229.         {
  230.             throw new IOException("文件sheet不存在");
  231.         }
  232.         boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
  233.         Map<String, PictureData> pictures;
  234.         if (isXSSFWorkbook)
  235.         {
  236.             pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
  237.         }
  238.         else
  239.         {
  240.             pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
  241.         }
  242.         // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
  243.         int rows = sheet.getLastRowNum();
  244.  
  245.         if (rows > 0)
  246.         {
  247.             // 定义一个map用于存放excel列的序号和field.
  248.             Map<String, Integer> cellMap = new HashMap<String, Integer>();
  249.             // 获取表头
  250.             Row heard = sheet.getRow(titleNum);
  251.             for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
  252.             {
  253.                 Cell cell = heard.getCell(i);
  254.                 if (StringUtils.isNotNull(cell))
  255.                 {
  256.                     String value = this.getCellValue(heard, i).toString();
  257.                     cellMap.put(value, i);
  258.                 }
  259.                 else
  260.                 {
  261.                     cellMap.put(null, i);
  262.                 }
  263.             }
  264.             // 有数据时才处理 得到类的所有field.
  265.             List<Object[]> fields = this.getFields();
  266.             Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
  267.             for (Object[] objects : fields)
  268.             {
  269.                 Excel attr = (Excel) objects[1];
  270.                 Integer column = cellMap.get(attr.name());
  271.                 if (column != null)
  272.                 {
  273.                     fieldsMap.put(column, objects);
  274.                 }
  275.             }
  276.             for (int i = titleNum + 1; i <= rows; i++)
  277.             {
  278.                 // 从第2行开始取数据,默认第一行是表头.
  279.                 Row row = sheet.getRow(i);
  280.                 // 判断当前行是否是空行
  281.                 if (isRowEmpty(row))
  282.                 {
  283.                     continue;
  284.                 }
  285.                 T entity = null;
  286.                 for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet())
  287.                 {
  288.                     Object val = this.getCellValue(row, entry.getKey());
  289.  
  290.                     // 如果不存在实例则新建.
  291.                     entity = (entity == null ? clazz.newInstance() : entity);
  292.                     // 从map中得到对应列的field.
  293.                     Field field = (Field) entry.getValue()[0];
  294.                     Excel attr = (Excel) entry.getValue()[1];
  295.                     // 取得类型,并根据对象类型设置值.
  296.                     Class<?> fieldType = field.getType();
  297.                     if (String.class == fieldType)
  298.                     {
  299.                         String s = Convert.toStr(val);
  300.                         if (StringUtils.endsWith(s, ".0"))
  301.                         {
  302.                             val = StringUtils.substringBefore(s, ".0");
  303.                         }
  304.                         else
  305.                         {
  306.                             String dateFormat = field.getAnnotation(Excel.class).dateFormat();
  307.                             if (StringUtils.isNotEmpty(dateFormat))
  308.                             {
  309.                                 val = parseDateToStr(dateFormat, val);
  310.                             }
  311.                             else
  312.                             {
  313.                                 val = Convert.toStr(val);
  314.                             }
  315.                         }
  316.                     }
  317.                     else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
  318.                     {
  319.                         val = Convert.toInt(val);
  320.                     }
  321.                     else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
  322.                     {
  323.                         val = Convert.toLong(val);
  324.                     }
  325.                     else if (Double.TYPE == fieldType || Double.class == fieldType)
  326.                     {
  327.                         val = Convert.toDouble(val);
  328.                     }
  329.                     else if (Float.TYPE == fieldType || Float.class == fieldType)
  330.                     {
  331.                         val = Convert.toFloat(val);
  332.                     }
  333.                     else if (BigDecimal.class == fieldType)
  334.                     {
  335.                         val = Convert.toBigDecimal(val);
  336.                     }
  337.                     else if (Date.class == fieldType)
  338.                     {
  339.                         if (val instanceof String)
  340.                         {
  341.                             val = DateUtils.parseDate(val);
  342.                         }
  343.                         else if (val instanceof Double)
  344.                         {
  345.                             val = DateUtil.getJavaDate((Double) val);
  346.                         }
  347.                     }
  348.                     else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)
  349.                     {
  350.                         val = Convert.toBool(val, false);
  351.                     }
  352.                     if (StringUtils.isNotNull(fieldType))
  353.                     {
  354.                         String propertyName = field.getName();
  355.                         if (StringUtils.isNotEmpty(attr.targetAttr()))
  356.                         {
  357.                             propertyName = field.getName() + "." + attr.targetAttr();
  358.                         }
  359.                         else if (StringUtils.isNotEmpty(attr.readConverterExp()))
  360.                         {
  361.                             val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
  362.                         }
  363.                         else if (StringUtils.isNotEmpty(attr.dictType()))
  364.                         {
  365.                             val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
  366.                         }
  367.                         else if (!attr.handler().equals(ExcelHandlerAdapter.class))
  368.                         {
  369.                             val = dataFormatHandlerAdapter(val, attr);
  370.                         }
  371.                         else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))
  372.                         {
  373.                             PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
  374.                             if (image == null)
  375.                             {
  376.                                 val = "";
  377.                             }
  378.                             else
  379.                             {
  380.                                 byte[] data = image.getData();
  381.                                 val = FileUtils.writeImportBytes(data);
  382.                             }
  383.                         }
  384.                         ReflectUtils.invokeSetter(entity, propertyName, val);
  385.                     }
  386.                 }
  387.                 list.add(entity);
  388.             }
  389.         }
  390.         return list;
  391.     }
  392.  
  393.     /**
  394.      * 对list数据源将其里面的数据导入到excel表单
  395.      *
  396.      * @param list 导出数据集合
  397.      * @param sheetName 工作表的名称
  398.      * @return 结果
  399.      */
  400.     public AjaxResult exportExcel(List<T> list, String sheetName,String sheetName2)
  401.     {
  402.         return exportExcel(list, sheetName,sheetName2);
  403.     }
  404.  
  405.     /**
  406.      * 对list数据源将其里面的数据导入到excel表单
  407.      *
  408.      * @param list 导出数据集合
  409.      * @param sheetName 工作表的名称
  410.      * @param title 标题
  411.      * @return 结果
  412.      */
  413.     public AjaxResult exportExcel(List<T> list,List<T> listyw, String sheetName,String sheetName2, String title,String title2)
  414.     {
  415.         this.init(list,listyw, sheetName,sheetName2, title, title2,Type.EXPORT, Excelyw.Type.EXPORT);
  416.         return exportExcel();
  417.     }
  418.  
  419.     /**
  420.      * 对list数据源将其里面的数据导入到excel表单
  421.      *
  422.      * @param response 返回数据
  423.      * @param list 导出数据集合
  424.      * @param sheetName 工作表的名称
  425.      * @return 结果
  426.      */
  427.     public void exportExcel(HttpServletResponse response, List<T> list, String sheetName,String sheetName2)
  428.     {
  429.         exportExcel(response, list, sheetName, sheetName2);
  430.     }
  431.  
  432.     /**
  433.      * 对list数据源将其里面的数据导入到excel表单
  434.      *
  435.      * @param response 返回数据
  436.      * @param list 导出数据集合
  437.      * @param sheetName 工作表的名称
  438.      * @param title 标题
  439.      * @return 结果
  440.      */
  441.     public void exportExcel(HttpServletResponse response,
  442.                             List<T> list, List<T> listyw,String sheetName,String sheetName2, String title, String title2)
  443.     {
  444.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  445.         response.setCharacterEncoding("utf-8");
  446.         this.init(list,listyw, sheetName,sheetName2, title,title2, Type.EXPORT, Excelyw.Type.EXPORT);
  447.         exportExcel(response);
  448.     }
  449.  
  450.     /**
  451.      * 对list数据源将其里面的数据导入到excel表单
  452.      *
  453.      * @param sheetName 工作表的名称
  454.      * @return 结果
  455.      */
  456.     public AjaxResult importTemplateExcel(String sheetName,String sheetName2)
  457.     {
  458.         return importTemplateExcel(sheetName, sheetName2);
  459.     }
  460.  
  461.     /**
  462.      * 对list数据源将其里面的数据导入到excel表单
  463.      *
  464.      * @param sheetName 工作表的名称
  465.      * @param title 标题
  466.      * @return 结果
  467.      */
  468.     public AjaxResult importTemplateExcel(String sheetName, String sheetName2,String title)
  469.     {
  470.         this.init(null,null, sheetName,sheetName2, title,title2, Type.IMPORT, Excelyw.Type.EXPORT);
  471.         return exportExcel();
  472.     }
  473.  
  474.     /**
  475.      * 对list数据源将其里面的数据导入到excel表单
  476.      *
  477.      * @param sheetName 工作表的名称
  478.      * @return 结果
  479.      */
  480.     public void importTemplateExcel(HttpServletResponse response,
  481.                                     String sheetName,String sheetName2)
  482.     {
  483.         importTemplateExcel(response, sheetName,sheetName2);
  484.     }
  485.  
  486.     /**
  487.      * 对list数据源将其里面的数据导入到excel表单
  488.      *
  489.      * @param sheetName 工作表的名称
  490.      * @param title 标题
  491.      * @return 结果
  492.      */
  493.     public void importTemplateExcel(HttpServletResponse response,
  494.                                     String sheetName,String sheetName2, String title, String title2)
  495.     {
  496.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  497.         response.setCharacterEncoding("utf-8");
  498.         this.init(null,null, sheetName, sheetName2,title,title2, Type.IMPORT, Excelyw.Type.EXPORT);
  499.         exportExcel(response);
  500.     }
  501.  
  502.     /**
  503.      * 对list数据源将其里面的数据导入到excel表单
  504.      *
  505.      * @return 结果
  506.      */
  507.     public void exportExcel(HttpServletResponse response)
  508.     {
  509.         try
  510.         {
  511.             writeSheet();
  512.             wb.write(response.getOutputStream());
  513.         }
  514.         catch (Exception e)
  515.         {
  516.             log.error("导出Excel异常{}", e.getMessage());
  517.         }
  518.         finally
  519.         {
  520.             IOUtils.closeQuietly(wb);
  521.         }
  522.     }
  523.  
  524.     /**
  525.      * 对list数据源将其里面的数据导入到excel表单
  526.      *
  527.      * @return 结果
  528.      */
  529.     public AjaxResult exportExcel()
  530.     {
  531.         OutputStream out = null;
  532.         try
  533.         {
  534.             writeSheet();
  535.             String filename = encodingFilename(sheetName);
  536.             out = new FileOutputStream(getAbsoluteFile(filename));
  537.             wb.write(out);
  538.             return AjaxResult.success(filename);
  539.         }
  540.         catch (Exception e)
  541.         {
  542.             log.error("导出Excel异常{}", e.getMessage());
  543.             throw new UtilException("导出Excel失败，请联系网站管理员！");
  544.         }
  545.         finally
  546.         {
  547.             IOUtils.closeQuietly(wb);
  548.             IOUtils.closeQuietly(out);
  549.         }
  550.     }
  551.  
  552.     /**
  553.      * 创建写入数据到Sheet
  554.      */
  555.     public void writeSheet()
  556.     {
  557.         // 取出一共有多少个sheet.
  558.         int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));
  559.         for (int index = 0; index < sheetNo; index++)
  560.         {
  561.             createSheet(sheetNo, index);
  562.             // 产生一行
  563.             Row row = sheet.createRow(rownum);
  564.             int column = 0;
  565.             // 写入各个字段的列头名称
  566.             for (Object[] os : fields)
  567.             {
  568.                 Excel excel = (Excel) os[1];
  569.                 this.createCell(excel, row, column++);
  570.             }
  571.             if (Type.EXPORT.equals(type))
  572.             {
  573.                 fillExcelData(index, row);
  574.                 addStatisticsRow();
  575.             }
  576.         }
  577.         int sheetNo2 = Math.max(1, (int) Math.ceil(listyw.size() * 1.0 / sheetSize));
  578.         for (int index = 0; index < sheetNo2; index++)
  579.         {
  580.             createSheet(sheetNo2, index);
  581.  
  582.             // 产生一行
  583.             Row row2 = sheet2.createRow(rownum);
  584.             int column = 0;
  585.             // 写入各个字段的列头名称
  586.             for (Object[] os : fields2)
  587.             {
  588.                 Excelyw excel = (Excelyw) os[1];
  589.                 this.createCell2(excel, row2, column++);
  590.             }
  591.             if (Excelyw.Type.EXPORT.equals(type2))
  592.             {
  593.                 fillExcelData2(index, row2);
  594.                 addStatisticsRow2();
  595.             }
  596.         }
  597.     }
  598.  
  599.     /**
  600.      * 填充excel数据
  601.      *
  602.      * @param index 序号
  603.      * @param row 单元格行
  604.      */
  605.     public void fillExcelData(int index, Row row)
  606.     {
  607.         int startNo = index * sheetSize;
  608.         int endNo = Math.min(startNo + sheetSize, list.size());
  609.         for (int i = startNo; i < endNo; i++)
  610.         {
  611.             row = sheet.createRow(i + 1 + rownum - startNo);
  612.             // 得到导出对象.
  613.             T vo = (T) list.get(i);
  614.             int column = 0;
  615.             for (Object[] os : fields)
  616.             {
  617.                 Field field = (Field) os[0];
  618.                 Excel excel = (Excel) os[1];
  619.                 this.addCell(excel, row, vo, field, column++);
  620.             }
  621.         }
  622.     }
  623.     public void fillExcelData2(int index, Row row2)
  624.     {
  625.         int startNo = index * sheetSize;
  626.         int endNo = Math.min(startNo + sheetSize, listyw.size());
  627.         for (int i = startNo; i < endNo; i++)
  628.         {
  629.             row2 = sheet2.createRow(i + 1 + rownum - startNo);
  630.             // 得到导出对象.
  631.             T vo = (T) listyw.get(i);
  632.             int column = 0;
  633.             for (Object[] os : fields2)
  634.             {
  635.                 Field field = (Field) os[0];
  636.                 Excelyw excel = (Excelyw) os[1];
  637.                 this.addCell2(excel, row2, vo, field, column++);
  638.             }
  639.         }
  640.     }
  641.     /**
  642.      * 创建表格样式
  643.      *
  644.      * @param wb 工作薄对象
  645.      * @return 样式列表
  646.      */
  647.     private Map<String, CellStyle> createStyles(Workbook wb)
  648.     {
  649.         // 写入各条记录,每条记录对应excel表中的一行
  650.         Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  651.         CellStyle style = wb.createCellStyle();
  652.         style.setWrapText(true);
  653.         style.setAlignment(HorizontalAlignment.CENTER);
  654.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  655.         Font titleFont = wb.createFont();
  656.         titleFont.setFontName("Arial");
  657.         titleFont.setFontHeightInPoints((short) 16);
  658.         titleFont.setBold(true);
  659.         style.setFont(titleFont);
  660.         styles.put("title", style);
  661.  
  662.         style = wb.createCellStyle();
  663.         style.setWrapText(true);
  664.         style.setAlignment(HorizontalAlignment.CENTER);
  665.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  666.         style.setBorderRight(BorderStyle.THIN);
  667.         style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  668.         style.setBorderLeft(BorderStyle.THIN);
  669.         style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  670.         style.setBorderTop(BorderStyle.THIN);
  671.         style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  672.         style.setBorderBottom(BorderStyle.THIN);
  673.         style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  674.         Font dataFont = wb.createFont();
  675.         dataFont.setFontName("Arial");
  676.         dataFont.setFontHeightInPoints((short) 11);
  677.         style.setFont(dataFont);
  678.         styles.put("data", style);
  679.  
  680.         style = wb.createCellStyle();
  681.         style.setWrapText(true);
  682.         style.cloneStyleFrom(styles.get("data"));
  683.         style.setAlignment(HorizontalAlignment.CENTER);
  684.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  685.         style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  686.         style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  687.         Font headerFont = wb.createFont();
  688.         headerFont.setFontName("Arial");
  689.         headerFont.setFontHeightInPoints((short) 11);
  690.         headerFont.setBold(true);
  691.         headerFont.setColor(IndexedColors.BLACK.getIndex());
  692.         style.setFont(headerFont);
  693.         styles.put("header", style);
  694.  
  695.         style = wb.createCellStyle();
  696.         style.setWrapText(true);
  697.         style.setAlignment(HorizontalAlignment.CENTER);
  698.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  699.         Font totalFont = wb.createFont();
  700.         totalFont.setFontName("Arial");
  701.         totalFont.setFontHeightInPoints((short) 11);
  702.         style.setFont(totalFont);
  703.         styles.put("total", style);
  704.  
  705.         styles.putAll(annotationStyles(wb));
  706.  
  707.         return styles;
  708.     }
  709.     private Map<String, CellStyle> createStyles2(Workbook wb)
  710.     {
  711.         // 写入各条记录,每条记录对应excel表中的一行
  712.         Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  713.         CellStyle style = wb.createCellStyle();
  714.         style.setWrapText(true);
  715.         style.setAlignment(HorizontalAlignment.CENTER);
  716.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  717.         Font titleFont = wb.createFont();
  718.         titleFont.setFontName("Arial");
  719.         titleFont.setFontHeightInPoints((short) 16);
  720.         titleFont.setBold(true);
  721.         style.setFont(titleFont);
  722.         styles.put("title", style);
  723.  
  724.         style = wb.createCellStyle();
  725.         style.setWrapText(true);
  726.         style.setAlignment(HorizontalAlignment.CENTER);
  727.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  728.         style.setBorderRight(BorderStyle.THIN);
  729.         style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  730.         style.setBorderLeft(BorderStyle.THIN);
  731.         style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  732.         style.setBorderTop(BorderStyle.THIN);
  733.         style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  734.         style.setBorderBottom(BorderStyle.THIN);
  735.         style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  736.         Font dataFont = wb.createFont();
  737.         dataFont.setFontName("Arial");
  738.         dataFont.setFontHeightInPoints((short) 11);
  739.         style.setFont(dataFont);
  740.         styles.put("data", style);
  741.  
  742.         style = wb.createCellStyle();
  743.         style.setWrapText(true);
  744.         style.cloneStyleFrom(styles.get("data"));
  745.         style.setAlignment(HorizontalAlignment.CENTER);
  746.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  747.         style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  748.         style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  749.         Font headerFont = wb.createFont();
  750.         headerFont.setFontName("Arial");
  751.         headerFont.setFontHeightInPoints((short) 11);
  752.         headerFont.setBold(true);
  753.         headerFont.setColor(IndexedColors.BLACK.getIndex());
  754.         style.setFont(headerFont);
  755.         styles.put("header", style);
  756.  
  757.         style = wb.createCellStyle();
  758.         style.setWrapText(true);
  759.         style.setAlignment(HorizontalAlignment.CENTER);
  760.         style.setVerticalAlignment(VerticalAlignment.CENTER);
  761.         Font totalFont = wb.createFont();
  762.         totalFont.setFontName("Arial");
  763.         totalFont.setFontHeightInPoints((short) 11);
  764.         style.setFont(totalFont);
  765.         styles.put("total", style);
  766.  
  767.         styles.putAll(annotationStyles2(wb));
  768.  
  769.         return styles;
  770.     }
  771.     /**
  772.      * 根据Excel注解创建表格样式
  773.      *
  774.      * @param wb 工作薄对象
  775.      * @return 自定义样式列表
  776.      */
  777.     private Map<String, CellStyle> annotationStyles(Workbook wb)
  778.     {
  779.         Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  780.         for (Object[] os : fields)
  781.         {
  782.             Excel excel = (Excel) os[1];
  783.             String key = "data_" + excel.align() + "_" + excel.color();
  784.             if (!styles.containsKey(key))
  785.             {
  786.                 CellStyle style = wb.createCellStyle();
  787.                 style = wb.createCellStyle();
  788.                 style.setWrapText(true);
  789.                 style.setAlignment(excel.align());
  790.                 style.setVerticalAlignment(VerticalAlignment.CENTER);
  791.                 style.setBorderRight(BorderStyle.THIN);
  792.                 style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  793.                 style.setBorderLeft(BorderStyle.THIN);
  794.                 style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  795.                 style.setBorderTop(BorderStyle.THIN);
  796.                 style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  797.                 style.setBorderBottom(BorderStyle.THIN);
  798.                 style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  799.                 Font dataFont = wb.createFont();
  800.                 dataFont.setFontName("Arial");
  801.                 dataFont.setFontHeightInPoints((short) 11);
  802.                 dataFont.setColor(excel.color().index);
  803.                 style.setFont(dataFont);
  804.                 styles.put(key, style);
  805.             }
  806.         }
  807.         return styles;
  808.     }
  809.     private Map<String, CellStyle> annotationStyles2(Workbook wb)
  810.     {
  811.         Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  812.         for (Object[] os : fields2)
  813.         {
  814.             Excelyw excel = (Excelyw) os[1];
  815.             String key = "data_" + excel.align() + "_" + excel.color();
  816.             if (!styles.containsKey(key))
  817.             {
  818.                 CellStyle style = wb.createCellStyle();
  819.                 style = wb.createCellStyle();
  820.                 style.setWrapText(true);
  821.                 style.setAlignment(excel.align());
  822.                 style.setVerticalAlignment(VerticalAlignment.CENTER);
  823.                 style.setBorderRight(BorderStyle.THIN);
  824.                 style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  825.                 style.setBorderLeft(BorderStyle.THIN);
  826.                 style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  827.                 style.setBorderTop(BorderStyle.THIN);
  828.                 style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  829.                 style.setBorderBottom(BorderStyle.THIN);
  830.                 style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  831.                 Font dataFont = wb.createFont();
  832.                 dataFont.setFontName("Arial");
  833.                 dataFont.setFontHeightInPoints((short) 11);
  834.                 dataFont.setColor(excel.color().index);
  835.                 style.setFont(dataFont);
  836.                 styles.put(key, style);
  837.             }
  838.         }
  839.         return styles;
  840.     }
  841.     /**
  842.      * 创建单元格
  843.      */
  844.     public Cell createCell(Excel attr, Row row, int column)
  845.     {
  846.         String key = "data_" + attr.align() + "_" + attr.color();
  847.         // 创建列
  848.         Cell cell = row.createCell(column);
  849.         row.setHeightInPoints(30);
  850.         // 写入列信息
  851.         cell.setCellValue(attr.name());
  852.         setDataValidation(attr, row, column);
  853.         cell.setCellStyle(styles.get("header"));
  854.         return cell;
  855.     }
  856.     public Cell createCell2(Excelyw attr, Row row2, int column)
  857.     {
  858.         // 创建列
  859.         Cell cell2 = row2.createCell(column);
  860.         row2.setHeightInPoints(30);
  861.         // 写入列信息
  862.         cell2.setCellValue(attr.name());
  863.         setDataValidation2(attr, row2, column);
  864.         cell2.setCellStyle(styles2.get("header"));
  865.         return cell2;
  866.     }
  867.     /**
  868.      * 设置单元格信息
  869.      *
  870.      * @param value 单元格值
  871.      * @param attr 注解相关
  872.      * @param cell 单元格信息
  873.      */
  874.     public void setCellVo(Object value, Excel attr, Cell cell)
  875.     {
  876.         if (ColumnType.STRING == attr.cellType())
  877.         {
  878.             String cellValue = Convert.toStr(value);
  879.             // 对于任何以表达式触发字符 =-+@开头的单元格，直接使用tab字符作为前缀，防止CSV注入。
  880.             if (StringUtils.startsWithAny(cellValue, FORMULA_STR))
  881.             {
  882.                 cellValue = RegExUtils.replaceFirst(cellValue, FORMULA_REGEX_STR, "\t$0");
  883.             }
  884.             cell.setCellValue(StringUtils.isNull(cellValue) ? attr.defaultValue() : cellValue + attr.suffix());
  885.         }
  886.         else if (ColumnType.NUMERIC == attr.cellType())
  887.         {
  888.             if (StringUtils.isNotNull(value))
  889.             {
  890.                 cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
  891.             }
  892.         }
  893.         else if (ColumnType.IMAGE == attr.cellType())
  894.         {
  895.             ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
  896.             String imagePath = Convert.toStr(value);
  897.             if (StringUtils.isNotEmpty(imagePath))
  898.             {
  899.                 byte[] data = ImageUtils.getImage(imagePath);
  900.                 getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
  901.                         cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
  902.             }
  903.         }
  904.     }
  905.     public void setCellVo2(Object value, Excelyw attr, Cell cell)
  906.     {
  907.         if (Excelyw.ColumnType.STRING == attr.cellType())
  908.         {
  909.             String cellValue = Convert.toStr(value);
  910.             // 对于任何以表达式触发字符 =-+@开头的单元格，直接使用tab字符作为前缀，防止CSV注入。
  911.             if (StringUtils.startsWithAny(cellValue, FORMULA_STR))
  912.             {
  913.                 cellValue = RegExUtils.replaceFirst(cellValue, FORMULA_REGEX_STR, "\t$0");
  914.             }
  915.             cell.setCellValue(StringUtils.isNull(cellValue) ? attr.defaultValue() : cellValue + attr.suffix());
  916.         }
  917.         else if (Excelyw.ColumnType.NUMERIC == attr.cellType())
  918.         {
  919.             if (StringUtils.isNotNull(value))
  920.             {
  921.                 cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
  922.             }
  923.         }
  924.         else if (Excelyw.ColumnType.IMAGE == attr.cellType())
  925.         {
  926.             ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
  927.             String imagePath = Convert.toStr(value);
  928.             if (StringUtils.isNotEmpty(imagePath))
  929.             {
  930.                 byte[] data = ImageUtils.getImage(imagePath);
  931.                 getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
  932.                         cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
  933.             }
  934.         }
  935.     }
  936.     /**
  937.      * 获取画布
  938.      */
  939.     public static Drawing<?> getDrawingPatriarch(Sheet sheet)
  940.     {
  941.         if (sheet.getDrawingPatriarch() == null)
  942.         {
  943.             sheet.createDrawingPatriarch();
  944.         }
  945.         return sheet.getDrawingPatriarch();
  946.     }
  947.  
  948.     /**
  949.      * 获取图片类型,设置图片插入类型
  950.      */
  951.     public int getImageType(byte[] value)
  952.     {
  953.         String type = FileTypeUtils.getFileExtendName(value);
  954.         if ("JPG".equalsIgnoreCase(type))
  955.         {
  956.             return Workbook.PICTURE_TYPE_JPEG;
  957.         }
  958.         else if ("PNG".equalsIgnoreCase(type))
  959.         {
  960.             return Workbook.PICTURE_TYPE_PNG;
  961.         }
  962.         return Workbook.PICTURE_TYPE_JPEG;
  963.     }
  964.  
  965.     /**
  966.      * 创建表格样式
  967.      */
  968.     public void setDataValidation(Excel attr, Row row, int column)
  969.     {
  970.         if (attr.name().indexOf("注：") >= 0)
  971.         {
  972.             sheet.setColumnWidth(column, 6000);
  973.         }
  974.         else
  975.         {
  976.             // 设置列宽
  977.             sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
  978.         }
  979.         if (StringUtils.isNotEmpty(attr.prompt()) || attr.combo().length > 0)
  980.         {
  981.             // 提示信息或只能选择不能输入的列内容.
  982.             setPromptOrValidation(sheet, attr.combo(), attr.prompt(), 1, 100, column, column);
  983.         }
  984.     }
  985.     public void setDataValidation2(Excelyw attr, Row row2, int column)
  986.     {
  987.         if (attr.name().indexOf("注：") >= 0)
  988.         {
  989.             sheet2.setColumnWidth(column, 6000);
  990.         }
  991.         else
  992.         {
  993.             // 设置列宽
  994.             sheet2.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
  995.         }
  996.         if (StringUtils.isNotEmpty(attr.prompt()) || attr.combo().length > 0)
  997.         {
  998.             // 提示信息或只能选择不能输入的列内容.
  999.             setPromptOrValidation(sheet2, attr.combo(), attr.prompt(), 1, 100, column, column);
  1000.         }
  1001.     }
  1002.     /**
  1003.      * 添加单元格
  1004.      */
  1005.     public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
  1006.     {
  1007.         Cell cell = null;
  1008.         try
  1009.         {
  1010.             // 设置行高
  1011.             row.setHeight(maxHeight);
  1012.             // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
  1013.             if (attr.isExport())
  1014.             {
  1015.                 // 创建cell
  1016.                 cell = row.createCell(column);
  1017.                 cell.setCellStyle(styles.get("data_" + attr.align() + "_" + attr.color()));
  1018. //                CellRangeAddress region = new CellRangeAddress(2, 7, 0, 0);
  1019. //                sheet.addMergedRegion(region);
  1020.                 // 用于读取对象中的属性
  1021.                 Object value = getTargetValue(vo, field, attr);
  1022.                 String dateFormat = attr.dateFormat();
  1023.                 String readConverterExp = attr.readConverterExp();
  1024.                 String separator = attr.separator();
  1025.                 String dictType = attr.dictType();
  1026.                 if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
  1027.                 {
  1028.                     cell.setCellValue(parseDateToStr(dateFormat, value));
  1029.                 }
  1030.                 else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
  1031.                 {
  1032.                     cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
  1033.                 }
  1034.                 else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))
  1035.                 {
  1036.                     cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));
  1037.                 }
  1038.                 else if (value instanceof BigDecimal && -1 != attr.scale())
  1039.                 {
  1040.                     cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
  1041.                 }
  1042.                 else if (!attr.handler().equals(ExcelHandlerAdapter.class))
  1043.                 {
  1044.                     cell.setCellValue(dataFormatHandlerAdapter(value, attr));
  1045.                 }
  1046.                 else
  1047.                 {
  1048.                     // 设置列类型
  1049.                     setCellVo(value, attr, cell);
  1050.                 }
  1051.                 addStatisticsData(column, Convert.toStr(value), attr);
  1052.             }
  1053.         }
  1054.         catch (Exception e)
  1055.         {
  1056.             log.error("导出Excel失败{}", e);
  1057.         }
  1058.         return cell;
  1059.     }
  1060.     public Cell addCell2(Excelyw attr, Row row2, T vo, Field field, int column)
  1061.     {
  1062.         Cell cell = null;
  1063.         try
  1064.         {
  1065.             // 设置行高
  1066.             row2.setHeight(maxHeight2);
  1067.             // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
  1068.             if (attr.isExport())
  1069.             {
  1070.                 // 创建cell
  1071.                 cell = row2.createCell(column);
  1072.                 cell.setCellStyle(styles2.get("data_" + attr.align() + "_" + attr.color()));
  1073.  
  1074.                 // 用于读取对象中的属性
  1075.                 Object value = getTargetValue2(vo, field, attr);
  1076.                 String dateFormat = attr.dateFormat();
  1077.                 String readConverterExp = attr.readConverterExp();
  1078.                 String separator = attr.separator();
  1079.                 String dictType = attr.dictType();
  1080.                 if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
  1081.                 {
  1082.                     cell.setCellValue(parseDateToStr(dateFormat, value));
  1083.                 }
  1084.                 else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
  1085.                 {
  1086.                     cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
  1087.                 }
  1088.                 else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))
  1089.                 {
  1090.                     cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));
  1091.                 }
  1092.                 else if (value instanceof BigDecimal && -1 != attr.scale())
  1093.                 {
  1094.                     cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
  1095.                 }
  1096.                 else if (!attr.handler().equals(ExcelHandlerAdapter.class))
  1097.                 {
  1098.                     cell.setCellValue(dataFormatHandlerAdapter2(value, attr));
  1099.                 }
  1100.                 else
  1101.                 {
  1102.                     // 设置列类型
  1103.                     setCellVo2(value, attr, cell);
  1104.                 }
  1105.                 if (heBing==true){
  1106.                     CellRangeAddress region = new CellRangeAddress(2, (short)listyw.size()+1, 0, (short)0);
  1107.                     sheet2.addMergedRegion(region);
  1108.                     this.heBing=false;
  1109.                 }
  1110.  
  1111.  
  1112.                 addStatisticsData2(column, Convert.toStr(value), attr);
  1113.             }
  1114.         }
  1115.         catch (Exception e)
  1116.         {
  1117.             log.error("导出Excel失败{}", e);
  1118.         }
  1119.         return cell;
  1120.     }
  1121.     /**
  1122.      * 设置 POI XSSFSheet 单元格提示或选择框
  1123.      *
  1124.      * @param sheet 表单
  1125.      * @param textlist 下拉框显示的内容
  1126.      * @param promptContent 提示内容
  1127.      * @param firstRow 开始行
  1128.      * @param endRow 结束行
  1129.      * @param firstCol 开始列
  1130.      * @param endCol 结束列
  1131.      */
  1132.     public void setPromptOrValidation(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow,
  1133.                                       int firstCol, int endCol)
  1134.     {
  1135.         DataValidationHelper helper = sheet.getDataValidationHelper();
  1136.         DataValidationConstraint constraint = textlist.length > 0 ? helper.createExplicitListConstraint(textlist) : helper.createCustomConstraint("DD1");
  1137.         CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
  1138.         DataValidation dataValidation = helper.createValidation(constraint, regions);
  1139.         if (StringUtils.isNotEmpty(promptContent))
  1140.         {
  1141.             // 如果设置了提示信息则鼠标放上去提示
  1142.             dataValidation.createPromptBox("", promptContent);
  1143.             dataValidation.setShowPromptBox(true);
  1144.         }
  1145.         // 处理Excel兼容性问题
  1146.         if (dataValidation instanceof XSSFDataValidation)
  1147.         {
  1148.             dataValidation.setSuppressDropDownArrow(true);
  1149.             dataValidation.setShowErrorBox(true);
  1150.         }
  1151.         else
  1152.         {
  1153.             dataValidation.setSuppressDropDownArrow(false);
  1154.         }
  1155.         sheet.addValidationData(dataValidation);
  1156.     }
  1157.  
  1158.     /**
  1159.      * 解析导出值 0=男,1=女,2=未知
  1160.      *
  1161.      * @param propertyValue 参数值
  1162.      * @param converterExp 翻译注解
  1163.      * @param separator 分隔符
  1164.      * @return 解析后值
  1165.      */
  1166.     public static String convertByExp(String propertyValue, String converterExp, String separator)
  1167.     {
  1168.         StringBuilder propertyString = new StringBuilder();
  1169.         String[] convertSource = converterExp.split(",");
  1170.         for (String item : convertSource)
  1171.         {
  1172.             String[] itemArray = item.split("=");
  1173.             if (StringUtils.containsAny(separator, propertyValue))
  1174.             {
  1175.                 for (String value : propertyValue.split(separator))
  1176.                 {
  1177.                     if (itemArray[0].equals(value))
  1178.                     {
  1179.                         propertyString.append(itemArray[1] + separator);
  1180.                         break;
  1181.                     }
  1182.                 }
  1183.             }
  1184.             else
  1185.             {
  1186.                 if (itemArray[0].equals(propertyValue))
  1187.                 {
  1188.                     return itemArray[1];
  1189.                 }
  1190.             }
  1191.         }
  1192.         return StringUtils.stripEnd(propertyString.toString(), separator);
  1193.     }
  1194.  
  1195.     /**
  1196.      * 反向解析值 男=0,女=1,未知=2
  1197.      *
  1198.      * @param propertyValue 参数值
  1199.      * @param converterExp 翻译注解
  1200.      * @param separator 分隔符
  1201.      * @return 解析后值
  1202.      */
  1203.     public static String reverseByExp(String propertyValue, String converterExp, String separator)
  1204.     {
  1205.         StringBuilder propertyString = new StringBuilder();
  1206.         String[] convertSource = converterExp.split(",");
  1207.         for (String item : convertSource)
  1208.         {
  1209.             String[] itemArray = item.split("=");
  1210.             if (StringUtils.containsAny(separator, propertyValue))
  1211.             {
  1212.                 for (String value : propertyValue.split(separator))
  1213.                 {
  1214.                     if (itemArray[1].equals(value))
  1215.                     {
  1216.                         propertyString.append(itemArray[0] + separator);
  1217.                         break;
  1218.                     }
  1219.                 }
  1220.             }
  1221.             else
  1222.             {
  1223.                 if (itemArray[1].equals(propertyValue))
  1224.                 {
  1225.                     return itemArray[0];
  1226.                 }
  1227.             }
  1228.         }
  1229.         return StringUtils.stripEnd(propertyString.toString(), separator);
  1230.     }
  1231.  
  1232.     /**
  1233.      * 解析字典值
  1234.      *
  1235.      * @param dictValue 字典值
  1236.      * @param dictType 字典类型
  1237.      * @param separator 分隔符
  1238.      * @return 字典标签
  1239.      */
  1240.     public static String convertDictByExp(String dictValue, String dictType, String separator)
  1241.     {
  1242.         return DictUtils.getDictLabel(dictType, dictValue, separator);
  1243.     }
  1244.  
  1245.     /**
  1246.      * 反向解析值字典值
  1247.      *
  1248.      * @param dictLabel 字典标签
  1249.      * @param dictType 字典类型
  1250.      * @param separator 分隔符
  1251.      * @return 字典值
  1252.      */
  1253.     public static String reverseDictByExp(String dictLabel, String dictType, String separator)
  1254.     {
  1255.         return DictUtils.getDictValue(dictType, dictLabel, separator);
  1256.     }
  1257.  
  1258.     /**
  1259.      * 数据处理器
  1260.      *
  1261.      * @param value 数据值
  1262.      * @param excel 数据注解
  1263.      * @return
  1264.      */
  1265.     public String dataFormatHandlerAdapter(Object value, Excel excel)
  1266.     {
  1267.         try
  1268.         {
  1269.             Object instance = excel.handler().newInstance();
  1270.             Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class });
  1271.             value = formatMethod.invoke(instance, value, excel.args());
  1272.         }
  1273.         catch (Exception e)
  1274.         {
  1275.             log.error("不能格式化数据 " + excel.handler(), e.getMessage());
  1276.         }
  1277.         return Convert.toStr(value);
  1278.     }
  1279.     public String dataFormatHandlerAdapter2(Object value, Excelyw excel)
  1280.     {
  1281.         try
  1282.         {
  1283.             Object instance = excel.handler().newInstance();
  1284.             Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class });
  1285.             value = formatMethod.invoke(instance, value, excel.args());
  1286.         }
  1287.         catch (Exception e)
  1288.         {
  1289.             log.error("不能格式化数据 " + excel.handler(), e.getMessage());
  1290.         }
  1291.         return Convert.toStr(value);
  1292.     }
  1293.     /**
  1294.      * 合计统计信息
  1295.      */
  1296.     private void addStatisticsData(Integer index, String text, Excel entity)
  1297.     {
  1298.         if (entity != null && entity.isStatistics())
  1299.         {
  1300.             Double temp = 0D;
  1301.             if (!statistics.containsKey(index))
  1302.             {
  1303.                 statistics.put(index, temp);
  1304.             }
  1305.             try
  1306.             {
  1307.                 temp = Double.valueOf(text);
  1308.             }
  1309.             catch (NumberFormatException e)
  1310.             {
  1311.             }
  1312.             statistics.put(index, statistics.get(index) + temp);
  1313.         }
  1314.     }
  1315.     private void addStatisticsData2(Integer index, String text, Excelyw entity)
  1316.     {
  1317.         if (entity != null && entity.isStatistics())
  1318.         {
  1319.             Double temp = 0D;
  1320.             if (!statistics2.containsKey(index))
  1321.             {
  1322.                 statistics2.put(index, temp);
  1323.             }
  1324.             try
  1325.             {
  1326.                 temp = Double.valueOf(text);
  1327.             }
  1328.             catch (NumberFormatException e)
  1329.             {
  1330.             }
  1331.             statistics2.put(index, statistics2.get(index) + temp);
  1332.         }
  1333.     }
  1334.     /**
  1335.      * 创建统计行
  1336.      */
  1337.     public void addStatisticsRow()
  1338.     {
  1339.         if (statistics.size() > 0)
  1340.         {
  1341.             Row row = sheet.createRow(sheet.getLastRowNum() + 1);
  1342.             Set<Integer> keys = statistics.keySet();
  1343.             Cell cell = row.createCell(0);
  1344.             cell.setCellStyle(styles.get("total"));
  1345.             cell.setCellValue("合计");
  1346.  
  1347.             for (Integer key : keys)
  1348.             {
  1349.                 cell = row.createCell(key);
  1350.                 cell.setCellStyle(styles.get("total"));
  1351.                 cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
  1352.             }
  1353.             statistics.clear();
  1354.         }
  1355.     }
  1356.     public void addStatisticsRow2()
  1357.     {
  1358.         if (statistics2.size() > 0)
  1359.         {
  1360.             Row row2 = sheet2.createRow(sheet2.getLastRowNum() + 1);
  1361.             Set<Integer> keys = statistics.keySet();
  1362.             Cell cell = row2.createCell(0);
  1363.             cell.setCellStyle(styles2.get("total"));
  1364.             cell.setCellValue("合计");
  1365.  
  1366.             for (Integer key : keys)
  1367.             {
  1368.                 cell = row2.createCell(key);
  1369.                 cell.setCellStyle(styles2.get("total"));
  1370.                 cell.setCellValue(DOUBLE_FORMAT.format(statistics2.get(key)));
  1371.             }
  1372.             statistics2.clear();
  1373.         }
  1374.     }
  1375.     /**
  1376.      * 编码文件名
  1377.      */
  1378.     public String encodingFilename(String filename)
  1379.     {
  1380.         filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
  1381.         return filename;
  1382.     }
  1383.  
  1384.     /**
  1385.      * 获取下载路径
  1386.      *
  1387.      * @param filename 文件名称
  1388.      */
  1389.     public String getAbsoluteFile(String filename)
  1390.     {
  1391.         String downloadPath = RuoYiConfig.getDownloadPath() + filename;
  1392.         File desc = new File(downloadPath);
  1393.         if (!desc.getParentFile().exists())
  1394.         {
  1395.             desc.getParentFile().mkdirs();
  1396.         }
  1397.         return downloadPath;
  1398.     }
  1399.  
  1400.     /**
  1401.      * 获取bean中的属性值
  1402.      *
  1403.      * @param vo 实体对象
  1404.      * @param field 字段
  1405.      * @param excel 注解
  1406.      * @return 最终的属性值
  1407.      * @throws Exception
  1408.      */
  1409.     private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
  1410.     {
  1411.         Object o = field.get(vo);
  1412.         if (StringUtils.isNotEmpty(excel.targetAttr()))
  1413.         {
  1414.             String target = excel.targetAttr();
  1415.             if (target.contains("."))
  1416.             {
  1417.                 String[] targets = target.split("[.]");
  1418.                 for (String name : targets)
  1419.                 {
  1420.                     o = getValue(o, name);
  1421.                 }
  1422.             }
  1423.             else
  1424.             {
  1425.                 o = getValue(o, target);
  1426.             }
  1427.         }
  1428.         return o;
  1429.     }
  1430.     private Object getTargetValue2(T vo, Field field, Excelyw excel) throws Exception
  1431.     {
  1432.         Object o = field.get(vo);
  1433.         if (StringUtils.isNotEmpty(excel.targetAttr()))
  1434.         {
  1435.             String target = excel.targetAttr();
  1436.             if (target.contains("."))
  1437.             {
  1438.                 String[] targets = target.split("[.]");
  1439.                 for (String name : targets)
  1440.                 {
  1441.                     o = getValue(o, name);
  1442.                 }
  1443.             }
  1444.             else
  1445.             {
  1446.                 o = getValue(o, target);
  1447.             }
  1448.         }
  1449.         return o;
  1450.     }
  1451.     /**
  1452.      * 以类的属性的get方法方法形式获取值
  1453.      *
  1454.      * @param o
  1455.      * @param name
  1456.      * @return value
  1457.      * @throws Exception
  1458.      */
  1459.     private Object getValue(Object o, String name) throws Exception
  1460.     {
  1461.         if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name))
  1462.         {
  1463.             Class<?> clazz = o.getClass();
  1464.             Field field = clazz.getDeclaredField(name);
  1465.             field.setAccessible(true);
  1466.             o = field.get(o);
  1467.         }
  1468.         return o;
  1469.     }
  1470.  
  1471.     /**
  1472.      * 得到所有定义字段
  1473.      */
  1474.     private void createExcelField()
  1475.     {
  1476.         this.fields = getFields();
  1477.         this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
  1478.         this.maxHeight = getRowHeight();
  1479.     }
  1480.     private void createExcelField2()
  1481.     {
  1482.         this.fields2 = getFields2();
  1483.         this.fields2 = this.fields2.stream().sorted(Comparator.comparing(objects -> ((Excelyw) objects[1]).sort())).collect(Collectors.toList());
  1484.         this.maxHeight2 = getRowHeight2();
  1485.     }
  1486.     /**
  1487.      * 获取字段注解信息
  1488.      */
  1489.     public List<Object[]> getFields()
  1490.     {
  1491.         List<Object[]> fields = new ArrayList<Object[]>();
  1492.         List<Field> tempFields = new ArrayList<>();
  1493.         tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
  1494.         tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
  1495.         for (Field field : tempFields)
  1496.         {
  1497.             // 单注解
  1498.             if (field.isAnnotationPresent(Excel.class))
  1499.             {
  1500.                 Excel attr = field.getAnnotation(Excel.class);
  1501.                 if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
  1502.                 {
  1503.                     field.setAccessible(true);
  1504.                     fields.add(new Object[] { field, attr });
  1505.                 }
  1506.             }
  1507.  
  1508.             // 多注解
  1509.             if (field.isAnnotationPresent(Excels.class))
  1510.             {
  1511.                 Excels attrs = field.getAnnotation(Excels.class);
  1512.                 Excel[] excels = attrs.value();
  1513.                 for (Excel attr : excels)
  1514.                 {
  1515.                     if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
  1516.                     {
  1517.                         field.setAccessible(true);
  1518.                         fields.add(new Object[] { field, attr });
  1519.                     }
  1520.                 }
  1521.             }
  1522.         }
  1523.         return fields;
  1524.     }
  1525.     public List<Object[]> getFields2()
  1526.     {
  1527.         List<Object[]> fields = new ArrayList<Object[]>();
  1528.         List<Field> tempFields = new ArrayList<>();
  1529.         tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
  1530.         tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
  1531.         for (Field field : tempFields)
  1532.         {
  1533.             // 单注解
  1534.             if (field.isAnnotationPresent(Excelyw.class))
  1535.             {
  1536.                 Excelyw attr = field.getAnnotation(Excelyw.class);
  1537.                 if (attr != null && (attr.type() == Excelyw.Type.ALL || attr.type() == type2))
  1538.                 {
  1539.                     field.setAccessible(true);
  1540.                     fields.add(new Object[] { field, attr });
  1541.                 }
  1542.             }
  1543.  
  1544.             // 多注解
  1545.             if (field.isAnnotationPresent(Excelyws.class))
  1546.             {
  1547.                 Excelyws attrs = field.getAnnotation(Excelyws.class);
  1548.                 Excelyw[] excels = attrs.value();
  1549.                 for (Excelyw attr : excels)
  1550.                 {
  1551.                     if (attr != null && (attr.type() == Excelyw.Type.ALL || attr.type() == type2))
  1552.                     {
  1553.                         field.setAccessible(true);
  1554.                         fields.add(new Object[] { field, attr });
  1555.                     }
  1556.                 }
  1557.             }
  1558.         }
  1559.         return fields;
  1560.     }
  1561.     /**
  1562.      * 根据注解获取最大行高
  1563.      */
  1564.     public short getRowHeight()
  1565.     {
  1566.         double maxHeight = 0;
  1567.         for (Object[] os : this.fields)
  1568.         {
  1569.             Excel excel = (Excel) os[1];
  1570.             maxHeight = Math.max(maxHeight, excel.height());
  1571.         }
  1572.         return (short) (maxHeight * 40);
  1573.     }
  1574.     public short getRowHeight2()
  1575.     {
  1576.         double maxHeight = 0;
  1577.         for (Object[] os : this.fields2)
  1578.         {
  1579.             Excelyw excel = (Excelyw) os[1];
  1580.             maxHeight = Math.max(maxHeight, excel.height());
  1581.         }
  1582.         return (short) (maxHeight * 100);
  1583.     }
  1584.     /**
  1585.      * 创建一个工作簿
  1586.      */
  1587.     public void createWorkbook()
  1588.     {
  1589.         this.wb = new SXSSFWorkbook(500);
  1590.         this.sheet = wb.createSheet();
  1591.         this.sheet2 = wb.createSheet();
  1592.         wb.setSheetName(0, sheetName);
  1593.         wb.setSheetName(1, "业务类导出");
  1594.         this.styles = createStyles(wb);
  1595.         this.styles2 = createStyles2(wb);
  1596.     }
  1597.  
  1598.     /**
  1599.      * 创建工作表
  1600.      *
  1601.      * @param sheetNo sheet数量
  1602.      * @param index 序号
  1603.      */
  1604.     public void createSheet(int sheetNo, int index)
  1605.     {
  1606.         // 设置工作表的名称.
  1607.         if (sheetNo > 1 && index > 0)
  1608.         {
  1609.             this.sheet = wb.createSheet();
  1610.             this.createTitle();
  1611.             wb.setSheetName(index, sheetName + index);
  1612.             this.sheet2 = wb.createSheet();
  1613.             this.createTitle2();
  1614.             wb.setSheetName(1, sheetName + index);
  1615.         }
  1616.     }
  1617.  
  1618.     /**
  1619.      * 获取单元格值
  1620.      *
  1621.      * @param row 获取的行
  1622.      * @param column 获取单元格列号
  1623.      * @return 单元格值
  1624.      */
  1625.     public Object getCellValue(Row row, int column)
  1626.     {
  1627.         if (row == null)
  1628.         {
  1629.             return row;
  1630.         }
  1631.         Object val = "";
  1632.         try
  1633.         {
  1634.             Cell cell = row.getCell(column);
  1635.             if (StringUtils.isNotNull(cell))
  1636.             {
  1637.                 if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
  1638.                 {
  1639.                     val = cell.getNumericCellValue();
  1640.                     if (DateUtil.isCellDateFormatted(cell))
  1641.                     {
  1642.                         val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
  1643.                     }
  1644.                     else
  1645.                     {
  1646.                         if ((Double) val % 1 != 0)
  1647.                         {
  1648.                             val = new BigDecimal(val.toString());
  1649.                         }
  1650.                         else
  1651.                         {
  1652.                             val = new DecimalFormat("0").format(val);
  1653.                         }
  1654.                     }
  1655.                 }
  1656.                 else if (cell.getCellType() == CellType.STRING)
  1657.                 {
  1658.                     val = cell.getStringCellValue();
  1659.                 }
  1660.                 else if (cell.getCellType() == CellType.BOOLEAN)
  1661.                 {
  1662.                     val = cell.getBooleanCellValue();
  1663.                 }
  1664.                 else if (cell.getCellType() == CellType.ERROR)
  1665.                 {
  1666.                     val = cell.getErrorCellValue();
  1667.                 }
  1668.  
  1669.             }
  1670.         }
  1671.         catch (Exception e)
  1672.         {
  1673.             return val;
  1674.         }
  1675.         return val;
  1676.     }
  1677.  
  1678.     /**
  1679.      * 判断是否是空行
  1680.      *
  1681.      * @param row 判断的行
  1682.      * @return
  1683.      */
  1684.     private boolean isRowEmpty(Row row)
  1685.     {
  1686.         if (row == null)
  1687.         {
  1688.             return true;
  1689.         }
  1690.         for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
  1691.         {
  1692.             Cell cell = row.getCell(i);
  1693.             if (cell != null && cell.getCellType() != CellType.BLANK)
  1694.             {
  1695.                 return false;
  1696.             }
  1697.         }
  1698.         return true;
  1699.     }
  1700.  
  1701.     /**
  1702.      * 获取Excel2003图片
  1703.      *
  1704.      * @param sheet 当前sheet对象
  1705.      * @param workbook 工作簿对象
  1706.      * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
  1707.      */
  1708.     public static Map<String, PictureData> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)
  1709.     {
  1710.         Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
  1711.         List<HSSFPictureData> pictures = workbook.getAllPictures();
  1712.         if (!pictures.isEmpty())
  1713.         {
  1714.             for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren())
  1715.             {
  1716.                 HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
  1717.                 if (shape instanceof HSSFPicture)
  1718.                 {
  1719.                     HSSFPicture pic = (HSSFPicture) shape;
  1720.                     int pictureIndex = pic.getPictureIndex() - 1;
  1721.                     HSSFPictureData picData = pictures.get(pictureIndex);
  1722.                     String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());
  1723.                     sheetIndexPicMap.put(picIndex, picData);
  1724.                 }
  1725.             }
  1726.             return sheetIndexPicMap;
  1727.         }
  1728.         else
  1729.         {
  1730.             return sheetIndexPicMap;
  1731.         }
  1732.     }
  1733.  
  1734.     /**
  1735.      * 获取Excel2007图片
  1736.      *
  1737.      * @param sheet 当前sheet对象
  1738.      * @param workbook 工作簿对象
  1739.      * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
  1740.      */
  1741.     public static Map<String, PictureData> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)
  1742.     {
  1743.         Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
  1744.         for (POIXMLDocumentPart dr : sheet.getRelations())
  1745.         {
  1746.             if (dr instanceof XSSFDrawing)
  1747.             {
  1748.                 XSSFDrawing drawing = (XSSFDrawing) dr;
  1749.                 List<XSSFShape> shapes = drawing.getShapes();
  1750.                 for (XSSFShape shape : shapes)
  1751.                 {
  1752.                     if (shape instanceof XSSFPicture)
  1753.                     {
  1754.                         XSSFPicture pic = (XSSFPicture) shape;
  1755.                         XSSFClientAnchor anchor = pic.getPreferredSize();
  1756.                         CTMarker ctMarker = anchor.getFrom();
  1757.                         String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
  1758.                         sheetIndexPicMap.put(picIndex, pic.getPictureData());
  1759.                     }
  1760.                 }
  1761.             }
  1762.         }
  1763.         return sheetIndexPicMap;
  1764.     }
  1765.  
  1766.     /**
  1767.      * 格式化不同类型的日期对象
  1768.      *
  1769.      * @param dateFormat 日期格式
  1770.      * @param val 被格式化的日期对象
  1771.      * @return 格式化后的日期字符
  1772.      */
  1773.     public String parseDateToStr(String dateFormat, Object val)
  1774.     {
  1775.         if (val == null)
  1776.         {
  1777.             return "";
  1778.         }
  1779.         String str;
  1780.         if (val instanceof Date)
  1781.         {
  1782.             str = DateUtils.parseDateToStr(dateFormat, (Date) val);
  1783.         }
  1784.         else if (val instanceof LocalDateTime)
  1785.         {
  1786.             str = DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDateTime) val));
  1787.         }
  1788.         else if (val instanceof LocalDate)
  1789.         {
  1790.             str = DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDate) val));
  1791.         }
  1792.         else
  1793.         {
  1794.             str = val.toString();
  1795.         }
  1796.         return str;
  1797.     }
  1798. }

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

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

  

闽ICP备14008679号