C# EPPlus read导入读取Excel到DataSet时日期DateTime列的处理

public static DataSet ReadExcelToDataSet(string filePath, string sheetName)
        {
            DataSet ds = new DataSet();
            DataRow dr;
            object objCellValue;
            string cellValue;
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
            {

                EPPlus 5.0 以后的版本需要指定 商业证书 或者非商业证书
                //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (ExcelPackage package = new ExcelPackage())
                {
                    package.Load(fs);
                    foreach (var sheet in package.Workbook.Worksheets)
                    {
                        if (sheet.Name.ToUpper().Trim() == sheetName.ToUpper().Trim())
                        {
                            #region
                            if (sheet.Dimension == null) continue;
                            var columnCount = sheet.Dimension.End.Column;
                            var rowCount = sheet.Dimension.End.Row;
                            if (rowCount > 0)
                            {
                                DataTable dt = new DataTable(sheet.Name);
                                for (int j = 0; j < columnCount; j++)//设置DataTable列名
                                {
                                    objCellValue = sheet.Cells[1, j + 1].Value;
                                    cellValue = objCellValue == null ? "" : objCellValue.ToString();
                                    dt.Columns.Add(cellValue, typeof(string));
                                }
                                for (int i = 2; i <= rowCount; i++)
                                {
                                    dr = dt.NewRow();
                                    for (int j = 1; j <= columnCount; j++)
                                    {
                                        objCellValue = sheet.Cells[i, j].Value;
                                        if (objCellValue != null)
                                        {
                                            if (sheet.Cells[i, j].Style.Numberformat.Format.IndexOf("yyyy") > -1 && sheet.Cells[i, j].Value.GetType().ToString() == "System.Double")//注意这里,是处理日期时间格式的关键代码 
                                                objCellValue = sheet.Cells[i, j].GetValue<DateTime>();
                                        }
                                        cellValue = objCellValue == null ? "" : objCellValue.ToString();
                                        dr[j - 1] = cellValue;
                                    }
                                    dt.Rows.Add(dr);
                                }
                                ds.Tables.Add(dt);
                            }
                            #endregion
                        }
                    }
                }
            }
            return ds;
        }

 

posted on 2020-08-25 10:58  itjeff  阅读(975)  评论(0编辑  收藏  举报

导航