Title says it all -- need to hide all rows and columns that are outside of the the rows and columns containing my data.

I have tried several options:

1. How to hide the following Un-used rows in Excel sheet using Java Apache POI?
2. Permanently Delete Empty Rows Apache POI using JAVA in Excel Sheet
3. How to hide the following Un-used rows in Excel sheet using Java Apache POI?

But these never produce the desired effect. I'm using apache poi version 4.1.1

See the following screenshots showing the excel format I have versus the format I want. (Since I am new on stackoverflow, it doesn't allow me to embed the pictures directly. Weird I know.)

What I have

What I need

CodePudding user response：

Mark the first "outside" column, hold CTRL SHIFT and then right arrow. Then, all columns should be highlighted. Right click, select "Hide".

Repeat the same with rows, select the first row outside of your data, hold CTRL SHIFT and press Arrow Down.

Best of luck! ^_^

CodePudding user response：

Hiding unused rows and columns is not provided by high level classes of apache poi until now.

Hiding unused rows is a setting in sheet format properties of Office Open XML, the format of XSSF (*.xlsx). There is defined how to handle rows per default. For example default row height. But there also can be set that rows are zero height per default. So only used rows, which have cells having content or format are visible. As apache poi does not have a method to set SheetFormatPr.setZeroHeight we need using the underlaying org.openxmlformats.schemas.spreadsheetml.x2006.main.* classes.

In binary BIFF format of HSSF (*.xls) hiding unused rows is a setting in DEFAULTROWHEIGHT record within the worksheet's record stream. There option flags can be set. Option flag 0x0002 means hiding unused rows. To set that using apache poi we need access to the org.apache.poi.hssf.record.DefaultRowHeightRecord. This only can be got from InternalSheet.

Hiding columns could be done using Sheet.setColumnHidden, but only for single columns. So to hide 100 columns one needs calling Sheet.setColumnHidden 100 times.

Excel also provides settings for column ranges from min column to max column. But Apache poi does not providing high level methods for this.

Using XSSF (Office Open XML) we need the org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols to get or set a org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol having the appropriate min and max and setHidden(true).

Using HSSF (BIFF) we need get or set the COLINFOrecord from/to the the worksheet's record stream which has the appropriate min and max and and setHidden(true).

The following complete example shows the code sample for the above. It uses ExcelExampleIn.xlsx or ExcelExampleIn.xls as input and sets unused rows hidden and sets columns hidden from given min to max column.

Tested and works using apache poi 4.1.1.

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.hssf.record.DefaultRowHeightRecord;
import org.apache.poi.hssf.record.ColumnInfoRecord;
import org.apache.poi.hssf.record.RecordBase; 

import java.util.List;

public class ExcelHideUnusedRowsAndColumns {
    
 static void setUnusedRowsHidden(Sheet sheet) throws Exception {
  if (sheet instanceof XSSFSheet) {
   // in OOXML set zeroHeight property true for all undefined rows, so only rows having special settings are visible
   XSSFSheet xssfSheet = (XSSFSheet)sheet;
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet();
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr ctSheetFormatPr = ctWorksheet.getSheetFormatPr();
   if (ctSheetFormatPr == null) ctSheetFormatPr = ctWorksheet.addNewSheetFormatPr();
   ctSheetFormatPr.setZeroHeight(true);
  } else if (sheet instanceof HSSFSheet) {
   // in BIFF file format set option flag 0x0002 in DEFAULTROWHEIGHT record
   HSSFSheet hssfSheet= (HSSFSheet)sheet; 
   java.lang.reflect.Field _sheet = HSSFSheet.class.getDeclaredField("_sheet");
   _sheet.setAccessible(true); 
   InternalSheet internalSheet = (InternalSheet)_sheet.get(hssfSheet);
   java.lang.reflect.Field defaultrowheight = InternalSheet.class.getDeclaredField("defaultrowheight");
   defaultrowheight.setAccessible(true); 
   DefaultRowHeightRecord defaultRowHeightRecord = (DefaultRowHeightRecord)defaultrowheight.get(internalSheet);
   defaultRowHeightRecord.setOptionFlags((short)2);
  }  
 }
 
 static void setColumnsHidden(Sheet sheet, int min, int max) throws Exception {
  if (sheet instanceof XSSFSheet) {
   // respect max column count 16384 (1 to 16384) for OOXML
   if (max > 16384) max = 16384;
   
   // in OOXML set cols settings in XML
   XSSFSheet xssfSheet = (XSSFSheet)sheet;
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet();
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols ctCols = ctWorksheet.getColsArray(0);
   boolean colSettingFound = false;
   for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol ctCol : ctCols.getColList()) {
    if (ctCol.getMin() == min && ctCol.getMax() == max) {
     colSettingFound = true;
     ctCol.setHidden(true);
    }
    System.out.println(ctCol);  
   }
   if (!colSettingFound) {
    org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol ctCol = ctCols.addNewCol();
    ctCol.setMin(min);
    ctCol.setMax(max);
    ctCol.setHidden(true);
    System.out.println(ctCol);  
   }   
  } else if (sheet instanceof HSSFSheet) {
   // in BIFF min and max are 0-based
   min = min -1;
   max = max -1;
   // respect max column count 256 (0 to 255) for BIFF
   if (max > 255) max = 255;
   
   // in BIFF file format set hidden property in COLINFO record
   HSSFSheet hssfSheet= (HSSFSheet)sheet; 
   java.lang.reflect.Field _sheet = HSSFSheet.class.getDeclaredField("_sheet");
   _sheet.setAccessible(true); 
   InternalSheet internalSheet = (InternalSheet)_sheet.get(hssfSheet);
   java.lang.reflect.Field _records = InternalSheet.class.getDeclaredField("_records");
   _records.setAccessible(true);
   @SuppressWarnings("unchecked") 
   List<RecordBase> records = (List<RecordBase>)_records.get(internalSheet);
   boolean colInfoFound = false;
   for (RecordBase record : records) {
    if (record instanceof ColumnInfoRecord) {
     ColumnInfoRecord columnInfoRecord = (ColumnInfoRecord)record;
     if (columnInfoRecord.getFirstColumn() == min && columnInfoRecord.getLastColumn() == max) {
      colInfoFound = true;
      columnInfoRecord.setHidden(true);
     }
     System.out.println(columnInfoRecord);
    }
   }
   if (!colInfoFound) {
    ColumnInfoRecord columnInfoRecord = new ColumnInfoRecord();
    columnInfoRecord.setFirstColumn(min);
    columnInfoRecord.setLastColumn(max);
    columnInfoRecord.setHidden(true);
    records.add(records.size()-1, columnInfoRecord); 
    System.out.println(columnInfoRecord);
   }
  }
 }


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

  String inFilePath = "./ExcelExampleIn.xlsx"; String outFilePath = "./ExcelExampleOut.xlsx";

  //String inFilePath = "./ExcelExampleIn.xls"; String outFilePath = "./ExcelExampleOut.xls";

  
  try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inFilePath));
       FileOutputStream out = new FileOutputStream(outFilePath ) ) {

   Sheet sheet = workbook.getSheetAt(0);
   
   //set unused rows hidden
   setUnusedRowsHidden(sheet);
   
   //set multiple columns hidden, here column 7 (G) to last column 16384 (XFD)
   setColumnsHidden(sheet, 7, 16384);

   
   workbook.write(out);
  }
 }
}