Attachment #29151 for bug #53500

View | Details | Raw Unified | Return to bug 53500
Collapse All | Expand All

Lines 42-47 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java (+4 lines)
42		42
43	import org.apache.poi.ss.formula.udf.UDFFinder;	43	import org.apache.poi.ss.formula.udf.UDFFinder;
44	import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;	44	import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
		45	import org.apache.poi.ss.util.CellRangeAddress;
45		46
46	/**	47	/**
47	* Streaming version of XSSFWorkbook implementing the "BigGridDemo" strategy.	48	* Streaming version of XSSFWorkbook implementing the "BigGridDemo" strategy.
Lines 662-667 Link Here
662	* @param endColumn 0 based end of repeating columns.	663	* @param endColumn 0 based end of repeating columns.
663	* @param startRow 0 based start of repeating rows.	664	* @param startRow 0 based start of repeating rows.
664	* @param endRow 0 based end of repeating rows.	665	* @param endRow 0 based end of repeating rows.
		666	*
		667	* @deprecated use {@link SXSSFSheet#setRepeatingRows(CellRangeAddress)}
		668	* or {@link SXSSFSheet#setRepeatingColumns(CellRangeAddress)}
665	*/	669	*/
666	public void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)	670	public void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)
667	{	671	{





package org.apache.poi.xssf.streaming;

import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;
import java.util.Map;

import org.apache.poi.hssf.util.PaneInformation;
import org.apache.poi.ss.SpreadsheetVersion;
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;


    }
    
    
    @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
    /**
     * Specifies how many rows can be accessed at most via getRow().




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.*;

        throw new IllegalArgumentException("Named range was not found: " + name);
    }


    /**
     * 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(XSSFName name) {
        if (!namedRanges.remove(name)) {
            throw new IllegalArgumentException("Name was not found: " + name);
        }
    }


    /**
     * Delete the printarea for the sheet specified
     *
     * @param sheetIndex 0-based sheet index (0 = First Sheet)

     * @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 XSSFSheet#setRepeatingRows(CellRangeAddress)}
     *        or {@link XSSFSheet#setRepeatingColumns(CellRangeAddress)}
     */
    public void setRepeatingRowsAndColumns(int sheetIndex,
                                           int startColumn, int endColumn,
                                           int startRow, int endRow) {
      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);

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

    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);




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;

    }
    
    
    @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);
      XSSFName name = getWorkbook().getBuiltInName(

      return null;
    }


}




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

        // 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();
        XSSFSheet sheet1 = wb.createSheet("First Sheet");

        sheet1.setRepeatingRows(null);
        sheet1.setRepeatingColumns(null);

        // set repeating rows and columns twice for the first sheet
        for (int i = 0; i < 2; i++) {
          sheet1.setRepeatingRows(CellRangeAddress.valueOf("1:4"));
          sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:A"));
            //sheet.createFreezePane(0, 3);
        }
        assertEquals(1, wb.getNumberOfNames());

        assertEquals("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.getRefersToFormula());

        //remove the columns part
        sheet1.setRepeatingColumns(null);
        assertEquals("'First Sheet'!$1:$4", nr1.getRefersToFormula());

        //revert
        sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:A"));

        //remove the rows part
        sheet1.setRepeatingRows(null);
        assertEquals("'First Sheet'!$A:$A", nr1.getRefersToFormula());

        //revert
        sheet1.setRepeatingRows(CellRangeAddress.valueOf("1:4"));

        // Save and re-open
        XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb);


        // check that setting RR&C on a second sheet causes a new Print_Titles built-in
        // name to be created
        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);

        assertEquals(XSSFName.BUILTIN_PRINT_TITLE, nr2.getNameName());
        assertEquals("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.getRefersToFormula());

        sheet2.setRepeatingRows(null);
        sheet2.setRepeatingColumns(null);
    }
}




                <section><title>Repeating rows and columns</title>
                    <p>
                        It's possible to set up repeating rows and columns in
                        your printouts by using the setRepeatingRows() and 
                        setRepeatingColumns() methods in the Sheet class.
                    </p>
                    <p>
                        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.
                    </p>
                    <source>
    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);




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;

 */
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.

     * @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) {
      HSSFSheet sheet = getSheetAt(sheetIndex);
        // 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");

      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);
            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);

        if (isNewRecord)
        {
            HSSFName newName = new HSSFName(this, nameRecord, nameRecord.isBuiltInName() ? null : workbook.getNameCommentRecord(nameRecord));
            names.add(newName);
        }

        HSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setValidSettings(false);

        sheet.setActive(true);
    }



        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

    }


    /**
     * 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);


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




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;

    }


  @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<Ptg> ptgList = new ArrayList<Ptg>();
    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) {

    }
    
    Ptg[] nameDefinition = rec.getNameDefinition();
    if (nameDefinition == null) {
      return null;
    }
    




        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);

Lines 111-118 Link Here

(-)src/java/org/apache/poi/ss/util/CellRangeAddress.java (-2 / +6 lines)
111	}	111	}
112		112
113	/**	113	/**
114	* @param ref usually a standard area ref (e.g. "B1:D8"). May be a single cell	114	* Creates a CellRangeAddress from a cell range reference string.
115	* ref (e.g. "B5") in which case the result is a 1 x 1 cell range.	115	*
		116	* @param ref usually a standard area ref (e.g. "B1:D8"). May be a single
		117	* cell ref (e.g. "B5") in which case the result is a 1 x 1 cell
		118	* range. May also be a whole row range (e.g. "3:5"), or a whole
		119	* column range (e.g. "C:F")
116	*/	120	*/
117	public static CellRangeAddress valueOf(String ref) {	121	public static CellRangeAddress valueOf(String ref) {
118	int sep = ref.indexOf(":");	122	int sep = ref.indexOf(":");




     * 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.
     */
    CellRangeAddress getRepeatingColumns();


    /**
     * Sets the repeating rows used when printing the sheet, as found in 
     * File->PageSetup->Sheet.
     * <p/>
     * Repeating rows cover a range of contiguous rows, e.g.:
     * <pre>
     * Sheet1!$1:$1
     * Sheet2!$5:$8</pre>
     * 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.:
     * <pre>
     * sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3"));</pre>
     * A null parameter value indicates that repeating rows should be removed 
     * from the Sheet:
     * <pre>
     * sheet.setRepeatingRows(null);</pre>
     * 
     * @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.
     * <p/>
     * Repeating columns cover a range of contiguous columns, e.g.:
     * <pre>
     * Sheet1!$A:$A
     * Sheet2!$C:$F</pre>
     * 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.:
     * <pre>
     * sheet.setRepeatingColumns(CellRangeAddress.valueOf("B:C"));</pre>
     * A null parameter value indicates that repeating columns should be removed 
     * from the Sheet:
     * <pre>
     * sheet.setRepeatingColumns(null);</pre>
     * 
     * @param columnRangeRef a {@link CellRangeAddress} containing the repeating 
     *        columns for the Sheet, or null.
     */
    void setRepeatingColumns(CellRangeAddress columnRangeRef);

}

Lines 23-28 Link Here

(-)src/java/org/apache/poi/ss/usermodel/Workbook.java (+4 lines)
23		23
24	import org.apache.poi.ss.formula.udf.UDFFinder;	24	import org.apache.poi.ss.formula.udf.UDFFinder;
25	import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;	25	import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
		26	import org.apache.poi.ss.util.CellRangeAddress;
26		27
27	/**	28	/**
28	* High level representation of a Excel workbook. This is the first object most users	29	* High level representation of a Excel workbook. This is the first object most users
Lines 284-289 Link Here
284	* @param endColumn 0 based end of repeating columns.	285	* @param endColumn 0 based end of repeating columns.
285	* @param startRow 0 based start of repeating rows.	286	* @param startRow 0 based start of repeating rows.
286	* @param endRow 0 based end of repeating rows.	287	* @param endRow 0 based end of repeating rows.
		288	*
		289	* @deprecated use {@link Sheet#setRepeatingRows(CellRangeAddress)}
		290	* or {@link Sheet#setRepeatingColumns(CellRangeAddress)}
287	*/	291	*/
288	void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow);	292	void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow);
289		293




    }


    /**
     * 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

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


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

    /**




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

}




        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 {

        assertEquals("Sheet2!$A$1:$IV$1", HSSFFormulaParser.toFormulaString(wb, nr.getNameDefinition())); // 1:1

        try {
          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




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

Return to bug 53500

Lines 52-57 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (-60 / +31 lines)
52	import org.apache.poi.ss.usermodel.Sheet;	52	import org.apache.poi.ss.usermodel.Sheet;
53	import org.apache.poi.ss.usermodel.Workbook;	53	import org.apache.poi.ss.usermodel.Workbook;
54	import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;	54	import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
		55	import org.apache.poi.ss.util.CellRangeAddress;
55	import org.apache.poi.ss.util.CellReference;	56	import org.apache.poi.ss.util.CellReference;
56	import org.apache.poi.ss.util.WorkbookUtil;	57	import org.apache.poi.ss.util.WorkbookUtil;
57	import org.apache.poi.util.*;	58	import org.apache.poi.util.*;
Lines 925-931 Link Here
925	throw new IllegalArgumentException("Named range was not found: " + name);	926	throw new IllegalArgumentException("Named range was not found: " + name);
926	}	927	}
927		928
		929
928	/**	930	/**
		931	* As {@link #removeName(String)} is not necessarily unique
		932	* (name + sheet index is unique), this method is more accurate.
		933	*
		934	* @param name the name to remove.
		935	*/
		936	void removeName(XSSFName name) {
		937	if (!namedRanges.remove(name)) {
		938	throw new IllegalArgumentException("Name was not found: " + name);
		939	}
		940	}
		941
		942
		943	/**
929	* Delete the printarea for the sheet specified	944	* Delete the printarea for the sheet specified
930	*	945	*
931	* @param sheetIndex 0-based sheet index (0 = First Sheet)	946	* @param sheetIndex 0-based sheet index (0 = First Sheet)
Lines 1129-1201 Link Here
1129	* @param endColumn 0 based end of repeating columns.	1144	* @param endColumn 0 based end of repeating columns.
1130	* @param startRow 0 based start of repeating rows.	1145	* @param startRow 0 based start of repeating rows.
1131	* @param endRow 0 based end of repeating rows.	1146	* @param endRow 0 based end of repeating rows.
		1147	*
		1148	* @deprecated use {@link XSSFSheet#setRepeatingRows(CellRangeAddress)}
		1149	* or {@link XSSFSheet#setRepeatingColumns(CellRangeAddress)}
1132	*/	1150	*/
1133	public void setRepeatingRowsAndColumns(int sheetIndex,	1151	public void setRepeatingRowsAndColumns(int sheetIndex,
1134	int startColumn, int endColumn,	1152	int startColumn, int endColumn,
1135	int startRow, int endRow) {	1153	int startRow, int endRow) {
1136	// Check arguments	1154	XSSFSheet sheet = getSheetAt(sheetIndex);
1137	if ((startColumn == -1 && endColumn != -1) \|\| startColumn < -1 \|\| endColumn < -1 \|\| startColumn > endColumn)	1155
1138	throw new IllegalArgumentException("Invalid column range specification");	1156	CellRangeAddress rows = null;
1139	if ((startRow == -1 && endRow != -1) \|\| startRow < -1 \|\| endRow < -1 \|\| startRow > endRow)	1157	CellRangeAddress cols = null;
1140	throw new IllegalArgumentException("Invalid row range specification");	1158
		1159	if (startRow != -1) {
		1160	rows = new CellRangeAddress(startRow, endRow, -1, -1);
		1161	}
		1162	if (startColumn != -1) {
		1163	cols = new CellRangeAddress(-1, -1, startColumn, endColumn);
		1164	}
1141		1165
1142	XSSFSheet sheet = getSheetAt(sheetIndex);	1166	sheet.setRepeatingRows(rows);
1143	boolean removingRange = startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1;	1167	sheet.setRepeatingColumns(cols);
1144
1145	XSSFName name = getBuiltInName(XSSFName.BUILTIN_PRINT_TITLE, sheetIndex);
1146	if (removingRange) {
1147	if(name != null)namedRanges.remove(name);
1148	return;
1149	}
1150	if (name == null) {
1151	name = createBuiltInName(XSSFName.BUILTIN_PRINT_TITLE, sheetIndex);
1152	}
1153
1154	String reference = getReferenceBuiltInRecord(name.getSheetName(), startColumn, endColumn, startRow, endRow);
1155	name.setRefersToFormula(reference);
1156
1157	// If the print setup isn't currently defined, then add it
1158	// in but without printer defaults
1159	// If it's already there, leave it as-is!
1160	CTWorksheet ctSheet = sheet.getCTWorksheet();
1161	if(ctSheet.isSetPageSetup() && ctSheet.isSetPageMargins()) {
1162	// Everything we need is already there
1163	} else {
1164	// Have initial ones put in place
1165	XSSFPrintSetup printSetup = sheet.getPrintSetup();
1166	printSetup.setValidSettings(false);
1167	}
1168	}	1168	}
1169		1169
1170	private static String getReferenceBuiltInRecord(String sheetName, int startC, int endC, int startR, int endR) {
1171	//windows excel example for built-in title: 'second sheet'!$E:$F,'second sheet'!$2:$3
1172	CellReference colRef = new CellReference(sheetName, 0, startC, true, true);
1173	CellReference colRef2 = new CellReference(sheetName, 0, endC, true, true);
1174
1175	String escapedName = SheetNameFormatter.format(sheetName);
1176
1177	String c;
1178	if(startC == -1 && endC == -1) c= "";
1179	else c = escapedName + "!$" + colRef.getCellRefParts()[2] + ":$" + colRef2.getCellRefParts()[2];
1180
1181	CellReference rowRef = new CellReference(sheetName, startR, 0, true, true);
1182	CellReference rowRef2 = new CellReference(sheetName, endR, 0, true, true);
1183
1184	String r = "";
1185	if(startR == -1 && endR == -1) r = "";
1186	else {
1187	if (!rowRef.getCellRefParts()[1].equals("0") && !rowRef2.getCellRefParts()[1].equals("0")) {
1188	r = escapedName + "!$" + rowRef.getCellRefParts()[1] + ":$" + rowRef2.getCellRefParts()[1];
1189	}
1190	}
1191
1192	StringBuffer rng = new StringBuffer();
1193	rng.append(c);
1194	if(rng.length() > 0 && r.length() > 0) rng.append(',');
1195	rng.append(r);
1196	return rng.toString();
1197	}
1198
1199	private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) {	1170	private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) {
1200	//windows excel example: Sheet1!$C$3:$E$4	1171	//windows excel example: Sheet1!$C$3:$E$4
1201	CellReference colRef = new CellReference(sheetName, startR, startC, true, true);	1172	CellReference colRef = new CellReference(sheetName, startR, startC, true, true);

Lines 41-46 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (-1 / +116 lines)
41	import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;	41	import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;
42	import org.apache.poi.ss.SpreadsheetVersion;	42	import org.apache.poi.ss.SpreadsheetVersion;
43	import org.apache.poi.ss.formula.FormulaShifter;	43	import org.apache.poi.ss.formula.FormulaShifter;
		44	import org.apache.poi.ss.formula.SheetNameFormatter;
44	import org.apache.poi.ss.usermodel.*;	45	import org.apache.poi.ss.usermodel.*;
45	import org.apache.poi.ss.util.CellRangeAddress;	46	import org.apache.poi.ss.util.CellRangeAddress;
46	import org.apache.poi.ss.util.CellRangeAddressList;	47	import org.apache.poi.ss.util.CellRangeAddressList;
Lines 3187-3202 Link Here
3187	}	3188	}
3188		3189
3189		3190
		3191	@Override
