From e215902a9471e742fd7bbc6af23cce7e0a04ecf1 Mon Sep 17 00:00:00 2001 From: Javen O'Neal Date: Sat, 2 Jan 2016 04:29:30 +0000 Subject: [PATCH] bug 58633: Set multiple CellStyle properties at once. Patch from Mark Murphy. git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1722607 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/hssf/usermodel/HSSFCell.java | 7 +- .../org/apache/poi/ss/usermodel/Cell.java | 7 +- src/java/org/apache/poi/ss/util/CellUtil.java | 70 ++++++++++++--- .../apache/poi/xssf/streaming/SXSSFCell.java | 9 +- .../apache/poi/xssf/usermodel/XSSFCell.java | 7 +- .../org/apache/poi/ss/util/TestCellUtil.java | 88 +++++++++++++++++++ 6 files changed, 168 insertions(+), 20 deletions(-) create mode 100644 src/testcases/org/apache/poi/ss/util/TestCellUtil.java diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java index 2009c2f774..571d086316 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java @@ -880,8 +880,11 @@ public class HSSFCell implements Cell { } /** - * set the style for the cell. The style should be an HSSFCellStyle created/retreived from - * the HSSFWorkbook. + *

Set the style for the cell. The style should be an HSSFCellStyle created/retreived from + * the HSSFWorkbook.

+ * + *

