首页新闻找找看学习计划

C# NPOI读取Excel文件时间格式时出错,求同行可以一起交流解决。我只有100元豆,全部奉献

0
悬赏园豆:100 [已解决问题] 解决于 2016-03-27 19:51

我想在VS中用C#实现读取Excel到DateTable,但当我读取用户定义的Excel中的自定义的时间数据类型数据的时候,拿不到我想要显示的时间类型数据。而是一组数字代码。我怎么才能将数据读取出来?

Excel中得到的数据格式和它普通的单元格格式数据如下图:

以下是Excel的常规数据显示

下面是我的读取Excel的到DataTable的方法

下面是对NPOI的数据引用


using NPOI;
using NPOI.XSSF;
using NPOI.XSSF.UserModel;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.Record;//NPOI.HSSF.Record.Formula.Eval改为了NPOI.SS.Formula.Eval;
using NPOI.SS.Formula.Eval;//同上
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.SS;
using NPOI.DDF;
using NPOI.SS.Util;

 

      public static DataTable ExcelToTableForXLSX(string file)
        {
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
                ISheet sheet = xssfworkbook.GetSheetAt(0);

                //表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if ((obj == null || obj.ToString() == string.Empty) && obj.ToString()!="Effective Date")
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else if (obj.ToString() == "Effective Date")
                    {dt.Columns.Add(new DataColumn(obj.ToString(),typeof(DateTime)));}
                    else
                    {dt.Columns.Add(new DataColumn(obj.ToString()));}
                    columns.Add(i);
                }
                //数据
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {

                        dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

下面是GetValueTypeForXLSX方法中的内容

 /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }

下面是XSSFCell的数据类的原定义

#region 程序集 NPOI.OOXML.dll, v2.1.3.1
// F:\20150528\WebApp\WebApp\bin\NPOI.OOXML.dll
#endregion

using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.Model;
using System;

namespace NPOI.XSSF.UserModel
{
    public class XSSFCell : ICell
    {
        public XSSFCell(XSSFRow row, CT_Cell cell);

        public CellRangeAddress ArrayFormulaRange { get; }
        //
        public bool BooleanCellValue { get; }
        //
        // 摘要: 
        //     Only valid for formula cells
        public CellType CachedFormulaResultType { get; }
        //
        // 摘要: 
        //     Returns cell comment associated with this cell
        public IComment CellComment { get; set; }
        //
        // 摘要: 
        //     Return a formula for the cell, for example, SUM(C4:E4)
        public string CellFormula { get; set; }
        //
        // 摘要: 
        //     Return the cell's style.
        public ICellStyle CellStyle { get; set; }
        //
        // 摘要: 
        //     Return the cell type.
        public CellType CellType { get; }
        //
        // 摘要: 
        //     Returns zero-based column index of this cell
        public int ColumnIndex { get; }
        //
        // 摘要: 
        //     Get the value of the cell as a date.
        public DateTime DateCellValue { get; }
        //
        // 摘要: 
        //     Returns the error message, such as #VALUE!
        public string ErrorCellString { get; }
        //
        // 摘要: 
        //     Get the value of the cell as an error code.  For strings, numbers, and bools,
        //     we throw an exception.  For blank cells we return a 0.
        public byte ErrorCellValue { get; }
        //
        // 摘要: 
        //     Returns hyperlink associated with this cell
        public IHyperlink Hyperlink { get; set; }
        public bool IsMergedCell { get; }
        public bool IsPartOfArrayFormulaGroup { get; }
        //
        public double NumericCellValue { get; }
        //
        public IRichTextString RichStringCellValue { get; }
        //
        public IRow Row { get; }
        //
        // 摘要: 
        //     Returns zero-based row index of a row in the sheet that contains this cell
        public int RowIndex { get; }
        //
        public ISheet Sheet { get; }
        //
        public string StringCellValue { get; }

