easyExcel中使用String类型去接收Date日期类型问题

韩_师兄

已于 2023-06-13 16:59:32 修改

阅读量2.7k

点赞数 1

分类专栏：一天一个bug 文章标签： excel java easyexcel

于 2023-06-13 16:52:56 首次发布

本文链接：https://blog.csdn.net/ABestRookie/article/details/131191831

版权

一天一个bug 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

easyExcel中使用String类型去接收Date日期类型问题

在easyExcel导出表格过程中,错误的使用了String类型去接收日期类型,最后导致日期值转换错误.

1 问题复现

1 导出类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Phone {
    
    @Excel(name = "商品id", width = 15, orderNum = "10")
    private int id;

    @Excel(name = "更新时间", format = "yyyy-MM-dd HH:mm:ss", width = 25, orderNum = "20")
    private Date updateTime;

    // 本应该使用Date,此处错误使用String
    @Excel(name = "创建时间", format = "yyyy-MM-dd HH:mm:ss" , width = 25, orderNum = "30")
    private String createTime;
}

2 导出工具类

public class ExcelUtils {
    /**
     * excel 导出
     *
     * @param list      数据
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  文件名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * 默认的 excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }
    }

}

3 导出控制类

    @RequestMapping(value = "/export")
    public void exportExcel(HttpServletResponse response) throws IOException {
        // 获取当前日期
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String format = simpleDateFormat.format(date);

        // 模拟数据,真实场景从数据库获取
        List<Phone> phoneDTOList = new ArrayList<>();
        phoneDTOList.add(new Phone(6, date, format));
        phoneDTOList.add(new Phone(10, date, format));
        phoneDTOList.add(new Phone(20, date, format));

		// 导出数据
        ExcelUtils.exportExcel(phoneDTOList, "手机信息表", "手机信息", Phone.class, "手机信息", response);
    }

4 导出结果

商品id	更新时间	创建时间
6	2023-06-12 23:27:48	2022-12-05 23:02:23
10	2023-06-12 23:27:48	2022-12-05 23:02:23
20	2023-06-12 23:27:48	2022-12-05 23:02:23

从上面的导出结果可以得知,在Excel生成过程中,日期类型,被有规律的修改过,导致创建时间和更新时间不相等.

2 问题排查

从上述分析可知,easyExcel生成表格过程中,单元格的赋值转换过程中,一定不同的判断.OK, 我们跟着代码一起冲!

1 入口代码, 即我们调用工具类,并传入集合数据.

        ExcelUtils.exportExcel(phoneDTOList, "手机信息表", "手机信息", Phone.class, "手机信息", response);

2 在下面方法时,传入了集合数据,并未返回,可知进入easyExcel源码里面.

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

3 ExcelExportUtil类中该方法,可知第二步,创建sheet过程中传入了集合值.

    /**
     * @param entity    表格标题属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass,
                                       Collection<?> dataSet) {
        Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
        new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet);
        return workbook;
    }

4 ExcelExportService类中该方法,可以看到是调用createSheetForMap方法时,传入了集合值.

    public void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass,
                            Collection<?> dataSet) {
			// 省略...
        try {
            List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
            // 得到所有字段
            Field[]     fileds   = PoiPublicUtil.getClassFields(pojoClass);
            ExcelTarget etarget  = pojoClass.getAnnotation(ExcelTarget.class);
            String      targetId = etarget == null ? null : etarget.value();
            getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass,
                    null, null);
            //获取所有参数后,后面的逻辑判断就一致了
            createSheetForMap(workbook, entity, excelParams, dataSet);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
        }
    }

5 进入了createSheetForMap方法, 调用insertDataToSheet方法,并传入集合值.

    public void createSheetForMap(Workbook workbook, ExportParams entity,
                                  List<ExcelExportEntity> entityList, Collection<?> dataSet) {
        // 省略...
        insertDataToSheet(workbook, entity, entityList, dataSet, sheet);
    }

6 进入insertDataToSheet方法,对集合迭代器遍历,并调用了createCells方法,传入遍历值.

    protected void insertDataToSheet(Workbook workbook, ExportParams entity,
                                     List<ExcelExportEntity> entityList, Collection<?> dataSet,Sheet sheet) {
        try {
     		// 省略...
            Iterator<?>  its      = dataSet.iterator();
            List<Object> tempList = new ArrayList<Object>();
            while (its.hasNext()) {
                Object t = its.next();
                index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];
                tempList.add(t);
                if (index >= MAX_NUM) {
                    break;
                }
            }
            if (entity.getFreezeCol() != 0) {
                sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);
            }

            mergeCells(sheet, excelParams, titleHeight);

            its = dataSet.iterator();
            for (int i = 0, le = tempList.size(); i < le; i++) {
                its.next();
                its.remove();
            }
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("List data more than max ,data size is {}",
                        dataSet.size());
            }
            // 发现还有剩余list 继续循环创建Sheet
            if (dataSet.size() > 0) {
                createSheetForMap(workbook, entity, entityList, dataSet);
            } else {
                // 创建合计信息
                addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet);
            }

        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
        }
    }

7 进入 createCells方法,调用getCellValue方法,并传入了遍历值.

    public int[] createCells(Drawing patriarch, int index, Object t,
                             List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook,
                             short rowHeight, int cellNum) {
        try {
        		   // 省略...
                    Object value = getCellValue(entity, t);

                    if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) {
                        createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                                index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),
                                entity);

                    } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) {
                        createDoubleCell(row, cellNum++, value == null ? "" : value.toString(),
                                index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity),
                                entity);
                    } else {
                        createImageCell(patriarch, entity, row, cellNum++,
                                value == null ? "" : value.toString(), t);
                    }
                    if (entity.isHyperlink()) {
                        row.getCell(cellNum - 1)
                                .setHyperlink(dataHandler.getHyperlink(
                                        row.getSheet().getWorkbook().getCreationHelper(), t,
                                        entity.getName(), value));
                    }
                }
            }
  			
			// 省略...
            return new int[]{maxHeight, cellNum};
        } catch (Exception e) {
            LOGGER.error("excel cell export error ,data is :{}", ReflectionToStringBuilder.toString(t));
            LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);
        }

    }

8 进入getCellValue方法, 因为日期类型的Format不为空,我们在实体上设置了为format = "yyyy-MM-dd HH:mm:ss", 所以进入dateFormatValue方法.

    public Object getCellValue(ExcelExportEntity entity, Object obj) throws Exception {
        Object value;
        
        // 省略...
        if (StringUtils.isNotEmpty(entity.getFormat())) {
            value = dateFormatValue(value, entity);
        }
        if (entity.getReplace() != null && entity.getReplace().length > 0) {
            value = replaceValue(entity.getReplace(), String.valueOf(value));
        }
        if (StringUtils.isNotEmpty(entity.getNumFormat())) {
            value = numFormatValue(value, entity);
        }
        if (StringUtils.isNotEmpty(entity.getDict()) && dictHandler != null) {
            value = dictHandler.toName(entity.getDict(), obj, entity.getName(), value);
        }
        if (needHandlerList != null && needHandlerList.contains(entity.getName())) {
            value = dataHandler.exportHandler(obj, entity.getName(), value);
        }
        if (StringUtils.isNotEmpty(entity.getSuffix()) && value != null) {
            value = value + entity.getSuffix();
        }
        if (value != null && StringUtils.isNotEmpty(entity.getEnumExportField())) {
            value = PoiReflectorUtil.fromCache(value.getClass()).getValue(value, entity.getEnumExportField());
        }
        return value == null ? "" : value.toString();
    }

9 进入dateFormatValue方法, 看到第一个判断,value是否是String类型实例, 且不为空, 创建时间的类型都满足,所以进入第一个if判断,且ExcelExportEntity类中的databaseFormat数据库格式默认为yyyyMMddHHmmss, 创建时间2023-06-12 23:27:48进过了数据库默认时间格式的装换后变成的Date类型值为2022-12-05 23:02:23 , 最后一步,都经过自定义的Format,将日期类型,转换成字符串返回.

(ps: 更新时间的类型是Date,所以进入第二个if中, 直接赋值,没有进行日期的装换)

    private Object dateFormatValue(Object value, ExcelExportEntity entity) throws Exception {
        Date temp = null;
        if (value instanceof String && StringUtils.isNoneEmpty(value.toString())) {
            SimpleDateFormat format = new SimpleDateFormat(entity.getDatabaseFormat());
            temp = format.parse(value.toString());
        } else if (value instanceof Date) {
            temp = (Date) value;
        } else if (value instanceof Instant) {
            Instant instant = (Instant) value;
            temp = Date.from(instant);
        } else if (value instanceof LocalDate) {
            LocalDate localDate = (LocalDate) value;
            temp = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());
        } else if (value instanceof LocalDateTime) {
            LocalDateTime localDateTime = (LocalDateTime) value;
            temp = Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());
        } else if (value instanceof ZonedDateTime) {
            ZonedDateTime zonedDateTime = (ZonedDateTime) value;
            temp = Date.from(zonedDateTime.toInstant());
        } else if (value instanceof OffsetDateTime) {
            OffsetDateTime offsetDateTime = (OffsetDateTime) value;
            temp = Date.from(offsetDateTime.toInstant());
        } else if (value instanceof java.sql.Date) {
            temp = new Date(((java.sql.Date) value).getTime());
        } else if (value instanceof java.sql.Time) {
            temp = new Date(((java.sql.Time) value).getTime());
        } else if (value instanceof java.sql.Timestamp) {
            temp = new Date(((java.sql.Timestamp) value).getTime());
        }
        if (temp != null) {
            SimpleDateFormat format = new SimpleDateFormat(entity.getFormat());
            if (StringUtils.isNotEmpty(entity.getTimezone())) {
                format.setTimeZone(TimeZone.getTimeZone(entity.getTimezone()));
            }
            value = format.format(temp);
        }
        return value;
    }

3 问题总结

通过上述问题排查,知道了问题最后生成的原因,解决方法也很明确,即将创建时间的字符串类型更换为Date日期类型,再次导出Excel,日期数据正确.

一个类型的错误使用,引发了一个不大不小的bug. 对于代码的修改和测试,还需要认真仔细,减少bug的出现.

本文到此结束,一天一个bug系列,不希望有下次! 拜拜了您嘞

韩_师兄

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
easyExcel中使用String类型去接收Date日期类型问题

通过上述问题排查,知道了问题最后生成的原因,解决方法也很明确,即将创建时间的字符串类型更换为Date日期类型,再次导出Excel,日期数据正确.一个类型的错误使用,引发了一个不大不小的bug. 对于代码的修改和测试,还需要认真仔细,减少bug的出现.本文到此结束,一天一个bug系列,不希望有下次!拜拜了您嘞。
复制链接

扫一扫