devbox
键盘狂人
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

easyExcel导入导出_easyexcel导出

作者：键盘狂人 | 2024-02-05 15:29:32

赞

踩

easyexcel导出

目录

EasyExcel工具类

pom导入依赖

1.相关代码

1.1 EasyExcel导出工具类

1.2CommonListener导入侦听器

1.3CustomMergeStrategy 合并策略(已改)

1.4 DownHandler 下拉处理(已解决大数据问题)

1.5 CustomMerge 合并单元格的注解

1.6 FieldDispose 导入自定义解析注解

1.7 DropDown 下拉注解

1.8 ExcelFieldType自定义数据解析常量

1.9 标题合并

2 .使用方法

2.1 导出导入实体--注解使用

2.2 Controller

2.3 service 调用

2.4 自定义批量方法 

EasyExcel工具类

       项目中常见的导出 一般使用 EasyExcel/POI，这里简单的列出了easyExcel 的用，easyExcel 使用简单方便，内存占用低，不会导致oom，不过导入导出要求多的话，写解析器也难受，哎本来写了很多的，结果好多找不到，然后暂时又没用到。在这里取出了常用的一些工具类，导出添加下拉框，单元格合并，多sheet导出等。导入则通过注解 解析字典值或校验唯一值，通过自定义的批量方法每500条插入一次

pom导入依赖

<!--excel导入导出-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

1.相关代码

