Freeze Panes in Excel

When working with large spreadsheets, scrolling through rows and columns can make it hard to keep track of important headers or key reference columns. Microsoft Excel’s Freeze Panes feature solves this problem by letting you lock specific rows or columns in place while you navigate the rest of your data.  This means that locked rows or columns will always be visible while you scroll through the worksheet.

Java Code

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.DataEndpoint;
import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.excel.ExcelDocument;
import com.northconcepts.datapipeline.excel.ExcelWriter;
import com.northconcepts.datapipeline.job.Job;

import org.apache.log4j.Logger;

public class FreezePanesInExcel {

    public static final Logger log = DataEndpoint.log;

    public static void main(String[] args) {
        DataReader reader = new CSVReader(new File("example/data/input/patient-visits-raw-10000.csv"))
                .setFieldNamesInFirstRow(true);

        ExcelDocument document = new ExcelDocument();
        DataWriter writer = new ExcelWriter(document).setSheetName("balance")
                .setFreezeRows(1) // freeze pane is applied to the first row only.
                .setFreezeColumns(2); // freeze pane is applied to the first two columns.

        Job.run(reader, writer);
        
        document.save(new File("example/data/output/patient-visits-with-freeze-pane.xlsx"));
    }

}

Code Walkthrough

1. Create a reader to read records. Here, we are reading a CSV file using CSVReader.
2. ExcelDocument and ExcelWriter instances are created.
3. Set the number of rows starting from the first row to be locked using ExcelWriter.setFreezeRows(Integer).
4. Set the number of columns starting from the first column to be locked using ExcelWriter.setFreezeColumns(Integer).
5. You can set either of them or both to freeze specific rows and/or columns.
6. Finally, the Excel document is saved via the ExcelDocument.save method using the file path of the output file patient-visits-with-freeze-pane.xlsx.

ExcelWriter and ExcelDocument

ExcelWriter is an output writer which can be used to write to an Excel file. It is a sub-class of AbstractWriter.html and overrides the open and close among other methods. ExcelDocument is a class that encapsulates an Excel document. Normally, when CSV or other types of files are written, the output file is saved via the writer itself. For ex. you can refer to the write to a CSV file example. However in the case of Excel documents, the files need to be opened/saved via ExcelDocument class. ExcelWriter is used purely for writing to the Excel file.