From c12f90378968b3ed7491cdbb36922a72c6dd5dca Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Wed, 6 Oct 2021 12:46:20 +0000 Subject: [PATCH] genericise cell copy logic git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1893931 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/xssf/usermodel/XSSFCell.java | 64 +----- .../apache/poi/xssf/usermodel/XSSFRow.java | 23 ++- .../ss/tests/util/TestSXSSFCellUtilCopy.java | 30 +++ .../ss/tests/util/TestXSSFCellUtilCopy.java | 30 +++ .../poi/xssf/usermodel/TestXSSFRow.java | 182 +++++++++++++++++- .../poi/hssf/usermodel/HSSFHyperlink.java | 1 + .../poi/ss/usermodel/CellCopyContext.java | 48 +++++ .../java/org/apache/poi/ss/util/CellUtil.java | 114 ++++++++++- .../poi/ss/util/BaseTestCellUtilCopy.java | 157 +++++++++++++++ .../poi/ss/util/TestHSSFCellUtilCopy.java | 29 +++ 10 files changed, 601 insertions(+), 77 deletions(-) create mode 100644 poi-ooxml/src/test/java/org/apache/poi/ss/tests/util/TestSXSSFCellUtilCopy.java create mode 100644 poi-ooxml/src/test/java/org/apache/poi/ss/tests/util/TestXSSFCellUtilCopy.java create mode 100644 poi/src/main/java/org/apache/poi/ss/usermodel/CellCopyContext.java create mode 100644 poi/src/test/java/org/apache/poi/ss/util/BaseTestCellUtilCopy.java create mode 100644 poi/src/test/java/org/apache/poi/ss/util/TestHSSFCellUtilCopy.java diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java index 4a07e068b5..80f3c7fdd7 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -46,6 +46,7 @@ import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; import org.apache.poi.ss.util.CellAddress; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; +import org.apache.poi.ss.util.CellUtil; import org.apache.poi.util.Beta; import org.apache.poi.util.Internal; import org.apache.poi.util.LocaleUtil; @@ -140,70 +141,11 @@ public final class XSSFCell extends CellBase { * @param srcCell The cell to take value, formula and style from * @param policy The policy for copying the information, see {@link CellCopyPolicy} * @throws IllegalArgumentException if copy cell style and srcCell is from a different workbook + * @see {@link CellUtil#copyCell(Cell, Cell, CellCopyPolicy, CellCopyContext)} */ @Beta - @Internal public void copyCellFrom(Cell srcCell, CellCopyPolicy policy) { - // Copy cell value (cell type is updated implicitly) - if (policy.isCopyCellValue()) { - if (srcCell != null) { - CellType copyCellType = srcCell.getCellType(); - if (copyCellType == CellType.FORMULA && !policy.isCopyCellFormula()) { - // Copy formula result as value - // FIXME: Cached value may be stale - copyCellType = srcCell.getCachedFormulaResultType(); - } - switch (copyCellType) { - case NUMERIC: - // DataFormat is not copied unless policy.isCopyCellStyle is true - if (DateUtil.isCellDateFormatted(srcCell)) { - setCellValue(srcCell.getDateCellValue()); - } - else { - setCellValue(srcCell.getNumericCellValue()); - } - break; - case STRING: - setCellValue(srcCell.getStringCellValue()); - break; - case FORMULA: - setCellFormula(srcCell.getCellFormula()); - break; - case BLANK: - setBlank(); - break; - case BOOLEAN: - setCellValue(srcCell.getBooleanCellValue()); - break; - case ERROR: - setCellErrorValue(srcCell.getErrorCellValue()); - break; - - default: - throw new IllegalArgumentException("Invalid cell type " + srcCell.getCellType()); - } - } else { //srcCell is null - setBlank(); - } - } - - // Copy CellStyle - if (policy.isCopyCellStyle()) { - setCellStyle(srcCell == null ? null : srcCell.getCellStyle()); - } - - final Hyperlink srcHyperlink = (srcCell == null) ? null : srcCell.getHyperlink(); - - if (policy.isMergeHyperlink()) { - // if srcCell doesn't have a hyperlink and destCell has a hyperlink, don't clear destCell's hyperlink - if (srcHyperlink != null) { - setHyperlink(new XSSFHyperlink(srcHyperlink)); - } - } else if (policy.isCopyHyperlink()) { - // overwrite the hyperlink at dest cell with srcCell's hyperlink - // if srcCell doesn't have a hyperlink, clear the hyperlink (if one exists) at destCell - setHyperlink(srcHyperlink == null ? null : new XSSFHyperlink(srcHyperlink)); - } + CellUtil.copyCell(srcCell, this, policy, null); } /** diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFRow.java index 5f640b000d..7cf05c2ccf 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFRow.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFRow.java @@ -27,6 +27,7 @@ import java.util.TreeMap; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellCopyContext; import org.apache.poi.ss.usermodel.CellCopyPolicy; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; @@ -34,6 +35,7 @@ import org.apache.poi.ss.usermodel.FormulaError; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.helpers.RowShifter; import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.ss.util.CellUtil; import org.apache.poi.util.Beta; import org.apache.poi.util.Internal; import org.apache.poi.xssf.model.StylesTable; @@ -646,12 +648,25 @@ public class XSSFRow implements Row, Comparable { */ @Beta public void copyRowFrom(Row srcRow, CellCopyPolicy policy) { + copyRowFrom(srcRow, policy, null); + } + + /** + * Copy the cells from srcRow to this row + * If this row is not a blank row, this will merge the two rows, overwriting + * the cells in this row with the cells in srcRow + * If srcRow is null, overwrite cells in destination row with blank values, styles, etc per cell copy policy + * srcRow may be from a different sheet in the same workbook + * @param srcRow the rows to copy from + * @param policy the policy to determine what gets copied + * @param context the context - see {@link CellCopyContext} + */ + @Beta + public void copyRowFrom(Row srcRow, CellCopyPolicy policy, CellCopyContext context) { if (srcRow == null) { // srcRow is blank. Overwrite cells with blank values, blank styles, etc per cell copy policy for (Cell destCell : this) { - final XSSFCell srcCell = null; - // FIXME: remove type casting when copyCellFrom(Cell, CellCopyPolicy) is added to Cell interface - ((XSSFCell)destCell).copyCellFrom(srcCell, policy); + CellUtil.copyCell(null, destCell, policy, context); } if (policy.isCopyMergedRegions()) { @@ -676,7 +691,7 @@ public class XSSFRow implements Row, Comparable { } else { for (final Cell c : srcRow) { final XSSFCell destCell = createCell(c.getColumnIndex()); - destCell.copyCellFrom(c, policy); + CellUtil.copyCell(c, destCell, policy, context); } final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet); diff --git a/poi-ooxml/src/test/java/org/apache/poi/ss/tests/util/TestSXSSFCellUtilCopy.java b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/util/TestSXSSFCellUtilCopy.java new file mode 100644 index 0000000000..6999e4c08a --- /dev/null +++ b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/util/TestSXSSFCellUtilCopy.java @@ -0,0 +1,30 @@ +/* ==================================================================== + 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.tests.util; + +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.BaseTestCellUtilCopy; +import org.apache.poi.xssf.streaming.SXSSFWorkbook; + +public class TestSXSSFCellUtilCopy extends BaseTestCellUtilCopy { + + @Override + protected Workbook createNewWorkbook() { + return new SXSSFWorkbook(); + } +} diff --git a/poi-ooxml/src/test/java/org/apache/poi/ss/tests/util/TestXSSFCellUtilCopy.java b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/util/TestXSSFCellUtilCopy.java new file mode 100644 index 0000000000..24e53c68f9 --- /dev/null +++ b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/util/TestXSSFCellUtilCopy.java @@ -0,0 +1,30 @@ +/* ==================================================================== + 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.tests.util; + +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.BaseTestCellUtilCopy; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; + +public class TestXSSFCellUtilCopy extends BaseTestCellUtilCopy { + + @Override + protected Workbook createNewWorkbook() { + return new XSSFWorkbook(); + } +} diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFRow.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFRow.java index 3c913467a3..b1b7688323 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFRow.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFRow.java @@ -24,6 +24,7 @@ import static org.junit.jupiter.api.Assertions.assertSame; import java.io.IOException; import org.apache.poi.common.usermodel.HyperlinkType; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.tests.usermodel.BaseTestXRow; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.XSSFITestDataProvider; @@ -83,6 +84,8 @@ public final class TestXSSFRow extends BaseTestXRow { ////////////////// + final int styleCount = workbook.getNumCellStyles(); + final XSSFRow destRow = destSheet.createRow(1); destRow.copyRowFrom(srcRow, new CellCopyPolicy()); @@ -139,16 +142,191 @@ public final class TestXSSFRow extends BaseTestXRow { assertEquals("SUM(src!B$5:D6)", cell.getCellFormula(), "Area3DPtg"); cell = destRow.getCell(col++); - assertNotNull(destRow.getCell(6)); + assertNotNull(cell); assertEquals("SUM(dest!B$5:D6)", cell.getCellFormula(), "Area3DPtg"); cell = destRow.getCell(col++); - assertNotNull(destRow.getCell(7)); + assertNotNull(cell); assertEquals("SUM(other!B$5:D6)", cell.getCellFormula(), "Area3DPtg"); + assertEquals(styleCount, workbook.getNumCellStyles(), "no new styles should be added by copyRow"); workbook.close(); } + @Test + void testCopyRowFromDifferentXssfWorkbook() throws IOException { + final XSSFWorkbook srcWorkbook = new XSSFWorkbook(); + final XSSFWorkbook destWorkbook = new XSSFWorkbook(); + final Sheet srcSheet = srcWorkbook.createSheet("src"); + final XSSFSheet destSheet = destWorkbook.createSheet("dest"); + srcWorkbook.createSheet("other"); + destWorkbook.createSheet("other"); + + final Row srcRow = srcSheet.createRow(0); + int col = 0; + //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) + srcRow.createCell(col++).setCellFormula("B5"); + srcRow.createCell(col++).setCellFormula("src!B5"); + srcRow.createCell(col++).setCellFormula("dest!B5"); + srcRow.createCell(col++).setCellFormula("other!B5"); + + //Test 2D and 3D Ref Ptgs with absolute row + srcRow.createCell(col++).setCellFormula("B$5"); + srcRow.createCell(col++).setCellFormula("src!B$5"); + srcRow.createCell(col++).setCellFormula("dest!B$5"); + srcRow.createCell(col++).setCellFormula("other!B$5"); + + //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) + srcRow.createCell(col++).setCellFormula("SUM(B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(src!B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(dest!B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(other!B5:D$5)"); + + ////////////////// + + final int destStyleCount = destWorkbook.getNumCellStyles(); + final XSSFRow destRow = destSheet.createRow(1); + destRow.copyRowFrom(srcRow, new CellCopyPolicy(), new CellCopyContext()); + + ////////////////// + + //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) + col = 0; + Cell cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("B6", cell.getCellFormula(), "RefPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("src!B6", cell.getCellFormula(), "Ref3DPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("dest!B6", cell.getCellFormula(), "Ref3DPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("other!B6", cell.getCellFormula(), "Ref3DPtg"); + + ///////////////////////////////////////////// + + //Test 2D and 3D Ref Ptgs with absolute row (Ptg row number shouldn't change) + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("B$5", cell.getCellFormula(), "RefPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("src!B$5", cell.getCellFormula(), "Ref3DPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("dest!B$5", cell.getCellFormula(), "Ref3DPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("other!B$5", cell.getCellFormula(), "Ref3DPtg"); + + ////////////////////////////////////////// + + //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) + // Note: absolute row changes from last cell to first cell in order + // to maintain topLeft:bottomRight order + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("SUM(B$5:D6)", cell.getCellFormula(), "Area2DPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("SUM(src!B$5:D6)", cell.getCellFormula(), "Area3DPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("SUM(dest!B$5:D6)", cell.getCellFormula(), "Area3DPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("SUM(other!B$5:D6)", cell.getCellFormula(), "Area3DPtg"); + + assertEquals(1, srcWorkbook.getNumCellStyles(), "srcWorkbook styles"); + assertEquals(destStyleCount + 1, destWorkbook.getNumCellStyles(), "destWorkbook styles"); + srcWorkbook.close(); + destWorkbook.close(); + } + + @Test + void testCopyRowFromDifferentHssfWorkbook() throws IOException { + final HSSFWorkbook srcWorkbook = new HSSFWorkbook(); + final XSSFWorkbook destWorkbook = new XSSFWorkbook(); + final Sheet srcSheet = srcWorkbook.createSheet("src"); + final XSSFSheet destSheet = destWorkbook.createSheet("dest"); + srcWorkbook.createSheet("other"); + destWorkbook.createSheet("other"); + + final Row srcRow = srcSheet.createRow(0); + int col = 0; + //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) + srcRow.createCell(col++).setCellFormula("B5"); + srcRow.createCell(col++).setCellFormula("other!B5"); + + //Test 2D and 3D Ref Ptgs with absolute row + srcRow.createCell(col++).setCellFormula("B$5"); + srcRow.createCell(col++).setCellFormula("other!B$5"); + + //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) + srcRow.createCell(col++).setCellFormula("SUM(B5:D$5)"); + srcRow.createCell(col++).setCellFormula("SUM(other!B5:D$5)"); + + ////////////////// + + final int destStyleCount = destWorkbook.getNumCellStyles(); + final XSSFRow destRow = destSheet.createRow(1); + CellCopyPolicy policy = new CellCopyPolicy(); + //hssf to xssf copy does not support cell style copying + policy.setCopyCellStyle(false); + destRow.copyRowFrom(srcRow, policy, new CellCopyContext()); + + ////////////////// + + //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks) + col = 0; + Cell cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("B6", cell.getCellFormula(), "RefPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("other!B6", cell.getCellFormula(), "Ref3DPtg"); + + ///////////////////////////////////////////// + + //Test 2D and 3D Ref Ptgs with absolute row (Ptg row number shouldn't change) + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("B$5", cell.getCellFormula(), "RefPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("other!B$5", cell.getCellFormula(), "Ref3DPtg"); + + ////////////////////////////////////////// + + //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks) + // Note: absolute row changes from last cell to first cell in order + // to maintain topLeft:bottomRight order + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("SUM(B$5:D6)", cell.getCellFormula(), "Area2DPtg"); + + cell = destRow.getCell(col++); + assertNotNull(cell); + assertEquals("SUM(other!B$5:D6)", cell.getCellFormula(), "Area3DPtg"); + + assertEquals(destStyleCount, destWorkbook.getNumCellStyles(), "destWorkbook styles"); + srcWorkbook.close(); + destWorkbook.close(); + } + @Test void testCopyRowWithHyperlink() throws IOException { final XSSFWorkbook workbook = new XSSFWorkbook(); diff --git a/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFHyperlink.java b/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFHyperlink.java index e145a5e89a..57551e10ca 100644 --- a/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFHyperlink.java +++ b/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFHyperlink.java @@ -286,4 +286,5 @@ public class HSSFHyperlink implements Hyperlink, Duplicatable { public int hashCode() { return record.hashCode(); } + } diff --git a/poi/src/main/java/org/apache/poi/ss/usermodel/CellCopyContext.java b/poi/src/main/java/org/apache/poi/ss/usermodel/CellCopyContext.java new file mode 100644 index 0000000000..f38ec050f3 --- /dev/null +++ b/poi/src/main/java/org/apache/poi/ss/usermodel/CellCopyContext.java @@ -0,0 +1,48 @@ +/* ==================================================================== + 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.usermodel; + +import org.apache.poi.util.Beta; + +import java.util.HashMap; +import java.util.Map; + +/** + * Used when the cells are being copied from one workbook to another. Data like cell styles + * need to be managed so that we do not create too many items in the destination workbook. + */ +@Beta +public class CellCopyContext { + private final Map styleMap = new HashMap<>(); + + /** + * @param srcStyle + * @return style that srcStyle is mapped to or null if no mapping exists + */ + public CellStyle getMappedStyle(CellStyle srcStyle) { + return styleMap.get(srcStyle); + } + + /** + * @param srcStyle style in source workbook + * @param mappedStyle equivalent style in destination workbook + */ + public void putMappedStyle(CellStyle srcStyle, CellStyle mappedStyle) { + styleMap.put(srcStyle, mappedStyle); + } +} diff --git a/poi/src/main/java/org/apache/poi/ss/util/CellUtil.java b/poi/src/main/java/org/apache/poi/ss/util/CellUtil.java index 438044b7d0..0407550b19 100644 --- a/poi/src/main/java/org/apache/poi/ss/util/CellUtil.java +++ b/poi/src/main/java/org/apache/poi/ss/util/CellUtil.java @@ -27,16 +27,10 @@ import java.util.Set; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; -import org.apache.poi.ss.usermodel.BorderStyle; -import org.apache.poi.ss.usermodel.Cell; -import org.apache.poi.ss.usermodel.CellStyle; -import org.apache.poi.ss.usermodel.FillPatternType; -import org.apache.poi.ss.usermodel.Font; -import org.apache.poi.ss.usermodel.HorizontalAlignment; -import org.apache.poi.ss.usermodel.Row; -import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.VerticalAlignment; -import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.common.Duplicatable; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.util.Beta; +import org.apache.poi.util.Internal; /** * Various utility functions that make working with a cells and rows easier. The various methods @@ -185,6 +179,106 @@ public final class CellUtil { return createCell(row, column, value, null); } + /** + * Copy cell value, formula and style, from srcCell per cell copy policy + * If srcCell is null, clears the cell value and cell style per cell copy policy + * + * This does not shift references in formulas. + * + * @param srcCell The cell to take value, formula and style from + * @param destCell The cell to copy to + * @param policy The policy for copying the information, see {@link CellCopyPolicy} + * @param context The context for copying, see {@link CellCopyContext} + * @throws IllegalArgumentException if copy cell style and srcCell is from a different workbook + * @throws IllegalStateException if srcCell hyperlink is not an instance of {@link Duplicatable} + */ + @Beta + public static void copyCell(Cell srcCell, Cell destCell, CellCopyPolicy policy, CellCopyContext context) { + // Copy cell value (cell type is updated implicitly) + if (policy.isCopyCellValue()) { + if (srcCell != null) { + CellType copyCellType = srcCell.getCellType(); + if (copyCellType == CellType.FORMULA && !policy.isCopyCellFormula()) { + // Copy formula result as value + // FIXME: Cached value may be stale + copyCellType = srcCell.getCachedFormulaResultType(); + } + switch (copyCellType) { + case NUMERIC: + // DataFormat is not copied unless policy.isCopyCellStyle is true + if (DateUtil.isCellDateFormatted(srcCell)) { + destCell.setCellValue(srcCell.getDateCellValue()); + } + else { + destCell.setCellValue(srcCell.getNumericCellValue()); + } + break; + case STRING: + destCell.setCellValue(srcCell.getStringCellValue()); + break; + case FORMULA: + destCell.setCellFormula(srcCell.getCellFormula()); + break; + case BLANK: + destCell.setBlank(); + break; + case BOOLEAN: + destCell.setCellValue(srcCell.getBooleanCellValue()); + break; + case ERROR: + destCell.setCellErrorValue(srcCell.getErrorCellValue()); + break; + + default: + throw new IllegalArgumentException("Invalid cell type " + srcCell.getCellType()); + } + } else { //srcCell is null + destCell.setBlank(); + } + } + + // Copy CellStyle + if (policy.isCopyCellStyle()) { + if (destCell.getSheet().getWorkbook() == srcCell.getSheet().getWorkbook()) { + destCell.setCellStyle(srcCell == null ? null : srcCell.getCellStyle()); + } else { + CellStyle srcStyle = srcCell.getCellStyle(); + CellStyle destStyle = context == null ? null : context.getMappedStyle(srcStyle); + if (destStyle == null) { + destStyle = destCell.getSheet().getWorkbook().createCellStyle(); + destStyle.cloneStyleFrom(srcStyle); + if (context != null) context.putMappedStyle(srcStyle, destStyle); + } + destCell.setCellStyle(destStyle); + } + } + + final Hyperlink srcHyperlink = (srcCell == null) ? null : srcCell.getHyperlink(); + + if (policy.isMergeHyperlink()) { + // if srcCell doesn't have a hyperlink and destCell has a hyperlink, don't clear destCell's hyperlink + if (srcHyperlink != null) { + if (srcHyperlink instanceof Duplicatable) { + Hyperlink newHyperlink = (Hyperlink)((Duplicatable)srcHyperlink).copy(); + destCell.setHyperlink(newHyperlink); + } else { + throw new IllegalStateException("srcCell hyperlink is not an instance of Duplicatable"); + } + } + } else if (policy.isCopyHyperlink()) { + // overwrite the hyperlink at dest cell with srcCell's hyperlink + // if srcCell doesn't have a hyperlink, clear the hyperlink (if one exists) at destCell + if (srcHyperlink == null) { + destCell.setHyperlink(null); + } else if (srcHyperlink instanceof Duplicatable) { + Hyperlink newHyperlink = (Hyperlink)((Duplicatable)srcHyperlink).copy(); + destCell.setHyperlink(newHyperlink); + } else { + throw new IllegalStateException("srcCell hyperlink is not an instance of Duplicatable"); + } + } + } + /** * Take a cell, and align it. * diff --git a/poi/src/test/java/org/apache/poi/ss/util/BaseTestCellUtilCopy.java b/poi/src/test/java/org/apache/poi/ss/util/BaseTestCellUtilCopy.java new file mode 100644 index 0000000000..5ffd322f3a --- /dev/null +++ b/poi/src/test/java/org/apache/poi/ss/util/BaseTestCellUtilCopy.java @@ -0,0 +1,157 @@ +/* ==================================================================== + 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 org.apache.poi.common.usermodel.HyperlinkType; +import org.apache.poi.ss.usermodel.*; +import org.junit.jupiter.api.Test; + +import java.io.IOException; +import java.util.List; + +import static org.junit.jupiter.api.Assertions.*; + +public abstract class BaseTestCellUtilCopy { + + protected Cell srcCell, destCell; //used for testCopyCellFrom_CellCopyPolicy + + @Test + public final void testCopyCellFrom_CellCopyPolicy_default() { + setUp_testCopyCellFrom_CellCopyPolicy(); + + // default copy policy + final CellCopyPolicy policy = new CellCopyPolicy(); + CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext()); + + assertEquals(CellType.FORMULA, destCell.getCellType()); + assertEquals("2+3", destCell.getCellFormula()); + assertEquals(srcCell.getCellStyle(), destCell.getCellStyle()); + } + + @Test + public final void testCopyCellFrom_CellCopyPolicy_value() { + setUp_testCopyCellFrom_CellCopyPolicy(); + + // Paste values only + final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellFormula(false).build(); + CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext()); + assertEquals(CellType.NUMERIC, destCell.getCellType()); + } + + @Test + public final void testCopyCellFrom_CellCopyPolicy_formulaWithUnregisteredUDF() { + setUp_testCopyCellFrom_CellCopyPolicy(); + + srcCell.setCellFormula("MYFUNC2(123, $A5, Sheet1!$B7)"); + + // Copy formula verbatim (no shifting). This is okay because copyCellFrom is Internal. + // Users should use higher-level copying functions to row- or column-shift formulas. + final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellFormula(true).build(); + CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext()); + assertEquals("MYFUNC2(123,$A5,Sheet1!$B7)", stringWithoutSpaces(destCell.getCellFormula())); + } + + @Test + public final void testCopyCellFrom_CellCopyPolicy_style() { + setUp_testCopyCellFrom_CellCopyPolicy(); + srcCell.setCellValue((String) null); + + // Paste styles only + final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellValue(false).build(); + CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext()); + assertEquals(srcCell.getCellStyle(), destCell.getCellStyle()); + + // Old cell value should not have been overwritten + assertNotEquals(CellType.BLANK, destCell.getCellType()); + assertEquals(CellType.BOOLEAN, destCell.getCellType()); + assertTrue(destCell.getBooleanCellValue()); + } + + @Test + public final void testCopyCellFrom_CellCopyPolicy_copyHyperlink() throws IOException { + setUp_testCopyCellFrom_CellCopyPolicy(); + final Workbook wb = srcCell.getSheet().getWorkbook(); + final CreationHelper createHelper = wb.getCreationHelper(); + + srcCell.setCellValue("URL LINK"); + Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL); + final String address = "https://poi.apache.org/"; + link.setAddress(address); + srcCell.setHyperlink(link); + + // Set link cell style (optional) + setLinkCellStyle(wb, srcCell); + + // Copy hyperlink + final CellCopyPolicy policy = new CellCopyPolicy.Builder().copyHyperlink(true).mergeHyperlink(false).build(); + CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext()); + assertNotNull(destCell.getHyperlink()); + + assertSame(srcCell.getSheet(), destCell.getSheet(), + "unit test assumes srcCell and destCell are on the same sheet"); + + final List links = srcCell.getSheet().getHyperlinkList(); + assertEquals(2, links.size(), "number of hyperlinks on sheet"); + assertEquals(address, links.get(0).getAddress()); + assertEquals(address, links.get(1).getAddress()); + checkHyperlinkCellRef(links.get(0), srcCell.getAddress()); + checkHyperlinkCellRef(links.get(1), destCell.getAddress()); + + wb.close(); + } + + private void setUp_testCopyCellFrom_CellCopyPolicy() { + @SuppressWarnings("resource") + final Workbook wb = createNewWorkbook(); + final Row row = wb.createSheet("Sheet1").createRow(0); + srcCell = row.createCell(0); + destCell = row.createCell(1); + + srcCell.setCellFormula("2+3"); + + final CellStyle style = wb.createCellStyle(); + style.setBorderTop(BorderStyle.THICK); + style.setFillBackgroundColor((short) 5); + srcCell.setCellStyle(style); + + destCell.setCellValue(true); + } + + protected void setLinkCellStyle(Workbook wb, Cell srcCell) { + CellStyle hlinkStyle = wb.createCellStyle(); + Font hlinkFont = wb.createFont(); + hlinkFont.setUnderline(Font.U_SINGLE); + hlinkFont.setColor(IndexedColors.BLUE.getIndex()); + hlinkStyle.setFont(hlinkFont); + srcCell.setCellStyle(hlinkStyle); + } + + protected String stringWithoutSpaces(String input) { + return input.replace(" ", ""); + } + + protected void checkHyperlinkCellRef(Hyperlink hyperlink, CellAddress cellRef) { + assertEquals(cellRef.getRow(), hyperlink.getFirstRow(), "first row"); + assertEquals(cellRef.getRow(), hyperlink.getLastRow(), "last row"); + assertEquals(cellRef.getColumn(), hyperlink.getFirstColumn(), "first column"); + assertEquals(cellRef.getColumn(), hyperlink.getLastColumn(), "last column"); + } + + protected abstract Workbook createNewWorkbook(); + +} diff --git a/poi/src/test/java/org/apache/poi/ss/util/TestHSSFCellUtilCopy.java b/poi/src/test/java/org/apache/poi/ss/util/TestHSSFCellUtilCopy.java new file mode 100644 index 0000000000..673e60ed56 --- /dev/null +++ b/poi/src/test/java/org/apache/poi/ss/util/TestHSSFCellUtilCopy.java @@ -0,0 +1,29 @@ +/* ==================================================================== + 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 org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.Workbook; + +public class TestHSSFCellUtilCopy extends BaseTestCellUtilCopy { + + @Override + protected Workbook createNewWorkbook() { + return new HSSFWorkbook(); + } +} -- 2.39.5