1.1 EasyExcel导出工具类

  1. /**
  2.  * Excel相关处理
  3.  * @author pw
  4.  */
  5. public class ExcelUtil {
  6. 	/**
  7. 	 *  单个sheet页导出
  8. 	 */
  9. 	public static void writeExcel(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
  10. 		 EasyExcel.write(getOutputStream(fileName, response), clazz)
  11. 				.excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
  12. 				.registerWriteHandler(getCellStyle()).doWrite(data);
  13. 	}
  14.  
  15. 	/**
  16. 	 *  sheet页导出.设置下拉框
  17. 	 */
  18. 	public static void writeExcel(HttpServletResponse response, List<?> data, String fileName, String sheetName,Map<Integer,List<String>> dropDownMap, Class<?> clazz) throws Exception {
  19. 	 EasyExcel.write(getOutputStream(fileName, response), clazz)
  20. 				.excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
  21. 			 	.registerWriteHandler(new DownHandler(dropDownMap))
  22. 				.registerWriteHandler(getCellStyle()).doWrite(data);
  23. 	}
  24. 	/**
  25. 	 *  合并导出
  26. 	 */
  27. 	public static void writeExcelCustomMerge(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class<?> clazz) throws Exception {
  28. 		EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(getCellStyle())
  29. 				.head(clazz)
  30. 				.registerWriteHandler(new CustomMergeStrategy(clazz))
  31. 				.doWrite(data);
  32. 	}
  33.     /**
  34. 	 *  自定义屏蔽字段加自定义标题
  35. 	 */
  36.     public static void downloadMergeTitle(OutputStream outputStream , String sheetName,Class<?> clazz, List<?> data, Set<String> set){
  37.         EasyExcel.write(outputStream, clazz)
  38.                 .excludeColumnFiledNames(set)
  39.                 .relativeHeadRowIndex(1)
  40.                 .registerWriteHandler(new CustomTitleWriteHandler(clazz,sheetName,set))
  41.                 .registerWriteHandler(writeCenterStyleBorder())
  42.                 .autoCloseStream(Boolean.TRUE)
  43.                 .sheet(sheetName)
  44.                 .doWrite(data);
  45.     }
  46. 	/**
  47. 	 *   多个sheet页导出
  48. 	 */
  49. 	public static void writeExcels(HttpServletResponse response,List<List<?>> data, String fileName, Class<?> clazz) throws Exception{
  50. 		ExcelWriter excelWriter = null;
  51. 		try{
  52. 			excelWriter = EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getCellStyle()).build();
  53. 			for(int i=0;i<data.size();i++){
  54. 				WriteSheet writeSheet = EasyExcel.writerSheet(i, "sheet"+i).build();
  55. 				excelWriter.write(data.get(i), writeSheet);
  56. 			}
  57. 		} finally {
  58. 			if(excelWriter != null){
  59. 				excelWriter.finish();
  60. 			}
  61. 		}
  62. 	}
  63. 	/**
  64. 	 * 模板复制导出
  65. 	 * @param data 数据结构最外层为需要导出的sheet页个数，里层为多种数据格式的集合，一般为list和map组合成的混合list
  66. 	 * @param templateFile 模板本地路径
  67. 	 */
  68. 	public static void copyFillTemplate(HttpServletResponse response,List<List<?>> data, String fileName,String templateFile) throws Exception{
  69. 		//excel模板
  70. 		File file = new File(templateFile);
  71. 		ExcelWriter excelWriter = null;
  72. 		try (FileInputStream fileInputStream = new FileInputStream(file); ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
  73. 			//通过poi复制出需要的sheet个数的模板
  74. 			XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
  75. 			workbook.setSheetName(0, "sheet1");
  76. 			for (int i = 1; i < data.size();i++) {
  77. 				workbook.cloneSheet(0, "sheet"+(i+1));
  78. 			}
  79. 			workbook.write(bos);
  80. 			byte[] bArray = bos.toByteArray();
  81. 			InputStream is = new ByteArrayInputStream(bArray);
  82. 			//通过easyExcel塞入参数
  83. 			excelWriter = EasyExcel.write(getOutputStream(fileName, response)).withTemplate(is).build();
  84. 			fillTemplateData(excelWriter,data);
  85. 		} finally {
  86. 			if(excelWriter != null){
  87. 				excelWriter.finish();
  88. 			}
  89. 		}
  90. 	}
  91.  
  92. 	/**
  93. 	 * 模板导出
  94. 	 */
  95. 	public static void fillTemplate(HttpServletResponse response,List<List<?>> data, String fileName,String templateFile) throws Exception{
  96. 		ExcelWriter excelWriter = null;
  97. 		try  {
  98. 			excelWriter = EasyExcel.write(getOutputStream(fileName, response)).withTemplate(new File(templateFile) ).build();
  99. 			fillTemplateData(excelWriter,data);
  100. 		} finally {
  101. 			if(excelWriter != null){
  102. 				excelWriter.finish();
  103. 			}
  104. 		}
  105. 	}
  106. 	/**
  107. 	 * 填充模板数据
  108. 	 * @param data 数据结构最外层为需要导出的sheet页个数，里层为多种数据格式的集合，一般为list和map组合成的混合list
  109. 	 */
  110. 	private static void fillTemplateData(ExcelWriter excelWriter, List<List<?>> data){
  111. 		for (int i = 0; i < data.size(); i++) {
  112. 			WriteSheet writeSheet = EasyExcel.writerSheet(i).build();
  113. 			for(Object o:data.get(i)){
  114. 				excelWriter.fill(o, writeSheet);
  115. 			}
  116. 		}
  117. 	}
  118.  
  119. 	/**
  120. 	 * 设置请求
  121. 	 */
  122. 	private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
  123. 		fileName = URLEncoder.encode(fileName, "UTF-8");
  124. 		// 告诉浏览器用什么软件可以打开此文件
  125. 		response.setHeader("content-Type", "application/vnd.ms-excel");
  126. 		response.setCharacterEncoding("utf8");
  127.         response.setHeader("Content-Disposition",  "attachment;filename*=utf-8'zh_cn'"+fileName+  +"."+ ExcelTypeEnum.XLSX);
  128. 		return response.getOutputStream();
  129. 	}
  130. 	/**
  131. 	 * 设置样式
  132. 	 */
  133. 	private static HorizontalCellStyleStrategy getCellStyle(){
  134. 		//表头样式
  135. 		WriteCellStyle headWriteCellStyle = new WriteCellStyle();
  136. 		//设置表头居中对齐
  137. 		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
  138. 		//内容样式
  139. 		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
  140. 		//设置内容靠左对齐
  141. 		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
  142. 		return new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
  143. 	}
  144. }

1.2CommonListener导入侦听器

