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....

Answer 3

There is no direct method in the current source code:

This is how the style is created:

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

with

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

and (in InternalWorkbook)

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

and with the workbook.createCellXF() resolving to:

public ExtendedFormatRecord createCellXF() {
    ExtendedFormatRecord xf = createExtendedFormat();

    records.add(records.getXfpos()+1, xf);
    records.setXfpos( records.getXfpos() + 1 );
    numxfs++;
    return xf;
}

So what is possible from the HSSFWorkbook, is to 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 make it short, from the top workbook, something like this:

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

This is all very horrible :)

Answer 4

Getting the cell and setting NOTHING to style may work :

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

Please try this may this will work for you.

Answer 5

You could work around the problem by copying data with any required cellstyles to a newly-created workbook. The cellstyle "survival" behind the scenes appears to be buggy, or not well thought-out.