Excel导出实例--2021/12/20

Integer.MAX_VALUE 为最大值

Vo实体类

peakTimeInfoVo.getLineDirection()==0 ? “上行”:“下行” 表达式

package com.spiov.cloud.schedule.vo;

import cn.iovnet.commons.utils.util.ExcelExportColumn;

/**
 * 高峰时间导出的数据列表
 *
 * @author guozh
 * @date 2021/12/20 11:30
 */
public class ExportPeakTimeVo {

    /**
     * 线路名称
     */
    @ExcelExportColumn(value = "线路名称", index = 1)
    private String lineName;
    /**
     * 线路方向
     */
    @ExcelExportColumn(value = "线路方向", index = 2)
    private String lineDirection;
    /**
     * 早高峰
     */
    @ExcelExportColumn(value = "早高峰", index = 3)
    private String morningTime;
    /**
     * 晚高峰
     */
    @ExcelExportColumn(value = "晚高峰", index = 4)
    private String eveningTime;

    public String getLineName() {
        return lineName;
    }

    public void setLineName(String lineName) {
        this.lineName = lineName;
    }

    public String getLineDirection() {
        return lineDirection;
    }

    public void setLineDirection(String lineDirection) {
        this.lineDirection = lineDirection;
    }

    public String getMorningTime() {
        return morningTime;
    }

    public void setMorningTime(String morningTime) {
        this.morningTime = morningTime;
    }

    public String getEveningTime() {
        return eveningTime;
    }

    public void setEveningTime(String eveningTime) {
        this.eveningTime = eveningTime;
    }
}

自定义注解ExcelExportColumn

