From 5a54ab14ce8161a8bff0a94dcc524a41a7d39863 Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: 星期日, 26 九月 2021 09:02:20 +0800
Subject: [PATCH] Excel注解支持导入导出标题信息

---
 src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java |  179 ++++++++++++++++++++++++++++++++++++++++++++++++++++-------
 1 files changed, 157 insertions(+), 22 deletions(-)

diff --git a/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java b/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
index 025a1cd..2784244 100644
--- a/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
+++ b/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
@@ -6,6 +6,7 @@
 import java.io.InputStream;
 import java.io.OutputStream;
 import java.lang.reflect.Field;
+import java.lang.reflect.Method;
 import java.math.BigDecimal;
 import java.text.DecimalFormat;
 import java.util.ArrayList;
@@ -46,6 +47,7 @@
 import org.apache.poi.ss.usermodel.VerticalAlignment;
 import org.apache.poi.ss.usermodel.Workbook;
 import org.apache.poi.ss.usermodel.WorkbookFactory;
+import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.ss.util.CellRangeAddressList;
 import org.apache.poi.util.IOUtils;
 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
@@ -125,6 +127,16 @@
     private List<Object[]> fields;
 
     /**
+     * 褰撳墠琛屽彿
+     */
+    private int rownum;
+    
+    /**
+     * 鏍囬
+     */
+    private String title;
+
+    /**
      * 鏈�澶ч珮搴�
      */
     private short maxHeight;
@@ -149,7 +161,7 @@
         this.clazz = clazz;
     }
 
-    public void init(List<T> list, String sheetName, Type type)
+    public void init(List<T> list, String sheetName, String title, Type type)
     {
         if (list == null)
         {
@@ -158,8 +170,27 @@
         this.list = list;
         this.sheetName = sheetName;
         this.type = type;
+        this.title = title;
         createExcelField();
         createWorkbook();
+        createTitle();
+    }
+
+    /**
+     * 鍒涘缓excel绗竴琛屾爣棰�
+     */
+    public void createTitle()
+    {
+        if (StringUtils.isNotEmpty(title))
+        {
+            Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
+            titleRow.setHeightInPoints(30);
+            Cell titleCell = titleRow.createCell(0);
+            titleCell.setCellStyle(styles.get("title"));
+            titleCell.setCellValue(title);
+            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
+                    this.fields.size() - 1));
+        }
     }
 
     /**
@@ -170,17 +201,30 @@
      */
     public List<T> importExcel(InputStream is) throws Exception
     {
-        return importExcel(StringUtils.EMPTY, is);
+        return importExcel(is, 0);
+    }
+
+    /**
+     * 瀵筫xcel琛ㄥ崟榛樿绗竴涓储寮曞悕杞崲鎴恖ist
+     * 
+     * @param is 杈撳叆娴�
+     * @param titleNum 鏍囬鍗犵敤琛屾暟
+     * @return 杞崲鍚庨泦鍚�
+     */
+    public List<T> importExcel(InputStream is, int titleNum) throws Exception
+    {
+        return importExcel(StringUtils.EMPTY, is, titleNum);
     }
 
     /**
      * 瀵筫xcel琛ㄥ崟鎸囧畾琛ㄦ牸绱㈠紩鍚嶈浆鎹㈡垚list
      * 
      * @param sheetName 琛ㄦ牸绱㈠紩鍚�
+     * @param titleNum 鏍囬鍗犵敤琛屾暟
      * @param is 杈撳叆娴�
      * @return 杞崲鍚庨泦鍚�
      */
-    public List<T> importExcel(String sheetName, InputStream is) throws Exception
+    public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception
     {
         this.type = Type.IMPORT;
         this.wb = WorkbookFactory.create(is);
@@ -209,7 +253,7 @@
             // 瀹氫箟涓�涓猰ap鐢ㄤ簬瀛樻斁excel鍒楃殑搴忓彿鍜宖ield.
             Map<String, Integer> cellMap = new HashMap<String, Integer>();
             // 鑾峰彇琛ㄥご
-            Row heard = sheet.getRow(0);
+            Row heard = sheet.getRow(titleNum);
             for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
             {
                 Cell cell = heard.getCell(i);
@@ -242,7 +286,7 @@
                     }
                 }
             }
