C# NPOI: Get the list of cell values in a named range

user_15722763 0
Viewed 246

I have a .xls file with plenty of named ranges containing values I would like to extract. The end goal is to automate that data extraction, but there is no fixed template apart from the named ranges.

I'm pretty new with the NPOI package and the farthest I could get was to retrieve the formula of the named range, though I'm not sure if that's the right way to go, since it involves parsing the returned string.

var file = new HSSFWorkbook(new FileStream(@"D:\test.xls", FileMode.Open));
var range = file.GetName("Test");
var test = range.RefersToFormula;
Console.WriteLine(test);

returns (as a string)

Sheet2!$C$2:$C$3

Instead of that formula, I would like to extract the values of the cells contained in that $C$2:$C$3 range. Is there an easier way to do that other than string parsing?

Thanks a lot!

npoi c#
1 Answers

I found the answer, if it can help somebody.

For a ranged column:

        var file = new HSSFWorkbook(new FileStream(@"D:\test.xls", FileMode.Open));
        var range = file.GetName("Test");
        var rangeAdresses = new NPOI.HSSF.Util.RangeAddress(range.RefersToFormula);
        var fromCell = new CellReference(rangeAdresses.FromCell);
        var toCell = new CellReference(rangeAdresses.ToCell);
        for (int i = fromCell.Row; i <= toCell.Row; i++) {
            var cell = file.GetSheetAt(0).GetRow(i).GetCell(fromCell.Col);
            Console.WriteLine(cell.StringCellValue);
        }
user_157227630
Related