How to use NPOI to read Excel spreadsheet that contains empty cells?
Try the GetCell
method with the MissingCellPolicy
:
ICell cell = row.GetCell(2, MissingCellPolicy.RETURN_NULL_AND_BLANK);
https://github.com/tonyqus/npoi/blob/master/main/HSSF/UserModel/HSSFRow.cs#L444
/// <summary> /// Get the hssfcell representing a given column (logical cell) /// 0-based. If you ask for a cell that is not defined then /// you get a null, unless you have set a different /// MissingCellPolicy on the base workbook. /// </summary> /// <param name="cellnum">0 based column number</param> /// <returns>Cell representing that column or null if undefined.</returns> public ICell GetCell(int cellnum) { return GetCell(cellnum, book.MissingCellPolicy); }
https://github.com/tonyqus/npoi/blob/master/main/HSSF/UserModel/HSSFWorkbook.cs#L429
[NonSerialized] private NPOI.SS.UserModel.MissingCellPolicy missingCellPolicy = NPOI.SS.UserModel.MissingCellPolicy.RETURN_NULL_AND_BLANK; /// <summary> /// Retrieves the current policy on what to do when /// getting missing or blank cells from a row. /// The default is to return blank and null cells. /// </summary> /// <value>The missing cell policy.</value> public NPOI.SS.UserModel.MissingCellPolicy MissingCellPolicy { get { return missingCellPolicy; } set { this.missingCellPolicy = value; } }
/// <summary>Missing cells are returned as null, Blank cells are returned as normal</summary> // Token: 0x04001517 RID: 5399 public static readonly MissingCellPolicy RETURN_NULL_AND_BLANK = new MissingCellPolicy(); /// <summary>Missing cells are returned as null, as are blank cells</summary> // Token: 0x04001518 RID: 5400 public static readonly MissingCellPolicy RETURN_BLANK_AS_NULL = new MissingCellPolicy(); /// <summary>A new, blank cell is Created for missing cells. Blank cells are returned as normal</summary> // Token: 0x04001519 RID: 5401 public static readonly MissingCellPolicy CREATE_NULL_AS_BLANK = new MissingCellPolicy();