前两天接了个问题的处理，就是页面导入Excel表格，表里日期数据入库后变成了数字的问题。

Excel表格内容：                      入库后的数据：

刚看到这个时是一脸懵，然后按照惯例自己随手测了下

这样不是没问题嘛，但是问题反馈方的大量数据已经生成，只能先表面临时解决（就是写个sql改，耗时最短，一般不推荐）。

完了后我细想，不对，临时解决不是长久办法，我要找出这个问题出现的原因，然后避免或解决该类问题。

其实大致推测一下就知道应该怎么避免该问题，那就是在Excel里设置日期列为日期型就行了。但是捏，可能我闲吧，而且有时候使用者并不按照规则来操作，所以还是仔细研究一下。

首先弄清不明白的问题点：

那5位数字是什么？

那些数字就是日期在Excel中的“真身”，1900年1月1日是Excel日期的第一天，也就是说数字“1”就代表1900年1月1日；其他日期所对应的数字就表示这个日期是从1900年1月1日起的第几天，上面的44150就是1900年1月1日起到2020年11月15日之间的天数。

尝试重现问题：

前面随手测的时候输入的日期是10月25，没有问题。

后面我又测了下，因为是在原页面上测试还要满足其他的条件限制，所以我又造了两条数据，在日期列第一行输入了11月14日，然后第二行的日期是Excel 下拉填充的，一保存，呃~“44149”，“44150”出现了。

大胆猜测原因：

1. Excel表里该日期列没有设置为日期型？
2. Excel表里日期格式填写问题？
3. Excel表里展示的是日期而不是数字，但到库里就是数字了，是不是读取工具jxl的原因？
4. 换成poi是不是就不会出现这种情况?
5. 因为我第二次测用了下拉填充，然后excel为了给日期加1所以将日期值默认为天数了？
6. 数据库该项数据类型没有设置成日期型导致？

验证：

为了验证前5条，我弄了个表格：

又分别写了jxl和poi两套导入读取Excel流程：

页面：

（本来可以不用写页面的，直接在main方法里测，但是为了高度还原场景，所以还是加了页面。）

<a class="btn btn-primary" @click="importTest1">导入测试1</a>
<a class="btn btn-primary" @click="importTest2">导入测试2</a>

<!-- 上传文件弹窗 -->
<div id="uploadExcel" style="display: none;">
    <form name="form_uploadImg" action="" enctype="multipart/form-data">
        <input id="file_data" type="file" name="file_data" style="height: 35px; width: 200px;"/>
    </form>
</div>

