spring boot 整合 poi 导出excel2

import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.map.LinkedMap;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.util.StringUtils;
import tv.zhongchi.common.enums.*;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @ClassName ExcelExportUtil
 * @Author ZhangRF
 * @CreateDate 2021/03/05
 * @Decription Excel导出工具
 */
@Slf4j
public class ExcelExportUtil {
    /**
     * Excel表格导出
     *
     * @param response    HttpServletResponse对象
     * @param excelData   Excel表格的数据，封装为List<List<String>>
     * @param sheetName   sheet的名字
     * @param fileName    导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<List<String>> excelData,
                                   String sheetName,
                                   String fileName,
                                   int columnWidth) throws IOException {
        XSSFWorkbook workbook = exportExcel(excelData, sheetName, columnWidth);
        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));

        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());

        //关闭workbook
        workbook.close();
    }

    /**
     * 创建Excel表格
     *
     * @param excelData
     * @param sheetName
     * @param columnWidth
     * @return
     */
    public static XSSFWorkbook exportExcel(List<List<String>> excelData,
                                           String sheetName,
                                           int columnWidth) {

        //声明一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        //生成一个表格，设置表格名称
        XSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        if (columnWidth > 0) {
            //设置表格列宽度
            sheet.setDefaultColumnWidth(columnWidth);
        }
        //Chris设置单元格自动换行
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //写入List<List<String>>中的数据
        int rowIndex = 0;
        for (List<String> data : excelData) {
            //创建一个row行，然后自增1
            XSSFRow row = sheet.createRow(rowIndex++);

            //遍历添加本行数据
            for (int i = 0; i < data.size(); i++) {
                //创建一个单元格
                XSSFCell cell = row.createCell(i);
                //Chirs设置单元格样式
                cell.setCellStyle(cellStyle);
                //创建一个内容对象
                XSSFRichTextString text = new XSSFRichTextString(data.get(i));

                //将内容对象的文字内容写入到单元格中
                cell.setCellValue(text);


            }
        }
        if (columnWidth == 0) {
            //列宽自适应
            autoColumnWidth(sheet);
        }

        return workbook;
    }

    /**
     * Excel表格写入磁盘
     *
     * @param path        磁盘路径
     * @param excelData
     * @param sheetName
     * @param fileName
     * @param columnWidth
     */
    public static void exportPathExcel(String path,
                                       List<List<String>> excelData,
                                       String sheetName,
                                       String fileName,
                                       int columnWidth) {
        try {
            XSSFWorkbook workbook = exportExcel(excelData, sheetName, columnWidth);
            //判断是否存在目录. 不存在则创建
            FileUtil.isPathExist(path);
            //输出Excel文件1
            FileOutputStream output = new FileOutputStream(path + fileName);
            workbook.write(output);//写入磁盘
            output.close();
        } catch (Exception e) {
            log.error("===报表写入磁盘失败===" + fileName);
        }

    }

    /**
     * Excel表格导出
     *
     * @param response  HttpServletResponse对象
     * @param list      导出的数据集
     * @param sheetName sheet的名字
     * @param fileName  导出Excel的文件名
     * @param clazz     实体,按照传入的实体字段顺序导出
     * @param <T>
     */
    public static <T> void exportExcel(HttpServletResponse response,
                                       List<?> list,
                                       String sheetName,
                                       String fileName,
                                       Class<T> clazz) throws IOException {
        List<List<String>> excelData = new ArrayList<>();
        List<String> head = exportHeadList(clazz);
        excelData.add(head);

        //导出数据集合
        excelData.addAll(exportDataList(list, clazz));

        exportExcel(response, excelData, sheetName, fileName, 15);
    }

    /**
     * 报表写入磁盘
     *
     * @param path      磁盘路径
     * @param list
     * @param sheetName
     * @param fileName
     * @param clazz
     * @param <T>
     * @throws IOException
     */
    public static <T> void exportPathExcel(String path,
                                           List<?> list,
                                           String sheetName,
                                           String fileName,
                                           Class<T> clazz) throws IOException {
        List<List<String>> excelData = new ArrayList<>();
        List<String> head = exportHeadList(clazz);
        excelData.add(head);

        //导出数据集合
        excelData.addAll(exportDataList(list, clazz));

        exportPathExcel(path, excelData, sheetName, fileName, 15);
    }

