JXLS Set Sheet Name

Question

I would like to provide a way for a sheet name to be specified by a command. The sheet names should be dynamically created based on the content in the sheet.

I note that it is possible to specify fixed sheet names for dynamic sheets when using the multisheet attribute of each-command.

1. In the version 1 documentation there is a reference to the ability to rename a sheet using ${workbook.setSheetName(0, department.name)}. Can this be used in v2? How is it supposed to work? Would workbook need to be added to the context or was it previously available?

2. I tried renaming the sheet in a custom command but it seems that there is too much dependence on sheet names in XlsArea for the name to be changed halfway through XlsArea.applyAt. The template sheet is not deleted and processing is not completed.

3. I thought about trying to get the custom command to add an area listener to change the sheet name. But the area listeners are only called on the parent area which I cannot access from within the command.

Thanks,

Wayne.

Answer 1

I doubt this is the correct way to do it, but til now this is the only way I've managed to do it:

Context context = PoiTransformer.createInitialContext();
Workbook workbook = WorkbookFactory.create(templateInputStream);
workbook.setSheetName(0, "newName");//Changing name of the first sheet
PoiTransformer transformer = PoiTransformer.createTransformer(workbook);
transformer.setOutputStream(resultOutputstream);
JxlsHelper.getInstance().processTemplate(context, transformer);

Answer 2

It can be done using folowing method of XLSTransformer class.

XLSTransformer transformer = new XLSTransformer();
setSpreadsheetToRename(oladSheetName, String newSheetName) 

Answer 3

If you use multisheet generation feature you can just pass your own CellRefGenerator to Each-Command and create the sheet names dynamically in it.

Currently it is not possible to pass the CellRefGenerator in Excel template but it should be quite straightforward to add this functionality.

Or you can set the CellRefGenerator with Java code as shown in the documentation.