3190	public CellRangeAddress getRepeatingRows() {	3192	public CellRangeAddress getRepeatingRows() {
3191	return getRepeatingRowsOrColums(true);	3193	return getRepeatingRowsOrColums(true);
3192	}	3194	}
3193		3195
3194		3196
		3197	@Override
3195	public CellRangeAddress getRepeatingColumns() {	3198	public CellRangeAddress getRepeatingColumns() {
3196	return getRepeatingRowsOrColums(false);	3199	return getRepeatingRowsOrColums(false);
3197	}	3200	}
3198		3201
		3202	@Override
		3203	public void setRepeatingRows(CellRangeAddress rowRangeRef) {
		3204	CellRangeAddress columnRangeRef = getRepeatingColumns();
		3205	setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef);
		3206	}
3199		3207
		3208
		3209	@Override
		3210	public void setRepeatingColumns(CellRangeAddress columnRangeRef) {
		3211	CellRangeAddress rowRangeRef = getRepeatingRows();
		3212	setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef);
		3213	}
		3214
		3215
		3216	private void setRepeatingRowsAndColumns(
		3217	CellRangeAddress rowDef, CellRangeAddress colDef) {
		3218	int col1 = -1;
		3219	int col2 = -1;
		3220	int row1 = -1;
		3221	int row2 = -1;
		3222
		3223	if (rowDef != null) {
		3224	row1 = rowDef.getFirstRow();
		3225	row2 = rowDef.getLastRow();
		3226	if ((row1 == -1 && row2 != -1)
		3227	\|\| row1 < -1 \|\| row2 < -1 \|\| row1 > row2) {
		3228	throw new IllegalArgumentException("Invalid row range specification");
		3229	}
		3230	}
		3231	if (colDef != null) {
		3232	col1 = colDef.getFirstColumn();
		3233	col2 = colDef.getLastColumn();
		3234	if ((col1 == -1 && col2 != -1)
		3235	\|\| col1 < -1 \|\| col2 < -1 \|\| col1 > col2) {
		3236	throw new IllegalArgumentException(
		3237	"Invalid column range specification");
		3238	}
		3239	}
		3240
		3241	int sheetIndex = getWorkbook().getSheetIndex(this);
		3242
		3243	boolean removeAll = rowDef == null && colDef == null;
		3244
		3245	XSSFName name = getWorkbook().getBuiltInName(
		3246	XSSFName.BUILTIN_PRINT_TITLE, sheetIndex);
		3247	if (removeAll) {
		3248	if (name != null) {
		3249	getWorkbook().removeName(name);
		3250	}
		3251	return;
		3252	}
		3253	if (name == null) {
		3254	name = getWorkbook().createBuiltInName(
		3255	XSSFName.BUILTIN_PRINT_TITLE, sheetIndex);
		3256	}
		3257
		3258	String reference = getReferenceBuiltInRecord(
		3259	name.getSheetName(), col1, col2, row1, row2);
		3260	name.setRefersToFormula(reference);
		3261
		3262	// If the print setup isn't currently defined, then add it
		3263	// in but without printer defaults
		3264	// If it's already there, leave it as-is!
		3265	if (worksheet.isSetPageSetup() && worksheet.isSetPageMargins()) {
		3266	// Everything we need is already there
		3267	} else {
		3268	// Have initial ones put in place
		3269	getPrintSetup().setValidSettings(false);
		3270	}
		3271	}
