How to hide the following Un-used rows in Excel sheet using Java Apache POI?

Question

I am populating a template Excel sheet, with the data from the database :

 for (Map<String, Object> resultRow : dbResults) {
        if ((currentRow = sheet.getRow(currentDataRow)) == null) {
            currentRow = sheet.createRow(currentDataRow);   // Creates a new row.
        }
        //currentRow.getRowStyle().setHidden(false);

        for (int i = 0; i < resultColumns; i++) {
            currentCell = currentRow.getCell(i, Row.CREATE_NULL_AS_BLANK);
            setCellValue(currentCell, resultRow.get(dbcolumnNames[i]));
        }
        currentDataRow += 1;
    }

// How to hide all empty/Un-used rows following currentDataRow ?

Aim to Achieve :

I want that the Un-Used rows following the populated rows should be hidden ?

All Populated rows must be visible.

Eg: If 1st 100 data rows are filled, then rows following 101 and onward should be hidden.

Please, help !!

score 6 · Answer 1 · answered Apr 06 '12 at 08:41

6

Row r = sheet.getRow(indexRow);
if ( r!=null ) {
    r.setZeroHeight(true);
}

answered Apr 06 '12 at 08:41

jezz

61
1
1

1

Well actually this is a workaround, but not what I was looking for ! – Yugal Jindle Apr 06 '12 at 10:48
2

From my testing, this corresponds exactly to the hide/show row feature available in the row header right-click menu. – qualidafial Feb 01 '13 at 00:24

score 3 · Accepted Answer · answered Aug 04 '11 at 09:00

3

POI recognizes the number of logical rows in your sheet when you create it. So as you populate it with 100 rows, it will create 100 records. The rest will appear when you open the XLS in Excel with the default layout - but these are not POI records.

You'll have to create some dummy rows after your last data record like this

for (int i=currentDataRow ;i<65000;i++)
                sheet.createRow(i);

Create a cell style, and set it to hidden

CellStyle hiddenstyle = workBook.createCellStyle();
hiddenstyle.setHidden(true);

Set this style for all rows from your last row to end of sheet

while (rows.hasNext()){
                Row row1 = rows.next ();
                row1.setRowStyle(hiddenstyle);


            }

answered Aug 04 '11 at 09:00

JoseK

31,141
14
104
131

I will try your answer and then later will let you know about the results. – Yugal Jindle Aug 04 '11 at 09:52
I don't know pal whats happening.. `row.setRowStye() method not found`. Similary, row.getRowStyle() method not found.. Actually, I already had a solution but its not working. I don't know why - When its given in the API docs, but not working when using in my program. God Knows ! – Yugal Jindle Aug 07 '11 at 04:07
Yap.. I am using the latest 3.7.. Its weird ! – Yugal Jindle Aug 07 '11 at 05:19

score 3 · Answer 3 · edited May 16 '18 at 09:51

3

row.setRowStye() and row.getRowStye() are present in poi-3.8-beta4.

edited May 16 '18 at 09:51

Draken

3,134
13
34
54

answered Aug 31 '11 at 08:01

mahmudaq

56
2

Can't say for 3.7 but i was using 3.7 beta3 (20100924) and they weren't present there – mahmudaq Dec 12 '11 at 14:02

How to hide the following Un-used rows in Excel sheet using Java Apache POI?

3 Answers3

Linked