Can EPPlus distinguish between blank cells and empty text cells in an Excel worksheet?

Question

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.

Answer 1

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.