Npoi Excel导入时日期格式的转换问题

NPOI 是一个用于处理 Office 文档的 .NET 库，它可以处理 Microsoft Excel（.xls 和 .xlsx）文件。NPOI 提供了创建、读取和编辑 Excel 文件的功能，而无需安装 Microsoft Office。NPOI 是基于 Apache POI 项目的 .NET 版本。

1、定义导入按钮

  <button id="chuan" type="button" class="btn btn-1g btn-info" onclick="$('#ExcelDaoru').click();">
        导入种子液excel
    </button>
    <br /><br /><hr /><br /><br />
    <input id="ExcelDaoru" type="file" style="display:none;" accept=".xls,.xlsx" />

2、定义按钮事件

 //导入种子液信息Excel
        $('#ExcelDaoru').fileupload({
                type: 'POST',
                url: '@Url.Action("ExcelDaoru")',
                dataType: 'json',
                autoUpload: true,
                minFileSize: 1,
                maxFileSize: 1024 * 1024 * 50,
                done: function (e, data) {
                    if (data.result.isSucced === true) {
                        alert("导入成功!");
                        //console.log(data.result);
                        //$('#grid-table').bootstrapTable('refresh');
                        for (var i = 0; i < data.result.zhongZiYeList.length; i++) {
                            var $temptr = $("#table-ZhongZiYeMuBan tr:first").clone();
                            $("#tbody-ZhongZiYe").append($temptr);
                            $temptr.find(".Shangguanjunzhong").val(data.result.zhongZiYeList[i].Shangguanjunzhong);
                            $temptr.find(".Shangguanshijian").val(jsonDateFormat(data.result.zhongZiYeList[i].Shangguanshijian)).datetimepicker({
                                format: 'YYYY-MM - DD'
                            });

                            $temptr.find(".Shangguanjunzhongpihao").val(data.result.zhongZiYeList[i].Shangguanjunzhongpihao);
                            $temptr.find(".Shangguanzhongziyetiji").val(data.result.zhongZiYeList[i].Shangguanzhongziyetiji);
                            $temptr.find(".Danduokelong").val(data.result.zhongZiYeList[i].Danduokelong);

                            //$temptr.find(".Yijipingbanhuaxianriqi").val(data.result.zhongZiYeList[i].Yijipingbanhuaxianriqi);
                            $temptr.find(".Yijipingbanhuaxianriqi").val(jsonDateFormat(data.result.zhongZiYeList[i].Yijipingbanhuaxianriqi)).datetimepicker({
                                format: 'YYYY-MM-DD'
                            });


                            $temptr.find(".Yijipingbanpihao").val(data.result.zhongZiYeList[i].Yijipingbanpihao);
                            $temptr.find(".Yijipingbanpeiyangwendu").val(data.result.zhongZiYeList[i].Yijipingbanpeiyangwendu);
                            //$temptr.find(".Yijipingbanpeiyangshijian").val(data.result.zhongZiYeList[i].Yijipingbanpeiyangshijian);
                            $temptr.find(".Yijipingbanpeiyangshijian").val(data.result.zhongZiYeList[i].Yijipingbanpeiyangshijian);
                            $temptr.find(".Yijijunluoxingtai").val(data.result.zhongZiYeList[i].Yijijunluoxingtai);

                            $temptr.find(".Erjipingbanhuaxianriqi").val(jsonDateFormat(data.result.zhongZiYeList[i].Erjipingbanhuaxianriqi)).datetimepicker({
                                format: 'YYYY-MM-DD'
                            });

                            $temptr.find(".Erjipingbanpihao").val(data.result.zhongZiYeList[i].Erjipingbanpihao);
                            $temptr.find(".Erjipingbanpeiyangwendu").val(data.result.zhongZiYeList[i].Erjipingbanpeiyangwendu);
                            $temptr.find(".Erjipingbanpeiyangshijian").val(data.result.zhongZiYeList[i].Erjipingbanpeiyangshijian);
                            $temptr.find(".Erjijunluoxingtai").val(data.result.zhongZiYeList[i].Erjijunluoxingtai);

                            $temptr.find(".Yijiyaopingpihao").val(data.result.zhongZiYeList[i].Yijiyaopingpihao);
                            $temptr.find(".Yijiyaopingjiezhongriqi").val(jsonDateFormat(data.result.zhongZiYeList[i].Yijiyaopingjiezhongriqi)).datetimepicker({
                                format: 'YYYY-MM-DD'
                            });

                            $temptr.find(".Yijiyaopingtixi").val(data.result.zhongZiYeList[i].Yijiyaopingtixi);
                            $temptr.find(".Yijiyaopingjiezhongliang").val(data.result.zhongZiYeList[i].Yijiyaopingjiezhongliang);
                            $temptr.find(".Yijiyaopingpeiyangtiaojian").val(data.result.zhongZiYeList[i].Yijiyaopingpeiyangtiaojian);
                            $temptr.find(".Yijiyaopingpeiyangshijian").val(data.result.zhongZiYeList[i].Yijiyaopingpeiyangshijian);
                            $temptr.find(".YijiyaopingOD").val(data.result.zhongZiYeList[i].YijiyaopingOD);
                            $temptr.find(".YijiyaopingPH").val(data.result.zhongZiYeList[i].YijiyaopingPH);
                            $temptr.find(".Yijiyaopingjingjian").val(data.result.zhongZiYeList[i].Yijiyaopingjingjian);

                            $temptr.find(".Erjiyaopingpihao").val(data.result.zhongZiYeList[i].Erjiyaopingpihao);
                            $temptr.find(".Erjiyaopingjiezhongriqi").val(jsonDateFormat(data.result.zhongZiYeList[i].Erjiyaopingjiezhongriqi)).datetimepicker({
                                format: 'YYYY-MM-DD'
                            });

                            $temptr.find(".Erjiyaopingtixi").val(data.result.zhongZiYeList[i].Erjiyaopingtixi);
                            $temptr.find(".Erjiyaopingjiezhongliang").val(data.result.zhongZiYeList[i].Erjiyaopingjiezhongliang);
                            $temptr.find(".Erjiyaopingpeiyangtiaojian").val(data.result.zhongZiYeList[i].Erjiyaopingpeiyangtiaojian);
                            $temptr.find(".Erjiyaopingpeiyangshijian").val(data.result.zhongZiYeList[i].Erjiyaopingpeiyangshijian);
                            $temptr.find(".ErjiyaopingOD").val(data.result.zhongZiYeList[i].ErjiyaopingOD);
                            $temptr.find(".ErjiyaopingPH").val(data.result.zhongZiYeList[i].ErjiyaopingPH);
                            $temptr.find(".Erjiyaopingjingjian").val(data.result.zhongZiYeList[i].Erjiyaopingjingjian);

                            $temptr.find(".Chaojingtai").val(data.result.zhongZiYeList[i].Chaojingtai);
                            $temptr.find(".Peiyangxiang").val(data.result.zhongZiYeList[i].Peiyangxiang);
                            $temptr.find(".Yaochuang").val(data.result.zhongZiYeList[i].Yaochuang);
                            $temptr.find(".Ziguangduji").val(data.result.zhongZiYeList[i].Ziguangduji);
                            $temptr.find(".Shangguanshiyanguocheng").val(data.result.zhongZiYeList[i].Shangguanshiyanguocheng);
                            $temptr.find(".Shangguanzhongziyezhibeisop").val(data.result.zhongZiYeList[i].Shangguanzhongziyezhibeisop);

                            $temptr.find(".Fangguanyangpinsijuntijianceshijian").val(jsonDateFormat(data.result.zhongZiYeList[i].Fangguanyangpinsijuntijianceshijian)).datetimepicker({
                                format: 'YYYY-MM-DD'
                            });

                            $temptr.find(".Fangguanyangpinsijuntijiancejieguo").val(data.result.zhongZiYeList[i].Fangguanyangpinsijuntijiancejieguo);
                            $temptr.find(".Beizhu").val(data.result.zhongZiYeList[i].Beizhu);

                            SetXuhao($temptr.parent());
                        }

      
                    }
                    else {
                        alert(data.result.errorMessage)
                    }
                },
                fail: function (e, data) {
                    var fileName = data.files[0].name;
                    console.log(e);
                    alert(fileName + "导入失败!");
                },
                always: function (e, data) {
                },
            });

    function jsonDateFormat(jsonDate) {
        try {
            var date = new Date(parseInt(jsonDate.replace("/Date(", "").replace(")/", ""), 10));
            var month = date.getMonth() + 1 < 10 ? "0" + (date.getMonth() + 1) : date.getMonth() + 1;
            var day = date.getDate() < 10 ? "0" + date.getDate() : date.getDate();
            var hours = date.getHours();
            var minutes = date.getMinutes();
            var seconds = date.getSeconds();
            var milliseconds = date.getMilliseconds();

            return date.getFullYear() + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds + "." + milliseconds;

        } catch (ex) {
            return "";
        }
    }

