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();
}

Answer 2

I know it's an old question, but there is a really simple solution for reading a file using NPOI. hope this would help someone.

Note that if you have formulas in the excel, this will give you formulas instead of values.

using NPOI.Extension;    

Create a class and set the column index

public class DataItem
{
    [Column(Index = 4)]
    public string E_Column { get; set; }

    [Column(Index = 6)]
    public string G_Column { get; set; }

    [Column(Index = 7)]
    public string H_Column { get; set; }
}

And now for reading the file, I suggest wrapping it with try - catch just in case

private List<DataItem> LoadExcelData()
{
    // Load all the data from sheet 0, starting from row 4
    List<DataItem> result = Excel.Load<DataItem>(@"C:\temp\test.xlsx", 4, 0).ToList();

    return result;
}