From 02678b307325a5bfd8bc4329e48a21f7639a7414 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sat, 4 Aug 2012 08:51:49 +0000 Subject: [PATCH] Bug 53500: split setRepatingRowsAndColumns into setRepeatingRows and setRepeatingColumns git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1369290 13f79535-47bb-0310-9956-ffa450edef68 --- .../content/xdocs/spreadsheet/quick-guide.xml | 37 +++-- .../apache/poi/hssf/usermodel/HSSFName.java | 12 ++ .../apache/poi/hssf/usermodel/HSSFSheet.java | 98 +++++++++++- .../poi/hssf/usermodel/HSSFWorkbook.java | 142 +++++++++--------- .../org/apache/poi/ss/usermodel/Sheet.java | 51 ++++++- .../org/apache/poi/ss/usermodel/Workbook.java | 4 + .../apache/poi/ss/util/CellRangeAddress.java | 8 +- .../apache/poi/xssf/streaming/SXSSFSheet.java | 41 ++++- .../poi/xssf/streaming/SXSSFWorkbook.java | 4 + .../apache/poi/xssf/usermodel/XSSFSheet.java | 117 ++++++++++++++- .../poi/xssf/usermodel/XSSFWorkbook.java | 93 ++++-------- .../poi/xssf/usermodel/TestXSSFName.java | 25 +-- .../poi/hssf/usermodel/TestHSSFSheet.java | 16 ++ .../poi/hssf/usermodel/TestHSSFWorkbook.java | 14 +- .../poi/ss/usermodel/BaseTestSheet.java | 74 +++++++++ .../poi/ss/usermodel/BaseTestWorkbook.java | 33 ++-- 16 files changed, 558 insertions(+), 211 deletions(-) diff --git a/src/documentation/content/xdocs/spreadsheet/quick-guide.xml b/src/documentation/content/xdocs/spreadsheet/quick-guide.xml index 2140cda6ea..b4dc72eba1 100644 --- a/src/documentation/content/xdocs/spreadsheet/quick-guide.xml +++ b/src/documentation/content/xdocs/spreadsheet/quick-guide.xml @@ -865,26 +865,31 @@ Examples:
Repeating rows and columns

It's possible to set up repeating rows and columns in - your printouts by using the setRepeatingRowsAndColumns() - function in the HSSFWorkbook class. + your printouts by using the setRepeatingRows() and + setRepeatingColumns() methods in the Sheet class.

- This function Contains 5 parameters. - The first parameter is the index to the sheet (0 = first sheet). - The second and third parameters specify the range for the columns to repreat. - To stop the columns from repeating pass in -1 as the start and end column. - The fourth and fifth parameters specify the range for the rows to repeat. - To stop the columns from repeating pass in -1 as the start and end rows. + These methods expect a CellRangeAddress parameter + which specifies the range for the rows or columns to + repeat. + For setRepeatingRows(), it should specify a range of + rows to repeat, with the column part spanning all + columns. + For setRepeatingColums(), it should specify a range of + columns to repeat, with the row part spanning all + rows. + If the parameter is null, the repeating rows or columns + will be removed.

