EPPLUS如何知道工作表单元格的格式 [英] EPPLUS how to know the format of the Worksheet cell

问题描述

我正在使用EPPlus读取excel工作表并将数据加载到数据表中以执行进一步的操作，然后将修改后的数据保存回excel文件中.

I am using EPPlus to read excel sheets and load the data into datatable to perform further operations and then save back the modified data to the excel file.

以下代码检查单元格值是否为浮点值，然后将浮点值转换为日期时间.

The below code checks if the cell value is a float value then it converts the float value to datetime.

当单元格值为日期时，代码可以正常工作，例如:Invoice Date = 42009，但是它将非日期值(例如:amount = 10)转换为日期.

The code works fine when the cell value is a date eg: Invoice Date = 42009 , but it converts the not a date value like eg : amount = 10 to a date.

EPPlus库中有什么方法可以确定单元格的格式(即常规"/日期"/数字"等)?

Is there any way in EPPlus library from which i can determine the format of the cell (i.e General/date/number etc) ?

float floatValue;

if (float.TryParse(Convert.ToString(oSheet.Cells[i, j].Value), out floatValue)
                                && Convert.ToString(oSheet.Cells[i, j].Style.Numberformat.Format).Contains("[$-409]d\\-mmm\\-yy;@"))
  {
      dr[j - 1] = String.Format("{0:d-MMM-yy}", DateTime.FromOADate(floatValue));
  }
  else
  {
       DateTime date;

       if (DateTime.TryParse(Convert.ToString(oSheet.Cells[i, j].Value), out date))
        {
                dr[j - 1] = String.Format("{0:d-MMM-yy}", date);
        }
        else
        {
               dr[j - 1] = Convert.ToString(oSheet.Cells[i, j].Value).Trim();
        }
   }

推荐答案

简短的答案是，如果Excel中的数据格式设置为适当的"日期字段(其格式设置为日期"，并且在其中没有三角形)单元格角落)，EPPlus将确定并自动将其转换为单元格存储中的日期.

The short answer is if the data in Excel is formatted as a "proper" date field (its format is set to Date and it does not have the triangle in the cell corner) EPPlus will determine that and automatically convert it to a date in the cell store.

Excel将此信息存储在XLSX xml文件中，例如sheet1.xml和styles.xml，您可以通过将文件扩展名更改为.ZIP并作为压缩文件夹打开来查看.因此，EPPlus将读取XML标志并将字段值自动转换为DateTime.如果要查看其详细信息，请下载EPPlus源代码，并查看ExcelWorksheet类中的函数private void SetValueFromXml(XmlTextReader xr, string type, int styleID, int row, int col).

Excel stores this information in the XLSX xml files, e.g. sheet1.xml and styles.xml, which you can see by changing the file extension to .ZIP and opening as a compressed folder. So EPPlus will read XML flags and convert the field values to DateTime automatically. If you want to see its gory detail download the EPPlus source code and look at the function private void SetValueFromXml(XmlTextReader xr, string type, int styleID, int row, int col) in the ExcelWorksheet class.

我创建了一个工作表，添加了一个日期值，然后将该值复制并粘贴到了单元格B2中，然后将格式设置为数字.然后，我将B2复制到B3，但在B3中的值前面加上了'，从而使该值成为字符串.像这样:

I created a sheet, added a date value, and copy pasted the value to cell B2 but then set the format to number. I then copied B2 to B3 but made the value in B3 a string by putting an ' in front of the value. Like this:

当我运行此单元测试时，一切都通过了:

and when I run this unit test everything passes:

[TestMethod]
public void Date_Number_Test()
{
    //http://stackoverflow.com/questions/28591763/epplus-how-to-know-the-format-of-the-worksheet-cell
    var existingFile = new FileInfo(@"c:\temp\datetest.xlsx");

    using (var package2 = new ExcelPackage(existingFile))
    {
        var ws = package2.Workbook.Worksheets["Sheet1"];

        var cell = ws.Cells["B1"];
        Assert.IsTrue(cell.Value is DateTime);

        cell = ws.Cells["B2"];
        Assert.IsTrue(cell.Value is double);

        cell = ws.Cells["B3"];
        Assert.IsTrue(cell.Value is string);
    }
}

但是，如果您要处理的表格的格式超出了您的控制范围，那么我认为您的方法是正确的，因为您必须确定数字的意图"是什么，而不是能够依靠EPPlus(真的很出色). )将其报告为.

But if you are dealing with a sheet with formatting outside your control then I think your approach is correct in that you will have to determine what the "intent" of the numbers are rather then being able to rely on EPPlus (really excel) is reporting them as.

这篇关于EPPLUS如何知道工作表单元格的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！