Dear POI Users,
I've been continuing working with CellStyles in XSSFWorkbooks. My goal is to
deal with user-defined style names in XSSFWorkbooks. In HSSFWorkbooks the
corresponding HSSFCellStyles support methods get/setUserDefinedName.
However, for XSSFCellStyles there currently is no such functionality. The
most reasonable way to me seems to go via ooxml-schemas CTCellStyles
(accessible via
XSSFWorkbook.getStylesSource().getCTStyleSheet().getCellStyles() ...) since
CTCellStyles support a method for setting a name for the corresponding cell
styles. However, dealing with ooxml-schema directly seems like a nightmare
to me. Getting a valid XSSF document (= one that can be opened by Excel) is
tricky and if it can be opened it's likely to be messed up. There seem to be
loads of dependencies between CTCellStyles, CTCellStyleXfs, CTXfs and
more... I don't seem to get my head around these (I've spent hours comparing
underlying XML documents) - and there does not seem to be any documentation
about ooxml-schema (at least I can't find any).
Did anybody already successfully deal with this? Any help is really
appreciated.
Below a piece of code that produces a file that can be opened - but
unfortunately seems to mess up the internal CellStyles.
XSSFWorkbook wb = ...
CTCellStyleXfs ctCellStyleXfs =
wb.getStylesSource().getCTStylesheet().getCellStyleXfs();
if(ctCellStyleXfs == null) {
ctCellStyleXfs =
wb.getStylesSource().getCTStylesheet().addNewCellStyleXfs();
ctCellStyleXfs.setCount(0);
}
CTCellStyles ctCellStyles =
wb.getStylesSource().getCTStylesheet().getCellStyles();
if(ctCellStyles == null) {
ctCellStyles =
wb.getStylesSource().getCTStylesheet().addNewCellStyles();
ctCellStyles.setCount(0);
}
long count = ctCellStyles.getCount() + 1;
CTCellStyle ctCellStyle = ctCellStyles.addNewCellStyle();
ctCellStyle.setName(name);
ctCellStyle.setXfId(count - 1);
ctCellStyles.setCount(count);
Thanks,
Martin
