poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r705701 - in /poi/branches/ooxml/src: examples/src/org/apache/poi/xssf/usermodel/examples/ ooxml/java/org/apache/poi/xssf/usermodel/ ooxml/testcases/org/apache/poi/xssf/usermodel/
Date Fri, 17 Oct 2008 18:43:37 GMT
Author: yegor
Date: Fri Oct 17 11:43:37 2008
New Revision: 705701

URL: http://svn.apache.org/viewvc?rev=705701&view=rev
Log:
applied patch #46003 by Gisella Bronzetti

Added:
    poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/SetPrintArea.java
  (with props)
Modified:
    poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
    poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
    poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java

Added: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/SetPrintArea.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/SetPrintArea.java?rev=705701&view=auto
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/SetPrintArea.java
(added)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/SetPrintArea.java
Fri Oct 17 11:43:37 2008
@@ -0,0 +1,42 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+package org.apache.poi.xssf.usermodel.examples;
+
+import java.io.FileOutputStream;
+
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+
+public class SetPrintArea {
+
+
+    public static void main(String[]args) throws Exception {
+        Workbook wb = new XSSFWorkbook();
+        Sheet sheet = wb.createSheet("Sheet1");
+        //wb.setPrintArea(0, "$A$1:$C$2");
+        //sets the print area for the first sheet
+        //Alternatively:
+        wb.setPrintArea(0, 1, 2, 0, 3); //is equivalent to using the name reference (See
the JavaDocs for more details)
+
+        // Create various cells and rows for spreadsheet.
+
+        FileOutputStream fileOut = new FileOutputStream("printArea.xlsx");
+        wb.write(fileOut);
+        fileOut.close();
+    }
+}

Propchange: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/SetPrintArea.java
------------------------------------------------------------------------------
    svn:executable = *