插入方法已优化，使用的是1.8的consumer，传入自定义方法

  1. import com.alibaba.excel.context.AnalysisContext;
  2. import com.alibaba.excel.event.AnalysisEventListener;
  3. import com.alibaba.excel.exception.ExcelDataConvertException;
  4. import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
  5. import com.cloud.common.core.excel.annotation.FieldDispose;
  6. import com.cloud.common.core.exception.CommonException;
  7. import lombok.Getter;
  8.  
  9. import java.lang.reflect.Field;
  10. import java.util.*;
  11. import java.util.function.Consumer;
  12.  
  13. /**
  14.  * 基于EasyExcel的导入工具类，插入使用批量插入，BATCH_COUNT=500
  15.  * @author pw
  16.  * @param <T> 中间接收类
  17.  */
  18. @Getter
  19. public class CommonListener<T> extends AnalysisEventListener<T> {
  20.     /**
  21.      *  执行的插入的service
  22.      */
  23.     private final Consumer<List<T>> p;
  24.     /**
  25.      * 字典
  26.      */
  27.     private final Map<String, Map<String,Object>> itemMap;
  28.     /**
  29.      * 中间类的字段
  30.      */
  31.     Field[] fields ;
  32.     /**
  33.      * 初始化参数，server 执行
  34.      * @param itemMap 字典数据,从里面去对应的数据
  35.      * @param p 执行方法
  36.      */
  37.     public CommonListener(Map<String,Map<String,Object>> itemMap,Class<T> tClass, Consumer<List<T>> p) {
  38.         this.p = p;
  39.         this.itemMap = itemMap;
  40.         this.fields = tClass.getDeclaredFields();
  41.     }
  42.     /**
  43.      * 批量插入数据的大小
  44.      */
  45.     private final int BATCH_COUNT = 500;
  46.     /**
  47.      * 存储数据的集合
  48.      */
  49.     private final List<T> rows = new ArrayList<>();
  50.     /**
  51.      * 存储数据的集合
  52.      */
  53.     private final Map<String, Set<String>> unique = new HashMap<>();
  54.     /**
  55.      * 存放失败的数据及原因
  56.      */
  57.     private final List<String> msgList = new ArrayList<>();
  58.  
  59.     @Override
  60.     public void onException(Exception exception, AnalysisContext context) {
  61.         if (exception instanceof ExcelDataConvertException) {
  62.             ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
  63.             msgList.add(String.format("第%s行，第%s列解析异常，数据为:%s",excelDataConvertException.getRowIndex(),
  64.                     excelDataConvertException.getColumnIndex(),excelDataConvertException.getCellData()));
  65.         }
  66.     }
  67.  
  68.     @Override
  69.     public void invoke(T data, AnalysisContext context) {
  70.         ReadRowHolder readRowHolder = context.readRowHolder();
  71.         try {
  72.             for ( Field field : fields )  {
  73.                 //得到属性值
  74.                 field.setAccessible(true);
  75.                 String value = (String) field.get(data);
  76.                 if(field.isAnnotationPresent(FieldDispose.class)){
  77.                     //获取字段注解
  78.                     FieldDispose annotation = field.getAnnotation(FieldDispose.class);
  79.                     switch (annotation.type()){
  80.                         //字典替换
  81.                         case DICT:
  82.                             if(!itemMap.get(annotation.value()).containsKey(value)){
  83.                                 msgList.add(String.format("第%s行异常，原因%s，数据为:%s",readRowHolder.getRowIndex()+1,annotation.errorMessage(),value));
  84.                                 return;
  85.                             }
  86.                             field.set(data, itemMap.get(annotation.value()).get(value));
  87.                             break;
  88.                         //唯一值判断
  89.                         case UNIQUE:
  90.                             if(unique.containsKey(annotation.value())){
  91.                                 if(unique.get(annotation.value()).contains(value)){
  92.                                     msgList.add(String.format("第%s行异常，原因%s，数据为:%s",readRowHolder.getRowIndex()+1,annotation.errorMessage(),value));
  93.                                     return;
  94.                                 }
  95.                             }else {
  96.                                 Set<String> m = new HashSet<>();
  97.                                 m.add(value);
  98.                                 unique.put(annotation.value(),m);
  99.                             }
  100.                             break;
  101.                         default:break;
  102.                     }
  103.                 }
  104.             }
  105.             rows.add(data);
  106.             if(rows.size()>=BATCH_COUNT){
  107.                 this.saveBatch();
  108.             }
  109.         }catch (Exception e){
  110.             msgList.add(String.format("第%s行异常，原因:%s",readRowHolder.getRowIndex()+1,e.getMessage()));
  111.         }
  112.     }
  113.  
  114.     /**
  115.      * 数据解析完成执行
  116.      */
  117.     @Override
  118.     public void doAfterAllAnalysed(AnalysisContext context) {
  119.         if(rows.size()>0){
  120.             this.saveBatch();
  121.         }
  122.     }
  123.  
  124.     private void saveBatch(){
  125.         try {
  126.             p.accept(rows);
  127.         }catch (CommonException e){
  128.             rows.clear();
  129.             msgList.add(e.getMessage());
  130.         }
  131.     }
  132. }

