C#.通过 NPOI 2.0 将 xlsx 日期单元格导入到 DataTable [英] C#. xlsx date cell import to DataTable by NPOI 2.0

问题描述

我尝试使用 NPOI 2.0 库将 .xlsx 文件转换为 DataTable 格式.没关系，但是我在转换为字符串日期单元格时遇到了问题.当我尝试使用像 row.GetCell(j).ToString() 这样的构造时 - 它抛出异常无法从文本单元格获取数值".我尝试使用 DateCellValue 属性，但它也抛出了这个异常.使用其他单元格格式效果很好.函数，我使用的是:

I'm tried to convert .xlsx file to DataTable format by using NPOI 2.0 library. It's OK, but I have a problem with convert to string date cell. When I try to use construction like row.GetCell(j).ToString() - it's threw exception "Cannot get numeric value from a text cell". I tried to use DateCellValue property, but it also threw this exception. With other cell formats it's work good. Function, that I use it's:

private DataTable xlsxToDT(string fileName)
    {
        DataTable table = new DataTable();
        XSSFWorkbook workbook = new XSSFWorkbook(new FileStream(fileName, FileMode.Open, FileAccess.Read));
        ISheet sheet = workbook.GetSheetAt(0);
        IRow headerRow = sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum;
        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }
        int rowCount = sheet.LastRowNum;
        for (int i = (sheet.FirstRowNum); i < sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i);
            DataRow dataRow = table.NewRow();
            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                {
                    //EXCEPTION GENERATING IN THIS CODE
                    dataRow[j] = row.GetCell(j).ToString();
                    ////////////////////////////
                }
            }
            table.Rows.Add(dataRow);
        }
        workbook = null;
        sheet = null;
        return table;
    }

更新:如果我插入像

row.GetCell(j).SetCellType(CellType.STRING);

在问题单元格中，我的值类似于36496.392581018517".另一个单元格正确转换

in problem cell I have value like "36496.392581018517". Another cells converted correctly

推荐答案

Excel 文件的第二列具有日期格式 (12/2/1999).在您当前的文化(ru-RU")中，NPOI 无法识别此格式.这似乎是 NPOI 中的一个错误，因为发生这种情况时，无法从该单元格中读取任何内容.我来到的唯一方法是在读取 excel 文件之前更改线程的文化(并在之后更改):

Second column of your excel file has a date format (12/2/1999). This format is not recognized by NPOI in your current culture ("ru-RU"). This seems like a bug in NPOI, becuase when this happens, there is no way to read anything from that cell. The only way I came to, is to change the thread's culture before reading the excel file (and change it back after):

private DataTable xlsxToDT(string fileName)
{
    var prevCulture = Thread.CurrentThread.CurrentCulture;
    Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
    try
    {
        // Put your whole method body here.
    }
    finally
    {
        Thread.CurrentThread.CurrentCulture = prevCulture;
    }
}

这篇关于C#.通过 NPOI 2.0 将 xlsx 日期单元格导入到 DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！