Modified: poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java?rev=705701&r1=705700&r2=705701&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java (original)
+++ poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java Fri Oct
17 11:43:37 2008
@@ -23,66 +23,165 @@
  * XSSF Implementation of a Named Range
  */
 public class XSSFName implements Name {
-	private XSSFWorkbook workbook;
-	private CTDefinedName ctName;
-	
-	protected XSSFName(XSSFWorkbook workbook) {
-		this.workbook = workbook;
-		this.ctName = CTDefinedName.Factory.newInstance();
-	}
-	protected XSSFName(CTDefinedName name, XSSFWorkbook workbook) {
-		this.workbook = workbook;
-		this.ctName = name;
-	}
-	
-	public boolean isFunctionName() {
-		// TODO Figure out how HSSF does this, and do the same!
-		return ctName.getFunction(); // maybe this works - verify
-	}
-	/**
-	 * Returns the underlying named range object
-	 */
-	protected CTDefinedName getCTName() {
-		return ctName;
-	}
-	
-	public String getNameName() {
-		return ctName.getName();
-	}
-	public void setNameName(String nameName) {
-		ctName.setName(nameName);
-	}
+    /**
+     * The following built-in names are defined in this SpreadsheetML
+     * specification:
+     * Built-in names reserved by SpreadsheetML begin with "_xlnm.". End users shall not
use
+     * this string for custom names in the user interface.
+     */
 
-	public String getReference() {
-		return ctName.getStringValue();
-	}
-	public void setReference(String ref) {
-		ctName.setStringValue(ref);
-	}
-	
-	public String getSheetName() {
-		if(ctName.isSetLocalSheetId()) {
-			// Given as explicit sheet id
-			long sheetId = ctName.getLocalSheetId();
-			if(sheetId >= 0) {
-				return workbook.getSheetName((int)sheetId);
-			}
-		} else {
-			// Is it embeded in the reference itself?
-			int excl = getReference().indexOf('!');
-			if(excl > -1) {
-				return getReference().substring(0, excl);
-			}
-		}
-		
-		// Not given at all
-		return null;
-	}
+    /**
+     * this defined name specifies the workbook's print area
+     */
+    public final static String  BUILTIN_PRINT_AREA            = "_xlnm.Print_Area";
 
-	public String getComment() {
-		return ctName.getComment();
-	}
-	public void setComment(String comment) {
-		ctName.setComment(comment);
+    /**
+     * this defined name specifies the row(s) or column(s) to repeat
+     *	at the top of each printed page.
+     */
+    public final static String  BUILTIN_PRINT_TITLE           = "_xlnm.Print_Titles";
+
+    //Filter & Advanced Filter
+
+    /**
+     * this defined name refers to a range containing the criteria values
+     * to be used in applying an advanced filter to a range of data
+     */
+    public final static String  BUILTIN_CRITERIA              = "_xlnm.Criteria:";
+
+
+    /**
+     * this defined name refers to the range containing the filtered
+     * output values resulting from applying an advanced filter criteria to a source
+     * range
+     */
+    public final static String  BUILTIN_EXTRACT              = "_xlnm.Extract:";
+
+    /**
+     * can be one of the following
+     * a. this defined name refers to a range to which an advanced filter has been
+     * applied. This represents the source data range, unfiltered.
+     * b. This defined name refers to a range to which an AutoFilter has been
+     * applied
+     */
+    public final static String  BUILTIN_FILTER_DB             = "_xlnm._FilterDatabase:";
+
+
+    //Miscellaneous
+
+    /**
+     * the defined name refers to a consolidation area
+     */
+    public final static String  BUILTIN_CONSOLIDATE_AREA      = "_xlnm.Consolidate_Area";
+
+    /**
+     * the range specified in the defined name is from a database data source
+     */
+    public final static String  BUILTIN_DATABASE              = "_xlnm.Database";
+
+    /**
+     * the defined name refers to a sheet title.
+     */
+    public final static String  BUILTIN_SHEET_TITLE           = "_xlnm.Sheet_Title";
+
+    private XSSFWorkbook workbook;
+    private CTDefinedName ctName;
+
+    protected XSSFName(XSSFWorkbook workbook) {
+	this.workbook = workbook;
+	this.ctName = CTDefinedName.Factory.newInstance();
+    }
+    protected XSSFName(CTDefinedName name, XSSFWorkbook workbook) {
+	this.workbook = workbook;
+	this.ctName = name;
+    }
+
+    public boolean isFunctionName() {
+	// TODO Figure out how HSSF does this, and do the same!
+	return ctName.getFunction(); // maybe this works - verify
+    }
+    /**
+     * Returns the underlying named range object
+     */
+    protected CTDefinedName getCTName() {
+	return ctName;
+    }
+
+    public String getNameName() {
+	return ctName.getName();
+    }
+    public void setNameName(String nameName) {
+	ctName.setName(nameName);
+    }
+
+    public String getReference() {
+	return ctName.getStringValue();
+    }
+    public void setReference(String ref) {
+	ctName.setStringValue(ref);
+    }
+
+    public void setLocalSheetId(int sheetId) {
+	ctName.setLocalSheetId(sheetId);
+    }
+
+    public int getLocalSheetId() {
+	return (int)ctName.getLocalSheetId();
+    }
+
+
+    public void setFunction(boolean value) {
+	ctName.setFunction(value);
+    }
+
+    public boolean getFunction() {
+	return ctName.getFunction();
+    }
+
+    public void setFunctionGroupId(int functionGroupId) {
+	ctName.setFunctionGroupId(functionGroupId);
+    }
+
+    public int getFunctionGroupId() {
+	return (int)ctName.getFunctionGroupId();
+    }
+
+    public String getSheetName() {
+	if(ctName.isSetLocalSheetId()) {
+	    // Given as explicit sheet id
+	    long sheetId = ctName.getLocalSheetId();
+	    if(sheetId >= 0) {
+		return workbook.getSheetName((int)sheetId);
+	    }
+	} else {
+	    // Is it embeded in the reference itself?
+	    int excl = getReference().indexOf('!');
+	    if(excl > -1) {
+		return getReference().substring(0, excl);
+	    }
 	}
+
+	// Not given at all
+	return null;
+    }
+
+    public String getComment() {
+	return ctName.getComment();
+    }
+    public void setComment(String comment) {
+	ctName.setComment(comment);
+    }
+
+
+    public int hashCode(){
+	return ctName.toString().hashCode();
+    }
+
+    public boolean equals(Object o){
+	if(!(o instanceof XSSFName)) return false;
+	XSSFName cf = (XSSFName)o;
+	return ctName.toString().equals(cf.getCTName().toString());
+    }
+
+
 }

Modified: poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=705701&r1=705700&r2=705701&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (original)
+++ poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java Fri
Oct 17 11:43:37 2008
@@ -28,6 +28,7 @@
 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.CellReference;
 import org.apache.poi.util.POILogFactory;
 import org.apache.poi.util.POILogger;
 import org.apache.poi.util.PackageHelper;
@@ -604,9 +605,16 @@
         return this.sheets.size();
     }
 
