如何获取包含日期的单元格的值并使用NPOI保持原始格式 [英] How to get the value of cell containing a date and keep the original formatting using NPOI

问题描述

我有一个使用DevExpress编辑的Excel文件，并且正在使用NPOI进行阅读.当我尝试以字符串形式获取日期单元格的值时，它不会保留原始值.

I have an Excel file that I edited using DevExpress and I am reading using NPOI. When I try to get the value of a date cell as string, it does not keep the original value.

例如: 在DevExpress网格中，我设置此值:2016-08-12.我想在字符串中获取相同的值，但得到42689.

For example: In a DevExpress grid I set this value: 2016-08-12. I want to obtain the same value in my string but instead I get 42689.

我获取单元格值的代码是这样的:

My code to get the cell value is like this:

    ICell cell = row.GetCell(i);
    cell.SetCellType(CellType.String);
    string fieldString = cell.StringCellValue;
    result = result + ";" + FieldValue; 

如何获取原始的格式化日期值?

How can I get the original formatted date value?

推荐答案

在Excel中，日期存储为数字.如果要获取格式化的日期，则需要检查该单元格是否包含日期(有一种实用程序方法)，然后获取该单元格的日期值，获取数据格式，最后将日期转换为字符串使用格式.您不应该强制CellType进行字符串设置，否则您将无法再知道该单元格最初保存的日期.我建议使用像这样的扩展方法，以根据其类型获取格式化的单元格值:

In Excel, dates are stored as numbers. If you want to get a formatted date, you'll need to check whether the cell contains a date (there's a utility method for that), then get the date value of the cell, get the data format, and finally convert the date to string using the format. You should not force the CellType to string or else you will no longer be able to tell that the cell originally held a date. I would recommend making an extension method like this to get the formatted cell value based on its type:

using NPOI.SS.UserModel;
public static class NpoiExtensions
{
    public static string GetFormattedCellValue(this ICell cell, IFormulaEvaluator eval = null)
    {
        if (cell != null)
        {
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;

                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        DateTime date = cell.DateCellValue;
                        ICellStyle style = cell.CellStyle;
                        // Excel uses lowercase m for month whereas .Net uses uppercase
                        string format = style.GetDataFormatString().Replace('m', 'M');
                        return date.ToString(format);
                    }
                    else
                    {
                        return cell.NumericCellValue.ToString();
                    }

                case CellType.Boolean:
                    return cell.BooleanCellValue ? "TRUE" : "FALSE";

                case CellType.Formula:
                    if (eval != null)
                        return GetFormattedCellValue(eval.EvaluateInCell(cell));
                    else
                        return cell.CellFormula;

                case CellType.Error:
                    return FormulaError.ForInt(cell.ErrorCellValue).String;
            }
        }
        // null or blank cell, or unknown cell type
        return string.Empty;
    }
}

然后，像这样使用它:

ICell cell = row.GetCell(i);
string fieldString = cell.GetFormattedCellValue();
result = result + ";" + FieldValue;

可选:如果单元格中有任何公式，并且希望对这些公式进行求值，则根据您的工作簿类型创建IFormulaEvaluator并将评估器传递给GetFormattedCellValue()方法.例如:

Optional: If you have any formulas in your cells and you want those formulas to be evaluated, then create an IFormulaEvaluator based on your workbook type and pass the evaluator to the GetFormattedCellValue() method. For example:

IFormulaEvaluator eval;
if (workbook is XSSFWorkbook)
    eval = new XSSFFormulaEvaluator(workbook);
else
    eval = new HSSFFormulaEvaluator(workbook);

...

ICell cell = row.GetCell(i);
string fieldString = cell.GetFormattedCellValue(eval);
result = result + ";" + FieldValue;

这篇关于如何获取包含日期的单元格的值并使用NPOI保持原始格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！