添加excel导出的功能

zhaoxiaohao

2020-12-28 861200b968f21a748aa322635f55e48e79dafb1e

添加excel导出的功能

 kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/ExcelUtils.java

New file
@@ -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);
    }
}

 kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/BackPatientOrderParam.java

New file
@@ -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;
}

 kidgrow-commons/kidgrow-common-spring-boot-starter/src/main/java/com/kidgrow/common/utils/excelModel/ExportField.java

New file
@@ -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;
}

 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)

 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>