- Workbook wb = new HSSFWorkbook(); - Sheet sheet1 = wb.createSheet("new sheet"); - Sheet sheet2 = wb.createSheet("second sheet"); - - // Set the columns to repeat from column 0 to 2 on the first sheet - wb.setRepeatingRowsAndColumns(0,0,2,-1,-1); - // Set the the repeating rows and columns on the second sheet. - wb.setRepeatingRowsAndColumns(1,4,5,1,2); + Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook(); + Sheet sheet1 = wb.createSheet("Sheet1"); + Sheet sheet2 = wb.createSheet("Sheet2"); + + // Set the rows to repeat from row 4 to 5 on the first sheet. + sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5")); + // Set the columns to repeat from column A to C on the second sheet + sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFName.java b/src/java/org/apache/poi/hssf/usermodel/HSSFName.java index 9309bd7165..d6d041430b 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFName.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFName.java @@ -200,6 +200,18 @@ public final class HSSFName implements Name { return HSSFFormulaParser.toFormulaString(_book, ptgs); } + + /** + * Sets the NameParsedFormula structure that specifies the formula for the + * defined name. + * + * @param ptgs the sequence of {@link Ptg}s for the formula. + */ + void setNameDefinition(Ptg[] ptgs) { + _definedNameRec.setNameDefinition(ptgs); + } + + public boolean isDeleted(){ Ptg[] ptgs = _definedNameRec.getNameDefinition(); return Ptg.doesFormulaReferToDeletedCell(ptgs); diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index aea7c7b017..40f85033cf 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -32,8 +32,10 @@ import org.apache.poi.hssf.record.aggregates.DataValidityTable; import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock; import org.apache.poi.ss.formula.FormulaShifter; +import org.apache.poi.ss.formula.ptg.MemFuncPtg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.Area3DPtg; +import org.apache.poi.ss.formula.ptg.UnionPtg; import org.apache.poi.hssf.util.PaneInformation; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.FormulaType; @@ -2004,16 +2006,110 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { } + @Override public CellRangeAddress getRepeatingRows() { return getRepeatingRowsOrColums(true); } + @Override public CellRangeAddress getRepeatingColumns() { return getRepeatingRowsOrColums(false); } + @Override + public void setRepeatingRows(CellRangeAddress rowRangeRef) { + CellRangeAddress columnRangeRef = getRepeatingColumns(); + setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef); + } + + + @Override + public void setRepeatingColumns(CellRangeAddress columnRangeRef) { + CellRangeAddress rowRangeRef = getRepeatingRows(); + setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef); + } + + + private void setRepeatingRowsAndColumns( + CellRangeAddress rowDef, CellRangeAddress colDef) { + int sheetIndex = _workbook.getSheetIndex(this); + int maxRowIndex = SpreadsheetVersion.EXCEL97.getLastRowIndex(); + int maxColIndex = SpreadsheetVersion.EXCEL97.getLastColumnIndex(); + + int col1 = -1; + int col2 = -1; + int row1 = -1; + int row2 = -1; + + if (rowDef != null) { + row1 = rowDef.getFirstRow(); + row2 = rowDef.getLastRow(); + if ((row1 == -1 && row2 != -1) || (row1 > row2) + || (row1 < 0 || row1 > maxRowIndex) + || (row2 < 0 || row2 > maxRowIndex)) { + throw new IllegalArgumentException("Invalid row range specification"); + } + } + if (colDef != null) { + col1 = colDef.getFirstColumn(); + col2 = colDef.getLastColumn(); + if ((col1 == -1 && col2 != -1) || (col1 > col2) + || (col1 < 0 || col1 > maxColIndex) + || (col2 < 0 || col2 > maxColIndex)) { + throw new IllegalArgumentException("Invalid column range specification"); + } + } + + short externSheetIndex = + _workbook.getWorkbook().checkExternSheet(sheetIndex); + + boolean setBoth = rowDef != null && colDef != null; + boolean removeAll = rowDef == null && colDef == null; + + HSSFName name = _workbook.getBuiltInName( + NameRecord.BUILTIN_PRINT_TITLE, sheetIndex); + if (removeAll) { + if (name != null) { + _workbook.removeName(name); + } + return; + } + if (name == null) { + name = _workbook.createBuiltInName( + NameRecord.BUILTIN_PRINT_TITLE, sheetIndex); + } + + List ptgList = new ArrayList(); + if (setBoth) { + final int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE + ptgList.add(new MemFuncPtg(exprsSize)); + } + if (colDef != null) { + Area3DPtg colArea = new Area3DPtg(0, maxRowIndex, col1, col2, + false, false, false, false, externSheetIndex); + ptgList.add(colArea); + } + if (rowDef != null) { + Area3DPtg rowArea = new Area3DPtg(row1, row2, 0, maxColIndex, + false, false, false, false, externSheetIndex); + ptgList.add(rowArea); + } + if (setBoth) { + ptgList.add(UnionPtg.instance); + } + + Ptg[] ptgs = new Ptg[ptgList.size()]; + ptgList.toArray(ptgs); + name.setNameDefinition(ptgs); + + HSSFPrintSetup printSetup = getPrintSetup(); + printSetup.setValidSettings(false); + setActive(true); + } + + private CellRangeAddress getRepeatingRowsOrColums(boolean rows) { NameRecord rec = getBuiltinNameRecord(NameRecord.BUILTIN_PRINT_TITLE); if (rec == null) { @@ -2021,7 +2117,7 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet { } Ptg[] nameDefinition = rec.getNameDefinition(); - if (rec.getNameDefinition() == null) { + if (nameDefinition == null) { return null; } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index a92f4bbff6..4ee1267053 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -49,13 +49,10 @@ import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.formula.SheetNameFormatter; -import org.apache.poi.ss.formula.ptg.Area3DPtg; -import org.apache.poi.ss.formula.ptg.MemFuncPtg; -import org.apache.poi.ss.formula.ptg.Ptg; -import org.apache.poi.ss.formula.ptg.UnionPtg; import org.apache.poi.ss.formula.udf.AggregatingUDFFinder; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.WorkbookUtil; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; @@ -75,8 +72,6 @@ import org.apache.commons.codec.digest.DigestUtils; */ public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.usermodel.Workbook { private static final Pattern COMMA_PATTERN = Pattern.compile(","); - private static final int MAX_ROW = 0xFFFF; - private static final int MAX_COLUMN = (short)0x00FF; /** * The maximum number of cell styles in a .xls workbook. @@ -957,80 +952,27 @@ public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss * @param endColumn 0 based end of repeating columns. * @param startRow 0 based start of repeating rows. * @param endRow 0 based end of repeating rows. + * + * @deprecated use {@link HSSFSheet#setRepeatingRows(CellRangeAddress)} + * or {@link HSSFSheet#setRepeatingColumns(CellRangeAddress)} */ public void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, - int startRow, int endRow) - { - // Check arguments - if (startColumn == -1 && endColumn != -1) throw new IllegalArgumentException("Invalid column range specification"); - if (startRow == -1 && endRow != -1) throw new IllegalArgumentException("Invalid row range specification"); - if (startColumn < -1 || startColumn >= MAX_COLUMN) throw new IllegalArgumentException("Invalid column range specification"); - if (endColumn < -1 || endColumn >= MAX_COLUMN) throw new IllegalArgumentException("Invalid column range specification"); - if (startRow < -1 || startRow > MAX_ROW) throw new IllegalArgumentException("Invalid row range specification"); - if (endRow < -1 || endRow > MAX_ROW) throw new IllegalArgumentException("Invalid row range specification"); - if (startColumn > endColumn) throw new IllegalArgumentException("Invalid column range specification"); - if (startRow > endRow) throw new IllegalArgumentException("Invalid row range specification"); - - HSSFSheet sheet = getSheetAt(sheetIndex); - short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex); - - boolean settingRowAndColumn = - startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1; - boolean removingRange = - startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1; - - int rowColHeaderNameIndex = findExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_PRINT_TITLE); - if (removingRange) { - if (rowColHeaderNameIndex >= 0) { - workbook.removeName(rowColHeaderNameIndex); - } - return; - } - boolean isNewRecord; - NameRecord nameRecord; - if (rowColHeaderNameIndex < 0) { - //does a lot of the house keeping for builtin records, like setting lengths to zero etc - nameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex+1); - isNewRecord = true; - } else { - nameRecord = workbook.getNameRecord(rowColHeaderNameIndex); - isNewRecord = false; - } - - List temp = new ArrayList(); - - if (settingRowAndColumn) { - final int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE - temp.add(new MemFuncPtg(exprsSize)); - } - if (startColumn >= 0) { - Area3DPtg colArea = new Area3DPtg(0, MAX_ROW, startColumn, endColumn, - false, false, false, false, externSheetIndex); - temp.add(colArea); - } - if (startRow >= 0) { - Area3DPtg rowArea = new Area3DPtg(startRow, endRow, 0, MAX_COLUMN, - false, false, false, false, externSheetIndex); - temp.add(rowArea); - } - if (settingRowAndColumn) { - temp.add(UnionPtg.instance); - } - Ptg[] ptgs = new Ptg[temp.size()]; - temp.toArray(ptgs); - nameRecord.setNameDefinition(ptgs); + int startRow, int endRow) { + HSSFSheet sheet = getSheetAt(sheetIndex); - if (isNewRecord) - { - HSSFName newName = new HSSFName(this, nameRecord, nameRecord.isBuiltInName() ? null : workbook.getNameCommentRecord(nameRecord)); - names.add(newName); - } + CellRangeAddress rows = null; + CellRangeAddress cols = null; - HSSFPrintSetup printSetup = sheet.getPrintSetup(); - printSetup.setValidSettings(false); + if (startRow != -1) { + rows = new CellRangeAddress(startRow, endRow, -1, -1); + } + if (startColumn != -1) { + cols = new CellRangeAddress(-1, -1, startColumn, endColumn); + } - sheet.setActive(true); + sheet.setRepeatingRows(rows); + sheet.setRepeatingColumns(cols); } @@ -1050,6 +992,26 @@ public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss return -1; } + + HSSFName createBuiltInName(byte builtinCode, int sheetIndex) { + NameRecord nameRecord = + workbook.createBuiltInName(builtinCode, sheetIndex + 1); + HSSFName newName = new HSSFName(this, nameRecord, null); + names.add(newName); + return newName; + } + + + HSSFName getBuiltInName(byte builtinCode, int sheetIndex) { + int index = findExistingBuiltinNameRecordIdx(sheetIndex, builtinCode); + if (index < 0) { + return null; + } else { + return names.get(index); + } + } + + /** * create a new Font and add it to the workbook's font table * @return new font object @@ -1477,6 +1439,25 @@ public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss } + /** + * As {@link #getNameIndex(String)} is not necessarily unique + * (name + sheet index is unique), this method is more accurate. + * + * @param name the name whose index in the list of names of this workbook + * should be looked up. + * @return an index value >= 0 if the name was found; -1, if the name was + * not found + */ + int getNameIndex(HSSFName name) { + for (int k = 0; k < names.size(); k++) { + if (name == names.get(k)) { + return k; + } + } + return -1; + } + + public void removeName(int index){ names.remove(index); workbook.removeName(index); @@ -1497,10 +1478,21 @@ public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss public void removeName(String name) { int index = getNameIndex(name); - removeName(index); } + + /** + * As {@link #removeName(String)} is not necessarily unique + * (name + sheet index is unique), this method is more accurate. + * + * @param name the name to remove. + */ + void removeName(HSSFName name) { + int index = getNameIndex(name); + removeName(index); + } + public HSSFPalette getCustomPalette() { return new HSSFPalette(workbook.getCustomPalette()); diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java index 4ef8842609..8b6b41678f 100644 --- a/src/java/org/apache/poi/ss/usermodel/Sheet.java +++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java @@ -965,10 +965,57 @@ public interface Sheet extends Iterable { * If the Sheet does not have any repeating columns defined, null is * returned. * - * @return an {@link CellRangeAddress} containing the repeating columns for the - * Sheet, or null. + * @return an {@link CellRangeAddress} containing the repeating columns for + * the Sheet, or null. */ CellRangeAddress getRepeatingColumns(); + /** + * Sets the repeating rows used when printing the sheet, as found in + * File->PageSetup->Sheet. + *

