From 1bb6dddc06b41d0e40295fcf7dad087634ebe781 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Fri, 4 Feb 2022 20:29:55 +0000 Subject: [PATCH] introduce CellReferenceType git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1897780 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/xssf/streaming/SXSSFWorkbook.java | 4 ++-- .../poi/xssf/usermodel/XSSFWorkbook.java | 14 +++++++---- .../poi/xssf/usermodel/TestXSSFWorkbook.java | 4 ++-- .../poi/hssf/usermodel/HSSFWorkbook.java | 24 +++++++++++++------ .../org/apache/poi/ss/usermodel/Workbook.java | 4 ++-- .../poi/hssf/usermodel/TestHSSFWorkbook.java | 4 ++-- 6 files changed, 35 insertions(+), 19 deletions(-) diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java index 50f945a44b..37ea1519be 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java @@ -1362,7 +1362,7 @@ public class SXSSFWorkbook implements Workbook { } @Override - public void setUseR1C1CellReferences(boolean useR1C1CellReferences) { - getXSSFWorkbook().setUseR1C1CellReferences(useR1C1CellReferences); + public void setCellReferenceType(CellReferenceType cellReferenceType) { + getXSSFWorkbook().setCellReferenceType(cellReferenceType); } } diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index dc4e8448e6..037531f3fb 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -1565,11 +1565,17 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Date1904Su } @Override - public void setUseR1C1CellReferences(boolean useR1C1CellReferences) { + public void setCellReferenceType(CellReferenceType cellReferenceType) { CTCalcPr calcPr = getCTWorkbook().getCalcPr(); - if (calcPr == null) calcPr = getCTWorkbook().addNewCalcPr(); - STRefMode.Enum refMode = useR1C1CellReferences ? R_1_C_1 : A_1; - calcPr.setRefMode(refMode); + if (cellReferenceType == CellReferenceType.UNKNOWN) { + if (calcPr != null) { + calcPr.unsetRefMode(); + } + } else { + if (calcPr == null) calcPr = getCTWorkbook().addNewCalcPr(); + STRefMode.Enum refMode = cellReferenceType == CellReferenceType.R1C1 ? R_1_C_1 : A_1; + calcPr.setRefMode(refMode); + } } private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) { diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java index 51a91fc920..235d16116a 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java @@ -1373,7 +1373,7 @@ public final class TestXSSFWorkbook extends BaseTestXWorkbook { XSSFWorkbook wb = openSampleWorkbook("WithTable.xlsx") ) { assertEquals(CellReferenceType.A1, wb.getCellReferenceType()); - wb.setUseR1C1CellReferences(true); + wb.setCellReferenceType(CellReferenceType.R1C1); assertEquals(CellReferenceType.R1C1, wb.getCellReferenceType()); wb.write(bos); try (XSSFWorkbook wb2 = new XSSFWorkbook(bos.toInputStream())) { @@ -1389,7 +1389,7 @@ public final class TestXSSFWorkbook extends BaseTestXWorkbook { XSSFWorkbook wb = new XSSFWorkbook() ) { assertEquals(CellReferenceType.UNKNOWN, wb.getCellReferenceType()); - wb.setUseR1C1CellReferences(true); + wb.setCellReferenceType(CellReferenceType.R1C1); assertEquals(CellReferenceType.R1C1, wb.getCellReferenceType()); wb.write(bos); try (XSSFWorkbook wb2 = new XSSFWorkbook(bos.toInputStream())) { diff --git a/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index 82473b60ff..9b79919177 100644 --- a/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -1757,7 +1757,10 @@ public final class HSSFWorkbook extends POIDocument implements Workbook { RefModeRecord refModeRecord = null; for (RecordBase record : records) { - if (record instanceof RefModeRecord) refModeRecord = (RefModeRecord)record; + if (record instanceof RefModeRecord) { + refModeRecord = (RefModeRecord)record; + break; + } } if (refModeRecord == null) { continue; @@ -1771,17 +1774,17 @@ public final class HSSFWorkbook extends POIDocument implements Workbook { } /** - * Configure workbook to use R1C1 cell references (as opposed to A1 cell references). + * Configure workbook to a specific cell reference type, e.g. R1C1 cell references (as opposed to A1 cell references). *

* Note that HSSF format stores this information at sheet level - so if the workbook has no sheets, * this call will have no effect. It is recommended that you call this (possibly again) just before * writing HSSFWorkbook. *

- * @param useR1C1CellReferences set to true if you want to configure workbook to use R1C1 cell references (as opposed to A1 cell references). + * @param cellReferenceType the type of cell references used * @since POI 5.2.1 */ @Override - public void setUseR1C1CellReferences(boolean useR1C1CellReferences) { + public void setCellReferenceType(CellReferenceType cellReferenceType) { for (HSSFSheet hssfSheet : _sheets) { InternalSheet internalSheet = hssfSheet.getSheet(); @@ -1790,20 +1793,27 @@ public final class HSSFWorkbook extends POIDocument implements Workbook { RefModeRecord refModeRecord = null; for (RecordBase record : records) { - if (record instanceof RefModeRecord) refModeRecord = (RefModeRecord)record; + if (record instanceof RefModeRecord) { + refModeRecord = (RefModeRecord)record; + break; + } } - if (useR1C1CellReferences) { + if (cellReferenceType == CellReferenceType.R1C1) { if (refModeRecord == null) { refModeRecord = new RefModeRecord(); records.add(records.size() - 1, refModeRecord); } refModeRecord.setMode(RefModeRecord.USE_R1C1_MODE); - } else { + } else if (cellReferenceType == CellReferenceType.A1) { if (refModeRecord == null) { refModeRecord = new RefModeRecord(); records.add(records.size() - 1, refModeRecord); } refModeRecord.setMode(RefModeRecord.USE_A1_MODE); + } else { + if (refModeRecord != null) { + records.remove(refModeRecord); + } } } } diff --git a/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java b/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java index 7f59ab17b5..64d6c1cf41 100644 --- a/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java +++ b/poi/src/main/java/org/apache/poi/ss/usermodel/Workbook.java @@ -639,8 +639,8 @@ public interface Workbook extends Closeable, Iterable { CellReferenceType getCellReferenceType(); /** - * @param useR1C1CellReferences set to true if you want to configure workbook to use R1C1 cell references (as opposed to A1 cell references). + * @param cellReferenceType the type of cell references used * @since POI 5.2.1 */ - void setUseR1C1CellReferences(boolean useR1C1CellReferences); + void setCellReferenceType(CellReferenceType cellReferenceType); } diff --git a/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java b/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java index 3851c44a7e..3cd5b70382 100644 --- a/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java +++ b/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java @@ -1186,7 +1186,7 @@ public final class TestHSSFWorkbook extends BaseTestWorkbook { HSSFWorkbook wb = openSampleWorkbook("49423.xls") ) { assertEquals(CellReferenceType.A1, wb.getCellReferenceType()); - wb.setUseR1C1CellReferences(true); + wb.setCellReferenceType(CellReferenceType.R1C1); assertEquals(CellReferenceType.R1C1, wb.getCellReferenceType()); wb.write(bos); try (HSSFWorkbook wb2 = new HSSFWorkbook(bos.toInputStream())) { @@ -1204,7 +1204,7 @@ public final class TestHSSFWorkbook extends BaseTestWorkbook { assertEquals(CellReferenceType.UNKNOWN, wb.getCellReferenceType()); HSSFSheet sheet = wb.createSheet(); assertEquals(CellReferenceType.A1, wb.getCellReferenceType()); - wb.setUseR1C1CellReferences(true); + wb.setCellReferenceType(CellReferenceType.R1C1); assertEquals(CellReferenceType.R1C1, wb.getCellReferenceType()); wb.write(bos); try (HSSFWorkbook wb2 = new HSSFWorkbook(bos.toInputStream())) { -- 2.39.5