3272
3273	private static String getReferenceBuiltInRecord(
3274	String sheetName, int startC, int endC, int startR, int endR) {
3275	// Excel example for built-in title:
3276	// 'second sheet'!$E:$F,'second sheet'!$2:$3
3277
3278	CellReference colRef =
3279	new CellReference(sheetName, 0, startC, true, true);
3280	CellReference colRef2 =
3281	new CellReference(sheetName, 0, endC, true, true);
3282	CellReference rowRef =
3283	new CellReference(sheetName, startR, 0, true, true);
3284	CellReference rowRef2 =
3285	new CellReference(sheetName, endR, 0, true, true);
3286
3287	String escapedName = SheetNameFormatter.format(sheetName);
3288
3289	String c = "";
3290	String r = "";
3291
3292	if(startC == -1 && endC == -1) {
3293	} else {
3294	c = escapedName + "!$" + colRef.getCellRefParts()[2]
3295	+ ":$" + colRef2.getCellRefParts()[2];
3296	}
3297
3298	if (startR == -1 && endR == -1) {
3299
3300	} else if (!rowRef.getCellRefParts()[1].equals("0")
3301	&& !rowRef2.getCellRefParts()[1].equals("0")) {
3302	r = escapedName + "!$" + rowRef.getCellRefParts()[1]
3303	+ ":$" + rowRef2.getCellRefParts()[1];
3304	}
3305
3306	StringBuffer rng = new StringBuffer();
3307	rng.append(c);
3308	if(rng.length() > 0 && r.length() > 0) {
3309	rng.append(',');
3310	}
3311	rng.append(r);
3312	return rng.toString();
3313	}
3314
3315
3200	private CellRangeAddress getRepeatingRowsOrColums(boolean rows) {	3316	private CellRangeAddress getRepeatingRowsOrColums(boolean rows) {
3201	int sheetIndex = getWorkbook().getSheetIndex(this);	3317	int sheetIndex = getWorkbook().getSheetIndex(this);
3202	XSSFName name = getWorkbook().getBuiltInName(	3318	XSSFName name = getWorkbook().getBuiltInName(
Lines 3232-3236 Link Here
3232	return null;	3348	return null;
3233	}	3349	}
3234		3350
3235
3236	}	3351	}

Lines 865-890 Link Here

(-)src/documentation/content/xdocs/spreadsheet/quick-guide.xml (-15 / +20 lines)
865	<section><title>Repeating rows and columns</title>	865	<section><title>Repeating rows and columns</title>
866	<p>	866	<p>
867	It's possible to set up repeating rows and columns in	867	It's possible to set up repeating rows and columns in
868	your printouts by using the setRepeatingRowsAndColumns()	868	your printouts by using the setRepeatingRows() and
869	function in the HSSFWorkbook class.	869	setRepeatingColumns() methods in the Sheet class.
870	</p>	870	</p>
871	<p>	871	<p>
872	This function Contains 5 parameters.	872	These methods expect a CellRangeAddress parameter
873	The first parameter is the index to the sheet (0 = first sheet).	873	which specifies the range for the rows or columns to
874	The second and third parameters specify the range for the columns to repreat.	874	repeat.
875	To stop the columns from repeating pass in -1 as the start and end column.	875	For setRepeatingRows(), it should specify a range of
876	The fourth and fifth parameters specify the range for the rows to repeat.	876	rows to repeat, with the column part spanning all
877	To stop the columns from repeating pass in -1 as the start and end rows.	877	columns.
		878	For setRepeatingColums(), it should specify a range of
		879	columns to repeat, with the row part spanning all
		880	rows.
		881	If the parameter is null, the repeating rows or columns
		882	will be removed.
