I'm currently using NPOI library to read the value of all cells in one row of a xlsx file and return a List<string>
. However, if a cell is part of a merged cell, then I need to get the value of that merged cell instead of a blank value. (In NPOI, if a cell is part of a merged cell but is not the top-most and left-most cell in that merged region, then when using FormatCellValue
on that cell, we got a empty string).
Let say that I have already retrieved the necessary row and get an IRow
object, and there is no empty cell at the left of this row. Also, my data will always be string
, there will be no other data type.
This is what I came up with:
private List<string> readRow(IRow row)
{
var rs = new List<string>();
// Since there would be no emtpy cell,
// we start from the left-most cell and read until the end.
for (int colIndex = 0; colIndex < row.LastCellNum; colIndex++)
{
var cell = row.GetCell(colIndex);
// Get the value of this cell, whether it is part of a merged cell or not.
var cellValue = getCellValue(cell);
rs.Add(cellValue);
}
return rs;
}
And the method to get cell value:
private string getCellValue(ICell cell)
{
var dataFormatter = new DataFormatter(CultureInfo.CurrentCulture);
// If this is not part of a merge cell,
// just get this cell's value like normal.
if (!cell.IsMergedCell)
{
return dataFormatter.FormatCellValue(cell);
}
// Otherwise, we need to find the value of this merged cell.
else
{
// Get current sheet.
var currentSheet = cell.Sheet;
// Loop through all merge regions in this sheet.
for (int i = 0; i < currentSheet.NumMergedRegions; i++)
{
var mergeRegion = currentSheet.GetMergedRegion(i);
// If this merged region contains this cell.
if (mergeRegion.FirstRow <= cell.RowIndex && cell.RowIndex <= mergeRegion.LastRow &&
mergeRegion.FirstColumn <= cell.ColumnIndex && cell.ColumnIndex <= mergeRegion.LastColumn)
{
// Find the top-most and left-most cell in this region.
var firstRegionCell = currentSheet.GetRow(mergeRegion.FirstRow)
.GetCell(mergeRegion.FirstColumn);
// And return its value.
return dataFormatter.FormatCellValue(firstRegionCell);
}
}
// This should never happen.
throw new Exception("Cannot find this cell in any merged region");
}
}
I was surprised to find that there is no simpler way to get value of merged cell using NPOI. What can I improve here?