1.3CustomMergeStrategy 合并策略(已改)

  1. /**
  2.  * 自定义单元格合并策略
  3.  * @author pw
  4.  */
  5. public class CustomMergeStrategy implements RowWriteHandler {
  6.     /**
  7.      * 主键下标
  8.      */
  9.     private Integer pkIndex;
  10.  
  11.     /**
  12.      * 需要合并的列的下标集合
  13.      */
  14.     private final List<Integer> needMergeColumnIndex = new ArrayList<>();
  15.  
  16.     /**
  17.      * DTO数据类型
  18.      */
  19.     private final Class<?> elementType;
  20.  
  21.     public CustomMergeStrategy(Class<?> elementType) {
  22.         this.elementType = elementType;
  23.     }
  24.  
  25.     @Override
  26.     public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {
  27.  
  28.     }
  29.  
  30.     @Override
  31.     public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {
  32.  
  33.     }
  34.  
  35.     @Override
  36.     public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
  37.         // 如果是标题,则直接返回
  38.         if (isHead) {
  39.             return;
  40.         }
  41.         // 获取当前sheet
  42.         Sheet sheet = writeSheetHolder.getSheet();
  43.         if (null == pkIndex) {
  44.             this.lazyInit(writeSheetHolder);
  45.         }
  46.         // 判断是否需要和上一行进行合并
  47.         // 不能和标题合并，只能数据行之间合并
  48.         int rowNum = row.getRowNum();
  49.         if (rowNum <= 1) {
  50.             return;
  51.         }
  52.         // 获取上一行数据
  53.         Row lastRow = sheet.getRow(row.getRowNum() - 1);
  54.         // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
  55.         if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
  56.             boolean isMerged = false;
  57.             for (Integer needMerIndex : needMergeColumnIndex) {
  58.                 //获得合并的区域
  59.                 List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
  60.                 for (int i = 0; i < mergeRegions.size(); i++) {
  61.                     CellRangeAddress cellRangeAddress = mergeRegions.get(i);
  62.                     // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
  63.                     if (cellRangeAddress.isInRange(rowNum - 1, needMerIndex)) {
  64.                         sheet.removeMergedRegion(i);
  65.                         cellRangeAddress.setLastRow(rowNum);
  66.                         sheet.addMergedRegionUnsafe(cellRangeAddress);
  67.                         isMerged = true;
  68.                     }
  69.                 }
  70.                 if (!isMerged) {
  71.                     CellRangeAddress cellRangeAddress = new CellRangeAddress(rowNum - 1, row.getRowNum(),
  72.                             needMerIndex, needMerIndex);
  73.                     sheet.addMergedRegionUnsafe(cellRangeAddress);
  74.                 }
  75.             }
  76.         }
  77.     }
  78.     /**
  79.      * 初始化主键下标和需要合并字段的下标
  80.      */
  81.     private void lazyInit(WriteSheetHolder writeSheetHolder) {
  82.  
  83.         // 获取当前sheet
  84.         Sheet sheet = writeSheetHolder.getSheet();
  85.  
  86.         // 获取标题行
  87.         Row titleRow = sheet.getRow(0);
  88.  
  89.         // 获取DTO所有的属性
  90.         Field[] fields = this.elementType.getDeclaredFields();
  91.  
  92.         // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
  93.         for (Field theField : fields) {
  94.             // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
  95.             ExcelProperty easyExcelAnn = theField.getAnnotation(ExcelProperty.class);
  96.             // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
  97.             if (null == easyExcelAnn) {
  98.                 continue;
  99.             }
  100.             // 获取自定义的注解，用于合并单元格
  101.             CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);
  102.  
  103.             // 没有@CustomMerge注解的默认不合并
  104.             if (null == customMerge) {
  105.                 continue;
  106.             }
  107.             for (int index = 0; index < fields.length; index++) {
  108.                 Cell theCell = titleRow.getCell(index);
  109.                 // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
  110.                 if (null == theCell) {
  111.                     continue;
  112.                 }
  113.                 // 将字段和excel的表头匹配上
  114.                 if (easyExcelAnn.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
  115.                     if (customMerge.isPk()) {
  116.                         pkIndex = index;
  117.                     }
  118.                     if (customMerge.needMerge()) {
  119.                         needMergeColumnIndex.add(index);
  120.                     }
  121.                 }
  122.             }
  123.         }
  124.         // 没有指定主键，则异常
  125.         if (null == this.pkIndex) {
  126.             throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
  127.         }
  128.  
  129.     }
  130. }