    /**
     * Excel表格导出
     *
     * @param response  HttpServletResponse对象
     * @param list      导出的数据集
     * @param sheetName sheet的名字
     * @param fileName  导出Excel的文件名
     * @param clazz     实体,按照传入的实体字段顺序导出
     * @param map       key实体字段参数，value表头值
     * @param <T>
     */
    public static <T> void exportExcel(HttpServletResponse response,
                                       List<?> list,
                                       String sheetName,
                                       String fileName,
                                       Class<T> clazz,
                                       LinkedMap<Object, Object> map) throws IOException {
        List<List<String>> excelData = new ArrayList<>();
        List<String> head = exportHeadTwoList(clazz, map);
        excelData.add(head);

        //导出数据集合
        excelData.addAll(exportDataList(list, clazz, map));

        exportExcel(response, excelData, sheetName, fileName, 15);
    }

    /**
     * Excel表格导出
     *
     * @param response        HttpServletResponse对象
     * @param list            导出的数据集
     * @param sheetName       sheet的名字
     * @param fileName        导出Excel的文件名
     * @param clazz           实体,按照传入的实体字段顺序导出
     * @param map             key实体字段参数，value表头值
     * @param payTypeMoneyMap key实体字段参数，value表数值，报表最后一行数据
     * @param <T>
     */
    public static <T> void exportExcel(HttpServletResponse response,
                                       List<?> list,
                                       String sheetName,
                                       String fileName,
                                       Class<T> clazz,
                                       LinkedMap<Object, Object> map,
                                       Map<Object, Object> payTypeMoneyMap) throws IOException {
        List<List<String>> excelData = new ArrayList<>();
        List<String> head = exportHeadTwoList(clazz, map);
        excelData.add(head);

        //导出数据集合
        excelData.addAll(exportDataList(list, clazz, map, payTypeMoneyMap));

        exportExcel(response, excelData, sheetName, fileName, 15);
    }

    /**
     * 获取表头集合
     *
     * @param clazz 实体,按照传入的实体字段顺序导出
     * @param <T>
     * @return
     */
    public static <T> List<String> exportHeadList(Class<T> clazz) {
        List<String> headList = new ArrayList<>();
        Field[] declaredFields = clazz.getDeclaredFields();
        for (int i = 0; i < declaredFields.length; i++) {
            Field declaredField = declaredFields[i];
            headList.add(declaredField.getAnnotation(ApiModelProperty.class).value());
        }
        return headList;
    }

    /**
     * 获取表头集合
     *
     * @param clazz 实体,按照传入的实体字段顺序导出
     * @param map   key实体字段参数，value表头值
     * @param <T>
     * @return
     */
    public static <T> List<String> exportHeadTwoList(Class<T> clazz, LinkedMap<Object, Object> map) {
        List<String> headList = new ArrayList<>();
        Field[] declaredFields = clazz.getDeclaredFields();
        for (int i = 0; i < declaredFields.length; i++) {
            Field declaredField = declaredFields[i];

            if (declaredField.getName().equals("payTypeIdAndMoneyMap")) {
                //当字段为payTypeIdAndMoneyMap 调用headList
                headList.addAll(exportHeadMap(map));
            } else if (declaredField.getName().equals("projectIdAndMoneyMap")) {
                headList.addAll(exportHeadMap(map));
            } else if (declaredField.getName().equals("map")) {
                headList.addAll(exportHeadMap(map));
            } else {
                headList.add(declaredField.getAnnotation(ApiModelProperty.class).value());
            }
        }
        return headList;
    }

    /**
     * 获取表头集合
     *
     * @param map key实体字段参数，value表头值
     * @return
     */
    public static List<String> exportHeadMap(LinkedMap<Object, Object> map) {
        List<String> headList = new ArrayList<>();
        for (Map.Entry<Object, Object> entity : map.entrySet()) {
            headList.add(entity.getValue().toString());
        }
        return headList;
    }

