20

When I read Excel worksheet using NPOI, empty cells are skipped. For example, it the row contains A, B, , C and I read it using 

IRow row = sheet.GetRow(rowNb)

then row.Cells[1].ToString() will output B (as expected) but row.Cells[2].ToString() will output C instead of an empty string. Is there a way to keep empty cells? Thanks.

Yulia V
  • 3,507
  • 10
  • 31
  • 64

2 Answers2

36

Try the GetCell method with the MissingCellPolicy:

ICell cell = row.GetCell(2, MissingCellPolicy.RETURN_NULL_AND_BLANK);
Richard Deeming
  • 29,830
  • 10
  • 79
  • 151
3

In completion to the accepted answer, the policy can be set on the workbook level as well

workbook.MissingCellPolicy = MissingCellPolicy.RETURN_NULL_AND_BLANK;

This way the policy is applied implicitly when you call GetCell, no need to pass it every time as a parameter

ICell cell = row.GetCell(2);

Note that (at least in the version I'm using) if you do row.Cells[index], it ignores the policy so it only works if you call row.GetCell(index)

SzilardD
  • 1,611
  • 2
  • 22
  • 40
  • Also as a complement, the `MissingCellPolicy.RETURN_NULL_AND_BLANK` makes the return of `row.GetCell()` be null when the cell is null. If you want it to return an empty string, use the `MissingCellPolicy.CREATE_NULL_AS_BLANK` policy. – Caio Tsubake Mar 28 '23 at 18:26