自定义注解

  3. import java.lang.annotation.Documented;
  4. import java.lang.annotation.Retention;
  5. import java.lang.annotation.Target;
  7. import static java.lang.annotation.ElementType.FIELD;
  8. import static java.lang.annotation.RetentionPolicy.RUNTIME;
  10. @Retention(RUNTIME)
  11. @Target(FIELD)
  12. @Documented
  13. public @interface ExcelExportAnnotation {
  15.     String title() default "";
  17.     int order() default 0;
  19. }
  2. import org.apache.commons.collections.CollectionUtils;
  3. import org.apache.commons.lang3.StringUtils;
  4. import org.apache.poi.ss.usermodel.*;
  5. import org.apache.poi.ss.util.CellRangeAddress;
  6. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  8. import com.hikvision.idatafusion.dgdpsiot.common.annotation.ExcelExportAnnotation;
  10. import java.lang.reflect.Field;
  11. import java.util.*;
  13. /**
  14.  * @author hanrensong
  15.  * @date 2021/4/24
  16.  */
  17. public class ExcelUtil {
  19.     public static <T> void listToSheet(Workbook workbook, List<? extends Object> list, Class<T> cls, String sheetName) {
  20.         if(workbook==null) {
  21.             return;
  22.         }
  23.         Sheet sheet = null;
  24.         if(StringUtils.isNotBlank(sheetName)) {
  25.             sheet = workbook.createSheet(sheetName);
  26.         }else {
  27.             sheet = workbook.createSheet();
  28.         }
  30.         Row header = sheet.createRow(0);
  31.         writeHead(header,cls);
  32.         if (CollectionUtils.isNotEmpty(list)) {
  33.             Map<Integer, Field> fields = new HashMap<>();
  34.             List<Integer> fieldOrders = new ArrayList<>();
  35.             parseClass(cls,fields,fieldOrders);
  36.             for(int i=0;i<list.size();i++) {
  37.                 Object t = list.get(i);
  38.                 Row row = sheet.createRow(i+1);
  39.                 for(int j=0; j<fieldOrders.size();j++) {
  40.                     Cell cell = row.createCell(j);
  42.                     Field field = fields.get(fieldOrders.get(j));
  43.                     try {
  44.                         field.setAccessible(true);
  45.                         Object value = field.get(t);
  46.                         if(value != null) {
  47.                             cell.setCellValue(String.valueOf(value));
  48.                         }
  49.                     } catch (IllegalArgumentException e) {
  50.                         e.printStackTrace();
  51.                     } catch (IllegalAccessException e) {
  52.                         e.printStackTrace();
  53.                     }
  54.                 }
  55.             }
  56.         }
  57.     }
  59.     /**
  60.      * @param cls
  61.      * @param fieldMap
  62.      * @param fieldOrders
  63.      */
  64.     private static <T> void parseClass(Class<T> cls, Map<Integer, Field> fieldMap, List<Integer> fieldOrders) {
  65.         Field[] fields = cls.getDeclaredFields();
  66.         for(Field field : fields) {
  67.             if(field.isAnnotationPresent(ExcelExportAnnotation.class)) {
  68.                 ExcelExportAnnotation annotation = field.getAnnotation(ExcelExportAnnotation.class);
  69.                 Integer order = new Integer(annotation.order());
  70.                 fieldMap.put(order, field);
  71.                 fieldOrders.add(order);
  72.             }
  73.         }
  74.         Collections.sort(fieldOrders);
  75.     }
  77.     /**
  78.      * @param row
  79.      * @param cls
  80.      */
  81.     private static <T> void writeHead(Row row, Class<T> cls) {
  82.         Field[] fields = cls.getDeclaredFields();
  83.         Map<Integer,String> titles = new HashMap<>();
  84.         List<Integer> orders = new ArrayList<>();
  85.         for(Field field : fields) {
  86.             if(field.isAnnotationPresent(ExcelExportAnnotation.class)) {
  87.                 ExcelExportAnnotation annotation = field.getAnnotation(ExcelExportAnnotation.class);
  88.                 Integer order = new Integer(annotation.order());
  89.                 titles.put(order, annotation.title());
  90.                 orders.add(order);
  91.             }
  92.         }
  93.         Collections.sort(orders);
  94.         for(Integer i : orders) {
  95.             Cell cell = row.createCell(i-1);
  96.             cell.setCellValue(titles.get(i));
  97.         }
  98.     }
  100.     public static <T> List<T> sheetToList(Sheet sheet, Class<T> voClass, int headerRowNumber){
  101.         if(sheet == null || voClass==null ){
  102.             return null;
  103.         }
  104.         List<T> result = new ArrayList<>();
  105.         Iterator<Row> rowIterator = sheet.iterator();
  106.         while(rowIterator.hasNext()) {
  107.             Row row = rowIterator.next();
  108.             if(row.getRowNum()<headerRowNumber) {
  109.                 continue;
  110.             }
  112.             parseRow(result,row,voClass);
  113.         }
  115.         return result;
  116.     }
  118.     /**
  119.      * @param result
  120.      * @param row
  121.      * @param voClass
  122.      */
  123.     private static <T> void parseRow(List<T> result, Row row, Class<T> voClass) {
  124.         Iterator<Cell> cellIterator = row.cellIterator();
  125.         T t = null;
  126.         try {
  127.             t = voClass.newInstance();
  128.             DataFormatter formatter = new DataFormatter();
  129.             while(cellIterator.hasNext()) {
  130.                 Cell cell = cellIterator.next();
  131.                 Field field = getField(cell.getColumnIndex()+1,voClass);
  132.                 if(field!=null) {
  133.                     field.setAccessible(true);
  134.                     field.set(t, formatter.formatCellValue(cell));
  135.                 }
  136.             }
  137.         } catch (Exception e) {
  138.             e.printStackTrace();
  139.         }
  141.         if(t!=null) {
  142.             result.add(t);
  143.         }
  145.     }
  147.     /**
  148.      * @param columnIndex
  149.      * @param voClass
  150.      */
  151.     private static <T> Field getField(int columnIndex, Class<T> voClass) {
  152.         Field[] fields = voClass.getDeclaredFields();
  153.         for(Field field : fields) {
  154.             if(field.isAnnotationPresent(ExcelExportAnnotation.class)) {
  155.                 ExcelExportAnnotation annotation = field.getAnnotation(ExcelExportAnnotation.class);
  156.                 if(annotation.order()==columnIndex) {
  157.                     return field;
  158.                 }
  159.             }
  160.         }
  161.         return null;
  162.     }
  164.     public static void mergeSheet(XSSFWorkbook workbook, int sheetIndex, int cellNum) {
  165.         Sheet sheet = workbook.getSheetAt(sheetIndex);
  166.         if (sheet.getLastRowNum() > 2) {
  167.             Iterator<Row> sheetIterator = sheet.rowIterator();
  168.             int rowStart =1;
  169.             String modelName = sheet.getRow(1).getCell(cellNum).getStringCellValue();
  170.             while(sheetIterator.hasNext()) {
  171.                 Row row = sheetIterator.next();
  172.                 if(row.getRowNum()<2) {
  173.                     continue;
  174.                 }
  175.                 String cellValue = row.getCell(cellNum).getStringCellValue();
  176.                 if(cellValue.equals(modelName)) {
  177.                     continue;
  178.                 }else {
  179.                     if(rowStart!=(row.getRowNum()-1)) {
  180.                         CellRangeAddress region = new CellRangeAddress(rowStart, row.getRowNum()-1, cellNum, cellNum);
  181.                         sheet.addMergedRegion(region);
  182.                     }
  183.                     rowStart = row.getRowNum();
  184.                     modelName = cellValue;
  185.                 }
  186.             }
  187.             if(rowStart!=sheet.getLastRowNum()) {
  188.                 CellRangeAddress region = new CellRangeAddress(rowStart, sheet.getLastRowNum(), cellNum, cellNum);
  189.                 sheet.addMergedRegion(region);
  190.             }
  191.         }
  192.     }
  194. //    public static void main(String[] args) throws IOException {
  195. //
  196. //
  197. //        XSSFWorkbook workbook = new XSSFWorkbook();
  198. //
  199. //        XSSFCellStyle style = workbook.createCellStyle();
  200. //        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  201. //        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  202. //        Sheet sheet = workbook.createSheet("sheet");
  203. //
  204. //        Row row0 = sheet.createRow(0);
  205. //        Cell cell_00 = row0.createCell(0);
  206. //        cell_00.setCellStyle(style);
  207. //        cell_00.setCellValue("日期");
  208. //        Cell cell_01 = row0.createCell(1);
  209. //        cell_01.setCellStyle(style);
  210. //        cell_01.setCellValue("午别");
  211. //        Row row1 = sheet.createRow(1);
  212. //        Cell cell_10 = row1.createCell(0);
  213. //        cell_10.setCellStyle(style);
  214. //        cell_10.setCellValue("20180414");
  215. //        Cell cell_11 = row1.createCell(1);
  216. //        cell_11.setCellStyle(style);
  217. //        cell_11.setCellValue("上午");
  218. //
  219. //        Row row2 = sheet.createRow(2);
  220. //        Cell cell_20 = row2.createCell(0);
  221. //        cell_20.setCellStyle(style);
  222. //        cell_20.setCellValue("20180413");
  223. //        Cell cell_21 = row2.createCell(1);
  224. //        cell_21.setCellStyle(style);
  225. //        cell_21.setCellValue("下午");
  226. //
  227. //        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
  228. //        // 行和列都是从0开始计数，且起始结束都会合并
  229. //        // 这里是合并excel中日期的两行为一行
  230. //        CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
  231. //        sheet.addMergedRegion(region);
  232. //
  233. //        File file = new File("E:\\demo1.xls");
  234. //        FileOutputStream fout = new FileOutputStream(file);
  235. //        workbook.write(fout);
  236. //        fout.close();
  237. //
  238. //    }
  239. }