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

Gagravarr · Accepted Answer · 2011-08-01 09:24:26Z

4

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

answered Aug 1, 2011 at 9:24

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dertkw · Accepted Answer · 2011-08-01 07:15:50Z

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();

answered Aug 1, 2011 at 7:15

dertkw

7,8585 gold badges39 silver badges45 bronze badges

1 Comment

user755806 Over a year ago

Hi Timbooo,I have tried but it is not repeating. i have 21 columns and 335 rows. please help me how to achieve it.

chZek · Accepted Answer · 2013-07-10 05:39:03Z

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);`

Collectives™ on Stack Overflow

Generate Excel using Apache POI

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related