        public ICell CopyCellTo(int targetIndex);
        //
        public string GetRawValue();
        //
        // 摘要: 
        //     Returns an A1 style reference to the location of this cell
        //
        // 返回结果: 
        //     A1 style reference to the location of this cell
        public string GetReference();
        //
        protected SharedStringsTable GetSharedStringSource();
        //
        protected StylesTable GetStylesSource();
        //
        // 摘要: 
        //     Removes the comment for this cell, if there is one.
        public void RemoveCellComment();
        //
        // 摘要: 
        //     Sets this cell as the active cell for the worksheet.
        public void SetAsActiveCell();
        public void SetCellErrorValue(byte errorCode);
        //
        // 摘要: 
        //     Set a error value for the cell
        //
        // 参数: 
        //   error:
        //     the error value to Set this cell to. For formulas we'll Set the precalculated
        //     value , for errors we'll set its value. For other types we will change the
        //     cell to an error cell and Set its value.
        public void SetCellErrorValue(FormulaError error);
        //
        public void SetCellFormula(string formula);
        //
        // 摘要: 
        //     Set the cells type (numeric, formula or string)
        //
        // 参数: 
        //   cellType:
        public void SetCellType(CellType cellType);
        //
        public void SetCellValue(bool value);
        //
        // 摘要: 
        //     Set a date value for the cell. Excel treats dates as numeric so you will
        //     need to format the cell as a date.
        //
        // 参数: 
        //   value:
        //     the date value to Set this cell to. For formulas we'll set the precalculated
        //     value, for numerics we'll Set its value. For other types we will change the
        //     cell to a numeric cell and Set its value.
        public void SetCellValue(DateTime value);
        //
        public void SetCellValue(double value);
        //
        public void SetCellValue(IRichTextString str);
        //
        public void SetCellValue(string str);
        //
        // 摘要: 
        //     Returns a string representation of the cell
        //
        // 返回结果: 
        //     Formula cells return the formula string, rather than the formula result.
        //      Dates are displayed in dd-MMM-yyyy format Errors are displayed as #ERR<errIdx>
        public override string ToString();
    }
}

下面是自己添加用来获取CellType数据类型的方法

 /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
薛兆敬的主页 薛兆敬 | 初学一级 | 园豆:170
提问于:2015-08-05 11:00
< >
分享
最佳答案
0

DateCellValue

这里有一点比较麻烦 因为excel会自动转化格式

为了避免转换格式 

可以参考这里

http://blog.csdn.net/lcnmdfx/article/details/38083887

收获园豆:60
小眼睛老鼠 | 老鸟四级 |园豆:2774 | 2015-08-05 12:10
其他回答(5)
0

亲,园豆是公开的,你明明有200园豆...

收获园豆:2
I,Robot | 园豆:9461 (大侠五级) | 2015-08-05 11:02

http://blog.sina.com.cn/s/blog_7e5841350101fdda.html

http://q.cnblogs.com/q/53434/

这两个链接可以参考一下。

支持(0) 反对(0) I,Robot | 园豆:9461 (大侠五级) | 2015-08-05 11:15

今天做任务刚赚的。谢谢回答。结贴的时候给你分园豆。

支持(0) 反对(0) 薛兆敬 | 园豆:170 (初学一级) | 2015-08-05 13:41
0

//
        // 摘要:
        //     Get the value of the cell as a date.
        public DateTime DateCellValue { get; }

这个值不是吗?

收获园豆:35
Launcher | 园豆:45030 (高人七级) | 2015-08-05 11:20
0

土豪

收获园豆:2
稳稳的河 | 园豆:4183 (老鸟四级) | 2015-08-05 17:42
0

楼主大概不知道博客园里每个人做任务的记录都可以查到……

收获园豆:1
小白菜T | 园豆:564 (小虾三级) | 2015-08-06 15:09
0

http://www.luofenming.com/show.aspx?id=ART2017122700001
我这里有关于时间格式问题,今天刚解决
直接用我这个类就可以,我已测试过 没问题

lqwvje | 园豆:204 (菜鸟二级) | 2018-09-04 16:47
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册