-            for (int i = 1; i <= rows; i++)
+            for (int i = titleNum + 1; i <= rows; i++)
             {
                 // 浠庣2琛屽紑濮嬪彇鏁版嵁,榛樿绗竴琛屾槸琛ㄥご.
                 Row row = sheet.getRow(i);
@@ -333,6 +377,10 @@
                         {
                             val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
                         }
+                        else if (!attr.handler().equals(ExcelHandlerAdapter.class))
+                        {
+                            val = dataFormatHandlerAdapter(val, attr);
+                        }
                         else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))
                         {
                             PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
@@ -364,7 +412,20 @@
      */
     public AjaxResult exportExcel(List<T> list, String sheetName)
     {
-        this.init(list, sheetName, Type.EXPORT);
+        return exportExcel(list, sheetName, StringUtils.EMPTY);
+    }
+    
+    /**
+     * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟
+     * 
+     * @param list 瀵煎嚭鏁版嵁闆嗗悎
+     * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+     * @param title 鏍囬
+     * @return 缁撴灉
+     */
+    public AjaxResult exportExcel(List<T> list, String sheetName, String title)
+    {
+        this.init(list, sheetName, title, Type.EXPORT);
         return exportExcel();
     }
 
@@ -377,11 +438,26 @@
      * @return 缁撴灉
      * @throws IOException
      */
-    public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException
+    public void exportExcel(HttpServletResponse response, List<T> list, String sheetName)throws IOException
+    {
+        exportExcel(response, list, sheetName, StringUtils.EMPTY);
+    }
+
+    /**
+     * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟
+     * 
+     * @param response 杩斿洖鏁版嵁
+     * @param list 瀵煎嚭鏁版嵁闆嗗悎
+     * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+     * @param title 鏍囬
+     * @return 缁撴灉
+     * @throws IOException
+     */
+    public void exportExcel(HttpServletResponse response, List<T> list, String sheetName, String title) throws IOException
     {
         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
         response.setCharacterEncoding("utf-8");
-        this.init(list, sheetName, Type.EXPORT);
+        this.init(list, sheetName, title, Type.EXPORT);
         exportExcel(response.getOutputStream());
     }
 
@@ -393,7 +469,19 @@
      */
     public AjaxResult importTemplateExcel(String sheetName)
     {
-        this.init(null, sheetName, Type.IMPORT);
+        return importTemplateExcel(sheetName, StringUtils.EMPTY);
+    }
+
+    /**
+     * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟
+     * 
+     * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+     * @param title 鏍囬
+     * @return 缁撴灉
+     */
+    public AjaxResult importTemplateExcel(String sheetName, String title)
+    {
+        this.init(null, sheetName, title, Type.IMPORT);
         return exportExcel();
     }
 
@@ -405,9 +493,21 @@
      */
     public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException
     {
+        importTemplateExcel(response, sheetName);
+    }
+
+    /**
+     * 瀵筶ist鏁版嵁婧愬皢鍏堕噷闈㈢殑鏁版嵁瀵煎叆鍒癳xcel琛ㄥ崟
+     * 
+     * @param sheetName 宸ヤ綔琛ㄧ殑鍚嶇О
+     * @param title 鏍囬
+     * @return 缁撴灉
+     */
+    public void importTemplateExcel(HttpServletResponse response, String sheetName, String title) throws IOException
+    {
         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
         response.setCharacterEncoding("utf-8");
-        this.init(null, sheetName, Type.IMPORT);
+        this.init(null, sheetName, title, Type.IMPORT);
         exportExcel(response.getOutputStream());
     }
 
@@ -468,13 +568,13 @@
     public void writeSheet()
     {
         // 鍙栧嚭涓�鍏辨湁澶氬皯涓猻heet.
-        double sheetNo = Math.ceil(list.size() / sheetSize);
-        for (int index = 0; index <= sheetNo; index++)
+        int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));
+        for (int index = 0; index < sheetNo; index++)
         {
             createSheet(sheetNo, index);
 
             // 浜х敓涓�琛�
-            Row row = sheet.createRow(0);
+            Row row = sheet.createRow(rownum);
             int column = 0;
             // 鍐欏叆鍚勪釜瀛楁鐨勫垪澶村悕绉�
             for (Object[] os : fields)
@@ -502,7 +602,7 @@
         int endNo = Math.min(startNo + sheetSize, list.size());
         for (int i = startNo; i < endNo; i++)
         {
-            row = sheet.createRow(i + 1 - startNo);
+            row = sheet.createRow(i + 1 + rownum - startNo);
             // 寰楀埌瀵煎嚭瀵硅薄.
             T vo = (T) list.get(i);
             int column = 0;
@@ -528,6 +628,16 @@
         // 鍐欏叆鍚勬潯璁板綍,姣忔潯璁板綍瀵瑰簲excel琛ㄤ腑鐨勪竴琛�
         Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
         CellStyle style = wb.createCellStyle();
+        style.setAlignment(HorizontalAlignment.CENTER);
+        style.setVerticalAlignment(VerticalAlignment.CENTER);
+        Font titleFont = wb.createFont();
+        titleFont.setFontName("Arial");
+        titleFont.setFontHeightInPoints((short) 16);
+        titleFont.setBold(true);
+        style.setFont(titleFont);
+        styles.put("title", style);
+
+        style = wb.createCellStyle();
         style.setAlignment(HorizontalAlignment.CENTER);
         style.setVerticalAlignment(VerticalAlignment.CENTER);
         style.setBorderRight(BorderStyle.THIN);
@@ -729,6 +839,10 @@
                 {
                     cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
                 }
+                else if (!attr.handler().equals(ExcelHandlerAdapter.class))
+                {
+                    cell.setCellValue(dataFormatHandlerAdapter(value, attr));
+                }
                 else
                 {
                     // 璁剧疆鍒楃被鍨�
@@ -899,6 +1013,28 @@
     public static String reverseDictByExp(String dictLabel, String dictType, String separator)
     {
         return DictUtils.getDictValue(dictType, dictLabel, separator);
+    }
+
+    /**
+     * 鏁版嵁澶勭悊鍣�
+     * 
+     * @param value 鏁版嵁鍊�
+     * @param excel 鏁版嵁娉ㄨВ
+     * @return
+     */
+    public String dataFormatHandlerAdapter(Object value, Excel excel)
+    {
+        try
+        {
+            Object instance = excel.handler().newInstance();
+            Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class });
+            value = formatMethod.invoke(instance, value, excel.args());
+        }
+        catch (Exception e)
+        {
+            log.error("涓嶈兘鏍煎紡鍖栨暟鎹� " + excel.handler(), e.getMessage());
+        }
+        return Convert.toStr(value);
     }
 
     /**
@@ -1086,6 +1222,9 @@
     public void createWorkbook()
     {
         this.wb = new SXSSFWorkbook(500);
+        this.sheet = wb.createSheet();
+        wb.setSheetName(0, sheetName);
+        this.styles = createStyles(wb);
     }
 
     /**
@@ -1094,17 +1233,13 @@
      * @param sheetNo sheet鏁伴噺
      * @param index 搴忓彿
      */
-    public void createSheet(double sheetNo, int index)
+    public void createSheet(int sheetNo, int index)
     {
-        this.sheet = wb.createSheet();
-        this.styles = createStyles(wb);
         // 璁剧疆宸ヤ綔琛ㄧ殑鍚嶇О.
-        if (sheetNo == 0)
+        if (sheetNo > 1 && index > 0)
         {
-            wb.setSheetName(index, sheetName);
-        }
-        else
-        {
+            this.sheet = wb.createSheet();
+            this.createTitle();
             wb.setSheetName(index, sheetName + index);
         }
     }

--
Gitblit v1.9.3