+ * Repeating rows cover a range of contiguous rows, e.g.: + * 

+     * Sheet1!$1:$1
+     * Sheet2!$5:$8
+ * The parameter {@link CellRangeAddress} should specify a column part + * which spans all columns, and a row part which specifies the contiguous + * range of repeating rows, e.g.: + * 
+     * sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3"));
+ * A null parameter value indicates that repeating rows should be removed + * from the Sheet: + * 
+     * sheet.setRepeatingRows(null);
+ * + * @param rowRangeRef a {@link CellRangeAddress} containing the repeating + * rows for the Sheet, or null. + */ + void setRepeatingRows(CellRangeAddress rowRangeRef); + + + /** + * Sets the repeating columns used when printing the sheet, as found in + * File->PageSetup->Sheet. + *

+ * Repeating columns cover a range of contiguous columns, e.g.: + * 

+     * Sheet1!$A:$A
+     * Sheet2!$C:$F
+ * The parameter {@link CellRangeAddress} should specify a row part + * which spans all rows, and a column part which specifies the contiguous + * range of repeating columns, e.g.: + * 
+     * sheet.setRepeatingColumns(CellRangeAddress.valueOf("B:C"));
+ * A null parameter value indicates that repeating columns should be removed + * from the Sheet: + * 
+     * sheet.setRepeatingColumns(null);
+ * + * @param columnRangeRef a {@link CellRangeAddress} containing the repeating + * columns for the Sheet, or null. + */ + void setRepeatingColumns(CellRangeAddress columnRangeRef); + } diff --git a/src/java/org/apache/poi/ss/usermodel/Workbook.java b/src/java/org/apache/poi/ss/usermodel/Workbook.java index ce117f4cde..a476a74f8c 100644 --- a/src/java/org/apache/poi/ss/usermodel/Workbook.java +++ b/src/java/org/apache/poi/ss/usermodel/Workbook.java @@ -23,6 +23,7 @@ import java.util.List; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; +import org.apache.poi.ss.util.CellRangeAddress; /** * High level representation of a Excel workbook. This is the first object most users @@ -284,6 +285,9 @@ public interface Workbook { * @param endColumn 0 based end of repeating columns. * @param startRow 0 based start of repeating rows. * @param endRow 0 based end of repeating rows. + * + * @deprecated use {@link Sheet#setRepeatingRows(CellRangeAddress)} + * or {@link Sheet#setRepeatingColumns(CellRangeAddress)} */ void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow); diff --git a/src/java/org/apache/poi/ss/util/CellRangeAddress.java b/src/java/org/apache/poi/ss/util/CellRangeAddress.java index 418161d752..b69476ea24 100644 --- a/src/java/org/apache/poi/ss/util/CellRangeAddress.java +++ b/src/java/org/apache/poi/ss/util/CellRangeAddress.java @@ -111,8 +111,12 @@ public class CellRangeAddress extends CellRangeAddressBase { } /** - * @param ref usually a standard area ref (e.g. "B1:D8"). May be a single cell - * ref (e.g. "B5") in which case the result is a 1 x 1 cell range. + * Creates a CellRangeAddress from a cell range reference string. + * + * @param ref usually a standard area ref (e.g. "B1:D8"). May be a single + * cell ref (e.g. "B5") in which case the result is a 1 x 1 cell + * range. May also be a whole row range (e.g. "3:5"), or a whole + * column range (e.g. "C:F") */ public static CellRangeAddress valueOf(String ref) { int sep = ref.indexOf(":"); diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java index 75596af705..84589a6fdc 100644 --- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java @@ -17,20 +17,32 @@ package org.apache.poi.xssf.streaming; -import java.io.*; +import java.io.IOException; +import java.io.InputStream; import java.util.Iterator; -import java.util.TreeMap; import java.util.Map; +import java.util.TreeMap; +import org.apache.poi.hssf.util.PaneInformation; import org.apache.poi.ss.SpreadsheetVersion; -import org.apache.poi.ss.usermodel.*; - -import org.apache.poi.ss.util.AreaReference; +import org.apache.poi.ss.usermodel.AutoFilter; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellRange; +import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.Comment; +import org.apache.poi.ss.usermodel.DataValidation; +import org.apache.poi.ss.usermodel.DataValidationHelper; +import org.apache.poi.ss.usermodel.Drawing; +import org.apache.poi.ss.usermodel.Footer; +import org.apache.poi.ss.usermodel.Header; +import org.apache.poi.ss.usermodel.PrintSetup; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.SheetConditionalFormatting; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.SheetUtil; import org.apache.poi.xssf.usermodel.XSSFSheet; - -import org.apache.poi.hssf.util.PaneInformation; -import org.apache.poi.ss.util.CellRangeAddress; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; @@ -1266,15 +1278,28 @@ public class SXSSFSheet implements Sheet, Cloneable } + @Override public CellRangeAddress getRepeatingRows() { return _sh.getRepeatingRows(); } + @Override public CellRangeAddress getRepeatingColumns() { return _sh.getRepeatingColumns(); } + @Override + public void setRepeatingRows(CellRangeAddress rowRangeRef) { + _sh.setRepeatingRows(rowRangeRef); + } + + @Override + public void setRepeatingColumns(CellRangeAddress columnRangeRef) { + _sh.setRepeatingColumns(columnRangeRef); + } + + //end of interface implementation /** diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java index 056dca313f..73c4b934cf 100644 --- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java @@ -42,6 +42,7 @@ import java.util.zip.ZipEntry; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; +import org.apache.poi.ss.util.CellRangeAddress; /** * Streaming version of XSSFWorkbook implementing the "BigGridDemo" strategy. @@ -662,6 +663,9 @@ public class SXSSFWorkbook implements Workbook * @param endColumn 0 based end of repeating columns. * @param startRow 0 based start of repeating rows. * @param endRow 0 based end of repeating rows. + * + * @deprecated use {@link SXSSFSheet#setRepeatingRows(CellRangeAddress)} + * or {@link SXSSFSheet#setRepeatingColumns(CellRangeAddress)} */ public void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow) { diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index 691fddb397..a69bf09f43 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -41,6 +41,7 @@ import org.apache.poi.openxml4j.opc.PackageRelationship; import org.apache.poi.openxml4j.opc.PackageRelationshipCollection; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.formula.FormulaShifter; +import org.apache.poi.ss.formula.SheetNameFormatter; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; @@ -3187,15 +3188,130 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } + @Override public CellRangeAddress getRepeatingRows() { return getRepeatingRowsOrColums(true); } + @Override public CellRangeAddress getRepeatingColumns() { return getRepeatingRowsOrColums(false); } + @Override + public void setRepeatingRows(CellRangeAddress rowRangeRef) { + CellRangeAddress columnRangeRef = getRepeatingColumns(); + setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef); + } + + + @Override + public void setRepeatingColumns(CellRangeAddress columnRangeRef) { + CellRangeAddress rowRangeRef = getRepeatingRows(); + setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef); + } + + + private void setRepeatingRowsAndColumns( + CellRangeAddress rowDef, CellRangeAddress colDef) { + int col1 = -1; + int col2 = -1; + int row1 = -1; + int row2 = -1; + + if (rowDef != null) { + row1 = rowDef.getFirstRow(); + row2 = rowDef.getLastRow(); + if ((row1 == -1 && row2 != -1) + || row1 < -1 || row2 < -1 || row1 > row2) { + throw new IllegalArgumentException("Invalid row range specification"); + } + } + if (colDef != null) { + col1 = colDef.getFirstColumn(); + col2 = colDef.getLastColumn(); + if ((col1 == -1 && col2 != -1) + || col1 < -1 || col2 < -1 || col1 > col2) { + throw new IllegalArgumentException( + "Invalid column range specification"); + } + } + + int sheetIndex = getWorkbook().getSheetIndex(this); + + boolean removeAll = rowDef == null && colDef == null; + + XSSFName name = getWorkbook().getBuiltInName( + XSSFName.BUILTIN_PRINT_TITLE, sheetIndex); + if (removeAll) { + if (name != null) { + getWorkbook().removeName(name); + } + return; + } + if (name == null) { + name = getWorkbook().createBuiltInName( + XSSFName.BUILTIN_PRINT_TITLE, sheetIndex); + } + + String reference = getReferenceBuiltInRecord( + name.getSheetName(), col1, col2, row1, row2); + name.setRefersToFormula(reference); + + // If the print setup isn't currently defined, then add it + // in but without printer defaults + // If it's already there, leave it as-is! + if (worksheet.isSetPageSetup() && worksheet.isSetPageMargins()) { + // Everything we need is already there + } else { + // Have initial ones put in place + getPrintSetup().setValidSettings(false); + } + } + + private static String getReferenceBuiltInRecord( + String sheetName, int startC, int endC, int startR, int endR) { + // Excel example for built-in title: + // 'second sheet'!$E:$F,'second sheet'!$2:$3 + + CellReference colRef = + new CellReference(sheetName, 0, startC, true, true); + CellReference colRef2 = + new CellReference(sheetName, 0, endC, true, true); + CellReference rowRef = + new CellReference(sheetName, startR, 0, true, true); + CellReference rowRef2 = + new CellReference(sheetName, endR, 0, true, true); + + String escapedName = SheetNameFormatter.format(sheetName); + + String c = ""; + String r = ""; + + if(startC == -1 && endC == -1) { + } else { + c = escapedName + "!$" + colRef.getCellRefParts()[2] + + ":$" + colRef2.getCellRefParts()[2]; + } + + if (startR == -1 && endR == -1) { + + } else if (!rowRef.getCellRefParts()[1].equals("0") + && !rowRef2.getCellRefParts()[1].equals("0")) { + r = escapedName + "!$" + rowRef.getCellRefParts()[1] + + ":$" + rowRef2.getCellRefParts()[1]; + } + + StringBuffer rng = new StringBuffer(); + rng.append(c); + if(rng.length() > 0 && r.length() > 0) { + rng.append(','); + } + rng.append(r); + return rng.toString(); + } + private CellRangeAddress getRepeatingRowsOrColums(boolean rows) { int sheetIndex = getWorkbook().getSheetIndex(this); @@ -3232,5 +3348,4 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { return null; } - } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index a9ec08adaa..639a78d423 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -52,6 +52,7 @@ import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; +import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.util.WorkbookUtil; import org.apache.poi.util.*; @@ -925,6 +926,20 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable endColumn) - throw new IllegalArgumentException("Invalid column range specification"); - if ((startRow == -1 && endRow != -1) || startRow < -1 || endRow < -1 || startRow > endRow) - throw new IllegalArgumentException("Invalid row range specification"); - - XSSFSheet sheet = getSheetAt(sheetIndex); - boolean removingRange = startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1; - - XSSFName name = getBuiltInName(XSSFName.BUILTIN_PRINT_TITLE, sheetIndex); - if (removingRange) { - if(name != null)namedRanges.remove(name); - return; - } - if (name == null) { - name = createBuiltInName(XSSFName.BUILTIN_PRINT_TITLE, sheetIndex); - } - - String reference = getReferenceBuiltInRecord(name.getSheetName(), startColumn, endColumn, startRow, endRow); - name.setRefersToFormula(reference); - - // If the print setup isn't currently defined, then add it - // in but without printer defaults - // If it's already there, leave it as-is! - CTWorksheet ctSheet = sheet.getCTWorksheet(); - if(ctSheet.isSetPageSetup() && ctSheet.isSetPageMargins()) { - // Everything we need is already there - } else { - // Have initial ones put in place - XSSFPrintSetup printSetup = sheet.getPrintSetup(); - printSetup.setValidSettings(false); - } - } - - private static String getReferenceBuiltInRecord(String sheetName, int startC, int endC, int startR, int endR) { - //windows excel example for built-in title: 'second sheet'!$E:$F,'second sheet'!$2:$3 - CellReference colRef = new CellReference(sheetName, 0, startC, true, true); - CellReference colRef2 = new CellReference(sheetName, 0, endC, true, true); - - String escapedName = SheetNameFormatter.format(sheetName); - - String c; - if(startC == -1 && endC == -1) c= ""; - else c = escapedName + "!$" + colRef.getCellRefParts()[2] + ":$" + colRef2.getCellRefParts()[2]; - - CellReference rowRef = new CellReference(sheetName, startR, 0, true, true); - CellReference rowRef2 = new CellReference(sheetName, endR, 0, true, true); - - String r = ""; - if(startR == -1 && endR == -1) r = ""; - else { - if (!rowRef.getCellRefParts()[1].equals("0") && !rowRef2.getCellRefParts()[1].equals("0")) { - r = escapedName + "!$" + rowRef.getCellRefParts()[1] + ":$" + rowRef2.getCellRefParts()[1]; - } - } - - StringBuffer rng = new StringBuffer(); - rng.append(c); - if(rng.length() > 0 && r.length() > 0) rng.append(','); - rng.append(r); - return rng.toString(); + XSSFSheet sheet = getSheetAt(sheetIndex); + + CellRangeAddress rows = null; + CellRangeAddress cols = null; + + if (startRow != -1) { + rows = new CellRangeAddress(startRow, endRow, -1, -1); + } + if (startColumn != -1) { + cols = new CellRangeAddress(-1, -1, startColumn, endColumn); + } + + sheet.setRepeatingRows(rows); + sheet.setRepeatingColumns(cols); } private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) { diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java index 1c640b4177..21b74ae11f 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java @@ -20,6 +20,7 @@ package org.apache.poi.xssf.usermodel; import org.apache.poi.xssf.XSSFTestDataSamples; import org.apache.poi.xssf.XSSFITestDataProvider; import org.apache.poi.ss.usermodel.BaseTestNamedRange; +import org.apache.poi.ss.util.CellRangeAddress; /** * @author Yegor Kozlov @@ -35,13 +36,15 @@ public final class TestXSSFName extends BaseTestNamedRange { // First test that setting RR&C for same sheet more than once only creates a // single Print_Titles built-in record XSSFWorkbook wb = new XSSFWorkbook(); - wb.createSheet("First Sheet"); + XSSFSheet sheet1 = wb.createSheet("First Sheet"); - wb.setRepeatingRowsAndColumns(0, -1, -1, -1, -1); + sheet1.setRepeatingRows(null); + sheet1.setRepeatingColumns(null); // set repeating rows and columns twice for the first sheet for (int i = 0; i < 2; i++) { - wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3); + sheet1.setRepeatingRows(CellRangeAddress.valueOf("1:4")); + sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:A")); //sheet.createFreezePane(0, 3); } assertEquals(1, wb.getNumberOfNames()); @@ -51,18 +54,18 @@ public final class TestXSSFName extends BaseTestNamedRange { assertEquals("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.getRefersToFormula()); //remove the columns part - wb.setRepeatingRowsAndColumns(0, -1, -1, 0, 3); + sheet1.setRepeatingColumns(null); assertEquals("'First Sheet'!$1:$4", nr1.getRefersToFormula()); //revert - wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3); + sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:A")); //remove the rows part - wb.setRepeatingRowsAndColumns(0, 0, 0, -1, -1); + sheet1.setRepeatingRows(null); assertEquals("'First Sheet'!$A:$A", nr1.getRefersToFormula()); //revert - wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3); + sheet1.setRepeatingRows(CellRangeAddress.valueOf("1:4")); // Save and re-open XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb); @@ -75,8 +78,9 @@ public final class TestXSSFName extends BaseTestNamedRange { // check that setting RR&C on a second sheet causes a new Print_Titles built-in // name to be created - nwb.createSheet("SecondSheet"); - nwb.setRepeatingRowsAndColumns(1, 1, 2, 0, 0); + XSSFSheet sheet2 = nwb.createSheet("SecondSheet"); + sheet2.setRepeatingRows(CellRangeAddress.valueOf("1:1")); + sheet2.setRepeatingColumns(CellRangeAddress.valueOf("B:C")); assertEquals(2, nwb.getNumberOfNames()); XSSFName nr2 = nwb.getNameAt(1); @@ -84,6 +88,7 @@ public final class TestXSSFName extends BaseTestNamedRange { assertEquals(XSSFName.BUILTIN_PRINT_TITLE, nr2.getNameName()); assertEquals("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.getRefersToFormula()); - nwb.setRepeatingRowsAndColumns(1, -1, -1, -1, -1); + sheet2.setRepeatingRows(null); + sheet2.setRepeatingColumns(null); } } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java index 1abf1026ed..61c9c052cc 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java @@ -57,6 +57,22 @@ public final class TestHSSFSheet extends BaseTestSheet { super(HSSFITestDataProvider.instance); } + + /** + * Test for Bugzilla #29747. + * Moved from TestHSSFWorkbook#testSetRepeatingRowsAndColumns(). + */ + public void testSetRepeatingRowsAndColumnsBug29747() { + HSSFWorkbook wb = new HSSFWorkbook(); + wb.createSheet(); + wb.createSheet(); + HSSFSheet sheet2 = wb.createSheet(); + sheet2.setRepeatingRows(CellRangeAddress.valueOf("1:2")); + NameRecord nameRecord = wb.getWorkbook().getNameRecord(0); + assertEquals(3, nameRecord.getSheetNumber()); + } + + public void testTestGetSetMargin() { baseTestGetSetMargin(new double[]{0.75, 0.75, 1.0, 1.0, 0.3, 0.3}); } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java index 68462a2e2f..b1e36af747 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java @@ -56,17 +56,6 @@ public final class TestHSSFWorkbook extends BaseTestWorkbook { return wb.getWorkbook(); } - public void testSetRepeatingRowsAndColumns() { - // Test bug 29747 - HSSFWorkbook b = new HSSFWorkbook( ); - b.createSheet(); - b.createSheet(); - b.createSheet(); - b.setRepeatingRowsAndColumns( 2, 0,1,-1,-1 ); - NameRecord nameRecord = b.getWorkbook().getNameRecord( 0 ); - assertEquals(3, nameRecord.getSheetNumber()); - } - public void testWindowOneDefaults() { HSSFWorkbook b = new HSSFWorkbook( ); try { @@ -501,7 +490,8 @@ public final class TestHSSFWorkbook extends BaseTestWorkbook { assertEquals("Sheet2!$A$1:$IV$1", HSSFFormulaParser.toFormulaString(wb, nr.getNameDefinition())); // 1:1 try { - wb.setRepeatingRowsAndColumns(3, 4, 5, 8, 11); + wb.getSheetAt(3).setRepeatingRows(CellRangeAddress.valueOf("9:12")); + wb.getSheetAt(3).setRepeatingColumns(CellRangeAddress.valueOf("E:F")); } catch (RuntimeException e) { if (e.getMessage().equals("Builtin (7) already exists for sheet (4)")) { // there was a problem in the code which locates the existing print titles name record diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java index 7a3c213bab..d879e1b6a1 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java @@ -712,4 +712,78 @@ public abstract class BaseTestSheet extends TestCase { assertNull(sheet.getPaneInformation()); } + + public void testGetRepeatingRowsAndColumns() { + Workbook wb = _testDataProvider.openSampleWorkbook( + "RepeatingRowsCols." + + _testDataProvider.getStandardFileNameExtension()); + + checkRepeatingRowsAndColumns(wb.getSheetAt(0), null, null); + checkRepeatingRowsAndColumns(wb.getSheetAt(1), "1:1", null); + checkRepeatingRowsAndColumns(wb.getSheetAt(2), null, "A:A"); + checkRepeatingRowsAndColumns(wb.getSheetAt(3), "2:3", "A:B"); + } + + + public void testSetRepeatingRowsAndColumnsBug47294(){ + Workbook wb = _testDataProvider.createWorkbook(); + Sheet sheet1 = wb.createSheet(); + sheet1.setRepeatingRows(CellRangeAddress.valueOf("1:4")); + assertEquals("1:4", sheet1.getRepeatingRows().formatAsString()); + + //must handle sheets with quotas, see Bugzilla #47294 + Sheet sheet2 = wb.createSheet("My' Sheet"); + sheet2.setRepeatingRows(CellRangeAddress.valueOf("1:4")); + assertEquals("1:4", sheet2.getRepeatingRows().formatAsString()); + } + + public void testSetRepeatingRowsAndColumns() { + Workbook wb = _testDataProvider.createWorkbook(); + Sheet sheet1 = wb.createSheet("Sheet1"); + Sheet sheet2 = wb.createSheet("Sheet2"); + Sheet sheet3 = wb.createSheet("Sheet3"); + + checkRepeatingRowsAndColumns(sheet1, null, null); + + sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5")); + sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); + sheet3.setRepeatingRows(CellRangeAddress.valueOf("1:4")); + sheet3.setRepeatingColumns(CellRangeAddress.valueOf("A:A")); + + checkRepeatingRowsAndColumns(sheet1, "4:5", null); + checkRepeatingRowsAndColumns(sheet2, null, "A:C"); + checkRepeatingRowsAndColumns(sheet3, "1:4", "A:A"); + + // write out, read back, and test refrain... + wb = _testDataProvider.writeOutAndReadBack(wb); + sheet1 = wb.getSheetAt(0); + sheet2 = wb.getSheetAt(1); + sheet3 = wb.getSheetAt(2); + + checkRepeatingRowsAndColumns(sheet1, "4:5", null); + checkRepeatingRowsAndColumns(sheet2, null, "A:C"); + checkRepeatingRowsAndColumns(sheet3, "1:4", "A:A"); + + // check removing repeating rows and columns + sheet3.setRepeatingRows(null); + checkRepeatingRowsAndColumns(sheet3, null, "A:A"); + + sheet3.setRepeatingColumns(null); + checkRepeatingRowsAndColumns(sheet3, null, null); + } + + private void checkRepeatingRowsAndColumns( + Sheet s, String expectedRows, String expectedCols) { + if (expectedRows == null) { + assertNull(s.getRepeatingRows()); + } else { + assertEquals(expectedRows, s.getRepeatingRows().formatAsString()); + } + if (expectedCols == null) { + assertNull(s.getRepeatingColumns()); + } else { + assertEquals(expectedCols, s.getRepeatingColumns().formatAsString()); + } + } + } diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java index fca31de5f7..15181245bc 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java @@ -360,39 +360,26 @@ public abstract class BaseTestWorkbook extends TestCase { } - public void testGetRepeatingRowsAnsColumns(){ - Workbook wb = _testDataProvider.openSampleWorkbook( - "RepeatingRowsCols." - + _testDataProvider.getStandardFileNameExtension()); - - Sheet sheet0 = wb.getSheetAt(0); - assertNull(sheet0.getRepeatingRows()); - assertNull(sheet0.getRepeatingColumns()); - - Sheet sheet1 = wb.getSheetAt(1); - assertEquals("1:1", sheet1.getRepeatingRows().formatAsString()); - assertNull(sheet1.getRepeatingColumns()); - - Sheet sheet2 = wb.getSheetAt(2); - assertNull(sheet2.getRepeatingRows()); - assertEquals("A:A", sheet2.getRepeatingColumns().formatAsString()); - - Sheet sheet3 = wb.getSheetAt(3); - assertEquals("2:3", sheet3.getRepeatingRows().formatAsString()); - assertEquals("A:B", sheet3.getRepeatingColumns().formatAsString()); - } - - + /** + * Test is kept to ensure stub for deprecated business method passes test. + * + * @Deprecated remove this test when + * {@link Workbook#setRepeatingRowsAndColumns(int, int, int, int, int)} + * is removed + */ + @Deprecated public void testSetRepeatingRowsAnsColumns(){ Workbook wb = _testDataProvider.createWorkbook(); Sheet sheet1 = wb.createSheet(); wb.setRepeatingRowsAndColumns(wb.getSheetIndex(sheet1), 0, 0, 0, 3); assertEquals("1:4", sheet1.getRepeatingRows().formatAsString()); + assertEquals("A:A", sheet1.getRepeatingColumns().formatAsString()); //must handle sheets with quotas, see Bugzilla #47294 Sheet sheet2 = wb.createSheet("My' Sheet"); wb.setRepeatingRowsAndColumns(wb.getSheetIndex(sheet2), 0, 0, 0, 3); assertEquals("1:4", sheet2.getRepeatingRows().formatAsString()); + assertEquals("A:A", sheet1.getRepeatingColumns().formatAsString()); } /** -- 2.39.5