@Target({ ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelExportColumn {
    /**
     * 字段名称(导出的中文名称)
     */
    String value() default "";

    int index() default 0;

}

Controller

/**
     * 高峰时间设置-导出
     * @param vo 查询条件
	 * @param request
	 * @param response
     * @return void
     * @author guozh
     * @date 2021/12/20 11:57
     */
    @PostMapping("/exportPeakTimeList")
    public CommonResponse exportPeakTimeList(@RequestBody OneWayTimeQueryVo vo, HttpServletRequest request, HttpServletResponse response){
        CommonResponse instance = CommonResponse.getInstance();
        Integer companyId = Integer.valueOf(HttpUtil.getCompanyId(request));
        Page<OneWayTimeQueryVo> page = new Page<>(1, Integer.MAX_VALUE);
        List<ExportPeakTimeVo> voList = peakTimeService.exportPeakTimeList(companyId, vo, page);
        ExcelUtil.exportExcelFile("高峰时间.xls",voList,ExportPeakTimeVo.class,response);
        IovnetLog log = IovnetLogUtil.getIovnetLog(request, DdyModuleNames.SYSTEM_SETUP_ID, DdyModuleNames.SYSTEM_SETUP_NAME, DdyModuleNames.PEAK_TIME_ID, DdyModuleNames.PEAK_TIME_NAME, "高峰时间设置-导出");
        instance.setIovnetLog(log);
        return instance;
    }

Service

/**
     * 高峰时间设置-导出
     * @param companyId 公司id
	 * @param vo 查询条件
	 * @param page 分页
     * @return java.util.List<com.spiov.cloud.schedule.vo.ExportPeakTimeVo>
     * @author guozh
     * @date 2021/12/20 11:58
     */
    List<ExportPeakTimeVo> exportPeakTimeList(Integer companyId, OneWayTimeQueryVo vo, Page<OneWayTimeQueryVo> page);

ServiceImpl

@Override
    public List<ExportPeakTimeVo> exportPeakTimeList(Integer companyId, OneWayTimeQueryVo vo, Page<OneWayTimeQueryVo> page) {
        List<PeakTimeInfoVo> peakTimeInfoList = getPeakTimeList(companyId, vo, page);
        List<ExportPeakTimeVo> voList = new ArrayList<>();
        for (PeakTimeInfoVo peakTimeInfoVo : peakTimeInfoList) {
            ExportPeakTimeVo exportPeakTimeVo = new ExportPeakTimeVo();
            exportPeakTimeVo.setLineName(peakTimeInfoVo.getLineName());
            exportPeakTimeVo.setLineDirection(peakTimeInfoVo.getLineDirection()==0 ? "上行":"下行");
            String morningBeginTime = DateUtil.DateToString(peakTimeInfoVo.getMorningBeginTime(), "HH:mm");
            String morningEndTime = DateUtil.DateToString(peakTimeInfoVo.getMorningEndTime(), "HH:mm");
            String eveningBeginTime = DateUtil.DateToString(peakTimeInfoVo.getEveningBeginTime(), "HH:mm");
            String eveningEndTime = DateUtil.DateToString(peakTimeInfoVo.getEveningEndTime(), "HH:mm");
            exportPeakTimeVo.setMorningTime(morningBeginTime + "-" + morningEndTime);
            exportPeakTimeVo.setEveningTime(eveningBeginTime + "-" + eveningEndTime);
            voList.add(exportPeakTimeVo);
        }
        return voList;
    }

测试

用到的exportExcelFile 方法

/**
     * 通用导出excel文件(在需要导出的字段上加注解ExcelExportColumn)
     * @author duyq
     * @date 2021/10/09 15:21
     * @param fileName 文件名称(自定义带后缀名.xls/.xlsx)
     * @param dataList 需要导出的数据
     * @param clazz 需要导出的数据对象
     * @return void
     */
    public static <T> void exportExcelFile(String fileName, List<T> dataList, Class<T> clazz, HttpServletResponse response) {
        // 创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        // 创建表头行
        HSSFRow excelRoot = sheet.createRow(0);
        // 设置表头样式
        HSSFCellStyle headStyle = setExcelHead(workbook);
        // 设置表头
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields).filter(field -> {
            ExcelExportColumn annotation = field.getAnnotation(ExcelExportColumn.class);
            if (annotation != null && annotation.index() > 0) {
                field.setAccessible(true);
                return true;
            }
            return false;
        }).sorted(Comparator.comparing(field -> {
            int index = 0;
            ExcelExportColumn annotation = field.getAnnotation(ExcelExportColumn.class);
            if (annotation != null) {
                index = annotation.index();
            }
            return index;
        })).collect(Collectors.toList());
        for (int i = 0; i < fieldList.size(); i++) {
            ExcelExportColumn annotation = fieldList.get(i).getAnnotation(ExcelExportColumn.class);
            String columnName = "";
            if (annotation != null) {
                columnName = annotation.value();
            }
            Cell cell = excelRoot.createCell(i);
            //设置每列宽度
            sheet.setColumnWidth(i, 5800);
            cell.setCellStyle(headStyle);
            cell.setCellValue(columnName);
        }
        // 设置行内容
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        if (dataList != null && !dataList.isEmpty()) {
            for (int j = 0; j < dataList.size(); j++) {
                HSSFRow row = sheet.createRow(j + 1);
                for (int k = 0; k < fieldList.size(); k++) {
                    Object value = "";
                    try {
                        // 将单词的首字母大写
                        String initStr = fieldList.get(k).getName().substring(0, 1).toUpperCase() + fieldList.get(k).getName().substring(1);
                        value = dataList.get(j).getClass().getMethod("get" + initStr).invoke(dataList.get(j));
                    } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
                        e.printStackTrace();
                    }
                    HSSFCell cell = row.createCell(k);
                    if (value != null) {
                        HSSFDataFormat df = workbook.createDataFormat();
                        if (value instanceof Integer) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(Integer.parseInt(value.toString()));
                        } else if (value instanceof BigDecimal) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cellStyle.setDataFormat(df.getFormat("#,##0.00"));
                            cell.setCellValue(Double.parseDouble(value.toString()));
                        } else if (value instanceof Date) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cellStyle.setDataFormat(df.getFormat("yyyy-MM-dd"));
                            cell.setCellValue(value.toString());
                        } else {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellStyle(cellStyle);
                    }
                }
            }
        }
        OutputStream outputStream = null;
        try {
            String name = new String((fileName + ".xls").getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            //设置response, 打开保存页面
            response.reset();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + name);
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

日期转化格式 工具类DateUtil

package cn.iovnet.commons.utils.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;


public class DateUtil {

    /**
     * 日期类型格式：yyyy-MM
     */
    public static String YYYY_MM = "yyyy-MM";

    /**
     * 日期类型格式：yyyy-MM-dd
     */
    public static String YYYY_MM_DD = "yyyy-MM-dd";

    /**
     * 日期类型格式：yyyyMMdd
     */
    public static String YYYYMMDD = "yyyyMMdd";

    /**
     * 日期类型格式：yyyy-MM-dd HH:mm
     */
    public static String YYYY_MM_DD_HHMM = "yyyy-MM-dd HH:mm";

    /**
     * 日期类型格式：yyyy-MM-dd HH:mm:ss
     */
    public static String YYYY_MM_DD_HHMMSS = "yyyy-MM-dd HH:mm:ss";

    /**
     * 日期类型格式：yyyyMMddHHmmss
     */
    public static String YYYYMMDDHHMMSS = "yyyyMMddHHmmss";

    /**
     * 时间戳类型格式：yyyy-MM-dd HH:mm:ss.SSS
     */
    public static String YYYY_MM_DD_HHMMSS_SSS = "yyyy-MM-dd HH:mm:ss.SSS";

    /**
     * 时间类型格式：HH:mm:ss
     */
    public static String HH_MM_SS = "HH:mm:ss";

	/**
	 * 转换字符串日期格式
	 * @author duyq
	 * @date 2021/06/30 11:51
	 * @param sendTime 日期
	 * @return java.lang.String
	 */
    public static String formatterSendTime(String sendTime) {
        LocalDateTime parseSendTime = LocalDateTime.parse(sendTime, DateTimeFormatter.ofPattern(YYYYMMDDHHMMSS));
        return parseSendTime.format(DateTimeFormatter.ofPattern(YYYY_MM_DD_HHMMSS));
    }


    /**
     * @description: 判断该字符串日期格式是否正确
     * @param strDate 字符串日期
     * @param pattern 日期格式
     * @author: duyq
     * @date: 2019/9/2 14:29
     * @return: boolean
     */
    public static boolean isDate(String strDate, String pattern) {
        boolean result = true;
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        try {
            sdf.setLenient(false);
            sdf.parse(strDate);
        } catch (ParseException e) {
            result = false;
        }
        return result;
    }

    /**
     * @Description 日期字符串转java.util.Date
     * @Author duyq
     * @Param [dateValue, format]
     * @Date 10:22 2019/6/15
     * @return java.util.Date
     **/
    public static Date StringToDate(String dateValue, String pattern) {
        Date date = null;
        if (dateValue != null && dateValue.length() != 0) {
            try {
                SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                date = sdf.parse(dateValue);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        } else {
            return null;
        }
        return date;
    }


    /**
     * @Description java.util.Date 转换成指定格式的日期字符串
     * @Author duyq
     * @Param [date, format]
     * @Date 11:11 2019/6/21
     * @return java.lang.String
     **/
    public static String DateToString(Date date, String pattern) {
        if (date == null) {
            return null;
        }
        try {
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
            return sdf.format(date);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * @Description 将当前日期转换成指定格式的字符串
     * @Author duyq
     * @Param [pattern]
     * @Date 11:52 2019/6/21
     * @return java.lang.String
     **/
    public static String getNowStringDate(String pattern) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
            Date now = new Date();
            return sdf.format(now);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * @description: 获取指定月份的第一天
     * @param patten 返回日期格式
     * @param date 指定月份日期
     * @author: duyq
     * @date: 2020/8/13 9:27
     * @return: java.lang.String
     */
    public static String getMonthFirstDay(String patten, String date) {
        try {
            int year = Integer.parseInt(date.split("-")[0]);
            int month = Integer.parseInt(date.split("-")[1]);
            SimpleDateFormat sdf = new SimpleDateFormat(patten);
            Calendar cale = Calendar.getInstance();
            cale.set(Calendar.YEAR, year);
            cale.set(Calendar.MONTH, month - 1);
            int lastDay = cale.getActualMinimum(Calendar.DATE);
            cale.set(Calendar.DAY_OF_MONTH, lastDay);
            return sdf.format(cale.getTime());
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * @description: 获取指定月份的最后一天
     * @param patten 返回日期格式
     * @param date 指定月份日期
     * @author: duyq
     * @date: 2020/8/13 9:27
     * @return: java.lang.String
     */
    public static String getMonthLastDay(String patten, String date) {
        try {
            int year = Integer.parseInt(date.split("-")[0]);
            int month = Integer.parseInt(date.split("-")[1]);
            SimpleDateFormat sdf = new SimpleDateFormat(patten);
            Calendar cale = Calendar.getInstance();
            cale.set(Calendar.YEAR, year);
            cale.set(Calendar.MONTH, month - 1);
            int lastDay = cale.getActualMaximum(Calendar.DATE);
            cale.set(Calendar.DAY_OF_MONTH, lastDay);
            return sdf.format(cale.getTime());
        } catch (Exception e) {
            return null;
        }
    }


    ///

    /**
     * 获取YYYY格式
     *
     * @return
     */
    public static String getYear() {
        return formatDate(new Date(), "yyyy");
    }

    /**
     * 获取YYYY格式
     *
     * @return
     */
    public static String getYear(Date date) {
        return formatDate(date, "yyyy");
    }

    /**
     * 获取YYYY-MM-DD格式
     *
     * @return
     */
    public static String getDay() {
        return formatDate(new Date(), "yyyy-MM-dd");
    }

    /**
     * 获取YYYY-MM-DD格式
     *
     * @return
     */
    public static String getDay(Date date) {
        return formatDate(date, "yyyy-MM-dd");
    }

    /**
     * 获取YYYYMMDD格式
     *
     * @return
     */
    public static String getDays() {
        return formatDate(new Date(), "yyyyMMdd");
    }

    /**
     * 获取YYYYMMDD格式
     *
     * @return
     */
    public static String getDays(Date date) {
        return formatDate(date, "yyyyMMdd");
    }

    /**
     * 获取YYYY-MM-DD HH:mm:ss格式
     *
     * @return
     */
    public static String getTime() {
        return formatDate(new Date(), "yyyy-MM-dd HH:mm:ss");
    }

    /**
     * 获取YYYY-MM-DD HH:mm:ss.SSS格式
     *
     * @return
     */
    public static String getMsTime() {
        return formatDate(new Date(), "yyyy-MM-dd HH:mm:ss.SSS");
    }

    /**
     * 获取YYYYMMDDHHmmss格式
     *
     * @return
     */
    public static String getAllTime() {
        return formatDate(new Date(), "yyyyMMddHHmmss");
    }

    /**
     * 获取YYYY-MM-DD HH:mm:ss格式
     *
     * @return
     */
    public static String getTime(Date date) {
        return formatDate(date, "yyyy-MM-dd HH:mm:ss");
    }

    public static String formatDate(Date date, String pattern) {
        String formatDate = null;
        if (StringUtils.isNotBlank(pattern)) {
            formatDate = DateFormatUtils.format(date, pattern);
        } else {
            formatDate = DateFormatUtils.format(date, "yyyy-MM-dd");
        }
        return formatDate;
    }

    /**
     * @Title: compareDate
     * @Description:(日期比较，如果s>=e 返回true 否则返回false)
     * @param s
     * @param e
     * @return boolean
     * @throws
     * @author luguosui
     */
    public static boolean compareDate(String s, String e) {
        if (parseDate(s) == null || parseDate(e) == null) {
            return false;
        }
        return parseDate(s).getTime() >= parseDate(e).getTime();
    }

    /**
     * 格式化日期
     *
     * @return
     */
    public static Date parseDate(String date) {
        return parse(date, "yyyy-MM-dd");
    }

    /**
     * 格式化日期
     *
     * @return
     */
    public static Date parseTime(String date) {
        return parse(date, "yyyy-MM-dd HH:mm:ss");
    }

    /**
     * 格式化日期
     *
     * @return
     */
    public static Date parse(String date, String pattern) {
        try {
            return DateUtils.parseDate(date, pattern);
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 格式化日期
     *
     * @return
     */
    public static String format(Date date, String pattern) {
        return DateFormatUtils.format(date, pattern);
    }

    /**
     * 把日期转换为Timestamp
     *
     * @param date
     * @return
     */
    public static Timestamp format(Date date) {
        return new Timestamp(date.getTime());
    }

    /**
     * 校验日期是否合法
     *
     * @return
     */
    public static boolean isValidDate(String s) {
        return parse(s, "yyyy-MM-dd HH:mm:ss") != null;
    }

    /**
     * 校验日期是否合法
     *
     * @return
     */
    public static boolean isValidDate(String s, String pattern) {
        return parse(s, pattern) != null;
    }

    public static int getDiffYear(String startTime, String endTime) {
        DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            int years = (int) (((fmt.parse(endTime).getTime() - fmt.parse(
                    startTime).getTime()) / (1000 * 60 * 60 * 24)) / 365);
            return years;
        } catch (Exception e) {
            // 如果throw java.text.ParseException或者NullPointerException，就说明格式不对
            return 0;
        }
    }

    /**
     * <li>功能描述：时间相减得到天数
     *
     * @param beginDateStr
     * @param endDateStr
     * @return long
     * @author Administrator
     */
    public static long getDaySub(String beginDateStr, String endDateStr) {
        long day = 0;
        SimpleDateFormat format = new SimpleDateFormat(
                "yyyy-MM-dd");
        Date beginDate = null;
        Date endDate = null;

        try {
            beginDate = format.parse(beginDateStr);
            endDate = format.parse(endDateStr);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        day = (endDate.getTime() - beginDate.getTime()) / (24 * 60 * 60 * 1000);
        // System.out.println("相隔的天数="+day);

        return day;
    }

    /**
     * 得到n天之后的日期
     *
     * @param days
     * @return
     */
    public static String getAfterDayDate(String days) {
        int daysInt = Integer.parseInt(days);

        Calendar canlendar = Calendar.getInstance(); // java.util包
        canlendar.add(Calendar.DATE, daysInt); // 日期减 如果不够减会将月变动
        Date date = canlendar.getTime();

        SimpleDateFormat sdfd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateStr = sdfd.format(date);

        return dateStr;
    }

    /**
     * 得到n天之后是周几
     *
     * @param days
     * @return
     */
    public static String getAfterDayWeek(String days) {
        int daysInt = Integer.parseInt(days);

        Calendar canlendar = Calendar.getInstance(); // java.util包
        canlendar.add(Calendar.DATE, daysInt); // 日期减 如果不够减会将月变动
        Date date = canlendar.getTime();

        SimpleDateFormat sdf = new SimpleDateFormat("E");
        String dateStr = sdf.format(date);

        return dateStr;
    }


}

Excel工具类

@Component
public class ExcelUtil {
    @Autowired
    private JdbcTemplate jdbcTemplate;

   
    public static List<Map<Integer, String>> readExcel(MultipartFile file, int titleRowNum, int startRow) {
        List<Map<Integer, String>> list = new ArrayList<>();
        InputStream inputStream;
        //创建Workbook对象
        Workbook workbook = null;
        try {
            inputStream = file.getInputStream();
            workbook = WorkbookFactory.create(inputStream);
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
        //获取工作表
        if (workbook != null) {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null || sheet.getPhysicalNumberOfRows() == 0) {
                    continue;
                }
                //获取标题行的第一列和最后一列的标记
                Row titleRow = sheet.getRow(titleRowNum - 1);
                short firstCellNum = titleRow.getFirstCellNum();
                short lastCellNum = titleRow.getLastCellNum();
                //获取表格中最后一行的行号
                int lastRowNum = sheet.getLastRowNum();
                //获取行
                for (int rowNum = startRow - 1; rowNum <= lastRowNum; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    Map<Integer, String> map = new HashMap<>();
                    //循环列
                    for (int i = firstCellNum; i < lastCellNum; i++) {
                        Cell cell = row.getCell(i);
                        if (cell != null) {
                            map.put(i, parseCell(cell));
                        } else {
                            map.put(i, null);
                        }
                    }
                    list.add(map);
                }
            }
        }
        return list;
    }


    /**
     * @Description 导入Excel表数据
     * @Author duyq
     * @Param [startRow] 从第几行开始读
     * @Date 8:23 2019/6/15
     * @return java.util.List<org.apache.poi.hssf.usermodel.HSSFRow>
     **/
    public static List<String[]> importExcel(MultipartFile file, int startRow) {
        List<String[]> result = new ArrayList<>();
        try (//1.创建输入流
             InputStream inputStream = file.getInputStream();
             //创建Workbook对象
             Workbook workbook = WorkbookFactory.create(inputStream)) {
            //获取工作表
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                return null;
            }
            //获取表格中最后一行的行号
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum < startRow) {
                return null;
            }
            Row row;
            Cell cell;
            //循环读取
            for (int rowNum = startRow - 1; rowNum <= lastRowNum; rowNum++) {
                row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                //获取当前行的第一列和最后一列的标记
                short firstCellNum = row.getFirstCellNum();
                short lastCellNum = row.getLastCellNum();
                String[] rowArray = new String[lastCellNum];
                //循环列
                if (lastCellNum != 0) {
                    for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                        cell = row.getCell(cellNum);
                        if (cell == null) {
                            rowArray[cellNum] = null;
                        } else {
                            rowArray[cellNum] = parseCell(cell);
                        }
                    }
                    result.add(rowArray);
                }
            }
        } catch (InvalidFormatException | IOException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @Description 解析单元格数据
     * @Author duyq
     * @Param [cell]
     * @Date 9:25 2019/6/15
     * @return java.lang.String
     **/
    private static String parseCell(Cell cell) {
        String strCell;
        //判断单元格类型
        switch (cell.getCellType()) {
            //字符串类型单元格
            case Cell.CELL_TYPE_STRING:
                strCell = cell.getRichStringCellValue().getString();
                break;
            //空单元格
            case Cell.CELL_TYPE_BLANK:
                strCell = null;
                break;
            //数学类型单元格(日期，时间，数字)
            case Cell.CELL_TYPE_NUMERIC:
                //日期类型
                if (DateUtil.isCellDateFormatted(cell)) {
                    try {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        strCell = sdf.format(cell.getDateCellValue());
                    } catch (Exception e) {
                        strCell = cell.getStringCellValue();
                    }
                    break;
                } else {
                    //数字
                    double temp = cell.getNumericCellValue();
                    //数字格式化工具
                    DecimalFormat format = new DecimalFormat();
                    //查看单元格中数据的具体样式类型
                    String style = cell.getCellStyle().getDataFormatString();
                    if ("\"￥\"#,##0.00_);[Red]\\(\"￥\"#,##0.00\\)".equals(style)) {
                        // 货币格式
                        format.applyPattern("#.00");
                        strCell = format.format(temp);
                    } else if ("0.00_ ".equals(style)) {
                        // 小数(double)
                        format.applyPattern("#.00");
                        strCell = format.format(temp);
                    } else {
                        // 整数(int)
                        format.applyPattern("#");
                        strCell = format.format(temp);
                    }
                }
                break;
            default:
                strCell = null;
        }
        return strCell;
    }


    /**
     * 读取第n个工作簿
     * @author lvzy
     * @date 2020/6/17
     * @param file 文件
     * @param index 工作簿索引
     * @param titleRowNum 标题行所在行号
     * @param startRow 从第几行开始读
     * @return 文件内容
     */
    public static List<Map<Integer, String>> readIndexExcel(MultipartFile file, Integer index, int titleRowNum, int startRow) throws Exception {
        List<Map<Integer, String>> list = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        //创建Workbook对象
        Workbook workbook = WorkbookFactory.create(inputStream);
        //获取工作表
        Sheet sheet = workbook.getSheetAt(index);
        if (null != sheet && sheet.getPhysicalNumberOfRows() > 0) {
            //获取标题行的第一列和最后一列的标记
            Row titleRow = sheet.getRow(titleRowNum - 1);
            short firstCellNum = titleRow.getFirstCellNum();
            short lastCellNum = titleRow.getLastCellNum();
            //获取表格中最后一行的行号
            int lastRowNum = sheet.getLastRowNum();
            //获取行
            for (int rowNum = startRow - 1; rowNum <= lastRowNum; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                Map<Integer, String> map = new HashMap<>();
                //循环列
                for (int i = firstCellNum; i < lastCellNum; i++) {
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        map.put(i, parseCell(cell));
                    } else {
                        map.put(i, null);
                    }
                }
                list.add(map);
            }
        }
        return list;
    }


    /**
     * Excel 导入通用工具类
     * @param file 文件
     * @param startRow Excel数据开始读入行
     * @param dataClass 带有ExcelImportAnnotation注解的实体类class对象
     */
    @Transactional(rollbackFor = RuntimeException.class)
    public CommonResponse excelImport(MultipartFile file, int startRow, Class<?> dataClass) {
        CommonResponse response = CommonResponse.getInstance();
        //获取Excel 中的数据
        List<String[]> datas = importExcel(file, startRow);
        if (datas == null || datas.size() == 0) {
            response.setErrorCode(Messages.CODE_10001);
            response.setErrorMessage("数据为空！");
            return response;
        }
        ExcelImportAnnotation classAnnotation = dataClass.getDeclaredAnnotation(ExcelImportAnnotation.class);
        if (classAnnotation == null) {
            response.setErrorCode(Messages.CODE_400103);
            response.setErrorMessage("注解不存在！");
            return response;
        }
        //数据需要导入的表名
        String tableName = classAnnotation.tableName();
        if (StringUtils.isEmpty(tableName)) {
            String[] classSplits = dataClass.getName().split("\\.");
            tableName = camelsToColumn(classSplits[classSplits.length - 1]);
        }
        //获取属性
        Field[] declaredFields = dataClass.getDeclaredFields();
        //拼接插入语句
        StringBuilder insertBuilder = new StringBuilder("insert into " + tableName + "(");
        //数据唯一性校验
        StringBuilder checkSql = new StringBuilder("select ");
        String[] caseField = classAnnotation.unionCaseField();
        Map<String, String> fieldMap = new HashMap<>();
        Map<String, String> fieldTypeMap = new HashMap<>();
        List<String> caseFields = new ArrayList<>();
        for (String field : caseField) {
            String column = camelsToColumn(field);
            fieldMap.put(field, column);
            caseFields.add(field);
            checkSql.append(column).append(", ");
        }
        checkSql.append("id from ").append(tableName).append(" where 0 = 1");
        int fieldLength = declaredFields.length;
        for (int i = 0; i < fieldLength; i++) {
            Field field = declaredFields[i];
            String columnName;
            ExcelImportAnnotation fieldAnnotation = field.getAnnotation(ExcelImportAnnotation.class);
            if (fieldAnnotation == null || "".equals(fieldAnnotation.column())) {
                String fieldName = field.getName();
                columnName = camelsToColumn(fieldName);
            } else {
                columnName = fieldAnnotation.column();
            }
            if (i == fieldLength - 1) {
                insertBuilder.append(columnName).append(")");
            } else {
                insertBuilder.append(columnName).append(",");
            }
            Class<?> fieldType = field.getType();
            fieldTypeMap.put(field.getName(), fieldType.getName());
        }
        insertBuilder.append(" values ");
        //唯一性校验标识
        boolean uniqSql = false;
        //拼接插入语句
        for (String[] data : datas) {
            if (data.length != fieldLength) {
                response.setErrorCode(Messages.CODE_500001);
                response.setErrorMessage("数据不匹配");
                return response;
            }
            StringBuilder checkSqlFlag = new StringBuilder();
            boolean cSql = false;
            checkSqlFlag.append(" or (");
            insertBuilder.append("(");
            for (int i = 0; i < data.length; i++) {
                String datum = data[i];
                //类型转换
                String fieldName = declaredFields[i].getName();
                Object dt = convertDataType(datum, fieldTypeMap.get(fieldName));
                insertBuilder.append(dt).append(",");

                if (caseFields.contains(fieldName)) {
                    checkSqlFlag.append(fieldMap.get(fieldName)).append(" = ").append(dt).append(" and ");
                    cSql = true;
                    uniqSql = true;
                }
            }
            insertBuilder.replace(insertBuilder.length() - 1, insertBuilder.length(), ")");
            insertBuilder.append(",");
            checkSqlFlag.append("1=1 )");
            if (cSql) {
                checkSql.append(checkSqlFlag);
            }
        }
        if (uniqSql) {
            checkSql.append(" limit 1");
            //数据校验
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(checkSql.toString());
            if (maps.size() > 0) {
                response.setErrorCode(Messages.CODE_120005);
                StringBuilder resultBuilder = new StringBuilder();
                maps.get(0).forEach((k, v) -> {
                    if (!"id".equals(k)) {
                        resultBuilder.append(v).append("  ");
                    }
                });
                response.setErrorMessage("数据已存在:" + resultBuilder.toString());
                return response;
            }
        }
        //数据插入
        String insertSql = insertBuilder.replace(insertBuilder.length() - 1, insertBuilder.length(), "").toString();
        jdbcTemplate.execute(insertSql);
        return response;
    }

    /**
     * 数据类型转换
     * @param datum 字符串数据
     * @param fieldType 数据类型
     */
    private static Object convertDataType(String datum, String fieldType) {
        if (datum == null) {
            return null;
        }
        if (fieldType.contains("int") || fieldType.contains("Integer")) {
            return Integer.valueOf(datum);
        }

        if (fieldType.contains("double") || fieldType.contains("Double")) {
            return Double.valueOf(datum);
        }

        if (fieldType.contains("float") || fieldType.contains("Float")) {
            return Float.valueOf(datum);
        }

        if (fieldType.contains("date")) {
            if (datum.length() == 10) {
                return cn.iovnet.commons.utils.util.DateUtil.parse(datum, "yyyy-MM-dd");
            } else {
                return cn.iovnet.commons.utils.util.DateUtil.parse(datum, "yyyy-MM-dd HH:mm:ss");
            }
        }

        return "'" + datum + "'";
    }


    private static String camelsToColumn(String camels) {
        int flag = -1;
        char[] chars = camels.toCharArray();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < chars.length; i++) {
            char c = chars[i];
            if (c >= 'A' && c <= 'Z' && (i - flag) > 1) {
                sb.append("_").append((char) (c + 32));
                flag = i;
            } else if (c >= 'A' && c <= 'Z' && (i - flag) == 1) {
                sb.append((char) (c + 32));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    /**
     * <p> 获取输出流 </p>
     *
     * @param request          请求
     * @param response         响应
     * @param originalFileName 文件名称
     */
    public static ServletOutputStream getServletOutputStream(HttpServletRequest request, HttpServletResponse response, String originalFileName) throws IOException {
        //告诉浏览器数据格式，将头和数据传到前台
        String downloadFileName = getEncodedFilename(request, originalFileName);
        String headStr = "attachment; filename=" + downloadFileName;
        response.setContentType("APPLICATION/OCTET-STREAM");
        response.setHeader("Content-Disposition", headStr);
        return response.getOutputStream();
    }

    /**
     * <p> 获取原始文件名 </p>
     *
     * @param originalFileName 原始文件名
     */
    private static String getEncodedFilename(HttpServletRequest request, String originalFileName) {
        String encodedFilename;
        String agent = request.getHeader("User-Agent");
        if (agent.contains("MSIE")) {
            //IE浏览器
            encodedFilename = URLEncoder.encode(originalFileName, StandardCharsets.UTF_8);
            encodedFilename = encodedFilename.replace("+", " ");
        } else if (agent.contains("Firefox")) {
            //火狐浏览器
            encodedFilename = "=?utf-8?B?" + Base64.encodeBase64String(originalFileName.getBytes(StandardCharsets.UTF_8)) + "?=";
        } else {
            //其他浏览器
            encodedFilename = URLEncoder.encode(originalFileName, StandardCharsets.UTF_8);
        }
        return encodedFilename;
    }

    /**
     * 通用导出excel文件(在需要导出的字段上加注解ExcelExportColumn)
     * @author duyq
     * @date 2021/10/09 15:21
     * @param fileName 文件名称(自定义带后缀名.xls/.xlsx)
     * @param dataList 需要导出的数据
     * @param clazz 需要导出的数据对象
     * @return void
     */
    public static <T> void exportExcelFile(String fileName, List<T> dataList, Class<T> clazz, HttpServletResponse response) {
        // 创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        // 创建表头行
        HSSFRow excelRoot = sheet.createRow(0);
        // 设置表头样式
        HSSFCellStyle headStyle = setExcelHead(workbook);
        // 设置表头
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields).filter(field -> {
            ExcelExportColumn annotation = field.getAnnotation(ExcelExportColumn.class);
            if (annotation != null && annotation.index() > 0) {
                field.setAccessible(true);
                return true;
            }
            return false;
        }).sorted(Comparator.comparing(field -> {
            int index = 0;
            ExcelExportColumn annotation = field.getAnnotation(ExcelExportColumn.class);
            if (annotation != null) {
                index = annotation.index();
            }
            return index;
        })).collect(Collectors.toList());
        for (int i = 0; i < fieldList.size(); i++) {
            ExcelExportColumn annotation = fieldList.get(i).getAnnotation(ExcelExportColumn.class);
            String columnName = "";
            if (annotation != null) {
                columnName = annotation.value();
            }
            Cell cell = excelRoot.createCell(i);
            //设置每列宽度
            sheet.setColumnWidth(i, 5800);
            cell.setCellStyle(headStyle);
            cell.setCellValue(columnName);
        }
        // 设置行内容
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        if (dataList != null && !dataList.isEmpty()) {
            for (int j = 0; j < dataList.size(); j++) {
                HSSFRow row = sheet.createRow(j + 1);
                for (int k = 0; k < fieldList.size(); k++) {
                    Object value = "";
                    try {
                        // 将单词的首字母大写
                        String initStr = fieldList.get(k).getName().substring(0, 1).toUpperCase() + fieldList.get(k).getName().substring(1);
                        value = dataList.get(j).getClass().getMethod("get" + initStr).invoke(dataList.get(j));
                    } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
                        e.printStackTrace();
                    }
                    HSSFCell cell = row.createCell(k);
                    if (value != null) {
                        HSSFDataFormat df = workbook.createDataFormat();
                        if (value instanceof Integer) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(Integer.parseInt(value.toString()));
                        } else if (value instanceof BigDecimal) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cellStyle.setDataFormat(df.getFormat("#,##0.00"));
                            cell.setCellValue(Double.parseDouble(value.toString()));
                        } else if (value instanceof Date) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cellStyle.setDataFormat(df.getFormat("yyyy-MM-dd"));
                            cell.setCellValue(value.toString());
                        } else {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellStyle(cellStyle);
                    }
                }
            }
        }
        OutputStream outputStream = null;
        try {
            String name = new String((fileName + ".xls").getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            //设置response, 打开保存页面
            response.reset();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + name);
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 通用导出excel文件(在需要导出的字段上加注解ExcelExportColumn)
     * @author duyq
     * @date 2021/10/09 15:21
     * @param fileName 文件名称(自定义带后缀名.xls/.xlsx)
     * @param titleName 标题行内容
     * @param dataList 需要导出的数据
     * @param clazz 需要导出的数据对象
     * @return void
     */
    public static <T> void exportExcelFile(String fileName, String titleName, List<T> dataList, Class<T> clazz, HttpServletResponse response) {
        // 创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        // 表头行索引
        int rootIndexNum = StringUtils.isNotBlank(titleName) ? 1 : 0;
        // 创建表头行
        HSSFRow excelRoot = sheet.createRow(rootIndexNum);
        // 设置表头样式
        HSSFCellStyle headStyle = setExcelHead(workbook);
        // 设置表头
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields).filter(field -> {
            ExcelExportColumn annotation = field.getAnnotation(ExcelExportColumn.class);
            if (annotation != null && annotation.index() > 0) {
                field.setAccessible(true);
                return true;
            }
            return false;
        }).sorted(Comparator.comparing(field -> {
            int index = 0;
            ExcelExportColumn annotation = field.getAnnotation(ExcelExportColumn.class);
            if (annotation != null) {
                index = annotation.index();
            }
            return index;
        })).collect(Collectors.toList());
        for (int i = 0; i < fieldList.size(); i++) {
            ExcelExportColumn annotation = fieldList.get(i).getAnnotation(ExcelExportColumn.class);
            String columnName = "";
            if (annotation != null) {
                columnName = annotation.value();
            }
            Cell cell = excelRoot.createCell(i);
            //设置每列宽度
            sheet.setColumnWidth(i, 5800);
            cell.setCellStyle(headStyle);
            cell.setCellValue(columnName);
        }
        // 根据表头列数合并单元格创建标题行
        if (StringUtils.isNotBlank(titleName)) {
            HSSFCellStyle titleCellStyle = workbook.createCellStyle();
            titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //字体
            HSSFFont headFont = workbook.createFont();
            headFont.setFontHeightInPoints((short) 12);
            headFont.setFontName("宋体");
            headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            titleCellStyle.setFont(headFont);
            HSSFRow titleRow = sheet.createRow(0);
            HSSFCell cell = titleRow.createCell(0);
            cell.setCellValue(titleName);
            cell.setCellStyle(titleCellStyle);
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, fieldList.size() - 1);
            sheet.addMergedRegion(region);
        }

        // 行样式:垂直居中、水平居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置行内容
        if (dataList != null && !dataList.isEmpty()) {
            for (int j = 0; j < dataList.size(); j++) {
                HSSFRow row = sheet.createRow(rootIndexNum + j + 1);
                for (int k = 0; k < fieldList.size(); k++) {
                    Object value = "";
                    try {
                        // 将单词的首字母大写
                        String initStr = fieldList.get(k).getName().substring(0, 1).toUpperCase() + fieldList.get(k).getName().substring(1);
                        value = dataList.get(j).getClass().getMethod("get" + initStr).invoke(dataList.get(j));
                    } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
                        e.printStackTrace();
                    }
                    HSSFCell cell = row.createCell(k);
                    if (value != null) {
                        HSSFDataFormat df = workbook.createDataFormat();
                        if (value instanceof Integer) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(Integer.parseInt(value.toString()));
                        } else if (value instanceof BigDecimal) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cellStyle.setDataFormat(df.getFormat("#,##0.00"));
                            cell.setCellValue(Double.parseDouble(value.toString()));
                        } else if (value instanceof Date) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cellStyle.setDataFormat(df.getFormat("yyyy-MM-dd"));
                            cell.setCellValue(value.toString());
                        } else {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value.toString());
                        }
                        cell.setCellStyle(cellStyle);
                    }
                }
            }
        }
        OutputStream outputStream = null;
        try {
            String name = new String((fileName + ".xls").getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            //设置response, 打开保存页面
            response.reset();
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + name);
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 设置表头样式
     * @author duyq
     * @date 2021/10/09 15:24
     * @return org.apache.poi.hssf.usermodel.HSSFCellStyle
     */
    private static HSSFCellStyle setExcelHead(HSSFWorkbook workbook) {
        HSSFCellStyle headStyle = workbook.createCellStyle();
        //字体
        HSSFFont headFont = workbook.createFont();
        headFont.setFontHeightInPoints((short) 11);
        headFont.setFontName("宋体");
        headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headStyle.setFont(headFont);

        //设置背景颜色
        headStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        //solid 填充  foreground  前景色
        headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        // 垂直居中
        headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 水平居中
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return headStyle;
    }


    /**
     * 解析excel单元格数据
     * @author duyq
     * @date 2021/10/27 09:33
     * @param file excel文件
     * @param titleRowNum 标题行的行号
     * @param startRow 从第几行开始读取内容
     * @param clazz 返回结果的对象
     * @return java.util.List<T>
     */
    public static <T> List<T> parseExcelData(MultipartFile file, int sheetNum, int titleRowNum, int startRow, Class<T> clazz) {
        List<T> list = new ArrayList<>();
        try {
            Field[] fields = clazz.getDeclaredFields();
            Arrays.stream(fields).forEach(field -> field.setAccessible(true));
            InputStream inputStream = file.getInputStream();
            //创建Workbook对象
            Workbook workbook = WorkbookFactory.create(inputStream);
            //获取工作表
            if (workbook != null) {
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null || sheet.getPhysicalNumberOfRows() == 0) {
                    return list;
                }
                //获取标题行的第一列和最后一列的标记
                Row titleRow = sheet.getRow(titleRowNum - 1);
                short firstCellNum = titleRow.getFirstCellNum();
                short lastCellNum = titleRow.getLastCellNum();
                //获取表格中最后一行的行号
                int lastRowNum = sheet.getLastRowNum();
                //获取行
                for (int rowNum = startRow - 1; rowNum <= lastRowNum; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row == null || isRowEmpty(row)) {
                        continue;
                    }
                    // 创建返回结果对象
                    T object = clazz.getDeclaredConstructor().newInstance();
                    //循环列
                    for (int i = firstCellNum; i < lastCellNum; i++) {
                        Cell cell = row.getCell(i);
                        ExcelImportColumn annotation = fields[i].getAnnotation(ExcelImportColumn.class);
                        if (cell != null && annotation != null && annotation.index() == i + 1) {
                            String parseCell = parseCell(cell);
                            handleField(object, parseCell, fields[i]);
                        }
                    }
                    list.add(object);
                }
            }
        } catch (IOException | InvalidFormatException | IllegalAccessException | InstantiationException | NoSuchMethodException | InvocationTargetException e) {
            e.printStackTrace();
        }
        return list;
    }

    private static <T> void handleField(T t, String value, Field field) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, InstantiationException {
        Class<?> type = field.getType();
        if (type == null || type == void.class || StringUtils.isBlank(value)) {
            return;
        }
        if (type == Object.class) {
            field.set(t, value);
            //数字类型
        } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
            if (type == int.class || type == Integer.class) {
                field.set(t, NumberUtils.toInt(value));
            } else if (type == long.class || type == Long.class) {
                field.set(t, NumberUtils.toLong(value));
            } else if (type == byte.class || type == Byte.class) {
                field.set(t, NumberUtils.toByte(value));
            } else if (type == short.class || type == Short.class) {
                field.set(t, NumberUtils.toShort(value));
            } else if (type == double.class || type == Double.class) {
                field.set(t, NumberUtils.toDouble(value));
            } else if (type == float.class || type == Float.class) {
                field.set(t, NumberUtils.toFloat(value));
            } else if (type == char.class || type == Character.class) {
                field.set(t, CharUtils.toChar(value));
            } else if (type == boolean.class) {
                field.set(t, BooleanUtils.toBoolean(value));
            } else if (type == BigDecimal.class) {
                field.set(t, new BigDecimal(value));
            }
        } else if (type == Boolean.class) {
            field.set(t, BooleanUtils.toBoolean(value));
        } else if (type == Date.class) {
            //
            field.set(t, value);
        } else if (type == String.class) {
            field.set(t, value);
        } else {
            Constructor<?> constructor = type.getConstructor(String.class);
            field.set(t, constructor.newInstance(value));
        }
    }

    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 创建excel文件
     * @author duyq
     * @date 2021/11/15 17:56
     * @param titleArray 表头
     * @param sheetName sheet名称
     * @return org.apache.poi.hssf.usermodel.HSSFWorkbook
     */
    public static HSSFWorkbook createExcel(String[] titleArray, String sheetName) {
        //创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style = workbook.createCellStyle();
        //字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);

        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置背景颜色
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        // solid 填充  foreground  前景色
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 表头
        HSSFRow excelRoot = sheet.createRow(0);
        // 此处设置数据格式
        for (int i = 0; i < titleArray.length; i++) {
            HSSFCell rootCell = excelRoot.createCell(i);
            rootCell.setCellValue(titleArray[i]);
            sheet.setColumnWidth(i, 5000);
            rootCell.setCellStyle(style);
        }
        return workbook;
    }

    /**
     * 输出创建的Excel文件
     */
    public static ResponseEntity<byte[]> outputExcel(Workbook workbook, HttpServletRequest request, String fileName) {
        //设置头信息
        HttpHeaders headers = new HttpHeaders();
        //设置响应的文件名
        String downloadFileName = DownloadUtil.getEncodedFilename(request, fileName);
        headers.setContentDispositionFormData("attachment", downloadFileName);
        headers.add("Access-Control-Expose-Headers", "filename");
        headers.add("filename", downloadFileName);
        //application/octet-stream二进制流数据的形式下载
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        try {
            workbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                byteArrayOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 给sheet页，添加下拉列表
     *
     * @param workbook    excel文件
     * @param targetSheet 需要操作的sheet页
     * @param options     下拉列表数据
     * @param column      下拉列表所在列 从'A'开始
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
        if (options != null && options.length > 0) {
            String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
            Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
            String nameName = column + "_parent";

            int rowIndex = 0;
            for (Object option : options) {
                int columnIndex = 0;
                Row row = optionsSheet.createRow(rowIndex++);
                Cell cell = row.createCell(columnIndex++);
                cell.setCellValue(option.toString());
            }
            createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);
            DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
            CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
            targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
            // 隐藏sheet页
            int sheetIndex = workbook.getSheetIndex(optionsSheet);
            workbook.setSheetHidden(sheetIndex, true);
        }
    }

    /**
     * 给sheet页  添加级联下拉列表
     *
     * @param workbook    excel
     * @param targetSheet 需要操作的sheet页
     * @param options     要添加的下拉列表内容
     * @param keyColumn   下拉列表1位置
     * @param valueColumn 级联下拉列表位置
     * @param fromRow     级联限制开始行
     * @param endRow      级联限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
        if (options != null && !options.isEmpty()) {
            String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
            Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
            List<String> firstLevelItems = new ArrayList<>();

            int rowIndex = 0;
            for (Map.Entry<String, List<String>> entry : options.entrySet()) {
                String parent = formatNameName(entry.getKey());
                firstLevelItems.add(parent);
                List<String> children = entry.getValue();

                int columnIndex = 0;
                Row row = hiddenSheet.createRow(rowIndex++);
                Cell cell = null;

                for (String child : children) {
                    cell = row.createCell(columnIndex++);
                    cell.setCellValue(child);
                }
                char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
                createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));

                DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
                CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
                targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
            }
            addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);
            // 隐藏sheet页
            int sheetIndex = workbook.getSheetIndex(hiddenSheet);
            workbook.setSheetHidden(sheetIndex, true);
        }
    }

    /**
     * 根据用户在keyColumn选择的key, 自动填充value到valueColumn
     *
     * @param workbook    excel
     * @param targetSheet 需要操作的sheet页
     * @param keyValues   匹配关系 {'key1','value1'},{'key2','value2'}
     * @param keyColumn   要匹配的列(例如: key1所在的列, 大写字母列名)
     * @param valueColumn 匹配到的内容列(例如: value1所在的列, 大写字母列名)
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addAutoMatchValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, String> keyValues, char keyColumn, char valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        int rowIndex = 0;
        for (Map.Entry<String, String> kv : keyValues.entrySet()) {
            Row totalSheetRow = hiddenSheet.createRow(rowIndex++);

            Cell cell = totalSheetRow.createCell(0);
            cell.setCellValue(kv.getKey());

            cell = totalSheetRow.createCell(1);
            cell.setCellValue(kv.getValue());
        }

        for (int i = fromRow; i <= endRow; i++) {
            Row totalSheetRow = targetSheet.getRow(i);
            if (totalSheetRow == null) {
                totalSheetRow = targetSheet.createRow(i);
            }

            Cell cell = totalSheetRow.getCell((int) valueColumn - 'A');
            if (cell == null) {
                cell = totalSheetRow.createCell((int) valueColumn - 'A');
            }

            String keyCell = String.valueOf(keyColumn) + (i + 1);
            String formula = String.format("IF(ISNA(VLOOKUP(%s,%s!A:B,2,0)),\"\",VLOOKUP(%s,%s!A:B,2,0))", keyCell, hiddenSheetName, keyCell, hiddenSheetName);

            cell.setCellFormula(formula);
        }
        addValidationToSheet(workbook, targetSheet, keyValues.keySet().toArray(), keyColumn, fromRow, endRow);
        // 隐藏sheet页
        int sheetIndex = workbook.getSheetIndex(hiddenSheet);
        workbook.setSheetHidden(sheetIndex, true);
    }


    private static Name createName(Workbook workbook, String nameName, String formula) {
        Name name = workbook.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(formula);
        return name;
    }

    /**
     * 不可数字开头
     */
    private static String formatNameName(String name) {
        name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
        if (Character.isDigit(name.charAt(0))) {
            name = "_" + name;
        }

        return name;
    }

    /**
     * 导出Map 数据信息 第一列为序号
     * @author heyan
     * @date 2021年12月6日
     * @param sheet 工作簿
     * @param list 数据列
     * @param ths 标题
     * @param params 参数
     */
	public static void setExportExcelData(HSSFSheet sheet, 
			List<Map<String, Object>> list, String[] ths,
			String[] params) {
        // 行
        HSSFRow row;
        // 单元格
        HSSFCell cell;
        // 构建表头
        row = sheet.createRow(0);
        for (int i = 0; i < ths.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(ths[i]);
        }
        for (int i = 0; i < list.size(); i++) {
        	Map<String, Object> data = list.get(i);
        	if (data != null) {
        		row = sheet.createRow(i + 1);
        		row.createCell(0).setCellValue(i + 1);
        		// 设置数据信息
        		for (int j = 1; j < params.length; j++) {
        			if (data.get(params[j]) != null) {
        				row.createCell(j).setCellValue(
        						data.get(params[j]).toString());
					} else {
						row.createCell(j).setCellValue("--");
					}
				}
        	}
		}
	}
    /**
     * 导出Map 数据信息 第一列为序号
     * @author heyan
     * @date 2021年12月6日
     * @param sheet 工作簿
     * @param list 数据列
     * @param ths 标题
     * @param params 参数
     */
	public static void setExportExcelTreeData(HSSFSheet sheet, 
			List<Map<String, Object>> list, String[] ths,
			String[] params) {
        // 行
        HSSFRow row;
        // 单元格
        HSSFCell cell;
        // 构建表头
        row = sheet.createRow(0);
        for (int i = 0; i < ths.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(ths[i]);
        }
        setTreeListData(1, sheet, list, params);
	}
	/**
	 * 子节点数据信息设置
	 * @author heyan
	 * @date 2021年12月9日
	 * @param indexNum 行数据
     * @param sheet 工作簿
     * @param list 数据列
     * @param params 参数
	 * @return indexNum
	 */
	@SuppressWarnings("unchecked")
	public static int setTreeListData(int indexNum, HSSFSheet sheet, 
			List<Map<String, Object>> list, String[] params) {
        // 行
        HSSFRow row;
        for (int i = 0; i < list.size(); i++) {
        	Map<String, Object> data = list.get(i);
        	if (data != null) {
        		row = sheet.createRow(indexNum);
        		// 设置数据信息
        		for (int j = 0; j < params.length; j++) {
        			if (data.get(params[j]) != null) {
        				row.createCell(j).setCellValue(
        						data.get(params[j]).toString());
					} else {
						row.createCell(j).setCellValue("--");
					}
				}
        		indexNum++;
        		indexNum = setTreeListData(indexNum, sheet, 
        				(List<Map<String, Object>>) data.get("children"), params);
        	}
		}
        return indexNum;
	}

}