How to hide different columns in different sheets of Excel using jXLS library to generate excel reports

Question

I am using jXLS library to generate excel reports.

I am using a generic template for all the reports and the column headers are populated from query result set metadata.

My generic template would contain 50 columns and if the result set has only 20 columns all other would be hidden. I can achieve this easily if its single worksheet in an excel by using xlsTransformer.setColumnsToHide() method.

The problem I am facing is my report can have multiple sheets and each sheet might have a different set of columns and I would need to hide different columns in different sheets but I can only hide columns for all the sheets combined as transformer.setColumnsToHide is set at document level.

Answer 1

You can set individual columns on specific sheets hidden by creating a CellView that is hidden and assigning it to the columns:

CellView hidden = new CellView();
hidden.setHidden(true);

sheet.setColumnView(1,hidden);
sheet.setColumnView(7,hidden);