Apache POI delete CellStyle from workbook

Question

Using apache POI ... I used workbook.CreateCellStyle(), if after a while I needed to delete the CellStyle created ... How do I remove it from the workbook? I can see it still remains even if it is unused.

What I need is something like workbook.deleteCellStyle(cellStyle.getIndex());

Answer 1

Judging from the source the following method deletes unused CellStyles:

org.apache.poi.hssf.usermodel.HSSFOptimiser.optimiseCellStyles(HSSFWorkbook)

Answer 2

As of r1391891, HSSFOptimiser will also remove un-used styles, in addition to removing duplicate cell styles.

So, grab yourself a recent nightly build / svn checkout build (or just wait for the 3.9-beta1 release in a month or so!), and then do something like:

NPOIFSFileSystem poifs = new NPOIFSFileSystem(new File("/path/to/excel/file.xls"));
HSSFWorkbook wb = new HSSFWorkbook(poifs.getRoot());
HSSFOptimiser.optimiseCellStyles(wb);

FileOutputStream fout = new FileoutputStream("optimised.xls");
wb.write(fout);
fout.close()

After that, optimsed.xls will contain no duplicated cell styles, and no un-used cell styles. (You could easily put the optimise step at the end of creating the file, if it's not already existing)

Note - the HSSFOptimiser approach will only work for .xls files, not for XSSF .xlsx ones. It should be possible to generalise the approach with not too much work, but for now it's HSSF only....