Read excel using npoi cell range address

Question

I know that NPOI is very awesome for create excel file. And now I want read excel in specific column. This is my excel format :

           D       E       F       G       H
3          Col1    Col2    Col3    Col4    Col5
4                  a               1       1/1/2014
5                  b               2       2/1/2014
6                  c               3       3/1/2014
7                  d               4       4/1/2014
8                  e               5       5/1/2014

I just wanna get Data in Col2, Col4, and Col5. It should be like this :

Col2   Col4   Col5
a      1      1/1/2014
b      2      2/1/2014
c      3      3/1/2014
d      4      4/1/2014
e      5      5/1/2014

what must i do ? can i use range address to get specific column and then get cell value ?

thanks in expert.

Answer 1

If you have a fixed worksheet template, the most simple way to achieve what you wanted would be to loop over the rows and retrieve the cell value at the specified index.

For example, based on OP's example:

var sheet = workbook.GetSheetAt(0); // assuming 0 is the worksheet index
for (var i = 0; i <= sheet.LastRowNum; i++)
{
  var row = sheet.GetRow(i);
  if (row == null) continue;

  // do your work here
  Console.Write("{0}\t", row.GetCell(4));
  Console.Write("{0}\t", row.GetCell(6));
  Console.Write("{0}\t", row.GetCell(7));
  Console.WriteLine();
}

But if you insist on using range address, you could try using the CellRangeAddress class like this:

var sheet = workbook.GetSheetAt(0);
var range = "E3:H8";
var cellRange = CellRangeAddress.ValueOf(range);      

for (var i = cellRange.FirstRow; i <= cellRange.LastRow; i++)
{
  var row = sheet.GetRow(i);
  for (var j = cellRange.FirstColumn; j <= cellRange.LastColumn; j++)
  {
    // skip cell with column index 5 (column F)
    if (j == 5) continue;

    // do your work here
    Console.Write("{0}\t", row.GetCell(j));                             
  }

  Console.WriteLine();
}