Apache POI Freeze Pane Excel Java Example

In this short tutorial, we will discuss how to freeze panes in an Excel workbook using Java and Apache POI with an example program. Often, you may generate a big Excel document with thousands of rows and would like to freeze the top row before sending the file. We will see how to do this and more in this tutorial. Specifically we will cover;


  • Freeze Top Row
  • Freeze Panes - Random Row
  • Freeze First Column
  • Freeze Rows + Columns

Freeze Top Row


The Java program that freezes top row in an Excel document using POI is provided below. In this program we use createFreezePane method and pass row #1 as input for freeze.
import java.io.FileOutputStream;
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.*;
public class freezeTopRow {  
        public static void main(String[] args) throws Exception{
                /* Read input file for creating Freeze Pane */
                FileInputStream input_document = new FileInputStream(new File("inputFile.xlsx"));               
                XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document);               
                XSSFSheet sheet = my_xlsx_workbook.getSheetAt(0); 
                /* Freeze top row alone */
                sheet.createFreezePane(0,1);
                /* Write Output to File */
                FileOutputStream output_file = new FileOutputStream(new File("Freeze_Pane_Output.xlsx")); 
                my_xlsx_workbook.write(output_file);
                input_document.close(); 
        }
}

Note that we passed the first input as "0" to the method above to signify that we don't want to freeze any columns.

Freeze Rows And Columns


To freeze rows and columns both, you have pass the right row and column reference to the createFreezePane method. As an example, if I pass 1,1 to the method above the top row as well as the first column will stay frozen in the workbook. Given below is an example usage


                /* Freeze top row and first column alone */
                sheet.createFreezePane(1,1);


Freeze Random Row


You can achieve this using the same approach, juts pass the row number or column number you want to freeze and you are done.

Freeze First Column


To freeze first column alone, pass 1,0 to createFreezePane method.

Finally, if you try editing the XLSX document you can observe that the worksheet has a "pane" section added that signifies the freeze in effect. See below:
<pane xSplit="1.0" ySplit="1.0" state="frozen" topLeftCell="B2" activePane="bottomRight"/>

1 comment:

  1. Thanks !!! It helped me a lot !!

    ReplyDelete