3、ExcelDaoru方法

        //excel导入
        [HttpPost]
        public JsonResult ExcelDaoru()
        {
            HttpPostedFileBase file = Request.Files?[0];
            string fileName = file?.FileName;
            string errorMessage = "";
            int zuiDaShangChuanFuJian = 1024 * 1024 * 50;// 100MB
            try
            {
                if (file == null || file.ContentLength <= 0)
                    throw new Exception("请上传文件!");

                if (file.ContentLength > zuiDaShangChuanFuJian)
                {
                    throw new Exception("文件大小不能超过 " + zuiDaShangChuanFuJian / (1024 * 1024) + "MB");
                }

                var extension = Path.GetExtension(file.FileName);//获取文件后缀名
                if (string.IsNullOrWhiteSpace(extension) || (!new List<string>() { ".xls", ".xlsx" }.Contains(extension.ToLower())))
                    throw new Exception("不支持使用此扩展名上传文件!");

                string baoCunMing = Guid.NewGuid().ToString("N") + extension;//生成一个随机文件名保存到本地并保存文件
                string xiangDuiBaoCunDir = "~/Upload/ExcelDaoru/" + DateTime.Now.ToString("yyyyMM");
                string jueDuiBaoCunDir = Server.MapPath(xiangDuiBaoCunDir);

                if (!Directory.Exists(jueDuiBaoCunDir))
                    Directory.CreateDirectory(jueDuiBaoCunDir);
                string jueDuiBaoCunDiZhi = jueDuiBaoCunDir + "/" + baoCunMing;
                file.SaveAs(jueDuiBaoCunDiZhi);


                DataTable dt = ExcelToDataTable("Sheet1", true, jueDuiBaoCunDiZhi);
                List<tbfjsjk_zhongziyepeiyang> zhongZiYeList = new List<tbfjsjk_zhongziyepeiyang>();
                //List<tbbg_fujian> fujianList = new List<tbbg_fujian>();
                if (dt != null && dt.Rows.Count > 0)
                {
                    //字段
                    List<string> ColumnsNameList = new List<string>();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        ColumnsNameList.Add(dc.ColumnName);
                    }

                    //值
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        tbfjsjk_zhongziyepeiyang zhongziyepeiyang = new  tbfjsjk_zhongziyepeiyang();
                        Type t = zhongziyepeiyang.GetType();
                        PropertyInfo[] PropertyList = t.GetProperties();
                        foreach (PropertyInfo item in PropertyList)
                        {
                            string name = item.Name;
                            string DisplayName = item.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName;
                            if (ColumnsNameList.Contains(DisplayName))
                            {
                                item.SetValue(zhongziyepeiyang, CheckType(dt.Rows[i][DisplayName], item.PropertyType), null);
                            }
                        }
                        zhongZiYeList.Add(zhongziyepeiyang);
                    }
                }

                return Json(new { isSucced = true, errorMessage = "", zhongZiYeList = zhongZiYeList });

            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }

            return Json(new { isSucced = false, errorMessage = string.Format("{0} fail to import!  {1}", fileName, errorMessage) });


        }

