From 861200b968f21a748aa322635f55e48e79dafb1e Mon Sep 17 00:00:00 2001
From: zhaoxiaohao <279049017@qq.com>
Date: Mon, 28 Dec 2020 14:10:46 +0800
Subject: [PATCH] 添加excel导出的功能
---
kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/BackPatientOrderParam.java | 20 +++
kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/ExportField.java | 30 +++++
kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/ExcelUtils.java | 290 ++++++++++++++++++++++++++++++++++++++++++++++++
kidgrow-web/kidgrow-web-manager/src/main/resources/static/module/apiUrl.js | 2
pom.xml | 11 +
5 files changed, 352 insertions(+), 1 deletions(-)
diff --git a/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/ExcelUtils.java b/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/ExcelUtils.java
new file mode 100644
index 0000000..c6c572b
--- /dev/null
+++ b/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/ExcelUtils.java
@@ -0,0 +1,290 @@
+package com.kidgrow.common.utils;
+
+import cn.afterturn.easypoi.excel.ExcelExportUtil;
+import cn.afterturn.easypoi.excel.ExcelImportUtil;
+import cn.afterturn.easypoi.excel.entity.ExportParams;
+import cn.afterturn.easypoi.excel.entity.ImportParams;
+import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
+import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
+import com.google.common.collect.Lists;
+import com.kidgrow.common.utils.excelModel.ExportField;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.springframework.util.CollectionUtils;
+import org.springframework.web.multipart.MultipartFile;
+
+import javax.servlet.http.HttpServletResponse;
+import java.io.File;
+import java.io.IOException;
+import java.io.InputStream;
+import java.lang.reflect.Method;
+import java.net.URLEncoder;
+import java.util.*;
+
+/**
+ * @author : heyanfeng
+ * create at: 2019-05-27 19:11
+ * @description: excel
+ */
+@Slf4j
+public class ExcelUtils {
+ public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
+ ExportParams exportParams = new ExportParams(title, sheetName);
+ exportParams.setCreateHeadRows(isCreateHeader);
+ defaultExport(list, pojoClass, fileName, response, exportParams);
+
+ }
+ public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
+ defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
+ }
+ public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
+ defaultExport(list, fileName, response);
+ }
+ public static void exportExcel(String fileName,ExportParams exportParams, List<ExcelExportEntity> entities,List<Map<String, Object>> list,HttpServletResponse response){
+ defaultExport(fileName,exportParams,entities,list,response);
+ }
+
+ private static void defaultExport(String fileName,ExportParams exportParams, List<ExcelExportEntity> entities,List<Map<String, Object>> list,HttpServletResponse response ){
+ Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entities, list);
+ if (workbook != null){
+ downLoadExcel(fileName, response, workbook);}
+ }
+
+ private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
+ Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
+ if (workbook != null) {
+ downLoadExcel(fileName, response, workbook);
+ }
+ }
+
+ private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
+ try {
+ response.setCharacterEncoding("UTF-8");
+ response.setHeader("content-Type", "application/vnd.ms-excel");
+ response.setHeader("Content-Disposition",
+ "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
+ workbook.write(response.getOutputStream());
+ } catch (IOException e) {
+ log.error("excel异常:{}",e.getMessage());
+ }
+ }
+ private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
+ Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
+ if (workbook != null){
+ downLoadExcel(fileName, response, workbook);}
+ }
+ public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass){
+ ImportParams params = new ImportParams();
+ params.setTitleRows(titleRows);
+ params.setHeadRows(headerRows);
+ List<T> list = null;
+ try {
+ File file = new File(filePath);
+ list = ExcelImportUtil.importExcel(file, pojoClass, params);
+ }catch (NoSuchElementException e){
+ throw new IllegalArgumentException("模板不能为空");
+ } catch (Exception e) {
+ log.error("excel异常:{}",e.getMessage());
+ }
+ return list;
+ }
+
+ public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass){
+ ImportParams params = new ImportParams();
+ params.setTitleRows(titleRows);
+ params.setHeadRows(headerRows);
+ List<T> list = null;
+ try {
+ list = ExcelImportUtil.importExcel(inputStream, pojoClass, params);
+ }catch (NoSuchElementException e){
+ throw new IllegalArgumentException("模板不能为空");
+ } catch (Exception e) {
+ log.error("excel异常:{}",e.getMessage());
+ }
+ return list;
+ }
+ public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
+ if (file == null){
+ return null;
+ }
+ ImportParams params = new ImportParams();
+ params.setTitleRows(titleRows);
+ params.setHeadRows(headerRows);
+ List<T> list = null;
+ try {
+ list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
+ }catch (NoSuchElementException e){
+ throw new IllegalArgumentException("excel文件不能为空");
+ } catch (Exception e) {
+ log.error("excel异常:{}",e.getMessage());
+ }
+ return list;
+ }
+
+
+ /**
+ * description: 动态生成excel 列
+ * create by heyanfeng at 2019-05-29 18:02
+ * @param ef
+ * @param entities
+ */
+ public static void dynamicNewAddExcel(List<ExportField> ef, List<ExcelExportEntity> entities) {
+ //单元格的excel 表头
+ ef.forEach(item -> {
+ //需要合并单元格的表头
+ List<ExportField> children = item.getChildren();
+ if(!CollectionUtils.isEmpty(children)){
+ ExcelExportEntity parent = new ExcelExportEntity(item.getFiledChineseName(), item.getEntityAttrName());
+ List<ExcelExportEntity> entitiesChildren = Lists.newArrayList();
+ children.forEach(e -> {
+ entitiesChildren.add(new ExcelExportEntity(e.getFiledChineseName(),e.getEntityAttrName(),30));
+ });
+ parent.setNeedMerge(true);
+ parent.setList(entitiesChildren);
+ entities.add(parent);
+ }else{
+ entities.add(new ExcelExportEntity(item.getFiledChineseName(),item.getEntityAttrName(),30));
+ }
+ });
+ }
+
+
+ /**
+ * description: 根据属性名称 获取属性的值
+ * create by heyanfeng at 2019-05-29 19:26
+ * @return Object
+ * @param fieldName
+ * @param o
+ */
+ public static Object getFieldValueByName(String fieldName,Object o){
+ try{
+ String firstLetter = fieldName.substring(0, 1).toUpperCase();
+ String getter = "get" + firstLetter + fieldName.substring(1);
+ Method method = o.getClass().getMethod(getter, new Class[] {});
+ Object value = method.invoke(o, new Object[]{});
+ return value;
+ }catch (Exception e){
+ log.error(e.getMessage(),e);
+ return null;
+ }
+ }
+
+ /**
+ * description: 组装excel 数据
+ * create by heyanfeng at 2019-05-29 20:30
+ * @return List<Map<String, Object>>
+ * @param ef
+ * @param statisData
+ */
+ public static List<Map<String, Object>> dynamicListDataByKey(List<ExportField> ef, List<?> statisData) {
+ //最终的数据
+ List<Map<String, Object>> datas = new ArrayList<>();
+ Map map;
+ for (Object t : statisData) {
+ map = new HashMap();
+ for (int j = 0; j < ef.size(); j++) {
+ List<ExportField> children = ef.get(j).getChildren();
+ if(!CollectionUtils.isEmpty(children)){
+ //遍历需要合并单元格的子列
+ traversechildren(map, t, children,ef.get(j).getEntityAttrName());
+ }else if(StringUtils.isNotBlank(ef.get(j).getEntityAttrName())){
+ map.put(ef.get(j).getEntityAttrName(),getFieldValueByName(ef.get(j).getEntityAttrName(),t));
+ }
+ }
+ datas.add(map);
+ }
+ return datas;
+ }
+
+ /**
+ * description: 遍历需要合并单元格的子列
+ * create by heyanfeng at 2019-05-31 14:19
+ */
+ private static void traversechildren(Map map, Object t, List<ExportField> children,String entityAttrName) {
+ ArrayList<Map<String,Object>> childrenMaps = Lists.newArrayList();
+ Map<String,Object> childrenMap= new HashMap();
+ for (int k = 0; k < children.size(); k++) {
+ if(StringUtils.isNotBlank(children.get(k).getEntityAttrName())){
+ childrenMap.put(children.get(k).getEntityAttrName(),getFieldValueByName(children.get(k).getEntityAttrName(),t));
+ }
+ }
+ childrenMaps.add(childrenMap);
+ map.put(entityAttrName,childrenMaps);
+ }
+
+
+ /**
+ * description: 组装sql 查询条件
+ * create by heyanfeng at 2019-05-30 10:09
+ */
+// public static String getExcelSql(List<ExportField> exportField) {
+// String fileds;
+// StringBuilder stringBuilder = new StringBuilder();
+// exportField.forEach(item -> {
+// //如果存在需要合并表头的列 则遍历
+// List<ExportField> children = item.getChildren();
+// if(!CollectionUtils.isEmpty(children)){
+// children.forEach(e -> {
+// if(StringUtils.isNotBlank(e.getFiled())){
+// stringBuilder.append(e.getFiled() + GlobalConstant.PIC_SPLIT);
+// }
+// });
+// }else if(StringUtils.isNotBlank(item.getFiled())){
+// stringBuilder.append(item.getFiled() + GlobalConstant.PIC_SPLIT);
+// }
+// });
+// stringBuilder.deleteCharAt(stringBuilder.lastIndexOf(GlobalConstant.PIC_SPLIT));
+// fileds = stringBuilder.toString();
+// return fileds;
+// }
+
+ /**
+ * description: 组装sql 查询条件
+ * create by heyanfeng at 2019-05-30 10:09
+ */
+// public static String getGroupBySql(List<ExportField> exportField) {
+// String fileds;
+// //是否加入 group by
+// Boolean[] flag = {false};
+// StringBuilder stringBuilder = new StringBuilder();
+// exportField.forEach(item -> {
+// if(item.getGroupBy() == null && StringUtils.isNotBlank(item.getFiled())){
+// stringBuilder.append(item.getFiled() + GlobalConstant.PIC_SPLIT);
+// }else if(item.getGroupBy() != null && item.getGroupBy() == YESNOEnum.YES.getValue()){
+// flag[0] = true;
+// }
+// });
+// stringBuilder.deleteCharAt(stringBuilder.lastIndexOf(GlobalConstant.PIC_SPLIT));
+// fileds = stringBuilder.toString();
+// if (flag[0]){
+// fileds = " group by " + fileds;
+// return fileds;
+// }
+// return "";
+// }
+
+
+ /**
+ * description: 检查导出字段是否为空
+ * create by heyanfeng at 2019-05-30 10:10
+ */
+ public static void checkExportField( List<ExportField> exportField) {
+ if(CollectionUtils.isEmpty(exportField)){
+ throw new IllegalArgumentException("导出的字段不能为空");
+ }
+ }
+
+ /**
+ * description: 动态导出生成excel
+ * create by heyanfeng at 2019-05-30 10:37
+ */
+ public static void DynamicExcel(String fileName, String title,String sheet,List<ExportField> exportField, HttpServletResponse response, List<?> list) {
+ //生成动态列
+ List<ExcelExportEntity> entities = Lists.newArrayList();
+ ExcelUtils.dynamicNewAddExcel(exportField, entities);
+ //组装数据entities
+ List<Map<String, Object>> maps = ExcelUtils.dynamicListDataByKey(exportField, list);
+ ExcelUtils.exportExcel(fileName,new ExportParams(title, sheet),entities,maps,response);
+ }
+}
\ No newline at end of file
diff --git a/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/BackPatientOrderParam.java b/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/BackPatientOrderParam.java
new file mode 100644
index 0000000..4d6eb1e
--- /dev/null
+++ b/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/BackPatientOrderParam.java
@@ -0,0 +1,20 @@
+package com.kidgrow.common.utils.excelModel;
+
+//import io.swagger.annotations.ApiModel;
+//import io.swagger.annotations.ApiModelProperty;
+import lombok.Data;
+
+import java.util.List;
+
+/**
+ * @program family_dr_api
+ * @description: 管理后台患者订单
+ * @author: luqiang
+ * @create: 2019/05/24 19:14
+ */
+//@ApiModel(value = "管理后台患者订单")
+@Data
+public class BackPatientOrderParam {
+// @ApiModelProperty("导出字段,逗号分割")
+ private List<ExportField> exportField;
+}
\ No newline at end of file
diff --git a/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/ExportField.java b/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/ExportField.java
new file mode 100644
index 0000000..e449512
--- /dev/null
+++ b/kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/ExportField.java
@@ -0,0 +1,30 @@
+package com.kidgrow.common.utils.excelModel;
+
+import lombok.Data;
+
+import java.util.List;
+
+/**
+ * @author : heyanfeng
+ * create at: 2019-05-27 22:00
+ * @description:
+ */
+//@ApiModel(value = "管理后台患者订单导出的字段")
+@Data
+public class ExportField {
+
+// @ApiModelProperty("实体映射的字段")
+ private String entityAttrName;
+
+// @ApiModelProperty("导出字段,中文描述")
+ private String filedChineseName;
+
+// @ApiModelProperty("组装sql 字段")
+ private String filed;
+
+// @ApiModelProperty("sql-group-by去除字段：0 否 1是")
+ private Integer groupBy;
+
+// @ApiModelProperty("合并单元格")
+ private List<ExportField> children;
+}
\ No newline at end of file
diff --git a/kidgrow-web/kidgrow-web-manager/src/main/resources/static/module/apiUrl.js b/kidgrow-web/kidgrow-web-manager/src/main/resources/static/module/apiUrl.js
index dbb5291..3c0c6dc 100644
--- a/kidgrow-web/kidgrow-web-manager/src/main/resources/static/module/apiUrl.js
+++ b/kidgrow-web/kidgrow-web-manager/src/main/resources/static/module/apiUrl.js
@@ -1,7 +1,7 @@
var my_api_server_url = 'http://127.0.0.1:8888/';
// var my_api_server_url = 'http://192.168.2.240:8888/';
// var my_api_server_url = 'http://open.zuul.kidgrow.cloud/';
- // var my_api_server_url = 'zuul.kidgrow.cloud/';
+// var my_api_server_url = 'https://zuul.kidgrow.cloud/';
//var my_api_server_url = 'https://zuul.kidgrow.cloud/';
if(my_api_server_url.indexOf("http")<0)
diff --git a/pom.xml b/pom.xml
index b4fcfd1..d991724 100644
--- a/pom.xml
+++ b/pom.xml
@@ -431,11 +431,22 @@
<artifactId>easypoi-base</artifactId>
<version>${easypoi.version}</version>
</dependency>
+<!-- <dependency>-->
+<!-- <groupId>cn.afterturn</groupId>-->
+<!-- <artifactId>easypoi-web</artifactId>-->
+<!-- <version>${easypoi.version}</version>-->
+<!-- </dependency>-->
+ <dependency>
+ <groupId>cn.afterturn</groupId>
+ <artifactId>easypoi-annotation</artifactId>
+ <version>${easypoi.version}</version>
+ </dependency>
<dependency>
<groupId>org.modelmapper</groupId>
<artifactId>modelmapper</artifactId>
<version>${modle-mapper.version}</version>
</dependency>
+
<!-- swagger -->
<dependency>
<groupId>io.springfox</groupId>
--
Gitblit v1.8.0