How to remove user-defined style for XLS via ApachePOI?

Question

I want remove some predefined styles for XLS - for example "Good". For XLSX there is no problem: create new CTCellStyle (unfortunatelly by reflection), setName("Good"), setBuiltinId(26) and setHidden(true) - now Excel (2016) doesn’t show "Good" style. Can I do sth like this for XLS?

EDIT

Sample code:

Hidding style for XLSX - there is no problem:

StylesTable styleSource = xssfWorkbook.getStylesSource(); // xssfWorkbook is instance of XSSFWorkbook
try {
    // get ctCellStyles (by reflection)
    Field field = StylesTable.class.getDeclaredField("doc");
    field.setAccessible(true);
    Object obj = field.get(styleSource);
    StyleSheetDocument ssd = (StyleSheetDocument) obj;
    CTStylesheet ctStyleSheet = ssd.getStyleSheet();
    CTCellStyles ctCellStyles = ctStyleSheet.getCellStyles();
    // find style "Good"
    for (int i = 0; i < ctCellStyles.sizeOfCellStyleArray(); i++) {
        CTCellStyle ctCellStyle = ctCellStyles.getCellStyleArray(i);
        if (ctCellStyle.getName().equals("Good")) {
            XmlBoolean hiddenXml = XmlBoolean.Factory.newInstance();
            hiddenXml.setStringValue("1");
            ctCellStyle.xsetHidden(hiddenXml);
        }
    }
} catch (Exception e) {}

Hidding style for XLS:

If style exists in workbook I can get it, but how to set it as "hidden"?

try {
    // get InternalWorkbook (by reflection)
    Field field = HSSFWorkbook.class.getDeclaredField("workbook");
    field.setAccessible(true);
    Object iwb = field.get(hssfWorkbook); // hssfWorkbook is instance of HSSFWorkbook
    InternalWorkbook internalWorkbook = (InternalWorkbook) iwb;
    // find style "Good"
    for (int xfIndex = 0; xfIndex < internalWorkbook.getNumRecords(); xfIndex++) {
        // try to get every record as StyleRecord from internalWorkbook
        StyleRecord styleRecord = internalWorkbook.getStyleRecord(xfIndex);
        if (styleRecord != null && styleRecord.getName() != null) {
            if (styleRecord.getName().equals("Good")) {
                new DebugUtil(styleRecord.getName());
                // TODO set here sth like "hidden" for styleRecord or maybe:
                // get style with current id from workbook
                HSSFCellStyle hssfCellStyle = hssfWorkbook.getCellStyleAt((short) xfIndex); // workbook is instance of org.apache.poi.ss.usermodel.Workbook
                // TODO set here sth like "hidden" for hssfCellStyle
            }
        }
    }
} catch (Exception e) {}

Even If I could mark style as "hidden", there is other problem: If I iterate from 0 to internalWorkbook.getNumRecords() I get only existing styles. So if I'm creating workbook self, probably I should create new StyleRecord and/or HSSFCellStyle and mark as "hidden". I tried this:

int size = internalWorkbook.getSize();
StyleRecord newStyleRecord = internalWorkbook.createStyleRecord(size);
HSSFCellStyle newHssfCellStyle = hssfWorkbook.createCellStyle();
newHssfCellStyle.setAlignment((short) 3); // align right, for tests, to see difference between original and created "Good" style
newStyleRecord.setName("Good");
// TODO set here sth like "hidden" for newStyleRecord and/or for newHssfCellStyle

This is the way to set my own "Good" style. If I don't do it, Excel (2016) will show default "Good" style.

score 1 · Answer 1 · answered Nov 03 '15 at 08:12

1

You should be able to use HSSFWorkbook.getCellStyleAt(int index) to access styles at a given position.

answered Nov 03 '15 at 08:12

centic

15,565
9
68
125

But `getCellStyleAt()` returns user-defined style (one of named styles)? I think it returns style for cell. I would like work with object form `StyleRecord` class. Maybe I wrote not clear enough: Excel (2016) has 47 named styles. If I remove one of them for one document, save that document as XLS, open it - deleted style is not shown on list. ... – Piotr Nov 03 '15 at 08:56
... I would like to do the same via ApachePOI: I’ve got style names which should be shown on list, but I need to tell ApachePOI to remove (hide?) others. I’ve got style names which I want hide. So maybe I must create them (by `InternalWorkbook.createStyleRecord(int)` and `Workbook.createCellStyle()`) and “mark” them as hidden? But how? Now if I do nothing, Excel shows all predefined styles (LibreOffice - for example - doesn’t). – Piotr Nov 03 '15 at 08:56
Can you add your sample code to the question, this usually makes it easier to see what happens. – centic Nov 03 '15 at 14:00

How to remove user-defined style for XLS via ApachePOI?

1 Answers1