878	</p>	883	</p>
879	<source>	884	<source>
880	Workbook wb = new HSSFWorkbook();	885	Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
881	Sheet sheet1 = wb.createSheet("new sheet");	886	Sheet sheet1 = wb.createSheet("Sheet1");
882	Sheet sheet2 = wb.createSheet("second sheet");	887	Sheet sheet2 = wb.createSheet("Sheet2");
883		888
884	// Set the columns to repeat from column 0 to 2 on the first sheet	889	// Set the rows to repeat from row 4 to 5 on the first sheet.
885	wb.setRepeatingRowsAndColumns(0,0,2,-1,-1);	890	sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5"));
886	// Set the the repeating rows and columns on the second sheet.	891	// Set the columns to repeat from column A to C on the second sheet
887	wb.setRepeatingRowsAndColumns(1,4,5,1,2);	892	sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C"));
888		893
889	FileOutputStream fileOut = new FileOutputStream("workbook.xls");	894	FileOutputStream fileOut = new FileOutputStream("workbook.xls");
890	wb.write(fileOut);	895	wb.write(fileOut);

Lines 49-61 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (-75 / +67 lines)
49	import org.apache.poi.ss.formula.FormulaShifter;	49	import org.apache.poi.ss.formula.FormulaShifter;
50	import org.apache.poi.ss.formula.FormulaType;	50	import org.apache.poi.ss.formula.FormulaType;
51	import org.apache.poi.ss.formula.SheetNameFormatter;	51	import org.apache.poi.ss.formula.SheetNameFormatter;
52	import org.apache.poi.ss.formula.ptg.Area3DPtg;
53	import org.apache.poi.ss.formula.ptg.MemFuncPtg;
54	import org.apache.poi.ss.formula.ptg.Ptg;
55	import org.apache.poi.ss.formula.ptg.UnionPtg;
56	import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;	52	import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
57	import org.apache.poi.ss.formula.udf.UDFFinder;	53	import org.apache.poi.ss.formula.udf.UDFFinder;
58	import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;	54	import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
		55	import org.apache.poi.ss.util.CellRangeAddress;
59	import org.apache.poi.ss.util.WorkbookUtil;	56	import org.apache.poi.ss.util.WorkbookUtil;
60	import org.apache.poi.util.POILogFactory;	57	import org.apache.poi.util.POILogFactory;
61	import org.apache.poi.util.POILogger;	58	import org.apache.poi.util.POILogger;
Lines 75-82 Link Here
75	*/	72	*/
76	public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.usermodel.Workbook {	73	public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.usermodel.Workbook {
77	private static final Pattern COMMA_PATTERN = Pattern.compile(",");	74	private static final Pattern COMMA_PATTERN = Pattern.compile(",");
78	private static final int MAX_ROW = 0xFFFF;
79	private static final int MAX_COLUMN = (short)0x00FF;
80		75
81	/**	76	/**
82	* The maximum number of cell styles in a .xls workbook.	77	* The maximum number of cell styles in a .xls workbook.
Lines 957-1036 Link Here
957	* @param endColumn 0 based end of repeating columns.	952	* @param endColumn 0 based end of repeating columns.
958	* @param startRow 0 based start of repeating rows.	953	* @param startRow 0 based start of repeating rows.
959	* @param endRow 0 based end of repeating rows.	954	* @param endRow 0 based end of repeating rows.
		955	*
		956	* @deprecated use {@link HSSFSheet#setRepeatingRows(CellRangeAddress)}
		957	* or {@link HSSFSheet#setRepeatingColumns(CellRangeAddress)}
960	*/	958	*/
961	public void setRepeatingRowsAndColumns(int sheetIndex,	959	public void setRepeatingRowsAndColumns(int sheetIndex,
962	int startColumn, int endColumn,	960	int startColumn, int endColumn,
963	int startRow, int endRow)	961	int startRow, int endRow) {
964	{	962	HSSFSheet sheet = getSheetAt(sheetIndex);
965	// Check arguments
966	if (startColumn == -1 && endColumn != -1) throw new IllegalArgumentException("Invalid column range specification");
967	if (startRow == -1 && endRow != -1) throw new IllegalArgumentException("Invalid row range specification");
968	if (startColumn < -1 \|\| startColumn >= MAX_COLUMN) throw new IllegalArgumentException("Invalid column range specification");
969	if (endColumn < -1 \|\| endColumn >= MAX_COLUMN) throw new IllegalArgumentException("Invalid column range specification");
970	if (startRow < -1 \|\| startRow > MAX_ROW) throw new IllegalArgumentException("Invalid row range specification");
971	if (endRow < -1 \|\| endRow > MAX_ROW) throw new IllegalArgumentException("Invalid row range specification");
972	if (startColumn > endColumn) throw new IllegalArgumentException("Invalid column range specification");
973	if (startRow > endRow) throw new IllegalArgumentException("Invalid row range specification");
974		963
975	HSSFSheet sheet = getSheetAt(sheetIndex);	964	CellRangeAddress rows = null;
976	short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);	965	CellRangeAddress cols = null;
977		966
978	boolean settingRowAndColumn =	967	if (startRow != -1) {
979	startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1;	968	rows = new CellRangeAddress(startRow, endRow, -1, -1);
980	boolean removingRange =	969	}
981	startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1;	970	if (startColumn != -1) {
		971	cols = new CellRangeAddress(-1, -1, startColumn, endColumn);
		972	}
982		973
983	int rowColHeaderNameIndex = findExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_PRINT_TITLE);	974	sheet.setRepeatingRows(rows);
984	if (removingRange) {	975	sheet.setRepeatingColumns(cols);
985	if (rowColHeaderNameIndex >= 0) {
986	workbook.removeName(rowColHeaderNameIndex);
987	}
988	return;
989	}
990	boolean isNewRecord;
991	NameRecord nameRecord;
992	if (rowColHeaderNameIndex < 0) {
993	//does a lot of the house keeping for builtin records, like setting lengths to zero etc
994	nameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex+1);
995	isNewRecord = true;
996	} else {
997	nameRecord = workbook.getNameRecord(rowColHeaderNameIndex);
998	isNewRecord = false;
999	}
1000
1001	List temp = new ArrayList();
1002
1003	if (settingRowAndColumn) {
1004	final int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE
1005	temp.add(new MemFuncPtg(exprsSize));
1006	}
1007	if (startColumn >= 0) {
1008	Area3DPtg colArea = new Area3DPtg(0, MAX_ROW, startColumn, endColumn,
1009	false, false, false, false, externSheetIndex);
1010	temp.add(colArea);
1011	}
1012	if (startRow >= 0) {
1013	Area3DPtg rowArea = new Area3DPtg(startRow, endRow, 0, MAX_COLUMN,
1014	false, false, false, false, externSheetIndex);
1015	temp.add(rowArea);
1016	}
1017	if (settingRowAndColumn) {
1018	temp.add(UnionPtg.instance);
1019	}
1020	Ptg[] ptgs = new Ptg[temp.size()];
1021	temp.toArray(ptgs);
1022	nameRecord.setNameDefinition(ptgs);
1023
1024	if (isNewRecord)
1025	{
1026	HSSFName newName = new HSSFName(this, nameRecord, nameRecord.isBuiltInName() ? null : workbook.getNameCommentRecord(nameRecord));
1027	names.add(newName);
1028	}
1029
1030	HSSFPrintSetup printSetup = sheet.getPrintSetup();
1031	printSetup.setValidSettings(false);
1032
1033	sheet.setActive(true);
1034	}	976	}
1035		977
1036		978
Lines 1050-1055 Link Here
1050	return -1;	992	return -1;
1051	}	993	}
1052		994
		995
		996	HSSFName createBuiltInName(byte builtinCode, int sheetIndex) {
		997	NameRecord nameRecord =
		998	workbook.createBuiltInName(builtinCode, sheetIndex + 1);
		999	HSSFName newName = new HSSFName(this, nameRecord, null);
		1000	names.add(newName);
		1001	return newName;
		1002	}
		1003
		1004
		1005	HSSFName getBuiltInName(byte builtinCode, int sheetIndex) {
		1006	int index = findExistingBuiltinNameRecordIdx(sheetIndex, builtinCode);
		1007	if (index < 0) {
		1008	return null;
		1009	} else {
		1010	return names.get(index);
		1011	}
		1012	}
		1013
		1014