1.4 DownHandler 下拉处理(已解决大数据问题)

  1. import com.alibaba.excel.write.handler.SheetWriteHandler;
  2. import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
  3. import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
  4. import com.cloud.common.core.excel.annotation.DropDown;
  5. import org.apache.poi.ss.usermodel.*;
  6. import org.apache.poi.ss.util.CellRangeAddressList;
  7. import org.apache.poi.xssf.usermodel.XSSFDataValidation;
  8.  
  9. import java.lang.reflect.Field;
  10. import java.util.Map;
  11.  
  12. /**
  13.  * @author admin
  14.  * @date 2020/5/17
  15.  * @desc
  16.  */
  17. public class DownHandler implements SheetWriteHandler {
  18.  
  19.     private final Map<Integer, String[]> dropDownMap;
  20.  
  21.     private int index;
  22.  
  23.     public DownHandler(Map<Integer, String[]> dropDownMap) {
  24.         this.dropDownMap = dropDownMap;
  25.         this.index = 0;
  26.     }
  27.  
  28.     @Override
  29.     public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  30.     }
  31.  
  32.     @Override
  33.     public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  34.         Sheet sheet = writeSheetHolder.getSheet();
  35.         ///开始设置下拉框 HSSFWorkbook
  36.         DataValidationHelper helper = sheet.getDataValidationHelper();
  37.         Field[] fields = writeWorkbookHolder.getClazz().getDeclaredFields();
  38.         int length = fields.length;
  39.         for (int i = 0; i < length; i++) {
  40.            if(fields[i].isAnnotationPresent(DropDown.class)){
  41.                dropDown(helper, sheet, i, fields[i].getDeclaredAnnotation(DropDown.class).value());
  42.            }
  43.         }
  44.         if (dropDownMap == null) {
  45.             return;
  46.         }
  47.         Workbook workbook = writeWorkbookHolder.getWorkbook();
  48.         dropDownMap.forEach((celIndex, value) -> {
  49.             if(value.length>20){
  50.                 dropDownBigData( helper, workbook ,sheet,celIndex, value);
  51.             }else {
  52.                 dropDown(helper, sheet,celIndex, value);
  53.             }
  54.         });
  55.     }
  56.  
  57.     private void dropDown(DataValidationHelper helper, Sheet sheet, Integer celIndex, String[] value) {
  58.         if(null== value || value.length<=0){
  59.             return;
  60.         }
  61.         this.dropDown(helper, sheet,celIndex, helper.createExplicitListConstraint(value));
  62.     }
  63.  
  64.     private void dropDownBigData(DataValidationHelper helper,Workbook workbook,Sheet sheet, Integer celIndex, String[] v) {
  65.         // 定义sheet的名称
  66.         String sheetName = "sheet" + celIndex;
  67.         // 1.创建一个隐藏的sheet 名称为 proviceSheet
  68.         Sheet sheet1 = workbook.createSheet(sheetName);
  69.         // 从第二个工作簿开始隐藏
  70.         this.index++;
  71.         // 设置隐藏
  72.         workbook.setSheetHidden(this.index, true);
  73.         // 2.循环赋值
  74.         for (int i = 0, length = v.length; i < length; i++) {
  75.             // i:表示你开始的行数 0表示你开始的列数
  76.             sheet1.createRow(i).createCell(0).setCellValue(v[i]);
  77.         }
  78.         Name name = workbook.createName();
  79.         name.setNameName(sheetName);
  80.         //代表 以A列1行开始获取N行下拉数据
  81.         name.setRefersToFormula(sheetName + "!$A$1:$A$" + (v.length));
  82.         // 设置下拉
  83.         this.dropDown(helper, sheet,celIndex,  helper.createFormulaListConstraint(sheetName));
  84.     }
  85.  
  86.     private void dropDown(DataValidationHelper helper, Sheet sheet, Integer celIndex, DataValidationConstraint constraint) {
  87.         // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
  88.         CellRangeAddressList addressList = new CellRangeAddressList(1, 100, celIndex, celIndex);
  89.         // 数据有效性对象
  90.         DataValidation dataValidation = helper.createValidation(constraint, addressList);
  91.         // 处理Excel兼容性问题
  92.         if (dataValidation instanceof XSSFDataValidation) {
  93.             //数据校验
  94.             dataValidation.setSuppressDropDownArrow(true);
  95.             //错误提示
  96.             dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
  97.             dataValidation.createErrorBox("提示", "此值与单元格定义数据不一致");
  98.             dataValidation.setShowErrorBox(true);
  99.             //选定提示
  100.             dataValidation.createPromptBox("填写说明：","填写内容只能为下拉中数据，其他数据将导致导入失败");
  101.             dataValidation.setShowPromptBox(true);
  102.             sheet.addValidationData(dataValidation);
  103.         } else {
  104.             dataValidation.setSuppressDropDownArrow(false);
  105.         }
  106.         sheet.addValidationData(dataValidation);
  107.     }
  108. }

