EPPlus可以区分Excel工作表中的空白单元格和空白文本单元格吗? [英] Can EPPlus distinguish between blank cells and empty text cells in an Excel worksheet?

问题描述

我正在使用EPPlus .NET库(v4.0.4)来解释已保存的Excel工作簿.在一个这样的工作表中，使用Excel的撇号"技巧将一些空单元格设置为文本"格式(也就是说，用户在这些单元格中输入了一个撇号，以便Excel将其显示为空白).

两个这样的单元格的XML示例如下:

  <c r="F6" s="1" t="inlineStr">
    <is>
      <t />
    </is>
  </c>
  <c r="G6" s="1" />

在这里，F6有一个撇号(即是一个空的文本单元格)，而G6则是真正的空白.

是否可以使用EPPlus读取此内联字符串"(inlineStr)属性，或者以其他方式区分这两个单元格? ExcelRange/ExcelRangeBase类具有诸如Value，Formula，Style等的属性，但是当检查代表上述两个单元格的对象时，我看不到它们之间的任何区别.

我知道我可以通过读取原始XML或使用其他库(也许是ClosedXML或类似的库)来手动完成此操作，但是如果可能的话，我想使用EPPlus.

解决方案

EPPlus使用null值表示一个空单元格，并为包含该空字符串的单元格返回空字符串.

这适用于读写操作.

因此，如果您使用epplus将空字符串写入单元格，则该单元格将包含一个空字符串(因此将显示为空，但不等于0)，而如果您使用epplus将单元格的值设置为null，则它将为在excel中为空(并且仍将显示为空白，但等于0)

这同样适用于读取操作.如果您已使用撇号技巧"，则可以从单元格的值中读取空字符串，但会得到一个空单元格值的空引用.

I'm using the EPPlus .NET library (v4.0.4) to interpret saved Excel workbooks. In one such worksheet, some empty cells have been set to 'text' format using the Excel 'apostrophe' trick (that is, the user has entered a single apostrophe in those cells, so that Excel will display them as blank).

Sample XML for two such cells is as follows:

  <c r="F6" s="1" t="inlineStr">
    <is>
      <t />
    </is>
  </c>
  <c r="G6" s="1" />

Here, F6 has an apostrophe (i.e. is an empty text cell) and G6 is genuinely blank.

Is is possible to read this 'inline string' (inlineStr) property using EPPlus or otherwise distinguish between these two cells? The ExcelRange / ExcelRangeBase classes have properties such as Value, Formula, Style etc but when examining objects representing the two cells above I can't see any difference between them.

I know I could do this manually by reading the raw XML or by using an alternative library (maybe ClosedXML or similar), but if at all possible I would like to do this using EPPlus.

解决方案

EPPlus uses a null value to represent an empty cell and returns the empty string for a cell containing the empty string.

This applies to both read and write operations.

So, if you write the empty string to a cell using epplus then that cell will contain an empty string (so will appear blank but will not equal 0) whereas if you set a cell's value to null using epplus then it will be empty in excel (and will still appear blank but will be equal to 0)

The same applies to read operations. You can read the empty string from a cell's value if you have used the 'apostrophe trick' but will get a null reference for the value of an empty cell.

这篇关于EPPlus可以区分Excel工作表中的空白单元格和空白文本单元格吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！