Apache POI Locking Header Rows

Is anyone out there familiar with a way to lock a row in a spreadsheet created with Apache POI 3.7? By locking I mean that I want the title row for the columns to remain visible when the user is scrolling through the rows. My created spreadsheet will have 500 rows and it would be beneficial if the column’s names were always visible.

In case you need to Freeze any particular row anywhere in the sheet you can use (Within org.apache.poi.ss.usermodel.Sheet) (Available in POI 3.7 as well)

Sheet.createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)

In your case if you want to freeze just your first x rows then the int leftmostColumn, int topRow section will get removed and you can use just

Sheet.createFreezePane(int colSplit, int rowSplit)

for example

sheet1.createFreezePane(0, 5); // this will freeze first five rows

To do this, you can create a freeze pane as follows:

workbook.getSheetAt(workbook.getActiveSheetIndex()).createFreezePane(0, 1);

This will freeze the first row in place. There's another method with more options, so check out the API.

The only thing to note would be if you're using XSSF workbooks - there is a mention of a bugfix in version 3.8-beta3 that fixed the behavior of freeze panes using XSSF spreadsheets:

50884 - XSSF and HSSF freeze panes now behave the same(poi-developers)

I don't know the details of this, but it would be worth investigating if you're in that boat.

You can not freeze a middle row without getting the rows above it also freezed.

Say you have 100 rows and your header row is at line 50. You might expect that only row 50 gets locked so that when scrolling from line 1-49, everything is scrolled up and when it reaches line 50, the 50th row scrolls to the top and stays there when lines 51-100 is scrolled.

But, there is a workaround. What you can do is, group the rows and then freeze them.

First, group the rows from 1-49 and then freeze panes from 1-50. Now the user can minimize the group and then work with the table with the table header locked and at the top.

sheet.groupRow(0, 49);
sheet.createFreezePane(0, 50);

There is a small catch though. MS Excel won't let you expand/collapse a group if the sheet is protected. For this you need to write a Macro.