Apache POI removes CellStyle from workbook

Question

Using apache POI ... I used workbook.CreateCellStyle (), if after some time I needed to delete the created CellStyle ... How to remove it from the book? I see that it still remains, even if it is not in use.

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

Answer 1

Starting with r1391891 , HSSFOptimiser will also remove unused styles in addition to removing duplicate cell styles.

So, grab yourself a new nightly build of the / 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 not contain duplicate cell styles, as well as unused cell styles. (You can easily put the optimization step at the end of the file creation, if it does not already exist)

Note. The HSSFOptimiser approach will only work for .xls files, not for XSSF.xlsx. It should be possible to generalize the approach with not too much work, but at the moment it is HSSF only ....

Answer 2

Judging by the source, the following method removes unused CellStyles:

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

Answer 3

There is no direct method in the current source code:

This creates the style:

 public HSSFCellStyle createCellStyle() { ... ExtendedFormatRecord xfr = workbook.createCellXF(); short index = (short) (getNumCellStyles() - 1); HSSFCellStyle style = new HSSFCellStyle(index, xfr, this); return style; } 

from

 public short getNumCellStyles() { return (short) workbook.getNumExFormats(); } 

and (in InternalWorkbook)

 public int getNumExFormats() { ... return numxfs; } 

and with workbook.createCellXF () allowing:

 public ExtendedFormatRecord createCellXF() { ExtendedFormatRecord xf = createExtendedFormat(); records.add(records.getXfpos()+1, xf); records.setXfpos( records.getXfpos() + 1 ); numxfs++; return xf; } 

So from the HSSFWorkbook you can call:

 InternalWorkbook getWorkbook() { return workbook; } 

and then on the InternalWorkbook object:

 public ExtendedFormatRecord getExFormatAt(int index) { int xfptr = records.getXfpos() - (numxfs - 1); xfptr += index; ExtendedFormatRecord retval = ( ExtendedFormatRecord ) records.get(xfptr); return retval; } public void removeExFormatRecord(ExtendedFormatRecord rec) { records.remove(rec); // this updates XfPos for us numxfs--; } 

So, to do this briefly, from the top book, something like this:

  InternalWorkbook w = workbook.getWorkbook(); ExtendedFormatRecord record = w.getExFormatAt(index); w.removeExFormatRecord(record); 

This is very terrible :)

Answer 4

Getting a cell and setting NOTHING in style can work:

 HSSFRow hr= workBook.getSheet("SheetName").getRow(rowIndex); HSSFCell hc=hr.getCell(cellIndex); hc.setCellStyle(null); 

Try it, it might work for you.

Answer 5

You can work around this problem by copying the data with any required cells into a newly created workbook. The violinist "survival" in the cell looks bad or not well thought out.