POI Java Excel solution - Indentation value

Question

Hi,

It's my first experience working with POI (Java Excel solution) and I am trying to set the indentation in one of the rows to 17 as below:

CellStyle style = repSheetPositions.getRow(2).getCell(0).getCellStyle();

When I check the indentation's value is:

short a = style.getIndention();

a = 15

After I change the value to:

repSheet.getRow(2).getCell(0).getCellStyle().setIndention((short) 17); 

and this time the value is:

short a = style.getIndention();

a = 1

Could you please help me?

Many thanks!

Answer 1

The problem is that Excel 2003 has a limit on the maximum indention for a cell. According to this article, Excel's maximum indention for a cell is 15.

The maximum indent value you can use is 15.

The HSSFCellStyle class must be taking this in to account by taking your value and keeping the remainder when dividing by 16. These are the outputs I get with different inputs:

15 => 15
16 => 0
17 => 1
18 => 2
31 => 15
32 => 0

However, when using an XSSFCellStyle (used for .xlsx workbooks, Excel 2007+), this issue disappears. With an XSSFCellStyle, I can set 17 and get 17 back.

If you use Excel 2003 and before (.xls), then there's nothing you can do; it's an Excel limit. However, Excel 2007+ supports indentions greater than 15. The workaround is to use an .xlsx workbook so Apache POI uses an XSSFCellStyle, which will support setIndention(short (17)) properly.