4、ExcelToDataTable方法

  private DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName)
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(sheetName);
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }
            var data = new DataTable();
            IWorkbook workbook = null;
            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
                {
                    workbook = new HSSFWorkbook(fs);
                }

                ISheet sheet = null;
                if (workbook != null)
                {
                    //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
                }
                if (sheet == null) return data;
                var firstRow = sheet.GetRow(0);
                //一行最后一个cell的编号 即总的列数
                int cellCount = firstRow.LastCellNum;
                int startRow;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        var cellValue = cell.StringCellValue;
                        if (cellValue == null) continue;
                        var column = new DataColumn(cellValue);
                        data.Columns.Add(column);
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                //最后一列的标号
                var rowCount = sheet.LastRowNum;

                for (var i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    // 没有数据的行默认是null
                    if (row == null) continue;
                    var dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        var cell = row.GetCell(j);
                        // 同理，没有数据的单元格都默认是null
                        if (cell != null)
                        {
                            //检查单元格的值是否为空
                            string cellValue = cell.ToString();
                            if (!string.IsNullOrEmpty(cellValue))
                            {
                                if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                                {
                                    // 如果单元格是日期格式，将其转换为您需要的日期格式
                                    //dataRow[j] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", DateTime.FromOADate(cell.NumericCellValue));
                                    dataRow[j] = string.Format("{0:yyyy-MM-dd}", DateTime.FromOADate(row.GetCell(j).NumericCellValue));
                                }
                                else
                                {
                                    dataRow[j] = cell.ToString();
                                }
                            }
                            else
                            {
                                dataRow[j] = DBNull.Value;//空值赋值
                            }
                        }
                    }
                    data.Rows.Add(dataRow);
                }

                return data;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(nameof(ExcelDaoru), ex);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

5、CheckType方法

   /// <summary>
        /// 对可空类型进行判断转换(*要不然会报错)
        /// </summary>
        /// <param name="value">DataReader字段的值</param>
        /// <param name="conversionType">该字段的类型</param>
        /// <returns></returns>
        private static object CheckType(object value, Type conversionType)
        {
            if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value == null)
                    return null;
                NullableConverter nullableConverter = new NullableConverter(conversionType);
                conversionType = nullableConverter.UnderlyingType;
            }
            return Convert.ChangeType(value, conversionType);
        }

注意：日期格式的转化，以及空值转化。否则会报错

/Date(1664553600000)/

解决方法：

DateTime在NPOI中的类型是Numeric ，判断如果是Numeric 类型的话再用.Net自带的DateUtil.IsCellDateFormatted(cell)方法判断是否为DateTime类型，如果2个都成立，取cell的DateCellValue属性

                    if ( row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j)))
                    {
                        dataRow[j] = row.GetCell(j).DateCellValue;
                    }