genericise cell copy logic

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1893931 13f79535-47bb-0310-9956-ffa450edef68

10 files changed, 601 insertions, 77 deletions

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<XSSFRow> {
      */
     @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<XSSFRow> {
         } 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,17 +142,192 @@ 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();
         final Sheet srcSheet = workbook.createSheet("src");
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<CellStyle, CellStyle> 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
@@ -186,6 +180,106 @@ public final class CellUtil {
     }
 
     /**
+     * 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.
      *
      * This is superior to cell.getCellStyle().setAlignment(align) because
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<? extends Hyperlink> 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();
+    }
+}