From bf78b31e9ccd74da40439fee11ae3d2262358524 Mon Sep 17 00:00:00 2001 From: Glen Stampoultzis Date: Thu, 6 Feb 2003 10:29:45 +0000 Subject: [PATCH] Support for freezepanes / split panes / header rows and columns on printouts... see documentation for details. git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@353002 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/xdocs/faq.xml | 15 + src/documentation/xdocs/hssf/quick-guide.xml | 116 +++++++ .../examples/RepeatingRowsAndColumns.java | 105 ++++++ .../examples/SplitAndFreezePanes.java | 90 +++++ .../hssf/usermodel/examples/ZoomSheet.java | 80 +++++ .../org/apache/poi/hssf/dev/BiffViewer.java | 3 + src/java/org/apache/poi/hssf/model/Sheet.java | 145 +++++++- .../org/apache/poi/hssf/model/Workbook.java | 198 +++++------ .../poi/hssf/model/WorkbookRecordList.java | 148 ++++++++ .../apache/poi/hssf/record/NameRecord.java | 148 ++++---- .../apache/poi/hssf/record/PaneRecord.java | 317 ++++++++++++++++++ .../poi/hssf/record/SelectionRecord.java | 12 +- .../poi/hssf/record/formula/AreaPtg.java | 2 +- .../poi/hssf/record/formula/AttrPtg.java | 3 - .../poi/hssf/record/formula/ControlPtg.java | 7 + .../poi/hssf/record/formula/MemFuncPtg.java | 131 ++++++++ .../apache/poi/hssf/record/formula/Ptg.java | 10 +- .../poi/hssf/record/formula/UnionPtg.java | 120 +++++++ .../apache/poi/hssf/usermodel/HSSFSheet.java | 79 ++++- .../poi/hssf/usermodel/HSSFWorkbook.java | 158 +++++++++ src/records/definitions/pane_record.xml | 18 + .../poi/hssf/record/TestPaneRecord.java | 117 +++++++ .../poi/hssf/usermodel/SanityChecker.java | 197 ++++++++++- .../poi/hssf/usermodel/TestHSSFSheet.java | 21 +- .../poi/hssf/usermodel/TestNamedRange.java | 6 +- .../poi/hssf/usermodel/TestSanityChecker.java | 212 ++++++++++++ 26 files changed, 2277 insertions(+), 181 deletions(-) create mode 100644 src/examples/src/org/apache/poi/hssf/usermodel/examples/RepeatingRowsAndColumns.java create mode 100644 src/examples/src/org/apache/poi/hssf/usermodel/examples/SplitAndFreezePanes.java create mode 100644 src/examples/src/org/apache/poi/hssf/usermodel/examples/ZoomSheet.java create mode 100644 src/java/org/apache/poi/hssf/model/WorkbookRecordList.java create mode 100644 src/java/org/apache/poi/hssf/record/PaneRecord.java create mode 100644 src/java/org/apache/poi/hssf/record/formula/ControlPtg.java create mode 100644 src/java/org/apache/poi/hssf/record/formula/MemFuncPtg.java create mode 100644 src/java/org/apache/poi/hssf/record/formula/UnionPtg.java create mode 100644 src/records/definitions/pane_record.xml create mode 100644 src/testcases/org/apache/poi/hssf/record/TestPaneRecord.java create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestSanityChecker.java diff --git a/src/documentation/xdocs/faq.xml b/src/documentation/xdocs/faq.xml index fdfbbb4262..a702b247be 100644 --- a/src/documentation/xdocs/faq.xml +++ b/src/documentation/xdocs/faq.xml @@ -267,5 +267,20 @@ fileOut.close(); + + + Will Poi read any spreadsheet and rewrite it with modifications. + + + Poi is not guanteed to read the contents of any spreadsheet. + Certain features may cause spreadsheets to fail to read. More + problematic is rewriting spreadsheets. Poi tried hard to + preserve the records of the original spreadsheet but some + features may cause problems. We advise that you limit the + formatting of spreadsheets you process so as to not be + unpleasantly suprised at a later stage. + + + diff --git a/src/documentation/xdocs/hssf/quick-guide.xml b/src/documentation/xdocs/hssf/quick-guide.xml index e53486d9ac..fb2ca011e7 100644 --- a/src/documentation/xdocs/hssf/quick-guide.xml +++ b/src/documentation/xdocs/hssf/quick-guide.xml @@ -35,6 +35,9 @@
  • Set page numbers on the footer of a sheet.
  • Shift rows.
  • Set a sheet as selected.
    • +
  • Set the zoom magnification for a sheet.
    • +
  • Create split and freeze panes.
    • +
  • Repeating rows and columns.
    • @@ -535,6 +538,119 @@ fileOut.close();
    + + +
    +

    + The zoom is expressed as a fraction. For example to + express a zoom of 75% use 3 for the numerator and + 4 for the denominator. +

    + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet1 = wb.createSheet("new sheet"); + sheet1.setZoom(3,4); // 75 percent magnification + FileOutputStream fileOut = new FileOutputStream("workbook.xls"); + wb.write(fileOut); + fileOut.close(); + +
    + + +
    +

    + There are two types of panes you can create; freeze panes and split panes. +

    +

    + A freeze pane is split by columns and rows. You create + a freeze pane using the following mechanism: +

    +

    + sheet1.createFreezePane( 3, 2, 3, 2 ); +

    +

    + The first two parameters are the columns and rows you + wish to split by. The second two parameters indicate + the cells that are visible in the bottom right quadrant. +

    +

    + + Split pains appear differently. The split area is + divided into four separate work area's. The split + occurs at the pixel level and the user is able to + adjust the split by dragging it to a new position. +

    +

    + + Split panes are created with the following call: +

    +

    + sheet2.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT ); +

    +

    + + The first parameter is the x position of the split. + This is in 1/20th of a point. A point in this case + seems to equate to a pixel. The second parameter is + the y position of the split. Again in 1/20th of a point. +

    +

    + The last parameter indicates which pane currently has + the focus. This will be one of HSSFSheet.PANE_LOWER_LEFT, + PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT. +

    + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet1 = wb.createSheet("new sheet"); + HSSFSheet sheet2 = wb.createSheet("second sheet"); + HSSFSheet sheet3 = wb.createSheet("third sheet"); + HSSFSheet sheet4 = wb.createSheet("fourth sheet"); + + // Freeze just one row + sheet1.createFreezePane( 0, 1, 0, 1 ); + // Freeze just one column + sheet2.createFreezePane( 1, 0, 1, 0 ); + // Freeze the columns and rows (forget about scrolling position of the lower right quadrant). + sheet3.createFreezePane( 2, 2 ); + // Create a split with the lower left side being the active quadrant + sheet4.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT ); + + FileOutputStream fileOut = new FileOutputStream("workbook.xls"); + wb.write(fileOut); + fileOut.close(); + +
    + + +
    +

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

    +

    + This function Contains 5 parameters. + The first parameter is the index to the sheet (0 = first sheet). + The second and third parameters specify the range for the columns to repreat. + To stop the columns from repeating pass in -1 as the start and end column. + The fourth and fifth parameters specify the range for the rows to repeat. + To stop the columns from repeating pass in -1 as the start and end rows. +

    + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet1 = wb.createSheet("new sheet"); + HSSFSheet sheet2 = wb.createSheet("second sheet"); + + // Set the columns to repeat from column 0 to 2 on the first sheet + wb.setRepeatingRowsAndColumns(0,0,2,-1,-1); + // Set the the repeating rows and columns on the second sheet. + wb.setRepeatingRowsAndColumns(1,4,5,1,2); + + FileOutputStream fileOut = new FileOutputStream("workbook.xls"); + wb.write(fileOut); + fileOut.close(); + +
    diff --git a/src/examples/src/org/apache/poi/hssf/usermodel/examples/RepeatingRowsAndColumns.java b/src/examples/src/org/apache/poi/hssf/usermodel/examples/RepeatingRowsAndColumns.java new file mode 100644 index 0000000000..e2589c1b90 --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/usermodel/examples/RepeatingRowsAndColumns.java @@ -0,0 +1,105 @@ +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ + +package org.apache.poi.hssf.usermodel.examples; + +import org.apache.poi.hssf.usermodel.*; +import org.apache.poi.poifs.filesystem.POIFSFileSystem; + +import java.io.IOException; +import java.io.FileOutputStream; +import java.io.FileInputStream; + +/** + * @author Glen Stampoultzis (glens at apache.org) + */ +public class RepeatingRowsAndColumns +{ + public static void main(String[] args) + throws IOException + { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet1 = wb.createSheet("first sheet"); + HSSFSheet sheet2 = wb.createSheet("second sheet"); + HSSFSheet sheet3 = wb.createSheet("third sheet"); + +// POIFSFileSystem fs = +// new POIFSFileSystem(new FileInputStream("workbook.xls")); +// HSSFWorkbook wb = new HSSFWorkbook(fs); +// HSSFSheet sheet1 = wb.getSheetAt(0); + + HSSFFont boldFont = wb.createFont(); + boldFont.setFontHeightInPoints((short)22); + boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); + + HSSFCellStyle boldStyle = wb.createCellStyle(); + boldStyle.setFont(boldFont); + + HSSFRow row = sheet1.createRow((short)1); + HSSFCell cell = row.createCell((short)0); + cell.setCellValue("This quick brown fox"); + cell.setCellStyle(boldStyle); + + // Set the columns to repeat from column 0 to 2 on the first sheet + wb.setRepeatingRowsAndColumns(0,0,2,-1,-1); + // Set the rows to repeat from row 0 to 2 on the second sheet. + wb.setRepeatingRowsAndColumns(1,-1,-1,0,2); + // Set the the repeating rows and columns on the third sheet. + wb.setRepeatingRowsAndColumns(2,4,5,1,2); + + FileOutputStream fileOut = new FileOutputStream("workbook.xls"); + wb.write(fileOut); + fileOut.close(); + } +} diff --git a/src/examples/src/org/apache/poi/hssf/usermodel/examples/SplitAndFreezePanes.java b/src/examples/src/org/apache/poi/hssf/usermodel/examples/SplitAndFreezePanes.java new file mode 100644 index 0000000000..f0a8163bcc --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/usermodel/examples/SplitAndFreezePanes.java @@ -0,0 +1,90 @@ +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ + +package org.apache.poi.hssf.usermodel.examples; + +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFSheet; + +import java.io.IOException; +import java.io.FileOutputStream; + +/** + * @author Glen Stampoultzis (glens at apache.org) + */ +public class SplitAndFreezePanes +{ + public static void main(String[] args) + throws IOException + { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet1 = wb.createSheet("new sheet"); + HSSFSheet sheet2 = wb.createSheet("second sheet"); + HSSFSheet sheet3 = wb.createSheet("third sheet"); + HSSFSheet sheet4 = wb.createSheet("fourth sheet"); + + // Freeze just one row + sheet1.createFreezePane( 0, 1, 0, 1 ); + // Freeze just one column + sheet2.createFreezePane( 1, 0, 1, 0 ); + // Freeze the columns and rows (forget about scrolling position of the lower right quadrant). + sheet3.createFreezePane( 2, 2 ); + // Create a split with the lower left side being the active quadrant + sheet4.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT ); + + FileOutputStream fileOut = new FileOutputStream("workbook.xls"); + wb.write(fileOut); + fileOut.close(); + } +} diff --git a/src/examples/src/org/apache/poi/hssf/usermodel/examples/ZoomSheet.java b/src/examples/src/org/apache/poi/hssf/usermodel/examples/ZoomSheet.java new file mode 100644 index 0000000000..244d949b71 --- /dev/null +++ b/src/examples/src/org/apache/poi/hssf/usermodel/examples/ZoomSheet.java @@ -0,0 +1,80 @@ +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ + +package org.apache.poi.hssf.usermodel.examples; + +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFSheet; + +import java.io.IOException; +import java.io.FileOutputStream; + +/** + * Sets the zoom magnication for a sheet. + * + * @author Glen Stampoultzis (glens at apache.org) + */ +public class ZoomSheet +{ + public static void main(String[] args) + throws IOException + { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet1 = wb.createSheet("new sheet"); + sheet1.setZoom(3,4); // 75 percent magnification + FileOutputStream fileOut = new FileOutputStream("workbook.xls"); + wb.write(fileOut); + fileOut.close(); + } +} diff --git a/src/java/org/apache/poi/hssf/dev/BiffViewer.java b/src/java/org/apache/poi/hssf/dev/BiffViewer.java index 59b368635f..4e0adb911c 100644 --- a/src/java/org/apache/poi/hssf/dev/BiffViewer.java +++ b/src/java/org/apache/poi/hssf/dev/BiffViewer.java @@ -627,6 +627,9 @@ public class BiffViewer { case NameRecord.sid: retval = new NameRecord( rectype, size, data ); break; + case PaneRecord.sid: + retval = new PaneRecord( rectype, size, data ); + break; default: retval = new UnknownRecord( rectype, size, data ); } diff --git a/src/java/org/apache/poi/hssf/model/Sheet.java b/src/java/org/apache/poi/hssf/model/Sheet.java index 69d9beecd8..5017583f92 100644 --- a/src/java/org/apache/poi/hssf/model/Sheet.java +++ b/src/java/org/apache/poi/hssf/model/Sheet.java @@ -119,11 +119,15 @@ public class Sheet implements Model private Iterator rowRecIterator = null; protected int eofLoc = 0; + public static final byte PANE_LOWER_RIGHT = (byte)0; + public static final byte PANE_UPPER_RIGHT = (byte)1; + public static final byte PANE_LOWER_LEFT = (byte)2; + public static final byte PANE_UPPER_LEFT = (byte)3; + /** * Creates new Sheet with no intialization --useless at this point * @see #createSheet(List,int,int) */ - public Sheet() { } @@ -2091,6 +2095,28 @@ public class Sheet implements Model return null; } + /** + * Sets the SCL record or creates it in the correct place if it does not + * already exist. + * + * @param sclRecord The record to set. + */ + public void setSCLRecord(SCLRecord sclRecord) + { + int oldRecordLoc = findFirstRecordLocBySid(SCLRecord.sid); + if (oldRecordLoc == -1) + { + // Insert it after the window record + int windowRecordLoc = findFirstRecordLocBySid(WindowTwoRecord.sid); + records.add(windowRecordLoc+1, sclRecord); + } + else + { + records.set(oldRecordLoc, sclRecord); + } + + } + /** * Finds the first occurance of a record matching a particular sid and * returns it's position. @@ -2282,4 +2308,121 @@ public class Sheet implements Model return eofLoc; } + /** + * Creates a split (freezepane). + * @param colSplit Horizonatal position of split. + * @param rowSplit Vertical position of split. + * @param topRow Top row visible in bottom pane + * @param leftmostColumn Left column visible in right pane. + */ + public void createFreezePane(int colSplit, int rowSplit, int topRow, int leftmostColumn ) + { + int loc = findFirstRecordLocBySid(WindowTwoRecord.sid); + PaneRecord pane = new PaneRecord(); + pane.setX((short)colSplit); + pane.setY((short)rowSplit); + pane.setTopRow((short) topRow); + pane.setLeftColumn((short) leftmostColumn); + if (rowSplit == 0) + { + pane.setTopRow((short)0); + pane.setActivePane((short)1); + } + else if (colSplit == 0) + { + pane.setLeftColumn((short)64); + pane.setActivePane((short)2); + } + else + { + pane.setActivePane((short)0); + } + records.add(loc+1, pane); + + WindowTwoRecord windowRecord = (WindowTwoRecord) records.get(loc); + windowRecord.setFreezePanes(true); + windowRecord.setFreezePanesNoSplit(true); + + SelectionRecord sel = (SelectionRecord) findFirstRecordBySid(SelectionRecord.sid); +// SelectionRecord sel2 = (SelectionRecord) sel.clone(); +// SelectionRecord sel3 = (SelectionRecord) sel.clone(); +// SelectionRecord sel4 = (SelectionRecord) sel.clone(); +// sel.setPane(PANE_LOWER_RIGHT); // 0 +// sel3.setPane(PANE_UPPER_RIGHT); // 1 + sel.setPane((byte)pane.getActivePane()); // 2 +// sel2.setPane(PANE_UPPER_LEFT); // 3 +// sel4.setActiveCellCol((short)Math.max(sel3.getActiveCellCol(), colSplit)); +// sel3.setActiveCellRow((short)Math.max(sel4.getActiveCellRow(), rowSplit)); + + int selLoc = findFirstRecordLocBySid(SelectionRecord.sid); +// sel.setActiveCellCol((short)15); +// sel.setActiveCellRow((short)15); +// sel2.setActiveCellCol((short)0); +// sel2.setActiveCellRow((short)0); + +// records.add(selLoc+1,sel2); +// records.add(selLoc+2,sel3); +// records.add(selLoc+3,sel4); + } + + /** + * Creates a split pane. + * @param xSplitPos Horizonatal position of split (in 1/20th of a point). + * @param ySplitPos Vertical position of split (in 1/20th of a point). + * @param topRow Top row visible in bottom pane + * @param leftmostColumn Left column visible in right pane. + * @param activePane Active pane. One of: PANE_LOWER_RIGHT, + * PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT + * @see #PANE_LOWER_LEFT + * @see #PANE_LOWER_RIGHT + * @see #PANE_UPPER_LEFT + * @see #PANE_UPPER_RIGHT + */ + public void createSplitPane(int xSplitPos, int ySplitPos, int topRow, int leftmostColumn, int activePane ) + { + int loc = findFirstRecordLocBySid(WindowTwoRecord.sid); + PaneRecord r = new PaneRecord(); + r.setX((short)xSplitPos); + r.setY((short)ySplitPos); + r.setTopRow((short) topRow); + r.setLeftColumn((short) leftmostColumn); + r.setActivePane((short) activePane); + records.add(loc+1, r); + + WindowTwoRecord windowRecord = (WindowTwoRecord) records.get(loc); + windowRecord.setFreezePanes(false); + windowRecord.setFreezePanesNoSplit(false); + + SelectionRecord sel = (SelectionRecord) findFirstRecordBySid(SelectionRecord.sid); +// SelectionRecord sel2 = (SelectionRecord) sel.clone(); +// SelectionRecord sel3 = (SelectionRecord) sel.clone(); +// SelectionRecord sel4 = (SelectionRecord) sel.clone(); + sel.setPane(PANE_LOWER_RIGHT); // 0 +// sel3.setPane(PANE_UPPER_RIGHT); // 1 +// sel4.setPane(PANE_LOWER_LEFT); // 2 +// sel2.setPane(PANE_UPPER_LEFT); // 3 +// sel4.setActiveCellCol((short)Math.max(sel3.getActiveCellCol(), colSplit)); +// sel3.setActiveCellRow((short)Math.max(sel4.getActiveCellRow(), rowSplit)); + + int selLoc = findFirstRecordLocBySid(SelectionRecord.sid); +// sel.setActiveCellCol((short)15); +// sel.setActiveCellRow((short)15); +// sel2.setActiveCellCol((short)0); +// sel2.setActiveCellRow((short)0); + +// records.add(selLoc+1,sel2); +// records.add(selLoc+2,sel3); +// records.add(selLoc+3,sel4); + } + + public SelectionRecord getSelection() + { + return selection; + } + + public void setSelection( SelectionRecord selection ) + { + this.selection = selection; + } + } diff --git a/src/java/org/apache/poi/hssf/model/Workbook.java b/src/java/org/apache/poi/hssf/model/Workbook.java index a1a4f4e419..e823d40f4d 100644 --- a/src/java/org/apache/poi/hssf/model/Workbook.java +++ b/src/java/org/apache/poi/hssf/model/Workbook.java @@ -79,9 +79,6 @@ import java.util.Locale; * Kit (Microsoft Press) and the documentation at http://sc.openoffice.org/excelfileformat.pdf * before even attempting to use this. * - * @todo Need a good way of keeping track of bookmarks in a list. Currently - * we are manually incrementing multiple indexes whenever new records - * are added. This mechanism makes it very easy to introduce bugs. * * @author Shawn Laubach (slaubach at apache dot org) (Data Formats) * @author Andrew C. Oliver (acoliver at apache dot org) @@ -109,45 +106,31 @@ public class Workbook implements Model { /** * this contains the Worksheet record objects */ - - protected ArrayList records = null; + protected WorkbookRecordList records = new WorkbookRecordList(); /** * this contains a reference to the SSTRecord so that new stings can be added * to it. */ - protected SSTRecord sst = null; /** * Holds the Extern Sheet with references to bound sheets */ - protected ExternSheetRecord externSheet= null; /** * holds the "boundsheet" records (aka bundlesheet) so that they can have their * reference to their "BOF" marker */ - - protected ArrayList boundsheets = new ArrayList(); protected ArrayList formats = new ArrayList(); protected ArrayList names = new ArrayList(); - protected int protpos = 0; // holds the position of the protect record. - protected int bspos = 0; // holds the position of the last bound sheet. - protected int tabpos = 0; // holds the position of the tabid record - protected int fontpos = 0; // hold the position of the last font record - protected int numfonts = 0; // hold the number of font records - protected int xfpos = 0; // hold the position of the last extended font record protected int numxfs = 0; // hold the number of extended format records - private int backuppos = 0; // holds the position of the backup record. - private int namepos = 0; // holds the position of last name record - private int supbookpos = 0; // holds the position of sup book - private int palettepos = 0; // hold the position of the palette, if applicable + protected int numfonts = 0; // hold the number of font records private short maxformatid = -1; // holds the max format id private boolean uses1904datewindowing = false; // whether 1904 date windowing is being used @@ -157,7 +140,6 @@ public class Workbook implements Model { * Creates new Workbook with no intitialization --useless right now * @see #createWorkbook(List) */ - public Workbook() { } @@ -173,7 +155,6 @@ public class Workbook implements Model { * @param recs an array of Record objects * @return Workbook object */ - public static Workbook createWorkbook(List recs) { log.log(DEBUG, "Workbook (readfile) created with reclen=", new Integer(recs.size())); @@ -193,7 +174,7 @@ public class Workbook implements Model { case BoundSheetRecord.sid : log.log(DEBUG, "found boundsheet record at " + k); retval.boundsheets.add(rec); - retval.bspos = k; + retval.records.setBspos( k ); break; case SSTRecord.sid : @@ -203,29 +184,29 @@ public class Workbook implements Model { case FontRecord.sid : log.log(DEBUG, "found font record at " + k); - retval.fontpos = k; + retval.records.setFontpos( k ); retval.numfonts++; break; case ExtendedFormatRecord.sid : log.log(DEBUG, "found XF record at " + k); - retval.xfpos = k; + retval.records.setXfpos( k ); retval.numxfs++; break; case TabIdRecord.sid : log.log(DEBUG, "found tabid record at " + k); - retval.tabpos = k; + retval.records.setTabpos( k ); break; case ProtectRecord.sid : log.log(DEBUG, "found protect record at " + k); - retval.protpos = k; + retval.records.setProtpos( k ); break; case BackupRecord.sid : log.log(DEBUG, "found backup record at " + k); - retval.backuppos = k; + retval.records.setBackuppos( k ); break; case ExternSheetRecord.sid : log.log(DEBUG, "found extern sheet record at " + k); @@ -234,18 +215,16 @@ public class Workbook implements Model { case NameRecord.sid : log.log(DEBUG, "found name record at " + k); retval.names.add(rec); - retval.namepos = k; +// retval.records.namepos = k; break; - case 0x1AE : - //Havent Implement the sup book , because we dont need extern ranges - //for now + case SupBookRecord.sid : log.log(DEBUG, "found SupBook record at " + k); - retval.supbookpos = k; +// retval.records.supbookpos = k; break; case FormatRecord.sid : - log.log(DEBUG, "found format record at " + k); - retval.formats.add(rec); - retval.maxformatid = retval.maxformatid >= ((FormatRecord)rec).getIndexCode() ? retval.maxformatid : ((FormatRecord)rec).getIndexCode(); + log.log(DEBUG, "found format record at " + k); + retval.formats.add(rec); + retval.maxformatid = retval.maxformatid >= ((FormatRecord)rec).getIndexCode() ? retval.maxformatid : ((FormatRecord)rec).getIndexCode(); break; case DateWindow1904Record.sid : log.log(DEBUG, "found datewindow1904 record at " + k); @@ -253,18 +232,18 @@ public class Workbook implements Model { break; case PaletteRecord.sid: log.log(DEBUG, "found palette record at " + k); - retval.palettepos = k; + retval.records.setPalettepos( k ); default : } records.add(rec); } //What if we dont have any ranges and supbooks - if (retval.supbookpos == 0) { - retval.supbookpos = retval.bspos + 1; - retval.namepos = retval.supbookpos + 1; - } +// if (retval.records.supbookpos == 0) { +// retval.records.supbookpos = retval.records.bspos + 1; +// retval.records.namepos = retval.records.supbookpos + 1; +// } - retval.records = records; + retval.records.setRecords(records); log.log(DEBUG, "exit create workbook from existing file function"); return retval; } @@ -288,17 +267,17 @@ public class Workbook implements Model { records.add( retval.createCodepage() ); records.add( retval.createDSF() ); records.add( retval.createTabId() ); - retval.tabpos = records.size() - 1; + retval.records.setTabpos( records.size() - 1 ); records.add( retval.createFnGroupCount() ); records.add( retval.createWindowProtect() ); records.add( retval.createProtect() ); - retval.protpos = records.size() - 1; + retval.records.setProtpos( records.size() - 1 ); records.add( retval.createPassword() ); records.add( retval.createProtectionRev4() ); records.add( retval.createPasswordRev4() ); records.add( retval.createWindowOne() ); records.add( retval.createBackup() ); - retval.backuppos = records.size() - 1; + retval.records.setBackuppos( records.size() - 1 ); records.add( retval.createHideObj() ); records.add( retval.createDateWindow1904() ); records.add( retval.createPrecision() ); @@ -308,7 +287,7 @@ public class Workbook implements Model { records.add( retval.createFont() ); records.add( retval.createFont() ); records.add( retval.createFont() ); - retval.fontpos = records.size() - 1; // last font record postion + retval.records.setFontpos( records.size() - 1 ); // last font record postion retval.numfonts = 4; // set up format records @@ -327,12 +306,12 @@ public class Workbook implements Model { records.add( retval.createExtendedFormat( k ) ); retval.numxfs++; } - retval.xfpos = records.size() - 1; + retval.records.setXfpos( records.size() - 1 ); for ( int k = 0; k < 6; k++ ) { records.add( retval.createStyle( k ) ); } - retval.palettepos = records.size(); + retval.records.setPalettepos( records.size() ); records.add( retval.createUseSelFS() ); for ( int k = 0; k < 1; k++ ) { // now just do 1 @@ -341,15 +320,17 @@ public class Workbook implements Model { records.add( bsr ); retval.boundsheets.add( bsr ); - retval.bspos = records.size() - 1; + retval.records.setBspos( records.size() - 1 ); } +// retval.records.supbookpos = retval.records.bspos + 1; +// retval.records.namepos = retval.records.supbookpos + 2; records.add( retval.createCountry() ); retval.sst = (SSTRecord) retval.createSST(); records.add( retval.sst ); records.add( retval.createExtendedSST() ); records.add( retval.createEOF() ); - retval.records = records; + retval.records.setRecords(records); log.log( DEBUG, "exit create new workbook from scratch" ); return retval; } @@ -379,7 +360,7 @@ public class Workbook implements Model { + " font records, you asked for " + idx); } FontRecord retval = - ( FontRecord ) records.get((fontpos - (numfonts - 1)) + index); + ( FontRecord ) records.get((records.getFontpos() - (numfonts - 1)) + index); return retval; } @@ -395,10 +376,8 @@ public class Workbook implements Model { public FontRecord createNewFont() { FontRecord rec = ( FontRecord ) createFont(); - ++fontpos; - ++bspos; - ++xfpos; - records.add(fontpos, rec); + records.add(records.getFontpos()+1, rec); + records.setFontpos( records.getFontpos() + 1 ); numfonts++; return rec; } @@ -433,7 +412,7 @@ public class Workbook implements Model { */ public BackupRecord getBackupRecord() { - return ( BackupRecord ) records.get(backuppos); + return ( BackupRecord ) records.get(records.getBackuppos()); } @@ -446,7 +425,7 @@ public class Workbook implements Model { * @param sheetname the name for the sheet */ - // for compartibility + // for compatibility public void setSheetName(int sheetnum, String sheetname ) { setSheetName( sheetnum, sheetname, (byte)0 ); } @@ -501,10 +480,10 @@ public class Workbook implements Model { if ((boundsheets.size() + 1) <= sheetnum) { throw new RuntimeException("Sheet number out of bounds!"); } - BoundSheetRecord bsr = - ( BoundSheetRecord ) createBoundSheet(sheetnum); + BoundSheetRecord bsr = (BoundSheetRecord ) createBoundSheet(sheetnum); - records.add(++bspos, bsr); + records.add(records.getBspos()+1, bsr); + records.setBspos( records.getBspos() + 1 ); boundsheets.add(bsr); fixTabIdRecord(); } @@ -512,8 +491,8 @@ public class Workbook implements Model { public void removeSheet(int sheetnum) { if (boundsheets.size() > sheetnum) { - records.remove(bspos - (boundsheets.size() - 1) + sheetnum); - bspos--; + records.remove(records.getBspos() - (boundsheets.size() - 1) + sheetnum); +// records.bspos--; boundsheets.remove(sheetnum); fixTabIdRecord(); } @@ -524,7 +503,7 @@ public class Workbook implements Model { * */ private void fixTabIdRecord() { - TabIdRecord tir = ( TabIdRecord ) records.get(tabpos); + TabIdRecord tir = ( TabIdRecord ) records.get(records.getTabpos()); short[] tia = new short[ boundsheets.size() ]; for (short k = 0; k < tia.length; k++) { @@ -551,7 +530,7 @@ public class Workbook implements Model { */ public int getNumExFormats() { - log.log(DEBUG, "getXF=", new Integer(boundsheets.size())); + log.log(DEBUG, "getXF=", new Integer(numxfs)); return numxfs; } @@ -563,7 +542,7 @@ public class Workbook implements Model { */ public ExtendedFormatRecord getExFormatAt(int index) { - int xfptr = xfpos - (numxfs - 1); + int xfptr = records.getXfpos() - (numxfs - 1); xfptr += index; ExtendedFormatRecord retval = @@ -582,10 +561,8 @@ public class Workbook implements Model { public ExtendedFormatRecord createCellXF() { ExtendedFormatRecord xf = createExtendedFormat(); - ++xfpos; - ++palettepos; - ++bspos; - records.add(xfpos, xf); + records.add(records.getXfpos()+1, xf); + records.setXfpos( records.getXfpos() + 1 ); numxfs++; return xf; } @@ -680,7 +657,7 @@ public class Workbook implements Model { // byte[] rec = (( byte [] ) bytes.get(k)); // System.arraycopy(rec, 0, retval, pos, rec.length); - Record record = (( Record ) records.get(k)); + Record record = records.get(k); // Let's skip RECALCID records, as they are only use for optimization if(record.getSid() != RecalcIdRecord.sid || ((RecalcIdRecord)record).isNeeded()) { pos += record.serialize(pos, retval); // rec.length; @@ -717,7 +694,7 @@ public class Workbook implements Model { // byte[] rec = (( byte [] ) bytes.get(k)); // System.arraycopy(rec, 0, data, offset + pos, rec.length); - Record record = (( Record ) records.get(k)); + Record record = records.get(k); // Let's skip RECALCID records, as they are only use for optimization if(record.getSid() != RecalcIdRecord.sid || ((RecalcIdRecord)record).isNeeded()) { pos += record.serialize(pos + offset, data); // rec.length; @@ -731,7 +708,7 @@ public class Workbook implements Model { int retval = 0; for (int k = 0; k < records.size(); k++) { - Record record = (( Record ) records.get(k)); + Record record = records.get(k); // Let's skip RECALCID records, as they are only use for optimization if(record.getSid() != RecalcIdRecord.sid || ((RecalcIdRecord)record).isNeeded()) { retval += record.getRecordSize(); @@ -1712,7 +1689,7 @@ public class Workbook implements Model { return refs; } - /** fins the sheet name by his extern sheet index + /** finds the sheet name by his extern sheet index * @param num extern sheet index * @return sheet name */ @@ -1725,6 +1702,19 @@ public class Workbook implements Model { return result; } + /** + * Finds the sheet index for a particular external sheet number. + * @param externSheetNumber The external sheet number to convert + * @return The index to the sheet found. + */ + public int getSheetIndexFromExternSheetIndex(int externSheetNumber) + { + if (externSheetNumber >= externSheet.getNumOfREFStructures()) + return -1; + else + return externSheet.getREFRecordAt(externSheetNumber).getIndexToFirstSupBook(); + } + /** returns the extern sheet number for specific sheet number , * if this sheet doesn't exist in extern sheet , add it * @param sheetNumber sheet number @@ -1803,7 +1793,27 @@ public class Workbook implements Model { NameRecord name = new NameRecord(); - records.add(++namepos, name); + // Not the most efficient way but the other way was causing too many bugs + int idx = findFirstRecordLocBySid(ExternSheetRecord.sid); + if (idx == -1) idx = findFirstRecordLocBySid(SupBookRecord.sid); + if (idx == -1) idx = findFirstRecordLocBySid(CountryRecord.sid); + + records.add(idx+names.size()+1, name); + names.add(name); + + return name; + } + + /** creates new name + * @return new name record + */ + public NameRecord addName(NameRecord name) + { + // Not the most efficient way but the other way was causing too many bugs + int idx = findFirstRecordLocBySid(ExternSheetRecord.sid); + if (idx == -1) idx = findFirstRecordLocBySid(SupBookRecord.sid); + if (idx == -1) idx = findFirstRecordLocBySid(CountryRecord.sid); + records.add(idx+names.size()+1, name); names.add(name); return name; @@ -1814,8 +1824,8 @@ public class Workbook implements Model { */ public void removeName(int namenum){ if (names.size() > namenum) { - records.remove(namepos - (names.size() - 1) + namenum); - namepos--; + int idx = findFirstRecordLocBySid(NameRecord.sid); + records.remove(idx + namenum); names.remove(namenum); } @@ -1825,9 +1835,12 @@ public class Workbook implements Model { * @return the new extern sheet record */ protected ExternSheetRecord createExternSheet(){ - ExternSheetRecord rec = new ExternSheetRecord(); + ExternSheetRecord externSheet = new ExternSheetRecord(); - records.add(supbookpos + 1 , rec); + int idx = findFirstRecordLocBySid(CountryRecord.sid); + + records.add(idx+1, externSheet); +// records.add(records.supbookpos + 1 , rec); //We also adds the supBook for internal reference SupBookRecord supbook = new SupBookRecord(); @@ -1835,9 +1848,10 @@ public class Workbook implements Model { supbook.setNumberOfSheets((short)getNumSheets()); //supbook.setFlag(); - records.add(supbookpos + 1 , supbook); + records.add(idx+1, supbook); +// records.add(records.supbookpos + 1 , supbook); - return rec; + return externSheet; } /** @@ -1879,9 +1893,9 @@ public class Workbook implements Model { */ public short createFormat( String format ) { - ++xfpos; //These are to ensure that positions are updated properly - ++palettepos; - ++bspos; +// ++xfpos; //These are to ensure that positions are updated properly +// ++palettepos; +// ++bspos; FormatRecord rec = new FormatRecord(); maxformatid = maxformatid >= (short) 0xa4 ? (short) ( maxformatid + 1 ) : (short) 0xa4; //Starting value from M$ empiracle study. rec.setIndexCode( maxformatid ); @@ -1889,7 +1903,7 @@ public class Workbook implements Model { rec.setFormatString( format ); int pos = 0; - while ( pos < records.size() && ( (Record) records.get( pos ) ).getSid() != FormatRecord.sid ) + while ( pos < records.size() && records.get( pos ).getSid() != FormatRecord.sid ) pos++; pos += formats.size(); formats.add( rec ); @@ -1934,16 +1948,13 @@ public class Workbook implements Model { * Returns the next occurance of a record matching a particular sid. */ public Record findNextRecordBySid(short sid, int pos) { - Iterator iterator = records.iterator(); - for (;pos > 0 && iterator.hasNext(); iterator.next(),pos--) - { - // intentionally empty - } - while (iterator.hasNext()) { + int matches = 0; + for (Iterator iterator = records.iterator(); iterator.hasNext(); ) { Record record = ( Record ) iterator.next(); if (record.getSid() == sid) { - return record; + if (matches++ == pos) + return record; } } return null; @@ -1951,7 +1962,7 @@ public class Workbook implements Model { public List getRecords() { - return records; + return records.getRecords(); } // public void insertChartRecords( List chartRecords ) @@ -1982,7 +1993,7 @@ public class Workbook implements Model { public PaletteRecord getCustomPalette() { PaletteRecord palette; - Record rec = (Record) records.get(palettepos); + Record rec = records.get(records.getPalettepos()); if (rec instanceof PaletteRecord) { palette = (PaletteRecord) rec; @@ -1990,8 +2001,7 @@ public class Workbook implements Model { else { palette = createPalette(); - records.add(palettepos, palette); - ++bspos; + records.add(records.getPalettepos(), palette); } return palette; } diff --git a/src/java/org/apache/poi/hssf/model/WorkbookRecordList.java b/src/java/org/apache/poi/hssf/model/WorkbookRecordList.java new file mode 100644 index 0000000000..a846d67532 --- /dev/null +++ b/src/java/org/apache/poi/hssf/model/WorkbookRecordList.java @@ -0,0 +1,148 @@ +package org.apache.poi.hssf.model; + +import org.apache.poi.hssf.record.Record; + +import java.util.ArrayList; +import java.util.List; +import java.util.Iterator; + +public class WorkbookRecordList +{ + private List records = new ArrayList(); + + private int protpos = 0; // holds the position of the protect record. + private int bspos = 0; // holds the position of the last bound sheet. + private int tabpos = 0; // holds the position of the tabid record + private int fontpos = 0; // hold the position of the last font record + private int xfpos = 0; // hold the position of the last extended font record + private int backuppos = 0; // holds the position of the backup record. +// public int namepos = 0; // holds the position of last name record +// public int supbookpos = 0; // holds the position of sup book + private int palettepos = 0; // hold the position of the palette, if applicable + + + public void setRecords( List records ) + { + this.records = records; + } + + public int size() + { + return records.size(); + } + + public Record get( int i ) + { + return (Record) records.get(i); + } + + public void add( int pos, Record r ) + { + records.add(pos, r); + if (getProtpos() >= pos) setProtpos( protpos + 1 ); + if (getBspos() >= pos) setBspos( bspos + 1 ); + if (getTabpos() >= pos) setTabpos( tabpos + 1 ); + if (getFontpos() >= pos) setFontpos( fontpos + 1 ); + if (getXfpos() >= pos) setXfpos( xfpos + 1 ); + if (getBackuppos() >= pos) setBackuppos( backuppos + 1 ); +// if (namepos >= pos) namepos++; +// if (supbookpos >= pos) supbookpos++; + if (getPalettepos() >= pos) setPalettepos( palettepos + 1 ); + } + + public List getRecords() + { + return records; + } + + public Iterator iterator() + { + return records.iterator(); + } + + public void remove( int pos ) + { + records.remove(pos); + if (getProtpos() >= pos) setProtpos( protpos - 1 ); + if (getBspos() >= pos) setBspos( bspos - 1 ); + if (getTabpos() >= pos) setTabpos( tabpos - 1 ); + if (getFontpos() >= pos) setFontpos( fontpos - 1 ); + if (getXfpos() >= pos) setXfpos( xfpos - 1 ); + if (getBackuppos() >= pos) setBackuppos( backuppos - 1 ); +// if (namepos >= pos) namepos--; +// if (supbookpos >= pos) supbookpos--; + if (getPalettepos() >= pos) setPalettepos( palettepos - 1 ); + } + + public int getProtpos() + { + return protpos; + } + + public void setProtpos( int protpos ) + { + this.protpos = protpos; + } + + public int getBspos() + { + return bspos; + } + + public void setBspos( int bspos ) + { + this.bspos = bspos; + } + + public int getTabpos() + { + return tabpos; + } + + public void setTabpos( int tabpos ) + { + this.tabpos = tabpos; + } + + public int getFontpos() + { + return fontpos; + } + + public void setFontpos( int fontpos ) + { + this.fontpos = fontpos; + } + + public int getXfpos() + { + return xfpos; + } + + public void setXfpos( int xfpos ) + { + this.xfpos = xfpos; + } + + public int getBackuppos() + { + return backuppos; + } + + public void setBackuppos( int backuppos ) + { + this.backuppos = backuppos; + } + + public int getPalettepos() + { + return palettepos; + } + + public void setPalettepos( int palettepos ) + { + this.palettepos = palettepos; + } + + +} diff --git a/src/java/org/apache/poi/hssf/record/NameRecord.java b/src/java/org/apache/poi/hssf/record/NameRecord.java index 98189b561f..30b5ade2ae 100644 --- a/src/java/org/apache/poi/hssf/record/NameRecord.java +++ b/src/java/org/apache/poi/hssf/record/NameRecord.java @@ -72,6 +72,7 @@ import org.apache.poi.hssf.util.SheetReferences; * REFERENCE:

    * @author Libin Roman (Vista Portal LDT. Developer) * @author Sergei Kozello (sergeikozello at mail.ru) + * @author Glen Stampoultzis (glens at apache.org) * @version 1.0-pre */ @@ -83,7 +84,7 @@ public class NameRecord extends Record { private byte field_2_keyboard_shortcut; private byte field_3_length_name_text; private short field_4_length_name_definition; - private short field_5_index_to_sheet; + private short field_5_index_to_sheet; // unused: see field_6 private short field_6_equals_to_index_to_sheet; private byte field_7_length_custom_menu; private byte field_8_length_description_text; @@ -99,6 +100,7 @@ public class NameRecord extends Record { private String field_16_help_topic_text; private String field_17_status_bar_text; + /** Creates new NameRecord */ public NameRecord() { field_13_name_definition = new Stack(); @@ -161,17 +163,28 @@ public class NameRecord extends Record { field_4_length_name_definition = length; } - /** sets the index number to the extern sheet (thats is what writen in ducomentetion - * but as i saw , its work direrent) + /** sets the index number to the extern sheet (thats is what writen in documentation + * but as i saw , it works differently) * @param index extern sheet index */ - public void setIndexToSheet(short index){ + public void setUnused(short index){ field_5_index_to_sheet = index; // field_6_equals_to_index_to_sheet is equal to field_5_index_to_sheet - field_6_equals_to_index_to_sheet = index; +// field_6_equals_to_index_to_sheet = index; + } + + public short getEqualsToIndexToSheet() + { + return field_6_equals_to_index_to_sheet; + } + + public void setEqualsToIndexToSheet(short value) + { + field_6_equals_to_index_to_sheet = value; } + /** sets the custom menu length * @param length custom menu length */ @@ -277,7 +290,7 @@ public class NameRecord extends Record { /** gets the index to extern sheet * @return index to extern sheet */ - public short getIndexToSheet(){ + public short getUnused(){ return field_5_index_to_sheet; } @@ -326,10 +339,14 @@ public class NameRecord extends Record { /** gets the definition, reference (Formula) * @return definition -- can be null if we cant parse ptgs */ - protected List getNameDefinition() { + public List getNameDefinition() { return field_13_name_definition; } + public void setNameDefinition(Stack nameDefinition) { + field_13_name_definition = nameDefinition; + } + /** get the custom menu text * @return custom menu text */ @@ -379,57 +396,64 @@ public class NameRecord extends Record { * @param data byte array containing instance data * @return number of bytes written */ - public int serialize(int offset, byte[] data) { - LittleEndian.putShort(data, 0 + offset, sid); + public int serialize( int offset, byte[] data ) + { + LittleEndian.putShort( data, 0 + offset, sid ); // size defined below - LittleEndian.putShort(data, 4 + offset, getOptionFlag()); + LittleEndian.putShort( data, 4 + offset, getOptionFlag() ); data[6 + offset] = getKeyboardShortcut(); data[7 + offset] = getNameTextLength(); - LittleEndian.putShort(data, 8 + offset, getDefinitionTextLength()); - LittleEndian.putShort(data, 10 + offset, getIndexToSheet()); - LittleEndian.putShort(data, 12 + offset, getIndexToSheet()); - data [14 + offset] = getCustomMenuLength(); - data [15 + offset] = getDescriptionTextLength(); - data [16 + offset] = getHelpTopicLength(); - data [17 + offset] = getStatusBarLength(); - data [18 + offset] = getCompressedUnicodeFlag(); - - if ( ( field_1_option_flag & (short)0x20 ) != 0 ) { - LittleEndian.putShort(data, 2 + offset, (short)( 16 + field_13_raw_name_definition.length )); - - data [19 + offset] = field_12_builtIn_name; + LittleEndian.putShort( data, 8 + offset, getDefinitionTextLength() ); + LittleEndian.putShort( data, 10 + offset, getUnused() ); + LittleEndian.putShort( data, 12 + offset, getEqualsToIndexToSheet() ); + data[14 + offset] = getCustomMenuLength(); + data[15 + offset] = getDescriptionTextLength(); + data[16 + offset] = getHelpTopicLength(); + data[17 + offset] = getStatusBarLength(); + data[18 + offset] = getCompressedUnicodeFlag(); + + /* temp: gjs + if ( ( field_1_option_flag & (short) 0x20 ) != 0 ) + { + LittleEndian.putShort( data, 2 + offset, (short) ( 16 + field_13_raw_name_definition.length ) ); + + data[19 + offset] = field_12_builtIn_name; System.arraycopy( field_13_raw_name_definition, 0, data, 20 + offset, field_13_raw_name_definition.length ); - + return 20 + field_13_raw_name_definition.length; - } - else { - LittleEndian.putShort(data, 2 + offset, (short)( 15 + getTextsLength())); - - - StringUtil.putCompressedUnicode(getNameText(), data , 19 + offset); - - int start_of_name_definition = 19 + field_3_length_name_text; - if (this.field_13_name_definition != null) { - serializePtgs(data, start_of_name_definition + offset); - } else { - System.arraycopy(field_13_raw_name_definition,0,data - ,start_of_name_definition + offset,field_13_raw_name_definition.length); + } + else + { */ + LittleEndian.putShort( data, 2 + offset, (short) ( 15 + getTextsLength() ) ); + + + StringUtil.putCompressedUnicode( getNameText(), data, 19 + offset ); + + int start_of_name_definition = 19 + field_3_length_name_text; + if ( this.field_13_name_definition != null ) + { + serializePtgs( data, start_of_name_definition + offset ); } - - int start_of_custom_menu_text = start_of_name_definition + field_4_length_name_definition; - StringUtil.putCompressedUnicode(getCustomMenuText(), data , start_of_custom_menu_text + offset); - - int start_of_description_text = start_of_custom_menu_text + field_8_length_description_text; - StringUtil.putCompressedUnicode(getDescriptionText(), data , start_of_description_text + offset); - - int start_of_help_topic_text = start_of_description_text + field_9_length_help_topic_text; - StringUtil.putCompressedUnicode(getHelpTopicText(), data , start_of_help_topic_text + offset); - - int start_of_status_bar_text = start_of_help_topic_text + field_10_length_status_bar_text; - StringUtil.putCompressedUnicode(getStatusBarText(), data , start_of_status_bar_text + offset); - - return getRecordSize(); - } + else + { + System.arraycopy( field_13_raw_name_definition, 0, data + , start_of_name_definition + offset, field_13_raw_name_definition.length ); + } + + int start_of_custom_menu_text = start_of_name_definition + field_4_length_name_definition; + StringUtil.putCompressedUnicode( getCustomMenuText(), data, start_of_custom_menu_text + offset ); + + int start_of_description_text = start_of_custom_menu_text + field_8_length_description_text; + StringUtil.putCompressedUnicode( getDescriptionText(), data, start_of_description_text + offset ); + + int start_of_help_topic_text = start_of_description_text + field_9_length_help_topic_text; + StringUtil.putCompressedUnicode( getHelpTopicText(), data, start_of_help_topic_text + offset ); + + int start_of_status_bar_text = start_of_help_topic_text + field_10_length_status_bar_text; + StringUtil.putCompressedUnicode( getStatusBarText(), data, start_of_status_bar_text + offset ); + + return getRecordSize(); + /* } */ } private void serializePtgs(byte [] data, int offset) { @@ -594,28 +618,30 @@ public class NameRecord extends Record { field_9_length_help_topic_text = data [12 + offset]; field_10_length_status_bar_text = data [13 + offset]; - + + /* + temp: gjs if ( ( field_1_option_flag & (short)0x20 ) != 0 ) { // DEBUG // System.out.println( "Built-in name" ); - + field_11_compressed_unicode_flag = data[ 14 + offset ]; field_12_builtIn_name = data[ 15 + offset ]; if ( (field_12_builtIn_name & (short)0x07) != 0 ) { field_12_name_text = "Print_Titles"; - + // DEBUG // System.out.println( field_12_name_text ); - + field_13_raw_name_definition = new byte[ field_4_length_name_definition ]; System.arraycopy( data, 16 + offset, field_13_raw_name_definition, 0, field_13_raw_name_definition.length ); - + // DEBUG // System.out.println( HexDump.toHex( field_13_raw_name_definition ) ); } } - else { + else { */ field_11_compressed_unicode_flag= data [14 + offset]; field_12_name_text = new String(data, 15 + offset, @@ -640,11 +666,11 @@ public class NameRecord extends Record { int start_of_status_bar_text = start_of_help_topic_text + field_10_length_status_bar_text; field_17_status_bar_text = new String(data, start_of_status_bar_text + offset, LittleEndian.ubyteToInt(field_10_length_status_bar_text)); - } + /*} */ } private Stack getParsedExpressionTokens(byte [] data, short size, - int offset, int start_of_expression) { + int offset, int start_of_expression) { Stack stack = new Stack(); int pos = start_of_expression + offset; int sizeCounter = 0; @@ -739,7 +765,7 @@ public class NameRecord extends Record { .append("\n"); buffer.append(" .size of the formula data = ").append( field_4_length_name_definition ) .append("\n"); - buffer.append(" .unused = ").append( field_5_index_to_sheet ) + buffer.append(" .unused = ").append( field_5_index_to_sheet ) .append("\n"); buffer.append(" .( 0 = Global name, otherwise index to sheet (one-based) ) = ").append( field_6_equals_to_index_to_sheet ) .append("\n"); diff --git a/src/java/org/apache/poi/hssf/record/PaneRecord.java b/src/java/org/apache/poi/hssf/record/PaneRecord.java new file mode 100644 index 0000000000..4ad572c576 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/PaneRecord.java @@ -0,0 +1,317 @@ + +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ + + +package org.apache.poi.hssf.record; + + + +import org.apache.poi.util.*; + +/** + * Describes the frozen and unfozen panes. + * NOTE: This source is automatically generated please do not modify this file. Either subclass or + * remove the record in src/records/definitions. + + * @author Glen Stampoultzis (glens at apache.org) + */ +public class PaneRecord + extends Record +{ + public final static short sid = 0x41; + private short field_1_x; + private short field_2_y; + private short field_3_topRow; + private short field_4_leftColumn; + private short field_5_activePane; + public final static short ACTIVE_PANE_LOWER_RIGHT = 0; + public final static short ACTIVE_PANE_UPPER_RIGHT = 1; + public final static short ACTIVE_PANE_LOWER_LEFT = 2; + public final static short ACTIVE_PANE_UPER_LEFT = 3; + + + public PaneRecord() + { + + } + + /** + * Constructs a Pane record and sets its fields appropriately. + * + * @param id id must be 0x41 or an exception + * will be throw upon validation + * @param size size the size of the data area of the record + * @param data data of the record (should not contain sid/len) + */ + + public PaneRecord(short id, short size, byte [] data) + { + super(id, size, data); + + } + + /** + * Constructs a Pane record and sets its fields appropriately. + * + * @param id id must be 0x41 or an exception + * will be throw upon validation + * @param size size the size of the data area of the record + * @param data data of the record (should not contain sid/len) + * @param offset of the record's data + */ + + public PaneRecord(short id, short size, byte [] data, int offset) + { + super(id, size, data, offset); + + } + + /** + * Checks the sid matches the expected side for this record + * + * @param id the expected sid. + */ + protected void validateSid(short id) + { + if (id != sid) + { + throw new RecordFormatException("Not a Pane record"); + } + } + + protected void fillFields(byte [] data, short size, int offset) + { + + int pos = 0; + field_1_x = LittleEndian.getShort(data, pos + 0x0 + offset); + field_2_y = LittleEndian.getShort(data, pos + 0x2 + offset); + field_3_topRow = LittleEndian.getShort(data, pos + 0x4 + offset); + field_4_leftColumn = LittleEndian.getShort(data, pos + 0x6 + offset); + field_5_activePane = LittleEndian.getShort(data, pos + 0x8 + offset); + + } + + public String toString() + { + StringBuffer buffer = new StringBuffer(); + + buffer.append("[PANE]\n"); + buffer.append(" .x = ") + .append("0x").append(HexDump.toHex( getX ())) + .append(" (").append( getX() ).append(" )"); + buffer.append(System.getProperty("line.separator")); + buffer.append(" .y = ") + .append("0x").append(HexDump.toHex( getY ())) + .append(" (").append( getY() ).append(" )"); + buffer.append(System.getProperty("line.separator")); + buffer.append(" .topRow = ") + .append("0x").append(HexDump.toHex( getTopRow ())) + .append(" (").append( getTopRow() ).append(" )"); + buffer.append(System.getProperty("line.separator")); + buffer.append(" .leftColumn = ") + .append("0x").append(HexDump.toHex( getLeftColumn ())) + .append(" (").append( getLeftColumn() ).append(" )"); + buffer.append(System.getProperty("line.separator")); + buffer.append(" .activePane = ") + .append("0x").append(HexDump.toHex( getActivePane ())) + .append(" (").append( getActivePane() ).append(" )"); + buffer.append(System.getProperty("line.separator")); + + buffer.append("[/PANE]\n"); + return buffer.toString(); + } + + public int serialize(int offset, byte[] data) + { + int pos = 0; + + LittleEndian.putShort(data, 0 + offset, sid); + LittleEndian.putShort(data, 2 + offset, (short)(getRecordSize() - 4)); + + LittleEndian.putShort(data, 4 + offset + pos, field_1_x); + LittleEndian.putShort(data, 6 + offset + pos, field_2_y); + LittleEndian.putShort(data, 8 + offset + pos, field_3_topRow); + LittleEndian.putShort(data, 10 + offset + pos, field_4_leftColumn); + LittleEndian.putShort(data, 12 + offset + pos, field_5_activePane); + + return getRecordSize(); + } + + /** + * Size of record (exluding 4 byte header) + */ + public int getRecordSize() + { + return 4 + 2 + 2 + 2 + 2 + 2; + } + + public short getSid() + { + return this.sid; + } + + public Object clone() { + PaneRecord rec = new PaneRecord(); + + rec.field_1_x = field_1_x; + rec.field_2_y = field_2_y; + rec.field_3_topRow = field_3_topRow; + rec.field_4_leftColumn = field_4_leftColumn; + rec.field_5_activePane = field_5_activePane; + return rec; + } + + + + + /** + * Get the x field for the Pane record. + */ + public short getX() + { + return field_1_x; + } + + /** + * Set the x field for the Pane record. + */ + public void setX(short field_1_x) + { + this.field_1_x = field_1_x; + } + + /** + * Get the y field for the Pane record. + */ + public short getY() + { + return field_2_y; + } + + /** + * Set the y field for the Pane record. + */ + public void setY(short field_2_y) + { + this.field_2_y = field_2_y; + } + + /** + * Get the top row field for the Pane record. + */ + public short getTopRow() + { + return field_3_topRow; + } + + /** + * Set the top row field for the Pane record. + */ + public void setTopRow(short field_3_topRow) + { + this.field_3_topRow = field_3_topRow; + } + + /** + * Get the left column field for the Pane record. + */ + public short getLeftColumn() + { + return field_4_leftColumn; + } + + /** + * Set the left column field for the Pane record. + */ + public void setLeftColumn(short field_4_leftColumn) + { + this.field_4_leftColumn = field_4_leftColumn; + } + + /** + * Get the active pane field for the Pane record. + * + * @return One of + * ACTIVE_PANE_LOWER_RIGHT + * ACTIVE_PANE_UPPER_RIGHT + * ACTIVE_PANE_LOWER_LEFT + * ACTIVE_PANE_UPER_LEFT + */ + public short getActivePane() + { + return field_5_activePane; + } + + /** + * Set the active pane field for the Pane record. + * + * @param field_5_activePane + * One of + * ACTIVE_PANE_LOWER_RIGHT + * ACTIVE_PANE_UPPER_RIGHT + * ACTIVE_PANE_LOWER_LEFT + * ACTIVE_PANE_UPER_LEFT + */ + public void setActivePane(short field_5_activePane) + { + this.field_5_activePane = field_5_activePane; + } + + +} // END OF CLASS + + + + diff --git a/src/java/org/apache/poi/hssf/record/SelectionRecord.java b/src/java/org/apache/poi/hssf/record/SelectionRecord.java index 9db020fd5a..48936860cc 100644 --- a/src/java/org/apache/poi/hssf/record/SelectionRecord.java +++ b/src/java/org/apache/poi/hssf/record/SelectionRecord.java @@ -64,11 +64,11 @@ import org.apache.poi.util.LittleEndian; * Description: shows the user's selection on the sheet * for write set num refs to 0

    * - * TODO : Implement reference subrecords + * TODO : Fully implement reference subrecords. * REFERENCE: PG 291 Microsoft Excel 97 Developer's Kit (ISBN: 1-57231-498-2)

    * @author Andrew C. Oliver (acoliver at apache dot org) * @author Jason Height (jheight at chariot dot net dot au) - * @version 2.0-pre + * @author Glen Stampoultzis (glens at apache.org) */ public class SelectionRecord @@ -264,10 +264,10 @@ public class SelectionRecord LittleEndian.putShort(data, 7 + offset, getActiveCellCol()); LittleEndian.putShort(data, 9 + offset, getActiveCellRef()); LittleEndian.putShort(data, 11 + offset, ( short ) 1); - LittleEndian.putShort(data, 13 + offset, ( short ) 0); - LittleEndian.putShort(data, 15 + offset, ( short ) 0); - data[ 17 + offset ] = 0; - data[ 18 + offset ] = 0; + LittleEndian.putShort(data, 13 + offset, ( short ) getActiveCellRow()); + LittleEndian.putShort(data, 15 + offset, ( short ) getActiveCellRow()); + data[ 17 + offset ] = (byte)getActiveCellCol(); + data[ 18 + offset ] = (byte)getActiveCellCol(); return getRecordSize(); } diff --git a/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java b/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java index 43cb92cd0f..0a80def422 100644 --- a/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java @@ -204,7 +204,7 @@ public class AreaPtg /** * sets the first row to relative or not - * @param isRelative or not. + * @param rel is relative or not. */ public void setFirstRowRelative(boolean rel) { field_3_first_column=rowRelative.setShortBoolean(field_3_first_column,rel); diff --git a/src/java/org/apache/poi/hssf/record/formula/AttrPtg.java b/src/java/org/apache/poi/hssf/record/formula/AttrPtg.java index 9cac2b21e9..b87e250696 100644 --- a/src/java/org/apache/poi/hssf/record/formula/AttrPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/AttrPtg.java @@ -98,9 +98,6 @@ public class AttrPtg offset++; // adjust past id field_1_options = data[ offset + 0 ]; field_2_data = LittleEndian.getShort(data, offset + 1); - System.out.println("OPTIONS = " + Integer.toHexString(getOptions())); - System.out.println("OPTIONS & 0x10 = " + (getOptions() & 0x10)); - //System.out.println(toString()); } public void setOptions(byte options) diff --git a/src/java/org/apache/poi/hssf/record/formula/ControlPtg.java b/src/java/org/apache/poi/hssf/record/formula/ControlPtg.java new file mode 100644 index 0000000000..a33ceeca54 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/ControlPtg.java @@ -0,0 +1,7 @@ +package org.apache.poi.hssf.record.formula; + +public abstract class ControlPtg + extends Ptg +{ + +} diff --git a/src/java/org/apache/poi/hssf/record/formula/MemFuncPtg.java b/src/java/org/apache/poi/hssf/record/formula/MemFuncPtg.java new file mode 100644 index 0000000000..55f14c94cf --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/MemFuncPtg.java @@ -0,0 +1,131 @@ +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ + +/* + * Ptg.java + * + * Created on October 28, 2001, 6:30 PM + */ +package org.apache.poi.hssf.record.formula; + +import org.apache.poi.util.LittleEndian; +import org.apache.poi.hssf.util.SheetReferences; + +/** + * @author Glen Stampoultzis (glens at apache.org) + */ +public class MemFuncPtg extends ControlPtg +{ + + public final static byte sid = 0x29; + private short field_1_len_ref_subexpression = 0; + + public MemFuncPtg() + { + //Required for clone methods + } + + /**Creates new function pointer from a byte array + * usually called while reading an excel file. + */ + public MemFuncPtg( byte[] data, int offset ) + { + offset++; + field_1_len_ref_subexpression = LittleEndian.getShort( data, offset + 0 ); + } + + public int getSize() + { + return 3; + } + + public void writeBytes( byte[] array, int offset ) + { + array[offset + 0] = sid ; + LittleEndian.putShort( array, offset + 1, (short)field_1_len_ref_subexpression ); + } + + public String toFormulaString( SheetReferences refs ) + { + return ""; + } + + public byte getDefaultOperandClass() + { + return 0; + } + + public int getNumberOfOperands() + { + return field_1_len_ref_subexpression; + } + + public Object clone() + { + MemFuncPtg ptg = new MemFuncPtg(); + ptg.field_1_len_ref_subexpression = this.field_1_len_ref_subexpression; + return ptg; + } + + public int getLenRefSubexpression() + { + return field_1_len_ref_subexpression; + } + + public void setLenRefSubexpression(int len) + { + field_1_len_ref_subexpression = (short)len; + } + +} \ No newline at end of file diff --git a/src/java/org/apache/poi/hssf/record/formula/Ptg.java b/src/java/org/apache/poi/hssf/record/formula/Ptg.java index fa0e5ba3c5..ff58021e71 100644 --- a/src/java/org/apache/poi/hssf/record/formula/Ptg.java +++ b/src/java/org/apache/poi/hssf/record/formula/Ptg.java @@ -214,6 +214,14 @@ public abstract class Ptg retval = new ParenthesisPtg(data, offset); break; + case MemFuncPtg.sid : + retval = new MemFuncPtg(data, offset); + break; + + case UnionPtg.sid : + retval = new UnionPtg(data, offset); + break; + case FuncPtg.sid : retval = new FuncPtg(data, offset); break; @@ -224,7 +232,7 @@ public abstract class Ptg case arrayFunc : retval = new FuncPtg(data, offset); break; - + case FuncVarPtg.sid : retval = new FuncVarPtg(data, offset); break; diff --git a/src/java/org/apache/poi/hssf/record/formula/UnionPtg.java b/src/java/org/apache/poi/hssf/record/formula/UnionPtg.java new file mode 100644 index 0000000000..4c8d0c06d9 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/UnionPtg.java @@ -0,0 +1,120 @@ +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ + +package org.apache.poi.hssf.record.formula; + +import org.apache.poi.hssf.util.SheetReferences; + +/** + * @author Glen Stampoultzis (glens at apache.org) + */ +public class UnionPtg extends OperationPtg +{ + public final static byte sid = 0x10; + + + public UnionPtg() + { + } + + public UnionPtg(byte [] data, int offset) + { + // doesn't need anything + } + + + public int getSize() + { + return 1; + } + + public void writeBytes( byte[] array, int offset ) + { + array[ offset + 0 ] = sid; + } + + public Object clone() + { + return new UnionPtg(); + } + + public int getType() + { + return TYPE_BINARY; + } + + /** Implementation of method from Ptg */ + public String toFormulaString(SheetReferences refs) + { + return ","; + } + + + /** implementation of method from OperationsPtg*/ + public String toFormulaString(String[] operands) + { + StringBuffer buffer = new StringBuffer(); + + buffer.append(operands[ 0 ]); + buffer.append(","); + buffer.append(operands[ 1 ]); + return buffer.toString(); + } + + public int getNumberOfOperands() + { + return 2; + } + +} diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java index 17b24aadb1..cacf9d79de 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java @@ -88,6 +88,12 @@ public class HSSFSheet public static final short TopMargin = Sheet.TopMargin; public static final short BottomMargin = Sheet.BottomMargin; + public static final byte PANE_LOWER_RIGHT = (byte)0; + public static final byte PANE_UPPER_RIGHT = (byte)1; + public static final byte PANE_LOWER_LEFT = (byte)2; + public static final byte PANE_UPPER_LEFT = (byte)3; + + /** * Used for compile-time optimization. This is the initial size for the collection of * rows. It is currently set to 20. If you generate larger sheets you may benefit @@ -537,9 +543,9 @@ public class HSSFSheet return record.getHCenter(); } - - - + + + /** * removes a merged region of cells (hence letting them free) * @param index of the region to unmerge @@ -860,6 +866,27 @@ public class HSSFSheet getSheet().setMargin( margin, size ); } + /** + * Sets the zoom magnication for the sheet. The zoom is expressed as a + * fraction. For example to express a zoom of 75% use 3 for the numerator + * and 4 for the denominator. + * + * @param numerator The numerator for the zoom magnification. + * @param denominator The denominator for the zoom magnification. + */ + public void setZoom( int numerator, int denominator) + { + if (numerator < 1 || numerator > 65535) + throw new IllegalArgumentException("Numerator must be greater than 1 and less than 65536"); + if (denominator < 1 || denominator > 65535) + throw new IllegalArgumentException("Denominator must be greater than 1 and less than 65536"); + + SCLRecord sclRecord = new SCLRecord(); + sclRecord.setNumerator((short)numerator); + sclRecord.setDenominator((short)denominator); + getSheet().setSCLRecord(sclRecord); + } + /** * Shifts rows between startRow and endRow n number of rows. * If you use a negative number, it will shift rows up. @@ -920,4 +947,50 @@ public class HSSFSheet int window2Loc = sheet.findFirstRecordLocBySid( WindowTwoRecord.sid ); sheet.getRecords().addAll( window2Loc, records ); } + + /** + * Creates a split (freezepane). + * @param colSplit Horizonatal position of split. + * @param rowSplit Vertical position of split. + * @param topRow Top row visible in bottom pane + * @param leftmostColumn Left column visible in right pane. + */ + public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow ) + { + if (colSplit < 0 || colSplit > 255) throw new IllegalArgumentException("Column must be between 0 and 255"); + if (rowSplit < 0 || rowSplit > 65535) throw new IllegalArgumentException("Row must be between 0 and 65535"); + if (leftmostColumn < colSplit) throw new IllegalArgumentException("leftmostColumn parameter must not be less than colSplit parameter"); + if (topRow < rowSplit) throw new IllegalArgumentException("topRow parameter must not be less than leftmostColumn parameter"); + getSheet().createFreezePane( colSplit, rowSplit, topRow, leftmostColumn ); + } + + /** + * Creates a split (freezepane). + * @param colSplit Horizonatal position of split. + * @param rowSplit Vertical position of split. + */ + public void createFreezePane( int colSplit, int rowSplit ) + { + createFreezePane( colSplit, rowSplit, colSplit, rowSplit ); + } + + /** + * Creates a split pane. + * @param xSplitPos Horizonatal position of split (in 1/20th of a point). + * @param ySplitPos Vertical position of split (in 1/20th of a point). + * @param topRow Top row visible in bottom pane + * @param leftmostColumn Left column visible in right pane. + * @param activePane Active pane. One of: PANE_LOWER_RIGHT, + * PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT + * @see #PANE_LOWER_LEFT + * @see #PANE_LOWER_RIGHT + * @see #PANE_UPPER_LEFT + * @see #PANE_UPPER_RIGHT + */ + public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane ) + { + getSheet().createSplitPane( xSplitPos, ySplitPos, topRow, leftmostColumn, activePane ); + } + + } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index 777b53f31c..914e9947f4 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -64,6 +64,9 @@ import org.apache.poi.hssf.eventmodel.EventRecordFactory; import org.apache.poi.hssf.model.Sheet; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.*; +import org.apache.poi.hssf.record.formula.MemFuncPtg; +import org.apache.poi.hssf.record.formula.Area3DPtg; +import org.apache.poi.hssf.record.formula.UnionPtg; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.poifs.filesystem.Entry; import org.apache.poi.poifs.filesystem.DirectoryEntry; @@ -78,6 +81,7 @@ import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import java.util.Iterator; +import java.util.Stack; /** * High level representation of a workbook. This is the first object most users @@ -461,6 +465,158 @@ public class HSSFWorkbook : true; } + /** + * Sets the repeating rows and columns for a sheet (as found in + * File->PageSetup->Sheet). This is function is included in the workbook + * because it creates/modifies name records which are stored at the + * workbook level. + *

    + * To set just repeating columns: + * 

    +     *  workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
+     *
    + * To set just repeating rows: + * 
    +     *  workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
+     *
    + * To remove all repeating rows and columns for a sheet. + * 
    +     *  workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);
+     *
    + * + * @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) + { + // 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 >= 0xFF) throw new IllegalArgumentException("Invalid column range specification"); + if (endColumn < -1 || endColumn >= 0xFF) throw new IllegalArgumentException("Invalid column range specification"); + if (startRow < -1 || startRow > 65535) throw new IllegalArgumentException("Invalid row range specification"); + if (endRow < -1 || endRow > 65535) throw new IllegalArgumentException("Invalid row range specification"); + if (startColumn > endColumn) throw new IllegalArgumentException("Invalid column range specification"); + if (startRow > endRow) throw new IllegalArgumentException("Invalid row range specification"); + + HSSFSheet sheet = getSheetAt(sheetIndex); + short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex); + + boolean settingRowAndColumn = + startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1; + boolean removingRange = + startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1; + + boolean isNewRecord = false; + NameRecord nameRecord; + nameRecord = findExistingRowColHeaderNameRecord(sheetIndex); + if (removingRange ) + { + if (nameRecord != null) + workbook.removeName(findExistingRowColHeaderNameRecordIdx(sheetIndex)); + return; + } + if ( nameRecord == null ) + { + nameRecord = workbook.createName(); + isNewRecord = true; + } + nameRecord.setOptionFlag((short)0x20); + nameRecord.setKeyboardShortcut((byte)0); + short definitionTextLength = settingRowAndColumn ? (short)0x001a : (short)0x000b; + nameRecord.setDefinitionTextLength(definitionTextLength); + nameRecord.setNameTextLength((byte)1); + nameRecord.setNameText(((char)7) + ""); + nameRecord.setUnused((short)0); + nameRecord.setEqualsToIndexToSheet((short)(externSheetIndex+1)); + nameRecord.setCustomMenuLength((byte)0); + nameRecord.setDescriptionTextLength((byte)0); + nameRecord.setHelpTopicLength((byte)0); + nameRecord.setStatusBarLength((byte)0); + Stack ptgs = new Stack(); + + if (settingRowAndColumn) + { + MemFuncPtg memFuncPtg = new MemFuncPtg(); + memFuncPtg.setLenRefSubexpression(23); + ptgs.add(memFuncPtg); + } + if (startColumn >= 0) + { + Area3DPtg area3DPtg1 = new Area3DPtg(); + area3DPtg1.setExternSheetIndex(externSheetIndex); + area3DPtg1.setFirstColumn((short)startColumn); + area3DPtg1.setLastColumn((short)endColumn); + area3DPtg1.setFirstRow((short)0); + area3DPtg1.setLastRow((short)0xFFFF); + ptgs.add(area3DPtg1); + } + if (startRow >= 0) + { + Area3DPtg area3DPtg2 = new Area3DPtg(); + area3DPtg2.setExternSheetIndex(externSheetIndex); + area3DPtg2.setFirstColumn((short)0); + area3DPtg2.setLastColumn((short)0x00FF); + area3DPtg2.setFirstRow((short)startRow); + area3DPtg2.setLastRow((short)endRow); + ptgs.add(area3DPtg2); + } + if (settingRowAndColumn) + { + UnionPtg unionPtg = new UnionPtg(); + ptgs.add(unionPtg); + } + nameRecord.setNameDefinition(ptgs); + + if (isNewRecord) + { + HSSFName newName = new HSSFName(workbook, nameRecord); + names.add(newName); + } + + HSSFPrintSetup printSetup = sheet.getPrintSetup(); + printSetup.setValidSettings(false); + + WindowTwoRecord w2 = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid); + w2.setPaged(true); + } + + private NameRecord findExistingRowColHeaderNameRecord( int sheetIndex ) + { + int index = findExistingRowColHeaderNameRecordIdx(sheetIndex); + if (index == -1) + return null; + else + return (NameRecord)workbook.findNextRecordBySid(NameRecord.sid, index); + } + + private int findExistingRowColHeaderNameRecordIdx( int sheetIndex ) + { + int index = 0; + NameRecord r = null; + while ((r = (NameRecord) workbook.findNextRecordBySid(NameRecord.sid, index)) != null) + { + int nameRecordSheetIndex = workbook.getSheetIndexFromExternSheetIndex(r.getEqualsToIndexToSheet() - 1); + if (isRowColHeaderRecord( r ) && nameRecordSheetIndex == sheetIndex) + { + return index; + } + index++; + } + + return -1; + } + + private boolean isRowColHeaderRecord( NameRecord r ) + { + return r.getOptionFlag() == 0x20 && ("" + ((char)7)).equals(r.getNameText()); + } + /** * create a new Font and add it to the workbook's font table * @return new font object @@ -812,4 +968,6 @@ public class HSSFWorkbook workbook.getRecords().add(loc, r); } + + } diff --git a/src/records/definitions/pane_record.xml b/src/records/definitions/pane_record.xml new file mode 100644 index 0000000000..f7b32b7d62 --- /dev/null +++ b/src/records/definitions/pane_record.xml @@ -0,0 +1,18 @@ + + Record + Record + Describes the frozen and unfozen panes. + Glen Stampoultzis (glens at apache.org) + + + + + + + + + + + + + diff --git a/src/testcases/org/apache/poi/hssf/record/TestPaneRecord.java b/src/testcases/org/apache/poi/hssf/record/TestPaneRecord.java new file mode 100644 index 0000000000..4bf1856c5e --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/TestPaneRecord.java @@ -0,0 +1,117 @@ + +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ + + +package org.apache.poi.hssf.record; + + +import junit.framework.TestCase; + +/** + * Tests the serialization and deserialization of the PaneRecord + * class works correctly. Test data taken directly from a real + * Excel file. + * + * @author Glen Stampoultzis (glens at apache.org) + */ +public class TestPaneRecord + extends TestCase +{ + byte[] data = new byte[] { + (byte)0x01, (byte)0x00, + (byte)0x02, (byte)0x00, + (byte)0x03, (byte)0x00, + (byte)0x04, (byte)0x00, + (byte)0x02, (byte)0x00, + }; + + public TestPaneRecord(String name) + { + super(name); + } + + public void testLoad() + throws Exception + { + PaneRecord record = new PaneRecord((short)0x41, (short)data.length, data); + + + assertEquals( (short)1, record.getX()); + assertEquals( (short)2, record.getY()); + assertEquals( (short)3, record.getTopRow()); + assertEquals( (short)4, record.getLeftColumn()); + assertEquals( PaneRecord.ACTIVE_PANE_LOWER_LEFT, record.getActivePane()); + + assertEquals( 14, record.getRecordSize() ); + + record.validateSid((short)0x41); + } + + public void testStore() + { + PaneRecord record = new PaneRecord(); + + record.setX( (short) 1); + record.setY( (short) 2); + record.setTopRow( (short) 3); + record.setLeftColumn( (short) 4); + record.setActivePane( PaneRecord.ACTIVE_PANE_LOWER_LEFT); + + byte [] recordBytes = record.serialize(); + assertEquals(recordBytes.length - 4, data.length); + for (int i = 0; i < data.length; i++) + assertEquals("At offset " + i, data[i], recordBytes[i+4]); + } +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/SanityChecker.java b/src/testcases/org/apache/poi/hssf/usermodel/SanityChecker.java index b8974c85a0..ced4113ce0 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/SanityChecker.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/SanityChecker.java @@ -58,7 +58,6 @@ import junit.framework.Assert; import org.apache.poi.hssf.model.Sheet; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.*; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; import java.util.List; @@ -68,15 +67,27 @@ import java.util.List; public class SanityChecker extends Assert { - private class CheckRecord + static class CheckRecord { Class record; - char occurance; // 1 = one time, M = many times + char occurance; // 1 = one time, M = 1..many times, * = 0..many, 0 = optional + private boolean together; - public CheckRecord(Class record, char occurance) + public CheckRecord( Class record, char occurance ) + { + this(record, occurance, true); + } + + /** + * @param record The record type to check + * @param occurance The occurance 1 = occurs once, M = occurs many times + * @param together + */ + public CheckRecord(Class record, char occurance, boolean together) { this.record = record; this.occurance = occurance; + this.together = together; } public Class getRecord() @@ -88,6 +99,96 @@ public class SanityChecker { return occurance; } + + public boolean isRequired() + { + return occurance == '1' || occurance == 'M'; + } + + public boolean isOptional() + { + return occurance == '0' || occurance == '*'; + } + + public boolean isTogether() + { + return together; + } + + public boolean isMany() + { + return occurance == '*' || occurance == 'M'; + } + + public int match( List records, int recordIdx ) + { + int firstRecord = findFirstRecord(records, getRecord(), recordIdx); + if (isRequired()) + { + return matchRequired( firstRecord, records, recordIdx ); + } + else + { + return matchOptional( firstRecord, records, recordIdx ); + } + } + + private int matchOptional( int firstRecord, List records, int recordIdx ) + { + if (firstRecord == -1) + { + return recordIdx; + } + + return matchOneOrMany( records, firstRecord ); +// return matchOneOrMany( records, recordIdx ); + } + + private int matchRequired( int firstRecord, List records, int recordIdx ) + { + if (firstRecord == -1) + { + fail("Manditory record missing or out of order: " + record); + } + + return matchOneOrMany( records, firstRecord ); +// return matchOneOrMany( records, recordIdx ); + } + + private int matchOneOrMany( List records, int recordIdx ) + { + if (isZeroOrOne()) + { + // check no other records + if (findFirstRecord(records, getRecord(), recordIdx+1) != -1) + fail("More than one record matched for " + getRecord().getName()); + } + else if (isZeroToMany()) + { + if (together) + { + int nextIdx = findFirstRecord(records, record, recordIdx+1); + while (nextIdx != -1) + { + if (nextIdx - 1 != recordIdx) + fail("Records are not together " + record.getName()); + recordIdx = nextIdx; + nextIdx = findFirstRecord(records, record, recordIdx+1); + } + } + } + return recordIdx+1; + } + + private boolean isZeroToMany() + { + return occurance == '*' || occurance == 'M'; + } + + private boolean isZeroOrOne() + { + return occurance == '0' || occurance == '1'; + } } CheckRecord[] workbookRecords = new CheckRecord[] { @@ -115,8 +216,11 @@ public class SanityChecker new CheckRecord(ExtendedFormatRecord.class, 'M'), new CheckRecord(StyleRecord.class, 'M'), new CheckRecord(UseSelFSRecord.class, '1'), - new CheckRecord(BoundSheetRecord.class, '1'), // Is this right? + new CheckRecord(BoundSheetRecord.class, 'M'), new CheckRecord(CountryRecord.class, '1'), + new CheckRecord(SupBookRecord.class, '0'), + new CheckRecord(ExternSheetRecord.class, '0'), + new CheckRecord(NameRecord.class, '*'), new CheckRecord(SSTRecord.class, '1'), new CheckRecord(ExtSSTRecord.class, '1'), new CheckRecord(EOFRecord.class, '1'), @@ -147,22 +251,24 @@ public class SanityChecker new CheckRecord(EOFRecord.class, '1') }; - public void checkWorkbookRecords(Workbook workbook) + private void checkWorkbookRecords(Workbook workbook) { List records = workbook.getRecords(); assertTrue(records.get(0) instanceof BOFRecord); assertTrue(records.get(records.size() - 1) instanceof EOFRecord); checkRecordOrder(records, workbookRecords); +// checkRecordsTogether(records, workbookRecords); } - public void checkSheetRecords(Sheet sheet) + private void checkSheetRecords(Sheet sheet) { List records = sheet.getRecords(); assertTrue(records.get(0) instanceof BOFRecord); assertTrue(records.get(records.size() - 1) instanceof EOFRecord); checkRecordOrder(records, sheetRecords); +// checkRecordsTogether(records, sheetRecords); } public void checkHSSFWorkbook(HSSFWorkbook wb) @@ -173,7 +279,63 @@ public class SanityChecker } - private void checkRecordOrder(List records, CheckRecord[] check) + /* + private void checkRecordsTogether(List records, CheckRecord[] check) + { + for ( int checkIdx = 0; checkIdx < check.length; checkIdx++ ) + { + int recordIdx = findFirstRecord(records, check[checkIdx].getRecord()); + boolean notFoundAndRecordRequired = (recordIdx == -1 && check[checkIdx].isRequired()); + if (notFoundAndRecordRequired) + { + fail("Expected to find record of class " + check.getClass() + " but did not"); + } + else if (recordIdx >= 0) + { + if (check[checkIdx].isMany()) + { + // Skip records that are together + while (recordIdx < records.size() && check[checkIdx].getRecord().isInstance(records.get(recordIdx))) + recordIdx++; + } + + // Make sure record does not occur in remaining records (after the next) + recordIdx++; + for (int recordIdx2 = recordIdx; recordIdx2 < records.size(); recordIdx2++) + { + if (check[checkIdx].getRecord().isInstance(records.get(recordIdx2))) + fail("Record occurs scattered throughout record chain:\n" + records.get(recordIdx2)); + } + } + } + } */ + + private static int findFirstRecord( List records, Class record, int startIndex ) + { + for (int i = startIndex; i < records.size(); i++) + { + if (record.getName().equals(records.get(i).getClass().getName())) + return i; + } + return -1; + } + +// private static int findFirstRecord( List records, Class record ) +// { +// return findFirstRecord ( records, record, 0 ); +// } + + void checkRecordOrder(List records, CheckRecord[] check) + { + int recordIdx = 0; + for ( int checkIdx = 0; checkIdx < check.length; checkIdx++ ) + { + recordIdx = check[checkIdx].match(records, recordIdx); + } + } + + /* + void checkRecordOrder(List records, CheckRecord[] check) { int checkIndex = 0; for (int recordIndex = 0; recordIndex < records.size(); recordIndex++) @@ -186,13 +348,30 @@ public class SanityChecker // skip over duplicate records if multiples are allowed while (recordIndex+1 < records.size() && check[checkIndex].getRecord().isInstance(records.get(recordIndex+1))) recordIndex++; +// lastGoodMatch = recordIndex; + } + else if (check[checkIndex].getOccurance() == '1') + { + // Check next record to make sure there's not more than one + if (recordIndex != records.size() - 1) + { + if (check[checkIndex].getRecord().isInstance(records.get(recordIndex+1))) + { + fail("More than one occurance of record found:\n" + records.get(recordIndex).toString()); + } + } +// lastGoodMatch = recordIndex; } +// else if (check[checkIndex].getOccurance() == '0') +// { +// +// } checkIndex++; } if (checkIndex >= check.length) return; } fail("Could not find required record: " + check[checkIndex]); - } + } */ } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java index 27aa0e24fa..0b39b345e5 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java @@ -59,6 +59,8 @@ import org.apache.poi.hssf.model.Sheet; import org.apache.poi.hssf.record.HCenterRecord; import org.apache.poi.hssf.record.VCenterRecord; import org.apache.poi.hssf.record.WSBoolRecord; +import org.apache.poi.hssf.record.SCLRecord; +import org.apache.poi.hssf.record.WindowTwoRecord; import java.io.File; import java.io.FileInputStream; @@ -69,7 +71,6 @@ import java.io.FileOutputStream; * * * @author Glen Stampoultzis (glens at apache.org) - * @version %I%, %G% */ public class TestHSSFSheet @@ -318,4 +319,22 @@ public class TestHSSFSheet assertTrue( s.getRow( 3 ) == null || s.getRow( 3 ).getPhysicalNumberOfCells() == 0 ); assertEquals( s.getRow( 4 ).getPhysicalNumberOfCells(), 5 ); } + + public void testZoom() + throws Exception + { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + assertEquals(-1, sheet.getSheet().findFirstRecordLocBySid(SCLRecord.sid)); + sheet.setZoom(3,4); + assertTrue(sheet.getSheet().findFirstRecordLocBySid(SCLRecord.sid) > 0); + SCLRecord sclRecord = (SCLRecord) sheet.getSheet().findFirstRecordBySid(SCLRecord.sid); + assertEquals(3, sclRecord.getNumerator()); + assertEquals(4, sclRecord.getDenominator()); + + int sclLoc = sheet.getSheet().findFirstRecordLocBySid(SCLRecord.sid); + int window2Loc = sheet.getSheet().findFirstRecordLocBySid(WindowTwoRecord.sid); + assertTrue(sclLoc == window2Loc + 1); + + } } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java b/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java index c55a851826..3f76ec7ba3 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java @@ -89,7 +89,11 @@ public class TestNamedRange sheetName = namedRange1.getSheetName(); //Getting its reference String referece = namedRange1.getReference(); - + + // sanity check + SanityChecker c = new SanityChecker(); + c.checkHSSFWorkbook(wb); + File file = File.createTempFile("testNamedRange", ".xls"); diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestSanityChecker.java b/src/testcases/org/apache/poi/hssf/usermodel/TestSanityChecker.java new file mode 100644 index 0000000000..dcdedbca01 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestSanityChecker.java @@ -0,0 +1,212 @@ +/* ==================================================================== + * The Apache Software License, Version 1.1 + * + * Copyright (c) 2002 The Apache Software Foundation. All rights + * reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in + * the documentation and/or other materials provided with the + * distribution. + * + * 3. The end-user documentation included with the redistribution, + * if any, must include the following acknowledgment: + * "This product includes software developed by the + * Apache Software Foundation (http://www.apache.org/)." + * Alternately, this acknowledgment may appear in the software itself, + * if and wherever such third-party acknowledgments normally appear. + * + * 4. The names "Apache" and "Apache Software Foundation" and + * "Apache POI" must not be used to endorse or promote products + * derived from this software without prior written permission. For + * written permission, please contact apache@apache.org. + * + * 5. Products derived from this software may not be called "Apache", + * "Apache POI", nor may "Apache" appear in their name, without + * prior written permission of the Apache Software Foundation. + * + * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED + * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES + * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR + * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, + * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT + * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF + * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, + * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT + * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF + * SUCH DAMAGE. + * ==================================================================== + * + * This software consists of voluntary contributions made by many + * individuals on behalf of the Apache Software Foundation. For more + * information on the Apache Software Foundation, please see + * . + */ +package org.apache.poi.hssf.usermodel; + +import junit.framework.TestCase; +import junit.framework.AssertionFailedError; + +import java.util.List; +import java.util.ArrayList; + +import org.apache.poi.hssf.record.*; + +/** + * Okay, this may seem strange but I need to test my test logic. + * + * @author Glen Stampoultzis (glens at apache.org) + */ +public class TestSanityChecker + extends TestCase +{ + public TestSanityChecker( String s ) + { + super( s ); + } + + public void testCheckRecordOrder() + throws Exception + { + final SanityChecker c = new SanityChecker(); + List records = new ArrayList(); + records.add(new BOFRecord()); + records.add(new InterfaceHdrRecord()); + records.add(new BoundSheetRecord()); + records.add(new EOFRecord()); + final SanityChecker.CheckRecord[] check = { + new SanityChecker.CheckRecord(BOFRecord.class, '1'), + new SanityChecker.CheckRecord(InterfaceHdrRecord.class, '0'), + new SanityChecker.CheckRecord(BoundSheetRecord.class, 'M'), + new SanityChecker.CheckRecord(NameRecord.class, '*'), + new SanityChecker.CheckRecord(EOFRecord.class, '1'), + }; + // check pass + c.checkRecordOrder(records, check); + records.add(2, new BoundSheetRecord()); + c.checkRecordOrder(records, check); + records.remove(1); // optional record missing + c.checkRecordOrder(records, check); + records.add(3, new NameRecord()); + records.add(3, new NameRecord()); // optional multiple record occurs more than one time + c.checkRecordOrder(records, check); + + // check fail + expectFail( new Runnable() { + public void run() + { + // check optional in wrong spot + List records = new ArrayList(); + records.add(new BOFRecord()); + records.add(new BoundSheetRecord()); + records.add(new InterfaceHdrRecord()); + records.add(new EOFRecord()); + c.checkRecordOrder(records, check); + } + }); + + expectFail( new Runnable() { + public void run() + { + // check optional one off occurs more than once + List records = new ArrayList(); + records.add(new BOFRecord()); + records.add(new InterfaceHdrRecord()); + records.add(new BoundSheetRecord()); + records.add(new InterfaceHdrRecord()); + records.add(new EOFRecord()); + c.checkRecordOrder(records, check); + } + }); + + expectFail( new Runnable() { + public void run() + { + // check many scattered + List records = new ArrayList(); + records.add(new BOFRecord()); + records.add(new BoundSheetRecord()); + records.add(new NameRecord()); + records.add(new EOFRecord()); + records.add(new NameRecord()); + c.checkRecordOrder(records, check); + } + }); + + expectFail( new Runnable() { + public void run() + { + // check missing manditory + List records = new ArrayList(); + records.add(new InterfaceHdrRecord()); + records.add(new BoundSheetRecord()); + records.add(new EOFRecord()); + c.checkRecordOrder(records, check); + } + }); + + expectFail( new Runnable() { + public void run() + { + // check missing 1..many + List records = new ArrayList(); + records.add(new BOFRecord()); + records.add(new InterfaceHdrRecord()); + records.add(new EOFRecord()); + c.checkRecordOrder(records, check); + } + }); + + expectFail( new Runnable() { + public void run() + { + // check wrong order + List records = new ArrayList(); + records.add(new InterfaceHdrRecord()); + records.add(new BoundSheetRecord()); + records.add(new BOFRecord()); + records.add(new EOFRecord()); + c.checkRecordOrder(records, check); + } + }); + + expectFail( new Runnable() { + public void run() + { + // check optional record in wrong order + List records = new ArrayList(); + records.add(new BOFRecord()); + records.add(new BoundSheetRecord()); + records.add(new InterfaceHdrRecord()); + records.add(new EOFRecord()); + c.checkRecordOrder(records, check); + } + }); + + } + + private void expectFail( Runnable runnable ) + { + boolean fail = false; + try + { + runnable.run(); + fail = true; + } + catch (AssertionFailedError pass) + { + } + assertTrue(!fail); + } + +} + -- 2.39.5