genericise cell copy logic

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

5 files changed, 339 insertions, 10 deletions

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();
+    }
+}