To change the style of a cell without affecting other cells that use the same style, + * use {@link org.apache.poi.ss.util.CellUtil#setCellStyleProperties(Cell, Map)}

* * @param style reference contained in the workbook * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createCellStyle() diff --git a/src/java/org/apache/poi/ss/usermodel/Cell.java b/src/java/org/apache/poi/ss/usermodel/Cell.java index 2fd501bd16..fa866bb4fa 100644 --- a/src/java/org/apache/poi/ss/usermodel/Cell.java +++ b/src/java/org/apache/poi/ss/usermodel/Cell.java @@ -327,8 +327,11 @@ public interface Cell { byte getErrorCellValue(); /** - * Set the style for the cell. The style should be an CellStyle created/retreived from - * the Workbook. + *

Set the style for the cell. The style should be an CellStyle created/retreived from + * the Workbook.

+ * + *

To change the style of a cell without affecting other cells that use the same style, + * use {@link org.apache.poi.ss.util.CellUtil#setCellStyleProperties(Cell, Map)}

* * @param style reference contained in the workbook. * If the value is null then the style information is removed causing the cell to used the default workbook style. diff --git a/src/java/org/apache/poi/ss/util/CellUtil.java b/src/java/org/apache/poi/ss/util/CellUtil.java index cc07b483f6..2af1430e88 100644 --- a/src/java/org/apache/poi/ss/util/CellUtil.java +++ b/src/java/org/apache/poi/ss/util/CellUtil.java @@ -17,6 +17,7 @@ package org.apache.poi.ss.util; +import java.util.Collections; import java.util.HashMap; import java.util.Locale; import java.util.Map; @@ -170,22 +171,36 @@ public final class CellUtil { } /** - * This method attempt to find an already existing CellStyle that matches what you want the - * style to be. If it does not find the style, then it creates a new one. If it does create a - * new one, then it applies the propertyName and propertyValue to the style. This is necessary - * because Excel has an upper limit on the number of Styles that it supports. + *

This method attempts to find an existing CellStyle that matches the cell's + * current style plus styles properties in properties. A new style is created if the + * workbook does not contain a matching style.

+ * + *

Modifies the cell style of cell without affecting other cells that use the + * same style.

+ * + *

This is necessary because Excel has an upper limit on the number of styles that it supports.

+ * + *

This function is more efficient than multiple calls to + * {@link #setCellStyleProperty(org.apache.poi.ss.usermodel.Cell, org.apache.poi.ss.usermodel.Workbook, String, Object)} + * if adding multiple cell styles.

+ * + *

For performance reasons, if this is the only cell in a workbook that uses a cell style, + * this method does NOT remove the old style from the workbook. + * + *

* - *@param workbook The workbook that is being worked with. - *@param propertyName The name of the property that is to be changed. - *@param propertyValue The value of the property that is to be changed. - *@param cell The cell that needs it's style changes + * @param cell The cell to change the style of + * @param properties The properties to be added to a cell style, as {propertyName: propertyValue}. + * @since POI 3.14 beta 2 */ - public static void setCellStyleProperty(Cell cell, Workbook workbook, String propertyName, - Object propertyValue) { + public static void setCellStyleProperties(Cell cell, Map properties) { + Workbook workbook = cell.getSheet().getWorkbook(); CellStyle originalStyle = cell.getCellStyle(); CellStyle newStyle = null; Map values = getFormatProperties(originalStyle); - values.put(propertyName, propertyValue); + values.putAll(properties); // index seems like what index the cellstyle is in the list of styles for a workbook. // not good to compare on! @@ -195,12 +210,14 @@ public final class CellUtil { CellStyle wbStyle = workbook.getCellStyleAt(i); Map wbStyleMap = getFormatProperties(wbStyle); + // the desired style already exists in the workbook. Use the existing style. if (wbStyleMap.equals(values)) { newStyle = wbStyle; break; } } + // the desired style does not exist in the workbook. Create a new style with desired properties. if (newStyle == null) { newStyle = workbook.createCellStyle(); setFormatProperties(newStyle, workbook, values); @@ -209,8 +226,39 @@ public final class CellUtil { cell.setCellStyle(newStyle); } + /** + *

This method attempts to find an existing CellStyle that matches the cell's + * current style plus a single style property propertyName with value + * propertyValue. + * A new style is created if the workbook does not contain a matching style.

+ * + *

Modifies the cell style of cell without affecting other cells that use the + * same style.

+ * + *

If setting more than one cell style property on a cell, use + * {@link #setCellStyleProperties(org.apache.poi.ss.usermodel.Cell, Map)}, + * which is faster and does not add unnecessary intermediate CellStyles to the workbook.

+ * + * @param workbook The workbook that is being worked with. + * @param propertyName The name of the property that is to be changed. + * @param propertyValue The value of the property that is to be changed. + * @param cell The cell that needs it's style changes + */ + public static void setCellStyleProperty(Cell cell, Workbook workbook, String propertyName, + Object propertyValue) { + if (cell.getSheet().getWorkbook() != workbook) { + throw new IllegalArgumentException("Cannot set cell style property. Cell does not belong to workbook"); + } + + Map values = Collections.singletonMap(propertyName, propertyValue); + setCellStyleProperties(cell, values); + } + /** * Returns a map containing the format properties of the given cell style. + * The returned map is not tied to style, so subsequent changes + * to style will not modify the map, and changes to the returned + * map will not modify the cell style. The returned map is mutable. * * @param style cell style * @return map of format properties (String -> Object) diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java index 90840df9fc..744b91f70b 100644 --- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java @@ -541,9 +541,12 @@ public class SXSSFCell implements Cell { } /** - * Set the style for the cell. The style should be an CellStyle created/retreived from - * the Workbook. - * + *

Set the style for the cell. The style should be an CellStyle created/retreived from + * the Workbook.

+ * + *

To change the style of a cell without affecting other cells that use the same style, + * use {@link org.apache.poi.ss.util.CellUtil#setCellStyleProperties(Cell, Map)}

+ * * @param style reference contained in the workbook. * If the value is null then the style information is removed causing the cell to used the default workbook style. * @see org.apache.poi.ss.usermodel.Workbook#createCellStyle() diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index b4a17688bf..15c7134010 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -615,9 +615,12 @@ public final class XSSFCell implements Cell { } /** - * Set the style for the cell. The style should be an XSSFCellStyle created/retreived from - * the XSSFWorkbook. + *

Set the style for the cell. The style should be an XSSFCellStyle created/retreived from + * the XSSFWorkbook.

* + *

To change the style of a cell without affecting other cells that use the same style, + * use {@link org.apache.poi.ss.util.CellUtil#setCellStyleProperties(Cell, Map)}

+ * * @param style reference contained in the workbook. * If the value is null then the style information is removed causing the cell to used the default workbook style. * @throws IllegalArgumentException if style belongs to a different styles source (most likely because style is from a different Workbook) diff --git a/src/testcases/org/apache/poi/ss/util/TestCellUtil.java b/src/testcases/org/apache/poi/ss/util/TestCellUtil.java new file mode 100644 index 0000000000..6ea3bdb924 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/util/TestCellUtil.java @@ -0,0 +1,88 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + ==================================================================== */ + +package org.apache.poi.ss.util; + +import static org.junit.Assert.assertEquals; + +import java.util.HashMap; +import java.util.Map; + +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; + +import org.junit.Test; + +/** + * Tests Spreadsheet CellUtil + * + * @see org.apache.poi.ss.util.CellUtil + */ +public final class TestCellUtil { + @Test + public void testSetCellStyleProperty() throws Exception { + Workbook wb = new HSSFWorkbook(); + Sheet s = wb.createSheet(); + Row r = s.createRow(0); + Cell c = r.createCell(0); + + // Add a border should create a new style + int styCnt1 = wb.getNumCellStyles(); + CellUtil.setCellStyleProperty(c, wb, CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN); + int styCnt2 = wb.getNumCellStyles(); + assertEquals(styCnt2, styCnt1+1); + + // Add same border to another cell, should not create another style + c = r.createCell(1); + CellUtil.setCellStyleProperty(c, wb, CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN); + int styCnt3 = wb.getNumCellStyles(); + assertEquals(styCnt3, styCnt2); + + wb.close(); + } + + @Test + public void testSetCellStyleProperties() throws Exception { + Workbook wb = new HSSFWorkbook(); + Sheet s = wb.createSheet(); + Row r = s.createRow(0); + Cell c = r.createCell(0); + + // Add multiple border properties to cell should create a single new style + int styCnt1 = wb.getNumCellStyles(); + Map props = new HashMap(); + props.put(CellUtil.BORDER_TOP, CellStyle.BORDER_THIN); + props.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN); + props.put(CellUtil.BORDER_LEFT, CellStyle.BORDER_THIN); + props.put(CellUtil.BORDER_RIGHT, CellStyle.BORDER_THIN); + CellUtil.setCellStyleProperties(c, props); + int styCnt2 = wb.getNumCellStyles(); + assertEquals(styCnt2, styCnt1+1); + + // Add same border another to same cell, should not create another style + c = r.createCell(1); + CellUtil.setCellStyleProperties(c, props); + int styCnt3 = wb.getNumCellStyles(); + assertEquals(styCnt3, styCnt2); + + wb.close(); + } +} -- 2.39.5