1053	/**	1015	/**
1054	* create a new Font and add it to the workbook's font table	1016	* create a new Font and add it to the workbook's font table
1055	* @return new font object	1017	* @return new font object
Lines 1477-1482 Link Here
1477	}	1439	}
1478		1440
1479		1441
		1442	/**
		1443	* As {@link #getNameIndex(String)} is not necessarily unique
		1444	* (name + sheet index is unique), this method is more accurate.
		1445	*
		1446	* @param name the name whose index in the list of names of this workbook
		1447	* should be looked up.
		1448	* @return an index value >= 0 if the name was found; -1, if the name was
		1449	* not found
		1450	*/
		1451	int getNameIndex(HSSFName name) {
		1452	for (int k = 0; k < names.size(); k++) {
		1453	if (name == names.get(k)) {
		1454	return k;
		1455	}
		1456	}
		1457	return -1;
		1458	}
		1459
		1460
1480	public void removeName(int index){	1461	public void removeName(int index){
1481	names.remove(index);	1462	names.remove(index);
1482	workbook.removeName(index);	1463	workbook.removeName(index);
Lines 1497-1506 Link Here
1497		1478
1498	public void removeName(String name) {	1479	public void removeName(String name) {
1499	int index = getNameIndex(name);	1480	int index = getNameIndex(name);
1500
1501	removeName(index);	1481	removeName(index);
1502	}	1482	}
1503		1483
		1484
		1485	/**
		1486	* As {@link #removeName(String)} is not necessarily unique
		1487	* (name + sheet index is unique), this method is more accurate.
		1488	*
		1489	* @param name the name to remove.
		1490	*/
		1491	void removeName(HSSFName name) {
		1492	int index = getNameIndex(name);
		1493	removeName(index);
		1494	}
		1495
1504	public HSSFPalette getCustomPalette()	1496	public HSSFPalette getCustomPalette()
1505	{	1497	{
1506	return new HSSFPalette(workbook.getCustomPalette());	1498	return new HSSFPalette(workbook.getCustomPalette());

Lines 32-39 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (-1 / +97 lines)
32	import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;	32	import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
33	import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;	33	import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
34	import org.apache.poi.ss.formula.FormulaShifter;	34	import org.apache.poi.ss.formula.FormulaShifter;
		35	import org.apache.poi.ss.formula.ptg.MemFuncPtg;
35	import org.apache.poi.ss.formula.ptg.Ptg;	36	import org.apache.poi.ss.formula.ptg.Ptg;
36	import org.apache.poi.ss.formula.ptg.Area3DPtg;	37	import org.apache.poi.ss.formula.ptg.Area3DPtg;
		38	import org.apache.poi.ss.formula.ptg.UnionPtg;
37	import org.apache.poi.hssf.util.PaneInformation;	39	import org.apache.poi.hssf.util.PaneInformation;
38	import org.apache.poi.ss.SpreadsheetVersion;	40	import org.apache.poi.ss.SpreadsheetVersion;
39	import org.apache.poi.ss.formula.FormulaType;	41	import org.apache.poi.ss.formula.FormulaType;
Lines 2004-2019 Link Here
2004	}	2006	}
2005		2007
2006		2008
		2009	@Override
2007	public CellRangeAddress getRepeatingRows() {	2010	public CellRangeAddress getRepeatingRows() {
2008	return getRepeatingRowsOrColums(true);	2011	return getRepeatingRowsOrColums(true);
2009	}	2012	}
2010		2013
2011		2014
		2015	@Override
2012	public CellRangeAddress getRepeatingColumns() {	2016	public CellRangeAddress getRepeatingColumns() {
2013	return getRepeatingRowsOrColums(false);	2017	return getRepeatingRowsOrColums(false);
2014	}	2018	}
2015		2019
2016		2020
		2021	@Override
		2022	public void setRepeatingRows(CellRangeAddress rowRangeRef) {
		2023	CellRangeAddress columnRangeRef = getRepeatingColumns();
		2024	setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef);
		2025	}
		2026
		2027
		2028	@Override
		2029	public void setRepeatingColumns(CellRangeAddress columnRangeRef) {
		2030	CellRangeAddress rowRangeRef = getRepeatingRows();
		2031	setRepeatingRowsAndColumns(rowRangeRef, columnRangeRef);
		2032	}
		2033
		2034
		2035	private void setRepeatingRowsAndColumns(
		2036	CellRangeAddress rowDef, CellRangeAddress colDef) {
		2037	int sheetIndex = _workbook.getSheetIndex(this);
		2038	int maxRowIndex = SpreadsheetVersion.EXCEL97.getLastRowIndex();
		2039	int maxColIndex = SpreadsheetVersion.EXCEL97.getLastColumnIndex();
		2040
		2041	int col1 = -1;
		2042	int col2 = -1;
		2043	int row1 = -1;
		2044	int row2 = -1;
		2045
		2046	if (rowDef != null) {
		2047	row1 = rowDef.getFirstRow();
		2048	row2 = rowDef.getLastRow();
		2049	if ((row1 == -1 && row2 != -1) \|\| (row1 > row2)
		2050	\|\| (row1 < 0 \|\| row1 > maxRowIndex)
		2051	\|\| (row2 < 0 \|\| row2 > maxRowIndex)) {
		2052	throw new IllegalArgumentException("Invalid row range specification");
		2053	}
		2054	}
		2055	if (colDef != null) {
		2056	col1 = colDef.getFirstColumn();
		2057	col2 = colDef.getLastColumn();
		2058	if ((col1 == -1 && col2 != -1) \|\| (col1 > col2)
		2059	\|\| (col1 < 0 \|\| col1 > maxColIndex)
		2060	\|\| (col2 < 0 \|\| col2 > maxColIndex)) {
		2061	throw new IllegalArgumentException("Invalid column range specification");
		2062	}
		2063	}
		2064
		2065	short externSheetIndex =
		2066	_workbook.getWorkbook().checkExternSheet(sheetIndex);
		2067
		2068	boolean setBoth = rowDef != null && colDef != null;
		2069	boolean removeAll = rowDef == null && colDef == null;
		2070
		2071	HSSFName name = _workbook.getBuiltInName(
		2072	NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
		2073	if (removeAll) {
		2074	if (name != null) {
		2075	_workbook.removeName(name);
		2076	}
		2077	return;
		2078	}
		2079	if (name == null) {
		2080	name = _workbook.createBuiltInName(
		2081	NameRecord.BUILTIN_PRINT_TITLE, sheetIndex);
		2082	}
		2083
		2084	List<Ptg> ptgList = new ArrayList<Ptg>();
2085	if (setBoth) {
2086	final int exprsSize = 2 * 11 + 1; // 2 * Area3DPtg.SIZE + UnionPtg.SIZE
2087	ptgList.add(new MemFuncPtg(exprsSize));
2088	}
2089	if (colDef != null) {
2090	Area3DPtg colArea = new Area3DPtg(0, maxRowIndex, col1, col2,
2091	false, false, false, false, externSheetIndex);
2092	ptgList.add(colArea);
2093	}
2094	if (rowDef != null) {
2095	Area3DPtg rowArea = new Area3DPtg(row1, row2, 0, maxColIndex,
2096	false, false, false, false, externSheetIndex);
2097	ptgList.add(rowArea);
2098	}
2099	if (setBoth) {
2100	ptgList.add(UnionPtg.instance);
2101	}
2102
2103	Ptg[] ptgs = new Ptg[ptgList.size()];
2104	ptgList.toArray(ptgs);
2105	name.setNameDefinition(ptgs);
2106
2107	HSSFPrintSetup printSetup = getPrintSetup();
2108	printSetup.setValidSettings(false);
2109	setActive(true);
2110	}
2111
2112
2017	private CellRangeAddress getRepeatingRowsOrColums(boolean rows) {	2113	private CellRangeAddress getRepeatingRowsOrColums(boolean rows) {
2018	NameRecord rec = getBuiltinNameRecord(NameRecord.BUILTIN_PRINT_TITLE);	2114	NameRecord rec = getBuiltinNameRecord(NameRecord.BUILTIN_PRINT_TITLE);
2019	if (rec == null) {	2115	if (rec == null) {
Lines 2021-2027 Link Here
2021	}	2117	}
2022		2118
2023	Ptg[] nameDefinition = rec.getNameDefinition();	2119	Ptg[] nameDefinition = rec.getNameDefinition();
2024	if (rec.getNameDefinition() == null) {	2120	if (nameDefinition == null) {
2025	return null;	2121	return null;
2026	}	2122	}
2027		2123

Lines 17-36 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (-8 / +33 lines)
17		17
18	package org.apache.poi.xssf.streaming;	18	package org.apache.poi.xssf.streaming;
19		19
20	import java.io.*;	20	import java.io.IOException;
		21	import java.io.InputStream;
21	import java.util.Iterator;	22	import java.util.Iterator;
		23	import java.util.Map;
22	import java.util.TreeMap;	24	import java.util.TreeMap;
23	import java.util.Map;
24		25
		26	import org.apache.poi.hssf.util.PaneInformation;
25	import org.apache.poi.ss.SpreadsheetVersion;	27	import org.apache.poi.ss.SpreadsheetVersion;
26	import org.apache.poi.ss.usermodel.*;	28	import org.apache.poi.ss.usermodel.AutoFilter;
27		29	import org.apache.poi.ss.usermodel.Cell;
28	import org.apache.poi.ss.util.AreaReference;	30	import org.apache.poi.ss.usermodel.CellRange;
		31	import org.apache.poi.ss.usermodel.CellStyle;
		32	import org.apache.poi.ss.usermodel.Comment;
		33	import org.apache.poi.ss.usermodel.DataValidation;
		34	import org.apache.poi.ss.usermodel.DataValidationHelper;
		35	import org.apache.poi.ss.usermodel.Drawing;
		36	import org.apache.poi.ss.usermodel.Footer;
		37	import org.apache.poi.ss.usermodel.Header;
		38	import org.apache.poi.ss.usermodel.PrintSetup;
		39	import org.apache.poi.ss.usermodel.Row;
		40	import org.apache.poi.ss.usermodel.Sheet;
		41	import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
		42	import org.apache.poi.ss.usermodel.Workbook;
		43	import org.apache.poi.ss.util.CellRangeAddress;
29	import org.apache.poi.ss.util.SheetUtil;	44	import org.apache.poi.ss.util.SheetUtil;
30	import org.apache.poi.xssf.usermodel.XSSFSheet;	45	import org.apache.poi.xssf.usermodel.XSSFSheet;
31
32	import org.apache.poi.hssf.util.PaneInformation;
33	import org.apache.poi.ss.util.CellRangeAddress;
34	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr;	46	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr;
35	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;	47	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
36		48
Lines 1266-1281 Link Here
1266	}	1278	}
1267		1279
1268		1280
		1281	@Override
1269	public CellRangeAddress getRepeatingRows() {	1282	public CellRangeAddress getRepeatingRows() {
1270	return _sh.getRepeatingRows();	1283	return _sh.getRepeatingRows();
1271	}	1284	}
1272		1285
1273		1286
		1287	@Override
1274	public CellRangeAddress getRepeatingColumns() {	1288	public CellRangeAddress getRepeatingColumns() {
1275	return _sh.getRepeatingColumns();	1289	return _sh.getRepeatingColumns();
1276	}	1290	}
1277		1291
		1292	@Override
		1293	public void setRepeatingRows(CellRangeAddress rowRangeRef) {
		1294	_sh.setRepeatingRows(rowRangeRef);
		1295	}
1278		1296
		1297	@Override
		1298	public void setRepeatingColumns(CellRangeAddress columnRangeRef) {
		1299	_sh.setRepeatingColumns(columnRangeRef);
		1300	}
		1301
		1302
		1303
1279	//end of interface implementation	1304	//end of interface implementation
1280	/**	1305	/**
1281	* Specifies how many rows can be accessed at most via getRow().	1306	* Specifies how many rows can be accessed at most via getRow().

Lines 20-25 Link Here

(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java (-10 / +15 lines)
20	import org.apache.poi.xssf.XSSFTestDataSamples;	20	import org.apache.poi.xssf.XSSFTestDataSamples;
21	import org.apache.poi.xssf.XSSFITestDataProvider;	21	import org.apache.poi.xssf.XSSFITestDataProvider;
22	import org.apache.poi.ss.usermodel.BaseTestNamedRange;	22	import org.apache.poi.ss.usermodel.BaseTestNamedRange;
		23	import org.apache.poi.ss.util.CellRangeAddress;
23		24
24	/**	25	/**
25	* @author Yegor Kozlov	26	* @author Yegor Kozlov
Lines 35-47 Link Here
35	// First test that setting RR&C for same sheet more than once only creates a	36	// First test that setting RR&C for same sheet more than once only creates a
36	// single Print_Titles built-in record	37	// single Print_Titles built-in record
37	XSSFWorkbook wb = new XSSFWorkbook();	38	XSSFWorkbook wb = new XSSFWorkbook();
38	wb.createSheet("First Sheet");	39	XSSFSheet sheet1 = wb.createSheet("First Sheet");
39		40
40	wb.setRepeatingRowsAndColumns(0, -1, -1, -1, -1);	41	sheet1.setRepeatingRows(null);
		42	sheet1.setRepeatingColumns(null);
41		43
42	// set repeating rows and columns twice for the first sheet	44	// set repeating rows and columns twice for the first sheet
43	for (int i = 0; i < 2; i++) {	45	for (int i = 0; i < 2; i++) {
44	wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3);	46	sheet1.setRepeatingRows(CellRangeAddress.valueOf("1:4"));
		47	sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:A"));
45	//sheet.createFreezePane(0, 3);	48	//sheet.createFreezePane(0, 3);
46	}	49	}
47	assertEquals(1, wb.getNumberOfNames());	50	assertEquals(1, wb.getNumberOfNames());
Lines 51-68 Link Here
51	assertEquals("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.getRefersToFormula());	54	assertEquals("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.getRefersToFormula());
52		55
53	//remove the columns part	56	//remove the columns part
54	wb.setRepeatingRowsAndColumns(0, -1, -1, 0, 3);	57	sheet1.setRepeatingColumns(null);
55	assertEquals("'First Sheet'!$1:$4", nr1.getRefersToFormula());	58	assertEquals("'First Sheet'!$1:$4", nr1.getRefersToFormula());
56		59
57	//revert	60	//revert
58	wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3);	61	sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:A"));
59		62
60	//remove the rows part	63	//remove the rows part
61	wb.setRepeatingRowsAndColumns(0, 0, 0, -1, -1);	64	sheet1.setRepeatingRows(null);
62	assertEquals("'First Sheet'!$A:$A", nr1.getRefersToFormula());	65	assertEquals("'First Sheet'!$A:$A", nr1.getRefersToFormula());
63		66
64	//revert	67	//revert
65	wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3);	68	sheet1.setRepeatingRows(CellRangeAddress.valueOf("1:4"));
66		69
67	// Save and re-open	70	// Save and re-open
68	XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb);	71	XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb);
Lines 75-82 Link Here
75		78
76	// check that setting RR&C on a second sheet causes a new Print_Titles built-in	79	// check that setting RR&C on a second sheet causes a new Print_Titles built-in
77	// name to be created	80	// name to be created
78	nwb.createSheet("SecondSheet");	81	XSSFSheet sheet2 = nwb.createSheet("SecondSheet");
79	nwb.setRepeatingRowsAndColumns(1, 1, 2, 0, 0);	82	sheet2.setRepeatingRows(CellRangeAddress.valueOf("1:1"));
		83	sheet2.setRepeatingColumns(CellRangeAddress.valueOf("B:C"));
80		84
81	assertEquals(2, nwb.getNumberOfNames());	85	assertEquals(2, nwb.getNumberOfNames());
82	XSSFName nr2 = nwb.getNameAt(1);	86	XSSFName nr2 = nwb.getNameAt(1);
Lines 84-89 Link Here
84	assertEquals(XSSFName.BUILTIN_PRINT_TITLE, nr2.getNameName());	88	assertEquals(XSSFName.BUILTIN_PRINT_TITLE, nr2.getNameName());
85	assertEquals("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.getRefersToFormula());	89	assertEquals("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.getRefersToFormula());
86		90
87	nwb.setRepeatingRowsAndColumns(1, -1, -1, -1, -1);	91	sheet2.setRepeatingRows(null);
		92	sheet2.setRepeatingColumns(null);
88	}	93	}
89	}	94	}

Lines 200-205 Link Here

(-)src/java/org/apache/poi/hssf/usermodel/HSSFName.java (+12 lines)
200	return HSSFFormulaParser.toFormulaString(_book, ptgs);	200	return HSSFFormulaParser.toFormulaString(_book, ptgs);
201	}	201	}
202		202
		203
		204	/**
		205	* Sets the NameParsedFormula structure that specifies the formula for the
		206	* defined name.
		207	*
		208	* @param ptgs the sequence of {@link Ptg}s for the formula.
		209	*/
		210	void setNameDefinition(Ptg[] ptgs) {
		211	_definedNameRec.setNameDefinition(ptgs);
		212	}
		213
		214
203	public boolean isDeleted(){	215	public boolean isDeleted(){
204	Ptg[] ptgs = _definedNameRec.getNameDefinition();	216	Ptg[] ptgs = _definedNameRec.getNameDefinition();
205	return Ptg.doesFormulaReferToDeletedCell(ptgs);	217	return Ptg.doesFormulaReferToDeletedCell(ptgs);

Lines 965-974 Link Here

(-)src/java/org/apache/poi/ss/usermodel/Sheet.java (-2 / +49 lines)
965	* If the Sheet does not have any repeating columns defined, null is	965	* If the Sheet does not have any repeating columns defined, null is
966	* returned.	966	* returned.
967	*	967	*
968	* @return an {@link CellRangeAddress} containing the repeating columns for the	968	* @return an {@link CellRangeAddress} containing the repeating columns for
969	* Sheet, or null.	969	* the Sheet, or null.
970	*/	970	*/
971	CellRangeAddress getRepeatingColumns();	971	CellRangeAddress getRepeatingColumns();
972		972
973		973
		974	/**
		975	* Sets the repeating rows used when printing the sheet, as found in
		976	* File->PageSetup->Sheet.
		977	* <p/>
		978	* Repeating rows cover a range of contiguous rows, e.g.:
		979	* <pre>
		980	* Sheet1!$1:$1
		981	* Sheet2!$5:$8</pre>
		982	* The parameter {@link CellRangeAddress} should specify a column part
		983	* which spans all columns, and a row part which specifies the contiguous
		984	* range of repeating rows, e.g.:
		985	* <pre>
		986	* sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3"));</pre>
		987	* A null parameter value indicates that repeating rows should be removed
		988	* from the Sheet:
		989	* <pre>
		990	* sheet.setRepeatingRows(null);</pre>
		991	*
		992	* @param rowRangeRef a {@link CellRangeAddress} containing the repeating
		993	* rows for the Sheet, or null.
		994	*/
		995	void setRepeatingRows(CellRangeAddress rowRangeRef);
		996
		997
		998	/**
		999	* Sets the repeating columns used when printing the sheet, as found in
		1000	* File->PageSetup->Sheet.
		1001	* <p/>
		1002	* Repeating columns cover a range of contiguous columns, e.g.:
		1003	* <pre>
		1004	* Sheet1!$A:$A
		1005	* Sheet2!$C:$F</pre>
		1006	* The parameter {@link CellRangeAddress} should specify a row part
		1007	* which spans all rows, and a column part which specifies the contiguous
		1008	* range of repeating columns, e.g.:
		1009	* <pre>
		1010	* sheet.setRepeatingColumns(CellRangeAddress.valueOf("B:C"));</pre>
		1011	* A null parameter value indicates that repeating columns should be removed
		1012	* from the Sheet:
		1013	* <pre>
		1014	* sheet.setRepeatingColumns(null);</pre>
		1015	*
		1016	* @param columnRangeRef a {@link CellRangeAddress} containing the repeating
		1017	* columns for the Sheet, or null.
		1018	*/
		1019	void setRepeatingColumns(CellRangeAddress columnRangeRef);
		1020
974	}	1021	}

Lines 360-398 Link Here

(-)src/testcases/org/apache/poi/ss/usermodel/BaseTestWorkbook.java (-23 / +10 lines)
360	}	360	}
361		361
362		362
363	public void testGetRepeatingRowsAnsColumns(){	363	/**
364	Workbook wb = _testDataProvider.openSampleWorkbook(	364	* Test is kept to ensure stub for deprecated business method passes test.
365	"RepeatingRowsCols."	365	*
366	+ _testDataProvider.getStandardFileNameExtension());	366	* @Deprecated remove this test when
367		367	* {@link Workbook#setRepeatingRowsAndColumns(int, int, int, int, int)}
368	Sheet sheet0 = wb.getSheetAt(0);	368	* is removed
369	assertNull(sheet0.getRepeatingRows());	369	*/
370	assertNull(sheet0.getRepeatingColumns());	370	@Deprecated
371
372	Sheet sheet1 = wb.getSheetAt(1);
373	assertEquals("1:1", sheet1.getRepeatingRows().formatAsString());
374	assertNull(sheet1.getRepeatingColumns());
375
376	Sheet sheet2 = wb.getSheetAt(2);
377	assertNull(sheet2.getRepeatingRows());
378	assertEquals("A:A", sheet2.getRepeatingColumns().formatAsString());
379
380	Sheet sheet3 = wb.getSheetAt(3);
381	assertEquals("2:3", sheet3.getRepeatingRows().formatAsString());
382	assertEquals("A:B", sheet3.getRepeatingColumns().formatAsString());
383	}
384
385
386	public void testSetRepeatingRowsAnsColumns(){	371	public void testSetRepeatingRowsAnsColumns(){
387	Workbook wb = _testDataProvider.createWorkbook();	372	Workbook wb = _testDataProvider.createWorkbook();
388	Sheet sheet1 = wb.createSheet();	373	Sheet sheet1 = wb.createSheet();
389	wb.setRepeatingRowsAndColumns(wb.getSheetIndex(sheet1), 0, 0, 0, 3);	374	wb.setRepeatingRowsAndColumns(wb.getSheetIndex(sheet1), 0, 0, 0, 3);
390	assertEquals("1:4", sheet1.getRepeatingRows().formatAsString());	375	assertEquals("1:4", sheet1.getRepeatingRows().formatAsString());
		376	assertEquals("A:A", sheet1.getRepeatingColumns().formatAsString());
391		377
392	//must handle sheets with quotas, see Bugzilla #47294	378	//must handle sheets with quotas, see Bugzilla #47294
393	Sheet sheet2 = wb.createSheet("My' Sheet");	379	Sheet sheet2 = wb.createSheet("My' Sheet");
394	wb.setRepeatingRowsAndColumns(wb.getSheetIndex(sheet2), 0, 0, 0, 3);	380	wb.setRepeatingRowsAndColumns(wb.getSheetIndex(sheet2), 0, 0, 0, 3);
395	assertEquals("1:4", sheet2.getRepeatingRows().formatAsString());	381	assertEquals("1:4", sheet2.getRepeatingRows().formatAsString());
		382	assertEquals("A:A", sheet1.getRepeatingColumns().formatAsString());
396	}	383	}
397		384
398	/**	385	/**

Lines 712-715 Link Here

(-)src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java (+74 lines)
712	assertNull(sheet.getPaneInformation());	712	assertNull(sheet.getPaneInformation());
713	}	713	}
714		714
		715
		716	public void testGetRepeatingRowsAndColumns() {
		717	Workbook wb = _testDataProvider.openSampleWorkbook(
		718	"RepeatingRowsCols."
		719	+ _testDataProvider.getStandardFileNameExtension());
		720
		721	checkRepeatingRowsAndColumns(wb.getSheetAt(0), null, null);
		722	checkRepeatingRowsAndColumns(wb.getSheetAt(1), "1:1", null);
		723	checkRepeatingRowsAndColumns(wb.getSheetAt(2), null, "A:A");
		724	checkRepeatingRowsAndColumns(wb.getSheetAt(3), "2:3", "A:B");
		725	}
		726
		727
		728	public void testSetRepeatingRowsAndColumnsBug47294(){
		729	Workbook wb = _testDataProvider.createWorkbook();
		730	Sheet sheet1 = wb.createSheet();
		731	sheet1.setRepeatingRows(CellRangeAddress.valueOf("1:4"));
		732	assertEquals("1:4", sheet1.getRepeatingRows().formatAsString());
		733
		734	//must handle sheets with quotas, see Bugzilla #47294
		735	Sheet sheet2 = wb.createSheet("My' Sheet");
		736	sheet2.setRepeatingRows(CellRangeAddress.valueOf("1:4"));
		737	assertEquals("1:4", sheet2.getRepeatingRows().formatAsString());
		738	}
		739
		740	public void testSetRepeatingRowsAndColumns() {
		741	Workbook wb = _testDataProvider.createWorkbook();
		742	Sheet sheet1 = wb.createSheet("Sheet1");
		743	Sheet sheet2 = wb.createSheet("Sheet2");
		744	Sheet sheet3 = wb.createSheet("Sheet3");
		745
		746	checkRepeatingRowsAndColumns(sheet1, null, null);
		747
		748	sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5"));
		749	sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C"));
		750	sheet3.setRepeatingRows(CellRangeAddress.valueOf("1:4"));
		751	sheet3.setRepeatingColumns(CellRangeAddress.valueOf("A:A"));
		752
		753	checkRepeatingRowsAndColumns(sheet1, "4:5", null);
		754	checkRepeatingRowsAndColumns(sheet2, null, "A:C");
		755	checkRepeatingRowsAndColumns(sheet3, "1:4", "A:A");
		756
		757	// write out, read back, and test refrain...
		758	wb = _testDataProvider.writeOutAndReadBack(wb);
		759	sheet1 = wb.getSheetAt(0);
		760	sheet2 = wb.getSheetAt(1);
		761	sheet3 = wb.getSheetAt(2);
		762
		763	checkRepeatingRowsAndColumns(sheet1, "4:5", null);
		764	checkRepeatingRowsAndColumns(sheet2, null, "A:C");
		765	checkRepeatingRowsAndColumns(sheet3, "1:4", "A:A");
		766
		767	// check removing repeating rows and columns
		768	sheet3.setRepeatingRows(null);
		769	checkRepeatingRowsAndColumns(sheet3, null, "A:A");
		770
		771	sheet3.setRepeatingColumns(null);
		772	checkRepeatingRowsAndColumns(sheet3, null, null);
		773	}
		774
		775	private void checkRepeatingRowsAndColumns(
		776	Sheet s, String expectedRows, String expectedCols) {
		777	if (expectedRows == null) {
		778	assertNull(s.getRepeatingRows());
779	} else {
780	assertEquals(expectedRows, s.getRepeatingRows().formatAsString());
781	}
782	if (expectedCols == null) {
783	assertNull(s.getRepeatingColumns());
784	} else {
785	assertEquals(expectedCols, s.getRepeatingColumns().formatAsString());
786	}
787	}
788
715	}	789	}

Lines 56-72 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFWorkbook.java (-12 / +2 lines)
56	return wb.getWorkbook();	56	return wb.getWorkbook();
57	}	57	}
58		58
59	public void testSetRepeatingRowsAndColumns() {
60	// Test bug 29747
61	HSSFWorkbook b = new HSSFWorkbook( );
62	b.createSheet();
63	b.createSheet();
64	b.createSheet();
65	b.setRepeatingRowsAndColumns( 2, 0,1,-1,-1 );
66	NameRecord nameRecord = b.getWorkbook().getNameRecord( 0 );
67	assertEquals(3, nameRecord.getSheetNumber());
68	}
69
70	public void testWindowOneDefaults() {	59	public void testWindowOneDefaults() {
71	HSSFWorkbook b = new HSSFWorkbook( );	60	HSSFWorkbook b = new HSSFWorkbook( );
72	try {	61	try {
Lines 501-507 Link Here
501	assertEquals("Sheet2!$A$1:$IV$1", HSSFFormulaParser.toFormulaString(wb, nr.getNameDefinition())); // 1:1	490	assertEquals("Sheet2!$A$1:$IV$1", HSSFFormulaParser.toFormulaString(wb, nr.getNameDefinition())); // 1:1
502		491
503	try {	492	try {
504	wb.setRepeatingRowsAndColumns(3, 4, 5, 8, 11);	493	wb.getSheetAt(3).setRepeatingRows(CellRangeAddress.valueOf("9:12"));
		494	wb.getSheetAt(3).setRepeatingColumns(CellRangeAddress.valueOf("E:F"));
505	} catch (RuntimeException e) {	495	} catch (RuntimeException e) {
506	if (e.getMessage().equals("Builtin (7) already exists for sheet (4)")) {	496	if (e.getMessage().equals("Builtin (7) already exists for sheet (4)")) {
507	// there was a problem in the code which locates the existing print titles name record	497	// there was a problem in the code which locates the existing print titles name record

Lines 57-62 Link Here

(-)src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (+16 lines)
57	super(HSSFITestDataProvider.instance);	57	super(HSSFITestDataProvider.instance);
58	}	58	}
59		59
		60
		61	/**
		62	* Test for Bugzilla #29747.
		63	* Moved from TestHSSFWorkbook#testSetRepeatingRowsAndColumns().
		64	*/
		65	public void testSetRepeatingRowsAndColumnsBug29747() {
		66	HSSFWorkbook wb = new HSSFWorkbook();
		67	wb.createSheet();
		68	wb.createSheet();
		69	HSSFSheet sheet2 = wb.createSheet();
		70	sheet2.setRepeatingRows(CellRangeAddress.valueOf("1:2"));
		71	NameRecord nameRecord = wb.getWorkbook().getNameRecord(0);
		72	assertEquals(3, nameRecord.getSheetNumber());
		73	}
		74
		75
60	public void testTestGetSetMargin() {	76	public void testTestGetSetMargin() {
61	baseTestGetSetMargin(new double[]{0.75, 0.75, 1.0, 1.0, 0.3, 0.3});	77	baseTestGetSetMargin(new double[]{0.75, 0.75, 1.0, 1.0, 0.3, 0.3});
62	}	78	}