0

why is not possible to 'properly' hide an Excel row using Apache POI (3.16)? It is just possible to call (XSSFRow) row.setZeroHeight(), which is also what the Busy developer's guide recommends. However, this is not the same as hiding the row the way Excel does it. You can 'Hide' and 'Unhide' rows with the respective context menu options.

I thought setting the row style should work, but it doesn't. In the resulting Excel file, the row can still be seen. 

package de.mwe;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Assert;
import org.testng.annotations.Test;


public class MWE {

@Test
public void testHidingRows() {
    final XSSFWorkbook wb = new XSSFWorkbook();
    String sname = "HideRowsTestSheet", cname = "TestName", cvalue = "TestVal";
    XSSFSheet sheet = wb.createSheet( sname );
    XSSFRow row = sheet.createRow( 0 );
    XSSFCell cell = row.createCell( (short) 0 );
    cell.setCellValue( cvalue );

    XSSFCellStyle hiddenRowStyle = wb.createCellStyle();
    hiddenRowStyle.setHidden( true );

    row.setRowStyle( hiddenRowStyle );
    Assert.assertTrue( row.getRowStyle().getHidden() );

    try (FileOutputStream fileOut = new FileOutputStream( new File( "target/PoiTestDrive.xlsx" ) )) {
        wb.write( fileOut );
    } catch ( IOException ex ) {
        ex.printStackTrace();
    }

    // does not work, resulting Excel file shows first row.

}

}

Matthias T
  • 1,230
  • 2
  • 10
  • 24
  • because setHidden is for cells not rows? –  Sep 13 '17 at 11:16
  • Possible duplicate of [How to Un-Hide a hidden row in Excel using Apache Poi in Java?](https://stackoverflow.com/questions/6923153/how-to-un-hide-a-hidden-row-in-excel-using-apache-poi-in-java) – Nikolas Sep 13 '17 at 11:19
  • 1
    The [Hiding and Un-Hiding Rows](http://poi.apache.org/spreadsheet/quick-guide.html#Hiding) is correct, except it must be `row.setZeroHeight(true);` for hiding a row. [Row.setZeroHeigh](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Row.html#setZeroHeight(boolean)) does exactly what Excel does while hiding a row. For `XSSF` it simply sets hidden property, see https://svn.apache.org/viewvc/poi/tags/REL_3_16_FINAL/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java?view=markup#l423 – Axel Richter Sep 13 '17 at 11:45

2 Answers2

2

The Busy developer's guide is correct for hiding/unhiding rows. But there is a little error since it must be row.setZeroHeight(true); for hiding a row.

The Row.setZeroHeight(boolean) does exactly what Excel does while hiding a row. It works for HSSF as well as for XSSF. For XSSF it simply sets hidden property to the rows XML, see XSSFRow.java.

Example:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;

public class CreateExcelHiddenRow {

 public static void main(String[] args) throws Exception {

  //Workbook wb = new HSSFWorkbook();
  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet();

  for (int r = 0; r < 3; r++) {
   Row row = sheet.createRow(r);
   Cell cell = row.createCell(0);
   cell.setCellValue("Row " + (r+1));
  }

  Row row = sheet.getRow(1);
  row.setZeroHeight(true);

  //FileOutputStream out = new FileOutputStream("CreateExcelHiddenRow.xls");   
  FileOutputStream out = new FileOutputStream("CreateExcelHiddenRow.xlsx");   
  wb.write(out);
  out.close();
  wb.close();

 }
}
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • You're right, I checked again. Do you know why using the cellStyle does not work? – Matthias T Sep 13 '17 at 12:50
  • `CellStyle` cannot work because the hiding a row is not a cell style but a property of the row. For `XSSF` it is ` – Axel Richter Sep 13 '17 at 12:53
  • Okay, it is somewhat misleading to use the CellStyle class for styling rows if not all properties of the CellStyle apply to rows as well. – Matthias T Sep 13 '17 at 14:29
0

You create a new row style. Instead, fetch the rows' existing Style and add your rule:

currentRow.getRowStyle().setHidden(true);

You kinda can refer to this post

Edit: You even create a new CellStyle as @RC mentioned.

Nikolas
  • 2,066
  • 1
  • 19
  • 20
  • Nope, does not work. Removing the creation of the cellStyle and just calling row.getRowStyle().setHidden(true) returns an NPE, because the row initially has no style. – Matthias T Sep 13 '17 at 11:47