1.5 CustomMerge 合并单元格的注解

  1. /**
  2.  * 自定义注解，用于判断是否需要合并以及合并的主键
  3.  * @author pw
  4.  */
  5. @Target({ElementType.FIELD})
  6. @Retention(RetentionPolicy.RUNTIME)
  7. @Inherited
  8. public @interface CustomMerge {
  9.  
  10.     /**
  11.      * 是否需要合并单元格
  12.      */
  13.     boolean needMerge() default false;
  14.  
  15.     /**
  16.      * 是否是主键,即该字段相同的行合并
  17.      */
  18.     boolean isPk() default false;
  19. }

1.6 FieldDispose 导入自定义解析注解

  1. /**
  2.  * 数据来源解析
  3.  * @author pw
  4.  */
  5. @Retention(RetentionPolicy.RUNTIME)
  6. @Target({ ElementType.FIELD})
  7. @Documented
  8. public @interface FieldDispose {
  9.     /**
  10.      * 解析类型
  11.      */
  12.     AnalyticalType type() default AnalyticalType.DICT;
  13.     /**
  14.      *绑定的数据，类型dict对应字典(传入的map),其他类型对应实体类字段
  15.      */
  16.     String[] value();
  17.     /**
  18.      * 错误提示
  19.      */
  20.     String errorMessage() default "";
  21.     /**
  22.      *仅拼接下使用，拼接间隔符，数组第几位即代表第几位后添加
  23.      */
  24.     String[] mark() default {};
  25. }

1.7 DropDown 下拉注解

  1. /**
  2.  * @author pw
  3.  * @date 2022/11/17
  4.  */
  5. @Documented
  6. @Retention(RetentionPolicy.RUNTIME)
  7. @Target({ElementType.FIELD})
  8. public @interface DropDown {
  9.     String[] value();
  10. }

1.8 ExcelFieldType自定义数据解析常量

  1. /**
  2.  * @author pw
  3.  */
  4.  
  5. public enum ExcelFieldType{
  6.     /**
  7.      * 字典
  8.      */
  9.     DICT ,
  10. /**
  11.      * 唯一值
  12.      */
  13.     UNIQUE
  14. }

1.9 标题合并

  1. import com.alibaba.excel.annotation.ExcelIgnore;
  2. import com.alibaba.excel.write.handler.SheetWriteHandler;
  3. import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
  4. import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
  5. import lombok.var;
  6. import org.apache.poi.ss.usermodel.*;
  7. import org.apache.poi.ss.util.CellRangeAddress;
  8.  
  9. import java.lang.reflect.Field;
  10. import java.util.Set;
  11.  
  12. /**
  13.  * @description: 表格标题处理
  14.  * @author: pw
  15.  * @date: 2023/3/2 17:00
  16.  **/
  17. public class CustomTitleWriteHandler implements SheetWriteHandler {
  18.  
  19.     /**
  20.      * 标题
  21.      */
  22.     private final String fileName;
  23.  
  24.     /**
  25.      * DTO数据类型
  26.      */
  27.     private final Class<?> elementType;
  28.  
  29.     /**
  30.      * 过滤的字段
  31.      */
  32.     private final Set<String> set;
  33.     public CustomTitleWriteHandler(Class<?> elementType, String fileName, Set<String> set) {
  34.         this.fileName = fileName;
  35.         this.elementType = elementType;
  36.         this.set = set;
  37.     }
  38.  
  39.     @Override
  40.     public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  41.  
  42.         Workbook workbook = writeWorkbookHolder.getWorkbook();
  43.         Sheet sheet = workbook.getSheetAt(0);
  44.         Row row1 = sheet.createRow(0);
  45.         row1.setHeight((short) 800);
  46.         Cell cell = row1.createCell(0);
  47.         //设置标题
  48.         cell.setCellValue(fileName);
  49.         CellStyle cellStyle = workbook.createCellStyle();
  50.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  51.         cellStyle.setAlignment(HorizontalAlignment.CENTER);
  52.         Font font = workbook.createFont();
  53.         font.setBold(true);
  54.         font.setFontHeight((short) 400);
  55.         font.setFontName("宋体");
  56.         cellStyle.setFont(font);
  57.         cell.setCellStyle(cellStyle);
  58.         // 获取clazz所有的属性
  59.         Field[] fields = this.elementType.getDeclaredFields();
  60.         //计算合并的长度
  61.         int mergeLength = fields.length - 1 - (null != set ? set.size() : 0);
  62.         //过滤注解屏蔽字段
  63.         for(Field f:fields){
  64.             if(f.isAnnotationPresent(ExcelIgnore.class)){
  65.                 mergeLength--;
  66.             }
  67.         }
  68.         sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, mergeLength));
  69.     }
  70. }

