CODEWITHSUNDEEP
c#excelnpoi
Fajar Wahyu
Fajar Wahyu

Reputation: 165

Read excel using npoi cell range address

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.

Upvotes: 4

Views: 20827

Answers (2)

Milana
Milana

Reputation: 772

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

Upvotes: 0

IronGeek
IronGeek

Reputation: 4883

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

Upvotes: 14

Related Questions