    /**
     * 解析数据集
     *
     * @param list
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<List<String>> exportDataList(List<?> list, Class<T> clazz) {
        List<List<String>> excelData = new ArrayList<>();
        for (Object obj : list) {
            T t = null;
            try {
                t = clazz.newInstance();
                BeanUtils.copyProperties(obj, t);
            } catch (Exception e) {
                continue;
            }

            //数据拼装
            List<String> dataList = new ArrayList<>();
            for (Field declaredField : t.getClass().getDeclaredFields()) {
                declaredField.setAccessible(true);
                dataList.add(getData(declaredField, t));
            }
            excelData.add(dataList);
        }

        return excelData;
    }

    /**
     * 解析数据集
     *
     * @param list
     * @param clazz
     * @param map   key实体字段参数(表头)，value表头值
     * @param <T>
     * @return
     */
    public static <T> List<List<String>> exportDataList(List<?> list, Class<T> clazz, LinkedMap<Object, Object> map) {
        List<List<String>> excelData = new ArrayList<>();
        for (Object obj : list) {
            T t = null;
            try {
                t = clazz.newInstance();
                BeanUtils.copyProperties(obj, t);
            } catch (Exception e) {
                continue;
            }

            //数据拼装
            List<String> dataList = new ArrayList<>();
            for (Field declaredField : t.getClass().getDeclaredFields()) {
                declaredField.setAccessible(true);

                String data = "";
                try {
                    if (declaredField.getName().equals("payTypeIdAndMoneyMap")) {
                        Map<Object, Object> payTypeMoneyMap = (Map<Object, Object>) declaredField.get(t);
                        //map 表头，payTypeIdAndMoneyMap value表头值
                        dataList.addAll(exportDataMap(map, payTypeMoneyMap));
                    } else if (declaredField.getName().equals("projectIdAndMoneyMap")) {
                        Map<Object, Object> projectIdAndMoneyMap = (Map<Object, Object>) declaredField.get(t);
                        dataList.addAll(exportDataMap(map, projectIdAndMoneyMap));
                    } else if (declaredField.getName().equals("map")) {
                        Map<Object, Object> dataMap = (Map<Object, Object>) declaredField.get(t);
                        dataList.addAll(exportDataMap(map, dataMap));
                    } else {
                        dataList.add(getData(declaredField, t));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    data = "";
                }
            }
            excelData.add(dataList);
        }

        return excelData;
    }

    /**
     * 解析数据集
     *
     * @param list
     * @param clazz
     * @param map
     * @param payTypeMoneyMap
     * @param <T>
     * @return
     */
    public static <T> List<List<String>> exportDataList(List<?> list, Class<T> clazz, LinkedMap<Object, Object> map,
                                                        Map<Object, Object> payTypeMoneyMap) {
        List<List<String>> excelData = new ArrayList<>();
        for (Object obj : list) {
            T t = null;
            try {
                t = clazz.newInstance();
                BeanUtils.copyProperties(obj, t);
            } catch (Exception e) {
                continue;
            }

            //数据拼装
            List<String> dataList = new ArrayList<>();
            for (Field declaredField : t.getClass().getDeclaredFields()) {
                declaredField.setAccessible(true);

                String data = "";
                try {
                    data = getData(declaredField, t);

                    if (StringUtils.isEmpty(data)) {
                        data = "";
                    }

                    dataList.add(data);
                } catch (Exception e) {
                    e.printStackTrace();
                    data = "";
                }
            }
            excelData.add(dataList);
        }

        return excelData;
    }

    /**
     * LinkedMap<Object, Object> 根据map的key值来确定传入的value值，不存在的都赋值0
     *
     * @param key   传入map集合中的key
     * @param value 导出的数值
     * @param map   LinkedMap<Object, Object> 根据map的key值来确定传入的value值，不存在的都赋值0
     * @return
     */
    public static List<String> exportDataMap(Object key, Object value, LinkedMap<Object, Object> map) {
        List<String> dataList = new ArrayList<>();
        for (Map.Entry<Object, Object> entity : map.entrySet()) {
            if (entity.getKey().equals(key)) {
                dataList.add(value.toString());
            } else {
                dataList.add(String.valueOf(0));
            }
        }
        return dataList;
    }


    /**
     * LinkedMap<Object, Object> 根据map的key值来确定传入的payTypeMoneyMap value值，不存在的都赋值0
     *
     * @param map             表头
     * @param payTypeMoneyMap 表头值
     * @return
     */
    public static List<String> exportDataMap(LinkedMap<Object, Object> map, Map<Object, Object> payTypeMoneyMap) {
        List<String> dataList = new ArrayList<>();
        for (Map.Entry<Object, Object> entity : map.entrySet()) {
            Object paidMoney = payTypeMoneyMap.get(entity.getKey());
            if (!StringUtils.isEmpty(paidMoney)) {
                dataList.add(paidMoney.toString());
            } else {
                dataList.add(String.valueOf(0));
            }
        }
        return dataList;
    }

    /**
     * 解码数据
     *
     * @param declaredField 字段
     * @param t             实体数据
     * @param <T>
     * @return
     */
    private static <T> String getData(Field declaredField, T t) {
        String data = "";
        try {
            if (!StringUtils.isEmpty(declaredField.get(t))) {
                String typeName = declaredField.getType().getName();
                String name = declaredField.getName();

                if (name.equals("businessType")) {
                    Integer businessType = (Integer) declaredField.get(t);
                    BusinessTypeEnum businessTypeEnum = BusinessTypeEnum.parse(businessType);
                    if (businessTypeEnum == null) {
                        data = "";
                    } else {
                        data = businessTypeEnum.getValue();
                    }
                } else if (name.equals("grade")) {
                    Integer grade = (Integer) declaredField.get(t);
                    GradeEnum gradeEnum = GradeEnum.parse(grade);
                    if (gradeEnum == null) {
                        data = "临时";
                    } else {
                        data = gradeEnum.getValue();
                    }
                } else if (name.equals("isFirstVisit")) {
                    Integer isFirstVisit = (Integer) declaredField.get(t);
                    IsFirstVisitEnum isFirstVisitEnum = IsFirstVisitEnum.parse(isFirstVisit);
                    if (StringUtils.isEmpty(isFirstVisitEnum)) {
                        data = "初诊";
                    } else if (isFirstVisit.equals(IsFirstVisitEnum.YES.getKey())) {
                        data = "初诊";
                    } else if (isFirstVisit.equals(IsFirstVisitEnum.NO.getKey())) {
                        data = "复诊";
                    } else {
                        data = "初诊";
                    }
                } else if (name.equals("sex")) {
                    Integer sex = (Integer) declaredField.get(t);
                    if (StringUtils.isEmpty(sex)) {
                        data = "";
                    } else if (sex.equals(1)) {
                        data = "男";
                    } else if (sex.equals(2)) {
                        data = "女";
                    }
                } else if (name.equals("lossStatus")) {
                    Integer lossStatus = (Integer) declaredField.get(t);
                    LossStatusEnum lossStatusEnum = LossStatusEnum.parse(lossStatus);
                    if (lossStatusEnum == null) {
                        data = "流失";
                    } else {
                        data = lossStatusEnum.getValue();
                    }
                } else if (name.equals("workStatus")) {
                    Integer workStatus = (Integer) declaredField.get(t);
                    WorkStatusEnum workStatusEnum = WorkStatusEnum.parse(workStatus);
                    if (workStatusEnum == null) {
                        data = "";
                    } else {
                        data = workStatusEnum.getValue();
                    }
                } else {
                    if (typeName.equals("java.util.Date")) {
                        data = DateUtil.GetFormatTime((Date) declaredField.get(t), DateUtil.YEAR_MONTH_DAY_HOUR_MINUTE_SECOND);
                    } else {
                        data = declaredField.get(t).toString();
                    }
                }
            }
            if (StringUtils.isEmpty(data)) {
                data = "";
            }
        } catch (Exception e) {
            e.printStackTrace();
            data = "";
        }

        return data;
    }

    /**
     * 列宽自适应
     *
     * @param sheet
     * @return
     */
    public static void autoColumnWidth(XSSFSheet sheet) {
        int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
        for (int i = 0; i < maxColumn; i++) {
            sheet.autoSizeColumn(i);
        }
        for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    try {
                        int length = currentCell.toString().getBytes("GBK").length;
                        if (columnWidth < length + 1) {
                            columnWidth = length + 8;
                        }
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
            int colWidth = sheet.getColumnWidth(columnNum) * 2;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(columnNum, colWidth < 3000 ? 3000 : colWidth);
            } else {
                sheet.setColumnWidth(columnNum, 6000);
            }
            //  sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }