Get columns' names in Excel file using Apache POI

Question

How to get the column names in an Excel file using Apache POI, to make sure that the columns are ordered as expected.

Do you mean "A", "B", ...? If not, what do you mean by column names? Columns are normally "named" by putting names in a top row. — Ed Staub, Nov 20 '11 at 15:05
@Ed Staub is it possible to get "A" and "B", i have a similar requirement — kavinder, Nov 05 '14 at 12:46

score 35 · Answer 1 · answered Apr 09 '15 at 12:50

There is a convenience method for this:

CellReference.convertNumToColString(cell.getColumnIndex());

To get the full name:

private static String getCellName(Cell cell)
{
    return CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
}

score 9 · Accepted Answer · edited Nov 23 '18 at 08:20

9

Or this:

cell.getSheet().getRow(0).getCell(currentcellIndex)
    .getRichStringCellValue().toString()

Row indexes start from 0.

edited Nov 23 '18 at 08:20

halfer

19,824
17
99
186

answered Apr 12 '12 at 21:15

Josh

138
1
2

2

If anyone can confirm whether rows start from 0 or not, that would be good. "Not sure if" is not ideal to have in an accepted answer. – halfer Jan 25 '17 at 08:54
@halfer I think that depends on how excel sheet in created. – user3153014 Sep 04 '18 at 09:02
1

@halfer Yes. Row's index start from 0. – Toan Dao Nov 23 '18 at 08:07

score 5 · Answer 3 · edited Jan 25 '17 at 08:53

Apache POI, translating Excel column number to letter

      FileInputStream fis = new FileInputStream(
      new File("D:\\workspace\\Writesheet.xlsx"));
      @SuppressWarnings("resource")
      XSSFWorkbook workbook = new XSSFWorkbook(fis);
      XSSFSheet spreadsheet = workbook.getSheetAt(0);

      int lastcell=spreadsheet.getRow(0).getLastCellNum();
      //Non empty Last cell Number or index return

      for(int i=0;i<=lastcell;i++)
      {
          try
          {
              System.out.println(CellReference.convertNumToColString(i));
          }catch(Exception e)
          {}
      }
      fis.close();

score 1 · Answer 4 · answered Mar 31 '18 at 19:55

1

To get cell name:

String cellName = new CellAddress(intRow, intCol).toString();

answered Mar 31 '18 at 19:55

Javier Pino

11
1

1

please add more explanation – Ahmad Al-Kurdi Mar 31 '18 at 19:59

Get columns' names in Excel file using Apache POI

4 Answers4

Linked

Related