Generate Excel using Apache POI

Question

I have generated an excel (.xls) document using Apache POI. I use HSSF classes.

Could someone advise me on the following question:

How can we keep the column headers in the diaplay part constant and have scroll only on the data part?

That is, I want the header columns to be always visible when the excel file is scrolled down.

Thanks, David

Answer 1

One option may be the repeating rows suggestion from timboo. Not sure on your exact needs, but it's possible you might want a Freeze Pane instead.

To have the first row always on screen, never scrolling off, you'd do:

Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("No Scroll");
sheet1.createFreezepane(0,1);

You can freeze rows, columns or both, see this javadoc or this for details

Answer 2

Check out the Apache POI Busy Developers Guide:

It's possible to set up repeating rows and columns in your printouts by using the setRepeatingRowsAndColumns() function in the HSSFWorkbook class.

This function Contains 5 parameters. The first parameter is the index to the sheet (0 = first sheet). The second and third parameters specify the range for the columns to repreat. To stop the columns from repeating pass in -1 as the start and end column. The fourth and fifth parameters specify the range for the rows to repeat. To stop the columns from repeating pass in -1 as the start and end rows.

Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
Sheet sheet2 = wb.createSheet("second sheet");

// Set the columns to repeat from column 0 to 2 on the first sheet
wb.setRepeatingRowsAndColumns(0,0,2,-1,-1);
// Set the the repeating rows and columns on the second sheet.
wb.setRepeatingRowsAndColumns(1,4,5,1,2);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

Answer 3

This should help you. I've searched for days and found this. This solved my problem.

 `Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet1 = wb.createSheet("new sheet");

    Row row = sheet1.createRow((short) 0);
    // Create a cell and put a value in it.
    row.createCell(0).setCellValue(
            createHelper.createRichTextString("Freeze"));
    for (int i = 1; i < 20; i++) {
        row = sheet1.createRow((short) i);
        row
                .createCell(0)
                .setCellValue(
                        createHelper
                                .createRichTextString("This is the Moving Row"));
    }

    // Freeze just one row
    sheet1.createFreezePane(0, 1, 0, 1);`