Index: src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java (revision 1786687) +++ src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java (revision ) @@ -46,22 +46,7 @@ import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder; import org.apache.poi.xssf.usermodel.extensions.XSSFCellFill; import org.apache.xmlbeans.XmlException; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorders; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellStyleXfs; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellXfs; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxf; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxfs; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFill; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFills; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFonts; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTNumFmt; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTNumFmts; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTStylesheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPatternType; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.StyleSheetDocument; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; /** * Table of styles shared across all sheets in a workbook. @@ -710,6 +695,8 @@ CTXf xf = createDefaultXf(); xf.setXfId(0); xfs.add(xf); + + createDefaultCellStyles(doc.getStyleSheet()); } private static CTXf createDefaultXf() { @@ -749,6 +736,27 @@ return xssfFont; } + /** + * Ensures there is at least one cell style in the styles collection. + * See https://msdn.microsoft.com/en-us/library/dd952635(v=office.12).aspx + * @param styleSheet Style sheet + * @return Cell styles collection + */ + private static void createDefaultCellStyles(CTStylesheet styleSheet) { + CTCellStyles cellStyles = styleSheet.getCellStyles(); + if (cellStyles == null) { + cellStyles = styleSheet.addNewCellStyles(); + cellStyles.setCount(0); + } + if (cellStyles.getCount() == 0) { + CTCellStyle normalCellStyle = cellStyles.addNewCellStyle(); + normalCellStyle.setName("Normal"); + normalCellStyle.setXfId(0); + normalCellStyle.setBuiltinId(0); + cellStyles.setCount(1); + } + } + @Internal public CTDxf getDxfAt(int idx) { return dxfs.get(idx); @@ -771,14 +779,10 @@ * Create a cell style in this style table. * Note - End users probably want to call {@link XSSFWorkbook#createCellStyle()} * rather than working with the styles table directly. - * @throws IllegalStateException if the maximum number of cell styles has been reached. + * @throws IllegalStateException if the maximum number of cell styles has been reached. */ public XSSFCellStyle createCellStyle() { - if (getNumCellStyles() > MAXIMUM_STYLE_ID) { - throw new IllegalStateException("The maximum number of Cell Styles was exceeded. " + - "You can define up to " + MAXIMUM_STYLE_ID + " style in a .xlsx Workbook"); - } - + checkNumStyles(); int xfSize = styleXfs.size(); CTXf xf = CTXf.Factory.newInstance(); xf.setNumFmtId(0); @@ -790,6 +794,38 @@ return new XSSFCellStyle(indexXf - 1, xfSize - 1, this, theme); } + /** + * Creates a new cell style which is based on a named style. + * @param name Name of style on which to base the new cell style + * @return Cell style based on the specified named style; null if there is no such named style + * @throws IllegalStateException if the maximum number of cell styles has been reached. + */ + public XSSFCellStyle createCellStyleForNamedStyle(String name) { + CTCellStyle cellStyle = findCellStyleByName(name); + if (cellStyle == null) { + return null; + } else { + checkNumStyles(); + int cellStyleXfId = (int) cellStyle.getXfId(); + CTXf xf = CTXf.Factory.newInstance(); + xf.setXfId(cellStyleXfId); + + int indexXf = putCellXf(xf); + return new XSSFCellStyle(indexXf - 1, cellStyleXfId, this, theme); + } + } + + /** + * Checks whether the maximum number of allowed cell styles has been exceeded. + * @throws IllegalStateException if the maximum number of cell styles has been reached. + */ + private void checkNumStyles() { + if (getNumCellStyles() > MAXIMUM_STYLE_ID) { + throw new IllegalStateException("The maximum number of cell styles was exceeded. " + + "You can define up to " + MAXIMUM_STYLE_ID + " styles in a .xlsx workbook."); + } + } + /** * Finds a font that matches the one with the supplied attributes * @deprecated POI 3.15 beta 2. Use {@link #findFont(boolean, short, short, String, boolean, boolean, short, byte)} instead. @@ -830,4 +866,45 @@ } return null; } + + /** + * Adds or replaces a cell style with the specified name + * @param name Name of cell style to add or replace + * @param cellStyleXf Cell style Xf + * @return Cell style Xf id + */ + @Internal + public int addOrReplaceNamedCellStyle(String name, CTXf cellStyleXf) { + CTCellStyle cellStyle = findCellStyleByName(name); + + int cellStyleXfId; + if (cellStyle == null) { + // There is no cell style with that name so we create a new one + CTCellStyles cellStyles = doc.getStyleSheet().getCellStyles(); + cellStyle = cellStyles.addNewCellStyle(); + cellStyle.setName(name); + cellStyles.setCount(cellStyles.getCount() + 1); + cellStyleXfId = putCellStyleXf(cellStyleXf) - 1; + cellStyle.setXfId(cellStyleXfId); + } else { + cellStyleXfId = (int) cellStyle.getXfId(); + replaceCellStyleXfAt(cellStyleXfId, cellStyleXf); + } + + return cellStyleXfId; + } + + /** + * Find a cell style by name + * @param name Name of cell style + * @return Cell style having the specified name; null if there is no such cell style. + */ + private CTCellStyle findCellStyleByName(String name) { + CTCellStyles cellStyles = doc.getStyleSheet().getCellStyles(); + for (int i = 0; i < cellStyles.getCount(); i++) { + CTCellStyle cellStyle = cellStyles.getCellStyleArray(i); + if (name != null && name.equals(cellStyle.getName())) return cellStyle; + } + return null; + } } Index: src/java/org/apache/poi/ss/usermodel/Workbook.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/java/org/apache/poi/ss/usermodel/Workbook.java (revision 1786687) +++ src/java/org/apache/poi/ss/usermodel/Workbook.java (revision ) @@ -344,6 +344,15 @@ */ CellStyle getCellStyleAt(int idx); + /** + * Retrieve a cell style which is based on the specified named style + * but has no other cell-specific formatting applied. + * + * @param name Name of style + * @return Cell style which is based on the specified named style + */ + CellStyle getCellStyle(String name); + /** * Write out this workbook to an Outputstream. * Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (revision 1786687) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (revision ) @@ -956,6 +956,19 @@ return stylesSource.getStyleAt(idx); } + /** + * Retrieve a cell style which is based on the specified named style + * but has no other cell-specific formatting applied. + * + * NOTE: This actually creates a new cell style which references the + * specified named style. + * + * @param name Name of style + * @return Cell style which is based on the specified named style + */ + @Override + public XSSFCellStyle getCellStyle(String name) { return stylesSource.createCellStyleForNamedStyle(name); } + /** * Get the font at the given index number * Index: src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java (revision 1786687) +++ src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java (revision ) @@ -890,6 +890,19 @@ return _wb.getCellStyleAt(idx); } + /** + * Retrieve a cell style which is based on the specified named style + * but has no other cell-specific formatting applied. + * + * NOTE: This actually creates a new cell style which references the + * specified named style. + * + * @param name Name of style + * @return Cell style which is based on the specified named style + */ + @Override + public CellStyle getCellStyle(String name) { return _wb.getCellStyle(name); } + /** * Closes the underlying {@link XSSFWorkbook} and {@link OPCPackage} * on which this Workbook is based, if any. Index: src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (revision 1786687) +++ src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (revision ) @@ -1350,6 +1350,24 @@ return new HSSFCellStyle((short)idx, xfr, this); } + /** + * Retrieve a cell style which is based on the specified named style + * but has no other cell-specific formatting applied. + * + * @param name Name of style + * @return Cell style which is based on the specified named style + */ + @Override + public HSSFCellStyle getCellStyle(String name) { + for (int i = 0; i < getNumCellStyles(); i++) { + HSSFCellStyle cs = getCellStyleAt(i); + String userStyleName = cs.getUserStyleName(); + if (userStyleName != null && userStyleName.equals(name)) return cs; + } + + return null; + } + /** * Closes the underlying {@link NPOIFSFileSystem} from which * the Workbook was read, if any. Index: src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCellStyle.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCellStyle.java (revision 1786687) +++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCellStyle.java (revision ) @@ -1062,4 +1062,77 @@ workbook.close(); } + + /** + * Creates a new cell style by using the style xf of the original cell style as the cell xf of the new cell style. + * This allows us to use the XSSFCellStyle getters to test properties of the style xf. + */ + private XSSFCellStyle styleXfAsCellXf(XSSFWorkbook workbook, XSSFCellStyle cs) { + int cellXfCount = workbook.getStylesSource().putCellXf(cs.getStyleXf()); + return new XSSFCellStyle(cellXfCount - 1, -1, workbook.getStylesSource(), workbook.getTheme()); + } + + /** + * Tests that an appropriate named cell style is created when an XSSFCellStyle is being converted + * to a named cell style and that the named cell style can be retrieved accordingly. + * @throws IOException + */ + @Test + public void testNamedCellStyle() throws IOException { + XSSFWorkbook workbook = new XSSFWorkbook(); + + // Name of test style + String styleName = "TestStyle"; + // Style properties + HorizontalAlignment alignment = HorizontalAlignment.DISTRIBUTED; + BorderStyle borderStyle1 = BorderStyle.MEDIUM_DASH_DOT; + XSSFFont font = workbook.createFont(); + short identation = 4; + + // Create a new cell style + XSSFCellStyle cellStyle = workbook.createCellStyle(); + cellStyle.setAlignment(alignment); + cellStyle.setBorderRight(borderStyle1); + cellStyle.setFont(font); + cellStyle.setWrapText(true); + cellStyle.setIndention(identation); + + // Set as new named style + cellStyle.asNamedStyle(styleName); + + // Get named cell style + XSSFCellStyle cs1 = workbook.getCellStyle(styleName); + XSSFCellStyle target1 = styleXfAsCellXf(workbook, cs1); + + // Test properties (1) + assertEquals(alignment, target1.getAlignmentEnum()); + assertEquals(borderStyle1, target1.getBorderRightEnum()); + assertEquals(font, target1.getFont()); + assertEquals(true, target1.getWrapText()); + assertEquals(identation, target1.getIndention()); + + // Change properties + XSSFColor color = new XSSFColor(IndexedColors.CORNFLOWER_BLUE); + FillPatternType fillPatternType = FillPatternType.THIN_HORZ_BANDS; + BorderStyle borderStyle2 = BorderStyle.HAIR; + cs1.setFillPattern(fillPatternType); + cs1.setFillBackgroundColor(color); + cs1.setBorderRight(borderStyle2); + + // Redefine named style + cs1.asNamedStyle(styleName); + + // Get named cell style + XSSFCellStyle cs2 = workbook.getCellStyle(styleName); + XSSFCellStyle target2 = styleXfAsCellXf(workbook, cs2); + + // Test properties (2) + assertEquals(alignment, target2.getAlignmentEnum()); + assertEquals(borderStyle2, target2.getBorderRightEnum()); + assertEquals(true, target2.getWrapText()); + assertEquals(font, target2.getFont()); + assertEquals(fillPatternType, target2.getFillPatternEnum()); + assertEquals(color, target2.getFillBackgroundXSSFColor()); + assertEquals(identation, target2.getIndention()); + } } Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java (revision 1786687) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java (revision ) @@ -36,15 +36,7 @@ import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide; import org.apache.poi.xssf.usermodel.extensions.XSSFCellFill; import org.apache.xmlbeans.XmlException; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorderPr; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellAlignment; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFill; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPatternFill; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.STBorderStyle; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPatternType; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; /** * @@ -60,7 +52,7 @@ private int _cellXfId; private final StylesTable _stylesSource; private CTXf _cellXf; - private final CTXf _cellStyleXf; + private CTXf _cellStyleXf; private XSSFFont _font; private XSSFCellAlignment _cellAlignment; private ThemesTable _theme; @@ -1481,6 +1473,75 @@ return _cellXf.getAlignment(); } + /** + * Converts this style into a named cell style. + * + * Any cell-specific formatting will be merged into a named cell style + * and cell-specific formatting will be reset. + */ + @Override + public void asNamedStyle(String name) { + CTXf newStyleXf = createMergedXf(); + int cellStyleXfId = _stylesSource.addOrReplaceNamedCellStyle(name, newStyleXf); + + // Replace cell and cell style xfs + _cellStyleXf = newStyleXf; + _cellXf = CTXf.Factory.newInstance(); + _cellXf.setXfId(cellStyleXfId); + _stylesSource.replaceCellXfAt(_cellXfId, _cellXf); + } + + /** + * Name of this style if this style represents a named cell style. + * May return null if there is no named cell style or if this + * style contains cell-specific formatting. + */ + @Override + public String getStyleName() { + CTCellStyles cellStyles = _stylesSource.getCTStylesheet().getCellStyles(); + if (cellStyles != null) { + CTXf expectedCellXf = CTXf.Factory.newInstance(); + + for (int i = 0; i < cellStyles.getCount(); i++) { + CTCellStyle cellStyle = cellStyles.getCellStyleArray(i); + int styleXfId = (int) cellStyle.getXfId(); + if (_stylesSource.getCellStyleXfAt(styleXfId).equals(_cellStyleXf)) { + // We need to check whether the cell Xf has been modified; if that's the case, this + // XSSFCellStyle does not just represent a named cell style but also contains cell-specific + // formatting + expectedCellXf.setXfId(styleXfId); + if (_cellXf.equals(expectedCellXf)) return cellStyle.getName(); + } + } + } + + return null; + } + + /** + * Creates a new merged CTXf from the existing cell style Xf and cell Xf. + */ + private CTXf createMergedXf() { + CTXf mergedXf = (CTXf) _cellStyleXf.copy(); + if (_cellXf.isSetAlignment()) mergedXf.setAlignment(_cellXf.getAlignment()); + if (_cellXf.isSetProtection()) mergedXf.setProtection(_cellXf.getProtection()); + if (_cellXf.isSetExtLst()) mergedXf.setExtLst(_cellXf.getExtLst()); + if (_cellXf.isSetNumFmtId()) mergedXf.setNumFmtId(_cellXf.getNumFmtId()); + if (_cellXf.isSetFontId()) mergedXf.setFontId(_cellXf.getFontId()); + if (_cellXf.isSetFillId()) mergedXf.setFillId(_cellXf.getFillId()); + if (_cellXf.isSetBorderId()) mergedXf.setBorderId(_cellXf.getBorderId()); + if (_cellXf.isSetQuotePrefix()) mergedXf.setQuotePrefix(_cellXf.getQuotePrefix()); + if (_cellXf.isSetPivotButton()) mergedXf.setPivotButton(_cellXf.getPivotButton()); + if (_cellXf.isSetApplyNumberFormat()) mergedXf.setApplyNumberFormat(_cellXf.getApplyNumberFormat()); + if (_cellXf.isSetApplyFont()) mergedXf.setApplyFont(_cellXf.getApplyFont()); + if (_cellXf.isSetApplyFill()) mergedXf.setApplyFill(_cellXf.getApplyFill()); + if (_cellXf.isSetApplyBorder()) mergedXf.setApplyBorder(_cellXf.getApplyBorder()); + if (_cellXf.isSetApplyAlignment()) mergedXf.setApplyAlignment(_cellXf.getApplyAlignment()); + if (_cellXf.isSetApplyProtection()) mergedXf.setApplyProtection(_cellXf.getApplyProtection()); + + return mergedXf; + } + /** * Returns a hash code value for the object. The hash is derived from the underlying CTXf bean. * Index: src/java/org/apache/poi/hssf/usermodel/HSSFCellStyle.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/java/org/apache/poi/hssf/usermodel/HSSFCellStyle.java (revision 1786687) +++ src/java/org/apache/poi/hssf/usermodel/HSSFCellStyle.java (revision ) @@ -1032,6 +1032,23 @@ public boolean getShrinkToFit() { return _format.getShrinkToFit(); } + + /** + * Converts this style into a named cell style + */ + @Override + public void asNamedStyle(String name) { + setUserStyleName(name); + } + + /** + * Name of this style if this style represents a + * named cell style + */ + @Override + public String getStyleName() { + return getUserStyleName(); + } /** * Get the reading order, for RTL/LTR ordering of Index: src/java/org/apache/poi/ss/usermodel/CellStyle.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/java/org/apache/poi/ss/usermodel/CellStyle.java (revision 1786687) +++ src/java/org/apache/poi/ss/usermodel/CellStyle.java (revision ) @@ -899,4 +899,15 @@ * it to fit if this text is too long? */ boolean getShrinkToFit(); + + /** + * Converts this style into a named cell style + */ + void asNamedStyle(String name); + + /** + * Name of this style if this style represents a + * named cell style + */ + String getStyleName(); }