java - Hide all extra excel columns with Apache-POI

Question

This is my aim:

I am generating an xlsx file using Apache POI. I would like to hide all the unused columns to the right of the columns I am using. In Excel, this is a relatively simple operation: I can select all the extraneous columns and tell them all to hide in one action. In Apache POI, the only option I seem to have is sheet.setColumnHidden(index, true). If I try to do this for every extraneous column:

for (int i = myLastColumn+1; i < 16384; ++i) {
    sheet.setColumnHidden(i, true);
}

then the library tries to create over 16 thousand columns in order to hide them, which is impractical: it gets slower and slower as it goes, and never finishes. There doesn't seem to be a setColumnRangeHidden or anything like that.

Is there a way to hide nearly all the columns in Apache POI?

Answer 1

Eventually, looking through how XSSFSheet and ColumnHelper work, I managed to find the bit of functionality I was missing.

When you try and hide a single column, the ColumnHelper makes a new column for the index you requested (if it didn't exist), and then sets it to be hidden. However, the column object is actually a CTCol with min and max fields set to the same index. If you create a CTCol object with different min and max fields, you can set the state of all matching columns in one action.

Thus:

CTCol col = sheet.getCTWorksheet().getColsArray(0).addNewCol();
col.setMin(myLastColumn+2);
col.setMax(16384); // the last column (1-indexed)
col.setHidden(true);