2 .使用方法

2.1 导出导入实体--注解使用

  1. /**
  2.  * @author pw
  3.  */
  4. @Data
  5. @ApiModel(value = "用户信息")
  6. @ColumnWidth(20)
  7. public class UserModel implements Serializable {
  8.  
  9. 	@ApiModelProperty(value = "登录账号")
  10. 	@ExcelProperty(value = "登录账号",index=0)
  11. 	@FieldDispose(type = ExcelFieldType.UNIQUE,value = "username",errorMessage = "登录账号不允许重复" )
  12. 	private String username;
  13.  
  14. 	@ApiModelProperty(value = "姓名")
  15. 	@ExcelProperty(value = "姓名",index=1)
  16. 	@FieldDispose(type = ExcelFieldType.UNIQUE,value ="nickName",errorMessage = "姓名不允许重复" )
  17. 	private String nickName;
  18.  
  19. 	@ApiModelProperty(value = "性别")
  20. 	@ExcelProperty(value = "性别",index=2)
  21. 	@FieldDispose(value = DictCodeConstants.SEX,errorMessage = "该性别标识不存在" )
  22. 	private String sex;
  23.  
  24. 	@ApiModelProperty(value = "手机号")
  25. 	@ExcelProperty(value = "手机号",index=3)
  26. 	private String phone;
  27.  
  28. 	@ApiModelProperty(value = "密码")
  29. 	@ExcelProperty(value = "初始密码",index = 4)
  30. 	private String password;
  31.  
  32. 	@ApiModelProperty(value = "职务")
  33. 	@ExcelProperty(value = "职务",index = 5)
  34. 	@FieldDispose(value = DictCodeConstants.USER_POST,errorMessage = "该职务标识不存在")
  35. 	private String post;
  36.  
  37. 	@ApiModelProperty(value = "角色")
  38. 	@ExcelProperty(value ="角色",index = 6)
  39. 	@FieldDispose(value = "role",errorMessage = "该角色标识不存在")
  40. 	private String role;
  41.  
  42. 	@ApiModelProperty("状态标识")
  43. 	@ExcelProperty(value = "状态标识",index = 7)
  44. 	@FieldDispose(value = DictCodeConstants.F_STATE,errorMessage = "该状态标识不存在")
  45. 	private String fstate;
  46.  
  47. 	@ApiModelProperty("备注")
  48. 	@ExcelProperty(value = "备注",index = 8)
  49. 	private String remarks;
  50. }

2.2 Controller

  1. 	@ApiOperation(value = "导入数据")
  2. 	@PostMapping("/importData")
  3. 	public R<Object> importData(@RequestParam("file") MultipartFile file) throws IOException {
  4. 		return userService.importData(file);
  5. 	}
  6.  
  7. 	@ApiOperation(value = "模板下载和导出数据")
  8. 	@GetMapping("/exportData")
  9. 	public void exportData(HttpServletResponse response, UserDTO dto, @RequestParam("type") int type) throws Exception {
  10. 		userService.exportData(response,dto,type);
  11. 	}