-    public String getPrintArea(int sheetIndex) {
-        // TODO Auto-generated method stub
-        return null;
+    /**
+     * Retrieves the reference for the printarea of the specified sheet, the sheet name is
appended to the reference even if it was not specified.
+     * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent
with java)
+     * @return String Null if no print area has been defined
+     */
+    public String getPrintArea(int sheetIndex) {	
+        XSSFName name = getSpecificBuiltinRecord(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
+        if (name == null) return null;
+        //adding one here because 0 indicates a global named region; doesnt make sense for
print areas
+        return name.getReference();
     }
 
     /**
@@ -826,21 +834,119 @@
         bookView.setActiveTab(index);
     }
 
+    /**
+     * Sets the printarea for the sheet provided
+     * <p>
+     * i.e. Reference = $A$1:$B$2
+     * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent
with java)
+     * @param reference Valid name Reference for the Print Area
+     */
     public void setPrintArea(int sheetIndex, String reference) {
-        // TODO Auto-generated method stub
-
+        XSSFName name = getSpecificBuiltinRecord(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
+        if (name == null) {
+            name = createBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
+            namedRanges.add(name);
+        }
+        name.setReference(reference);
     }
 
+    /**
+     * For the Convenience of Java Programmers maintaining pointers.
+     * @see #setPrintArea(int, String)
+     * @param sheetIndex Zero-based sheet index (0 = First Sheet)
+     * @param startColumn Column to begin printarea
+     * @param endColumn Column to end the printarea
+     * @param startRow Row to begin the printarea
+     * @param endRow Row to end the printarea
+     */
     public void setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow,
int endRow) {
-        // TODO Auto-generated method stub
+        String reference=getReferencePrintArea(getSheetName(sheetIndex), startColumn, endColumn,
startRow, endRow);
+        setPrintArea(sheetIndex, reference);
+    }
+
+    /**
+     * Sets the repeating rows and columns for a sheet.
+     *   This is function is included in the workbook
+     * because it creates/modifies name records which are stored at the
+     * workbook level.
+     * <p>
+     * To set just repeating columns:
+     * <pre>
+     *  workbook.setRepeatingRowsAndColumns(0,0,1,-1,-1);
+     * </pre>
+     * To set just repeating rows:
+     * <pre>
+     *  workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
+     * </pre>
+     * To remove all repeating rows and columns for a sheet.
+     * <pre>
+     *  workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);
+     * </pre>
+     *
+     * @param sheetIndex    0 based index to sheet.
+     * @param startColumn   0 based start of repeating columns.
+     * @param endColumn     0 based end of repeating columns.
+     * @param startRow      0 based start of repeating rows.
+     * @param endRow        0 based end of repeating rows.
+     */
+    public void setRepeatingRowsAndColumns(int sheetIndex,
+                                           int startColumn, int endColumn,
+                                           int startRow, int endRow) {
+        //TODO
+    }
+
 
+    private 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);
+        CellReference colRef2 = new CellReference(sheetName, endR, endC, true, true);
+
+        String c = "'" + sheetName + "'!$" + colRef.getCellRefParts()[2] + "$" + colRef.getCellRefParts()[1]
+ ":$" + colRef2.getCellRefParts()[2] + "$" + colRef2.getCellRefParts()[1];
+        return c;
     }
 
