代码之家  ›  专栏  ›  技术社区  ›  Ryan

上载列数据为dateTime但C#无法计算NPOI的Excel文件

  •  0
  • Ryan  · 技术社区  · 4 年前

    我的Excel有如下列数据 enter image description here

    01-1月-2021 而不是 2021-01-01 然后我把错误排成一行 entity.startTime = Convert.ToDateTime(row[0].ToString().Trim());

     for (int j = 0; j < HttpContext.Current.Request.Files.Count; j++)
            {
                string fileName = HttpContext.Current.Request.Files[j].FileName;
                if (fileName.Contains(".xlsx") || fileName.Contains(".xls"))
                {
                    dataset = ExcelHelper.ExcelStreamToDataSet(HttpContext.Current.Request.Files[j].InputStream, HttpContext.Current.Request.Files[j].FileName);
                  
                    bool isFileFind = false;
                    List<ExcelRowErrorInfoAssetInfo> fileErrorAllList = new List<ExcelRowErrorInfoAssetInfo>();
                   
                    for (int i = 0; i < dataset.Tables.Count; i++)
                    {
                        DataTable table = dataset.Tables[i];
                        string sheetName = table.TableName;
                        #region 
                        for (int r = 0; r < table.Rows.Count; r++)
                        {
                            DataRow row = table.Rows[r];
                            int cnt = row.ItemArray.ToList().Where(a => string.IsNullOrEmpty(a.ToString())).Count();
                            if (cnt < row.ItemArray.Count())
                            {
                                if (sheetName == "基本信息")
                                {
                                    AssetsInfoMap entity = new AssetsInfoMap();
                                    //startTime  is type of DateTime
                                    entity.startTime = Convert.ToDateTime(row[0].ToString().Trim());
                                    entity.endTime = Convert.ToDateTime(row[1].ToString().Trim());
                                    
                                }
                            }
                        }
                        #endregion
                    }
    
    public static DataSet ExcelStreamToDataSet(Stream stream, string fileName)
        {
            DataSet dataset = new DataSet();
            int startRow = 0;
            try
            {
                IWorkbook workbook = null;
                if (fileName.IndexOf(".xlsx") > 0) 
                    workbook = new XSSFWorkbook(stream);
                else if (fileName.IndexOf(".xls") > 0) 
                    workbook = new HSSFWorkbook(stream);
    
                int sheetCount=workbook.NumberOfSheets;
                for (int m = 0; m < sheetCount-1; m++)
                {
                    DataTable data = new DataTable();
                    ISheet sheet = workbook.GetSheetAt(m);
                    data.TableName= sheet.SheetName;
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        int cellCount = firstRow.LastCellNum; 
    
                        for (int k = firstRow.FirstCellNum; k < cellCount; ++k)
                        {
                            ICell cell = firstRow.GetCell(k);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
    
                      
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue; 
                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                if (row.GetCell(j) != null) 
                                    dataRow[j] = row.GetCell(j).ToString();
                            }
                            data.Rows.Add(dataRow);
                        }
                    }
                    dataset.Tables.Add(data);
                }
    
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
            }
            return dataset;
        }
    }
    

    那么,如何获得正确的数据分配给我的模型,你有什么想法,根据我的上述代码?

    1 回复  |  直到 4 年前
        1
  •  0
  •   ProgrammingLlama Raveena Sarda    4 年前

    ICell 定义属性 .DateCellValue

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

    现在将把您的值存储为 DateTime 而不是作为一个 string . 我们现在需要取消你的 -&燃气轮机; 一串 日期时间 转换:

    entity.startTime = (DateTime)row[0];