ThinkTibits!: POI Hide UnHide Rows Columns Java Example

In this tutorial, we will discuss how to hide / unhide rows / columns in an Excel worksheet using Apache POI, with suitable Java Examples.We will cover the following topics in this example:

Hide Single Row of Data
Hide Multiple rows in a worksheet
Hide rows with setZeroHeight method
Unhide rows (single / multiple) from a worksheet
Hide single / multiple columns
Unhide colums in a worksheet
Hide worksheets using Apache POI

A step by step guide will be provided where possible for all the cases above. The examples in this post are applicable for both new workbooks / modification of existing workbooks - you can use them where appropriate.A complete Java Program which you can use to practice hide / unhide snippets from each section, is provided at the end;

Excel POI Hide Single Row Example

To hide a single row in your Excel workbook, you can invoke the setHidden method in CTRow class. You get access to CTRow via getCTRow method in XSSFRow class. You can do this as you create the rows in the worksheet. Alternatively, you can loop through a worksheet by reading it and hide rows that are not required on the same approach (Hide rows on existing workbooks). The key Java code segment is shown below:

                import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
                ....
                ...
                ...
                /* Row #1 */
                XSSFRow row2 = sheet.createRow(1);
                XSSFCell cell21 = row2.createCell(0);
                cell21.setCellValue("Matt");
                XSSFCell cell22 = row2.createCell(1);
                cell22.setCellValue("English");
                XSSFCell cell23 = row2.createCell(2);
                cell23.setCellValue(54);
                /* Hide Row using getCTRow and setHidden Method */
                row2.getCTRow().setHidden(true);

Excel POI Hide Multiple Rows Example

To hide multiple rows in a worksheet, you have to invoke setHidden method against multiple rows by iteratively looping through them on by one. You follow the approach to hide single rows against a set of rows inside a loop. Inside the loop, you can have the condition to be checked for hiding – if required. The Java code to hide multiple rows is provided below:

                import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
                ..
                ..
                /* Hide Multiple Rows Iteratively*/
                /* Add conditions inside this loop to conditionally hide rows */
                XSSFRow r1;
                for(Row r : sheet) 
                {
                        r1=(XSSFRow) r;
                        r1.getCTRow().setHidden(true);
                }

POI Hide Rows - setZeroHeight Method

You can also hide rows (set them invisible) inside a worksheet by using setZeroHeight method in XSSFRow class. This meets the same outcome and makes the rows invisible in the worksheet. You can also follow this iteratively across rows and columns and hide multiple rows by this approach. The Java Code segment is shown below:

                /* Row #2 */
                XSSFRow row3 = sheet.createRow(2);
                XSSFCell cell31 = row3.createCell(0);
                cell31.setCellValue("Pitt");
                XSSFCell cell32 = row3.createCell(1);
                cell32.setCellValue("English");
                XSSFCell cell33 = row3.createCell(2);
                cell33.setCellValue(90);
                /* Hide Rows using setZeroHeight Method */
                row3.setZeroHeight(true);

Code to hide multiple rows by setZeroHeight method:

                /* Hide Multiple Rows using setZeroHeight*/
                /* Add conditions if required */
                XSSFRow r1;
                for(Row r : sheet) 
                {
                        r1=(XSSFRow) r;
                        r1.setZeroHeight(true);
                }

You should not use setHeight method and pass a value of 0 to it, as it does not hide the rows but rather shrinks the height to near zero.

Excel POI Unhide Rows Java Example

To unhide rows from a worksheet, you can first use getZeroHeight method to check if the row is hidden. If the row is hidden, then you can use setZeroHeight method and send a value of False to it to unhide a row. You can repeat the same process across all the rows that are hidden. Alternatively, you can fetch a single row using getRow method in XSSFSheet and invoke setZeroHeight against the row. You can also use setHidden method and pass false to unhide a row. Java examples are as under:

                /* Unhide Single Row - Hides second row, 0 based*/
                sheet.getRow(1).setZeroHeight(false);
                ...
                ...
                /* Unhide multiple Rows - over a loop */
                XSSFRow r1;
                for(Row r : sheet) 
                {
                        r1=(XSSFRow) r;
                        r1.setZeroHeight(false);//unhide
                }
                ..
                ..              
                /* Use setHidden method to unhide */
                sheet.getRow(2).getCTRow().setHidden(false);

Excel POI Hide Column - Java example

Off to columns now, you need to follow a slightly different method to hide columns in a worksheet. The code to hide a single column in Excel using POI, uses setColumnHidden method and passes column index and hidden state to it. A code snippet is shown below:

                /* Hide column in worksheet */
                /* Pass column ID (0 based) and set True to Hide */
                sheet.setColumnHidden(1,true);

Hide Multiple Columns using Apache POI

You can invoke setColumnHidden method multiple times, to hide multiple columns in a worksheet. Interestingly, POI implementation adds one line for every column that is hidden to the worksheet XML file as captured below:

<cols>
<col min="2" max="2" hidden="true" width="8.0" customWidth="false"/>
<col min="3" max="3" hidden="true" width="8.0" customWidth="false"/>
</cols>

However, Microsoft Office implements this differently by setting min=2 and max=3 and just having a single col tag as shown below:

<col min="2" max="3" hidden="true" width="8.0" customWidth="false"/>

You can just follow what POI does here, however it is good to know that such an approach exists, that you can take advantage of using OOXML if required. However, if you use max based approach, your columns will have to be consecutive to each other.

You can also try hiding a column by using setColumnWidth method and pass 0 as width to a column, but this does not set the hidden parameter against a column to true. It only makes the width to 0 and cannot be unhidden in Excel like a standard hidden column. If you want to make the width of a column to zero, you can follow the code as shown below:

                /* Set column width to 0 to make a column invisible */
                /* Does not hide the column */
                sheet.setColumnWidth(1,0);

Excel POI Unhide Columns - Java Example

To unhide columns from a worksheet, you can use the same setColumnHidden method and pass a value of false against a column ID. If you want to check a column is hidden or not, you can use isColumnHidden method to do so. That way, you can unhide a column only if it is hidden. (POI does not throw an error if you accidentally hide a column that is hidden already or vice versa). The Java code to unhide columns from a worksheet is shown below:

                /* Unhide column in a worksheet */
                /* Set parameter to false in setColumnHidden to unhide */
                sheet.setColumnHidden(1,false);

Hide worksheets using Apache POI

You can use the method setSheetHidden to hide a worksheet using Apache POI. This method is a part of XSSFWorkbook class. Make sure you have more than one sheet in the workbook when you hide one of the sheets. Otherwise, you will get an error when this sheet (No compile / Run time errors !) is opened in Microsoft Excel. The famous error

Excel found unreadable content in …

The Java code snippet to hide a worksheet in a workbook is shown below:

                /* Hide Worksheet - Apache POI */
                /* Pass Sheet ID as Zero Based */
                new_workbook.setSheetHidden(0,true);

To try the examples in this post, you can use the AutoFilter example and replace the code where required.
Check out all our Apache POI Tutorials.

Hope this tutorial helped you to solve your hide / unhide problems in rows / columns using Apache POI and Java. If you are stuck, you can post a comment on the blog so we can help.

POI Hide UnHide Rows Columns Java Example