-    public void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn,
int startRow, int endRow) {
-        // TODO Auto-generated method stub
+    //****************** NAME RANGE *************************
 
+    private CTDefinedNames getDefinedNames() {
+        return workbook.getDefinedNames() == null ? workbook.addNewDefinedNames() : workbook.getDefinedNames();
     }
 
+
+    public XSSFName getSpecificBuiltinRecord(String builtInCode, int sheetNumber) {
+        for (XSSFName name : namedRanges) {
+            if (name.getNameName().equalsIgnoreCase(builtInCode) && name.getLocalSheetId()
== sheetNumber) {
+                return name;
+            }
+        }
+        return null;
+    }
+
+    /**
+     * Generates a NameRecord to represent a built-in region
+     * @return a new NameRecord
+     */
+    public XSSFName createBuiltInName(String builtInName, int sheetNumber) {
+        if (sheetNumber < 0 || sheetNumber+1 > Short.MAX_VALUE) {
+            throw new IllegalArgumentException("Sheet number ["+sheetNumber+"]is not valid
");
+        }
+        
+        CTDefinedName nameRecord=getDefinedNames().addNewDefinedName();
+        nameRecord.setName(builtInName);
+        nameRecord.setLocalSheetId(sheetNumber);
+   
+        XSSFName name=new XSSFName(nameRecord,this);        
+        //while(namedRanges.contains(name)) {
+        for(XSSFName nr :  namedRanges){
+            if(nr.equals(name))
+            throw new RuntimeException("Builtin (" + builtInName 
+                    + ") already exists for sheet (" + sheetNumber + ")");
+        }     
+
+        return name;
+    }
+    
+    //*******************************************
+    
     /**
      * We only set one sheet as selected for compatibility with HSSF.
      */

Modified: poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java?rev=705701&r1=705700&r2=705701&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
(original)
+++ poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
Fri Oct 17 11:43:37 2008
@@ -31,6 +31,7 @@
 import org.apache.poi.ss.usermodel.RichTextString;
 import org.apache.poi.ss.usermodel.Sheet;
 import org.apache.poi.ss.usermodel.StylesSource;
+import org.apache.poi.ss.usermodel.Workbook;
 import org.apache.poi.xssf.XSSFTestDataSamples;
 import org.apache.poi.xssf.model.StylesTable;
 import org.openxml4j.opc.ContentTypes;
@@ -150,6 +151,73 @@
 		workbook.removeSheetAt(0);
 		assertEquals(0, workbook.getNumberOfSheets());
 	}
+
+	public void testPrintArea(){
+	 Workbook workbook = new XSSFWorkbook();
+	 Sheet sheet = workbook.createSheet("Test Print Area");
+	 String sheetName = workbook.getSheetName(0);
+	 
+	// String reference = sheetName+"!$A$1:$B$1";
+	// workbook.setPrintArea(0, reference);
+	 workbook.setPrintArea(0,1,5,4,9);
+	 
+	 String retrievedPrintArea = workbook.getPrintArea(0);
+
+ 	 //assertNotNull("Print Area not defined for first sheet", retrievedPrintArea);
+	 assertEquals("'"+sheetName+"'!$B$5:$F$10", retrievedPrintArea);
+}
+	
+	public void _testRepeatingRowsAndColums() {
+		// 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 sheet = wb.createSheet("FirstSheet");
+		
+		// set repeating rows and columns twice for the first sheet
+		for (int i = 0; i < 2; i++) {
+			wb.setRepeatingRowsAndColumns(0, 0, 0, 0, 3);
+			//sheet.createFreezePane(0, 3);
+		}
+		assertEquals(1, wb.getNumberOfNames());
+		XSSFName nr1 = wb.getNameAt(0);
+		
+		assertEquals(XSSFName.BUILTIN_PRINT_TITLE, nr1.getNameName());
+		assertEquals("'FirstSheet'!$A:$A,'FirstSheet'!$1:$4", nr1.getReference());
+		
+		// Save and re-open
+		XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb);
+
+		assertEquals(1, nwb.getNumberOfNames());
+		nr1 = nwb.getNameAt(0);
+		
+		assertEquals(XSSFName.BUILTIN_PRINT_TITLE, nr1.getNameName());
+		assertEquals("'FirstSheet'!$A:$A,'FirstSheet'!$1:$4", nr1.getReference());
+		
+		// check that setting RR&C on a second sheet causes a new Print_Titles built-in
+		// name to be created
+		sheet = nwb.createSheet("SecondSheet");
+		nwb.setRepeatingRowsAndColumns(1, 1, 2, 0, 0);
+
+		assertEquals(2, nwb.getNumberOfNames());
+		XSSFName nr2 = nwb.getNameAt(1);
+		
+		assertEquals(XSSFName.BUILTIN_PRINT_TITLE, nr2.getNameName());
+		assertEquals("'SecondSheet'!$B:$C,'SecondSheet'!$1:$1", nr2.getReference());
+		
+		if (false) {
+			// In case you fancy checking in excel, to ensure it
+			//  won't complain about the file now
+			try {
+				File tempFile = File.createTempFile("POI-45126-", ".xlsx");
+				FileOutputStream fout = new FileOutputStream(tempFile);
+				nwb.write(fout);
+				fout.close();
+				System.out.println("check out " + tempFile.getAbsolutePath());
+			} catch (IOException e) {
+				throw new RuntimeException(e);
+			}
+		}
+	}
 	
 	/**
 	 * Tests that we can save a new document



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message