Accessing column headers of an excel file in Java

Question

I want to access data related to headers in my excel file . For example, as shown in attached image , 724 is related to ValueSetId and RediffZone is related to ProductName.

I want some operations which will be based on headers, for example , for all the users with sampleName Cat and ValuseSetId 724 ,the valueSetID should be incremented by a random number .

And I have made headers in excel file using insert table menu and setting headers. I think that is the only way to specify headers in excel for your columns .

My excel is like this:( i couldn't attach image as I don't have enough point,sorry abt that !!)

ProductName      SampleName      Categoryname         ValueSetID
----------------------------------------------------------------------------
Sherrif  /           She /            Primal  /            256
----------------------------------------------------------------------------
CateZore /           Cat /            Non-Primal /          724
------------------------------------------------------------------------------
RediffZone /          Red /            Primal /             991
-------------------------------------------------------------------------------

And Header header=sheet.getHeader(); don't give those headers abt which I am talking .

possible duplicate of [Get columns' names in excel file using Apache POI](http://stackoverflow.com/questions/8202319/get-columns-names-in-excel-file-using-apache-poi) — Erich Kitzmueller, Jun 26 '14 at 12:29

Jamie Cockburn · Answer 1 · 2014-06-26T12:52:21.893

1

getHeader() has nothing to do with the column headings, that function is to do with page headings when the document is printed.

Excel, and therefore POI have no concept of the columns themselves having headers. You just need to access the data in the first row of the sheet:

// Assuming "column headers" are in the first row
XSSFRow header_row = sheet.getRow(0);

// Assuming 4 columns
for (int i = 0; i < 4; i++) {
     XSSFCell header_cell = header_row.getCell(i);
     String header = header_cell = header_cell.getStringCellValue();
     // Do something with string
}

If you wanted a variable number of columns, you could instead loop until the cell value is blank.

edited Jun 26 '14 at 12:52

answered Jun 26 '14 at 12:47

Jamie Cockburn

7,379
1
24
37

AFAIK, excels do have the concept of headers but its just you can't change the default column/row headers and moreover you can hide/display the headers with some settings . – Abhishek Jun 26 '14 at 12:53
Thanks for your comments, but I am able to iterate over rows and cells in sheet to get values, but my use case is not just about iteration . – Abhishek Jun 26 '14 at 12:56

score 0 · Answer 2 · answered Aug 16 '19 at 09:14

I think you can't tell if the cell is a Header or not. However you can get its style using getCellStyle()

Then you might be able to say if it is a header based on the font style/size/color etc ..

Here is example to check if the first cell's style is bold:

XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(0);
XSSFCell cell = row.getCell(0);
XSSFCellStyle style = cell.getCellStyle();
boolean isBold = style.getFont().getBold();

System.out.println("isBold " + isBold);

score 0 · Answer 3 · answered Dec 12 '20 at 12:32

I would recommend using some Class mapping to make it easier. This way all the headers can be mapped to the Class properties and you can compare these properties or play with them. Poiji library provides a simple way to do that. But as mentioned in other answers you can iterate over the header row(0) and get an arraylist of the headers sequentially. So instead of:

Header header=sheet.getHeader();

may be you can write:

Header header=constantList.get(this.getColumnIndex()); //this = current cell

Accessing column headers of an excel file in Java

3 Answers3