2.3 service 调用

  1.     @Override
  2.     public R<Object> importData(MultipartFile file) throws IOException {
  3.         //填充对应词典
  4.         Map<String,Map<String,Object>> map = new HashMap<>();
  5.         map.put(DictCodeConstants.SEX,this.getDictItemKey(DictCodeConstants.SEX));
  6.         map.put(DictCodeConstants.USER_POST,this.getDictItemKey(DictCodeConstants.USER_POST));
  7.         map.put(DictCodeConstants.USER_CLASSIFY,this.getDictItemKey(DictCodeConstants.USER_CLASSIFY));
  8.         map.put(DictCodeConstants.F_STATE,this.getDictItemKey(DictCodeConstants.F_STATE));
  9.         map.put("role",sysRoleService.getRoleNameMap());
  10.         //解析器
  11.         CommonListener<UserModel> commonListener = new CommonListener<>(map, UserModel.class,this::excelBatch);
  12.         EasyExcel.read(file.getInputStream()).head(UserModel.class).registerReadListener(commonListener).sheet().doRead();
  13.         List<String> msgList = commonListener.getMsgList();
  14.         if(msgList.size()>0){
  15.             return R.failed(StringUtils.join(msgList,","));
  16.         }
  17.         return R.ok();
  18.     }
  19.  
  20.     @Override
  21.     public void exportData(HttpServletResponse response, UserDTO dto, int type) throws Exception {
  22.         List<UserModel> list = new ArrayList<>();
  23.         Map<Integer,String[]> dropDownMap = new HashMap<>(4);
  24.         if(type == 1) {
  25.             //查询需导出数据
  26.             list = this.baseMapper.getExcelList(dto);
  27.         }else {
  28.             //填充下拉框
  29.             dropDownMap.put(2,this.getDictItem(DictCodeConstants.SEX));
  30.             dropDownMap.put(5,this.getDictItem(DictCodeConstants.USER_POST));
  31.             dropDownMap.put(6,sysRoleService.getRoleName());
  32.             dropDownMap.put(7,this.getDictItem(DictCodeConstants.USER_CLASSIFY));
  33.             dropDownMap.put(8,this.getDictItem(DictCodeConstants.F_STATE));
  34.         }
  35.         ExcelUtil.writeExcel(response,list, String.valueOf(System.currentTimeMillis()), "科室人员", dropDownMap, UserModel.class);
  36.     }
  37.    /**
  38.      * 获取下拉框字典
  39.      * @param type 字典code
  40.      * @return  String[]
  41.      */
  42.     private String[] getDictItem(String type){
  43.   List<SysDictItem> dictByType = DictUtil.getDictItemList(type);
  44.       //  List<SysDictItem> dictByType = dictItemService.getDictByType(type);
  45.         if(null!=dictByType){
  46.            return dictByType.stream().map(SysDictItem::getDictItemName).toArray(String[]::new);
  47.         }
  48.         return null;
  49.     }
  50.     
  51.     /**
  52.      * 获取字典 -name，key
  53.      * @param type 字典code
  54.      * @return  List<String>
  55.      */
  56.     private Map<String,Object> getDictItemKey(String type){
  57. //优化后的接口 DictUtil 字典工具类，其它文章里https://blog.csdn.net/weixin_42653892/article/details/126827174
  58.   return DictUtil.getDictItemNameMap(type);
  59.       //  List<SysDictItem> dictByType = dictItemService.getDictByType(type);
  60.       //  if(null!=dictByType){
  61.          //  return //dictByType.stream().collect(Collectors.toMap(SysDictItem::getDictItemName, SysDictItem::getDictItemCode));
  62.   //      }
  63.   //      return new HashMap<>(4);
  64.     }

2.4 自定义批量方法 

  1.   @Override
  2.     public boolean excelBatch(Collection<UserModel> userList) {
  3.          //数据处理及批量添加
  4.         //返回提示
  5.         String  returnMsg= "";
  6.         if(b.length()>0){
  7.             b.append("表格存在重复数据：");
  8.             returnMsg=b.toString();
  9.         }
  10.         if(repeatData.size()>0){
  11.             returnMsg+="数据库已存在数据：";
  12.             returnMsg+=StringUtils.join(repeatData,",");
  13.         }
  14.         if(returnMsg.length()>0){
  15.             throw new CommonException(returnMsg);
  16.         }
  17.         return true;
  18.     }
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/键盘狂人/article/detail/61066
推荐阅读
相关标签

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

  

闽ICP备14008679号