From f5a0a843ac965a23bd8407ba9c97209a8e933cae Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Tue, 1 Feb 2022 11:06:37 +0000 Subject: [PATCH] add excel Workbook.setUseR1C1CellReferences(boolean) git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1897652 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/xssf/streaming/SXSSFWorkbook.java | 5 ++++ .../poi/xssf/usermodel/XSSFWorkbook.java | 27 ++++++------------ .../poi/hssf/usermodel/HSSFWorkbook.java | 28 +++++++++++++++++++ .../org/apache/poi/ss/usermodel/Workbook.java | 6 ++++ 4 files changed, 48 insertions(+), 18 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 584a1f3f23..3c392cc63c 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 @@ -1359,4 +1359,9 @@ public class SXSSFWorkbook implements Workbook { public Boolean usesR1C1CellReferences() { return getXSSFWorkbook().usesR1C1CellReferences(); } + + @Override + public void setUseR1C1CellReferences(boolean useR1C1CellReferences) { + getXSSFWorkbook().setUseR1C1CellReferences(useR1C1CellReferences); + } } 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 47e19e2956..0fd6eb56a9 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 @@ -97,24 +97,7 @@ import org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlObject; import org.apache.xmlbeans.XmlOptions; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBookView; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBookViews; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcPr; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedNames; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExternalReference; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCache; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCaches; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheets; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookProtection; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCalcMode; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.STSheetState; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; /** * High level representation of a SpreadsheetML workbook. This is the first object most users @@ -1580,6 +1563,14 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Date1904Su return null; } + @Override + public void setUseR1C1CellReferences(boolean useR1C1CellReferences) { + CTCalcPr calcPr = getCTWorkbook().getCalcPr(); + if (calcPr == null) calcPr = getCTWorkbook().addNewCalcPr(); + STRefMode.Enum refMode = useR1C1CellReferences ? R_1_C_1 : A_1; + calcPr.setRefMode(refMode); + } + private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) { //windows excel example: Sheet1!$C$3:$E$4 CellReference colRef = new CellReference(sheetName, startR, startC, true, true); 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 86cd7440fd..139e19e2fe 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 @@ -1769,6 +1769,34 @@ public final class HSSFWorkbook extends POIDocument implements Workbook { return null; } + @Override + public void setUseR1C1CellReferences(boolean useR1C1CellReferences) { + for (HSSFSheet hssfSheet : _sheets) { + + InternalSheet internalSheet = hssfSheet.getSheet(); + + List records = internalSheet.getRecords(); + + RefModeRecord refModeRecord = null; + for (RecordBase record : records) { + if (record instanceof RefModeRecord) refModeRecord = (RefModeRecord)record; + } + if (useR1C1CellReferences) { + if (refModeRecord == null) { + refModeRecord = new RefModeRecord(); + records.add(records.size() - 1, refModeRecord); + } + refModeRecord.setMode(RefModeRecord.USE_R1C1_MODE); + } else { + if (refModeRecord == null) { + refModeRecord = new RefModeRecord(); + records.add(records.size() - 1, refModeRecord); + } + refModeRecord.setMode(RefModeRecord.USE_A1_MODE); + } + } + } + int getNameIndex(String name) { for (int k = 0; k < names.size(); k++) { 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 041d7a785f..3851ed581e 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 @@ -638,4 +638,10 @@ public interface Workbook extends Closeable, Iterable { * @since POI 5.2.1 */ Boolean usesR1C1CellReferences(); + + /** + * @param useR1C1CellReferences set to true if you want to configure workbook to use R1C1 cell references (as opposed to A1 cell references). + * @since POI 5.2.1 + */ + void setUseR1C1CellReferences(boolean useR1C1CellReferences); } -- 2.39.5