js：

       importTest1: function () {
            layer.open({
                type: 1,
                skin: 'layui-layer-molv',
                title: '导入Excel',
                area: ['300px', '200px'],
                shadeClose: false,
                content: jQuery("#uploadExcel"),
                btn: ['确定', '取消'],
                btn1: function (index) {
                    $.ajaxFileUpload({
                        url: '../importTest1',
                        secureuri: true,
                        fileElementId: 'file_data',
                        dataType: 'json',
                        success: function (data) {
                            if (data.code == 0) {
                                alert(data.toString());
                            } else if (data.code == 1) {
                                alert(data.msg);
                            }
                        }
                    });
                }
            });
        },
        importTest2: function () {
            layer.open({
                type: 1,
                skin: 'layui-layer-molv',
                title: '导入Excel',
                area: ['300px', '200px'],
                shadeClose: false,
                content: jQuery("#uploadExcel"),
                btn: ['确定', '取消'],
                btn1: function (index) {
                    $.ajaxFileUpload({
                        url: '../importTest2',
                        secureuri: true,
                        fileElementId: 'file_data',
                        dataType: 'json',
                        success: function (data) {
                            if (data.code == 0) {
                                alert(data.toString());
                            } else if (data.code == 1) {
                                alert(data.msg);
                            }
                        }
                    });
                }
            });
        }

 后端：

    /*jxl读取*/
    @ResponseBody
    @RequestMapping("importTest1")
    public R importTest1(HttpServletRequest request) throws Exception {
        MultipartHttpServletRequest mpReq = (MultipartHttpServletRequest) request;
        MultipartFile file = mpReq.getFile("file_data");
        FileInputStream fio = (FileInputStream) file.getInputStream();
        List<List<Object>> dataList = new ArrayList<>();
        Workbook book = Workbook.getWorkbook(fio);
        Sheet sheet = book.getSheet(0);
        for (int j = 0; j < sheet.getRows(); j++) {
            String[] singleRow = new String[sheet.getColumns()];
            for (int i = 0; i < sheet.getColumns(); i++) {
                Cell cell = sheet.getCell(i, j);
                singleRow[i] = cell.getContents();
                if (cell.getType() == CellType.DATE) {
                    DateCell dateCell = (DateCell) cell;
                    Date date = dateCell.getDate();
                    singleRow[i] = new SimpleDateFormat("yyyy/MM/dd").format(date);
                } else {
                    singleRow[i] = cell.getContents();
                }
            }
            List<Object> list=Arrays.asList(singleRow);
            dataList.add(list);
        }
        book.close();
        System.out.println(dataList.toString());
        return R.ok().put(dataList);
    }

    /*poi读取*/
    @ResponseBody
    @RequestMapping("importTest2")
    public R importTest2(HttpServletRequest request) throws Exception {
        MultipartHttpServletRequest mpReq = (MultipartHttpServletRequest) request;
        MultipartFile file = mpReq.getFile("file_data");
        FileInputStream fio = (FileInputStream) file.getInputStream();
        List<List<Object>> dataList = new ArrayList<>();
        HSSFWorkbook excel = new HSSFWorkbook(fio);
        HSSFSheet sheet = excel.getSheetAt(0);
        for (Iterator ite = sheet.rowIterator(); ite.hasNext(); ) {
            List<Object> list = new ArrayList<>();
            HSSFRow row = (HSSFRow) ite.next();
            for (Iterator itet = row.cellIterator(); itet.hasNext(); ) {
                HSSFCell cell = (HSSFCell) itet.next();
                if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        list.add(cell.getDateCellValue());
                    } else {
                        list.add(cell.getNumericCellValue());
                    }
                } else {
                    list.add(cell.getStringCellValue());
                }
            }
            dataList.add(list);
        }
        System.out.println(dataList);
        return R.ok().put(dataList);
    }

测试页面开始测试：

验证结果：

jxl读取结果

 poi读取结果

结果对比：

对比结论：

1. 对于excel日期型列，用jxl读取，输入上述各种格式的日期都不会变成日期数字代码，而poi读取还是会变；
2. jxl读取非日期型列输入的“10-15”、“10月15日”、“10/15”、“2020年10月15日”格式的日期会变成日期数字代码；
3. poi读取日期型列和非日期型列输入的“10-15”、“10月15日”、“10/15”、“2020年10月15日”格式的日期都会变成日期数字代码；
4. poi读取日期型列输入的“2020/10/15”、“2020-10-15”格式的日期会变成日期数字代码；
5. jxl读取日期型列输入的“1015”、“10.15”、“20201015”格式的日期会被转换成不合理的日期“1902/10/11”、“1900/01/10”、“57208/06/30”；
6. poi读取日期型列输入的“1015”、“20201015”格式的日期会变成“1015.0”、“2.0201015E7”；

问题的解决或避免：

最佳的解决方案是在Excel里限制列为日期型列，然后使用jxl读取，且日期格式不要用“1015”、“10.15”、“20201015”这类的（适用本文讨论的问题）。

对了，最后关于最后一条推测，我做了个小试验：

在类型为datetime的数据项里存入日期数字代码看会怎么样？

然后保存结果如下

呃，看来MySQL数据库的datetime是不识别Excel日期数字代码规则的，所以指望数据库设置成日期型来解决变为日期数字代码的问题是不可行的。

同理，下面的方案在本例中也不那么完美：

通过代码解决日期变为日期数字代码的问题的不完美方案

代码里拿到1900年1月1日的日期，在这个日期上加上44119天是可以得到2020年10月15号这个日期，但是当传入的是1015这样的数值呢，得到的日期前面也有是1902/10/11，而且关于44119是加天数，1015是保持原样不加天数的判断规则也很难定。

其实，归根到底，避免这类问题的最直接方案就是预先规定好或限制好用户导入的日期格式。如果此格式在一开始就要求了，那我现在问题也是解决了个寂寞了，哈哈哈。

点此欢迎光临我的个人网站【一几文星球】

微信公众号，欢迎关注，一起学习。