/components/forks/poi/src/loci/poi/hssf/model/Sheet.java

Large files files are truncated, but you can click here to view the full file

/*
 * #%L
 * Fork of Apache Jakarta POI.
 * %%
 * Copyright (C) 2008 - 2013 Open Microscopy Environment:
 *   - Board of Regents of the University of Wisconsin-Madison
 *   - Glencoe Software, Inc.
 *   - University of Dundee
 * %%
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * #L%
 */

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */


package loci.poi.hssf.model;

import loci.poi.hssf.record.*;
import loci.poi.hssf.record.aggregates.ColumnInfoRecordsAggregate;
import loci.poi.hssf.record.aggregates.FormulaRecordAggregate;
import loci.poi.hssf.record.aggregates.RowRecordsAggregate;
import loci.poi.hssf.record.aggregates.ValueRecordsAggregate;
import loci.poi.hssf.record.formula.Ptg;
import loci.poi.hssf.util.PaneInformation;

import loci.poi.util.POILogFactory;
import loci.poi.util.POILogger;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;   // normally I don't do this, buy we literally mean ALL

/**
 * Low level model implementation of a Sheet (one workbook contains many sheets)
 * This file contains the low level binary records starting at the sheets BOF and
 * ending with the sheets EOF.  Use HSSFSheet for a high level representation.
 * <P>
 * The structures of the highlevel API use references to this to perform most of their
 * operations.  Its probably unwise to use these low level structures directly unless you
 * really know what you're doing.  I recommend you read the Microsoft Excel 97 Developer's
 * Kit (Microsoft Press) and the documentation at http://sc.openoffice.org/excelfileformat.pdf
 * before even attempting to use this.
 * <P>
 * @author  Andrew C. Oliver (acoliver at apache dot org)
 * @author  Glen Stampoultzis (glens at apache.org)
 * @author  Shawn Laubach (slaubach at apache dot org) Gridlines, Headers, Footers, PrintSetup, and Setting Default Column Styles
 * @author Jason Height (jheight at chariot dot net dot au) Clone support. DBCell & Index Record writing support
 * @author  Brian Sanders (kestrel at burdell dot org) Active Cell support
 * @author  Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little)
 *
 * @see loci.poi.hssf.model.Workbook
 * @see loci.poi.hssf.usermodel.HSSFSheet
 * @version 1.0-pre
 */

public class Sheet implements Model
{
    public static final short   LeftMargin = 0;
    public static final short   RightMargin = 1;
    public static final short   TopMargin = 2;
    public static final short   BottomMargin = 3;

    private static POILogger            log              = POILogFactory.getLogger(Sheet.class);

    protected ArrayList                  records           =     null;
              int                        preoffset         =     0;            // offset of the sheet in a new file
              int                        loc               =     0;
    protected int                        dimsloc           =     0;
    protected DimensionsRecord           dims;
    protected DefaultColWidthRecord      defaultcolwidth   =     null;
    protected DefaultRowHeightRecord     defaultrowheight  =     null;
    protected GridsetRecord              gridset           =     null;
    protected PrintSetupRecord           printSetup        =     null;
    protected HeaderRecord               header            =     null;
    protected FooterRecord               footer            =     null;
    protected PrintGridlinesRecord       printGridlines    =     null;
    protected WindowTwoRecord            windowTwo         =     null;
    protected MergeCellsRecord           merged            =     null;
    protected Margin[]                   margins           =     null;
    protected List                       mergedRecords     =     new ArrayList();
    protected int                        numMergedRegions  =     0;
    protected SelectionRecord            selection         =     null;
    protected ColumnInfoRecordsAggregate columns           =     null;
    protected ValueRecordsAggregate      cells             =     null;
    protected RowRecordsAggregate        rows              =     null;
    private   Iterator                   valueRecIterator  =     null;
    private   Iterator                   rowRecIterator    =     null;
    protected int                        eofLoc            =     0;
    protected ProtectRecord              protect           =     null;
    protected PageBreakRecord            rowBreaks         =     null;
    protected PageBreakRecord            colBreaks         =     null;
    protected ObjectProtectRecord        objprotect        =     null;
    protected ScenarioProtectRecord      scenprotect       =     null;
    protected PasswordRecord             password          =     null;

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

    /**
     * read support  (offset used as starting point for search) for low level
     * API.  Pass in an array of Record objects, the sheet number (0 based) and
     * a record offset (should be the location of the sheets BOF record).  A Sheet
     * object is constructed and passed back with all of its initialization set
     * to the passed in records and references to those records held. This function
     * is normally called via Workbook.
     *
     * @param recs array containing those records in the sheet in sequence (normally obtained from RecordFactory)
     * @param sheetnum integer specifying the sheet's number (0,1 or 2 in this release)
     * @param offset of the sheet's BOF record
     *
     * @return Sheet object with all values set to those read from the file
     *
     * @see loci.poi.hssf.model.Workbook
     * @see loci.poi.hssf.record.Record
     */
    public static Sheet createSheet(List recs, int sheetnum, int offset)
    {
        if (log.check( POILogger.DEBUG ))
            log.logFormatted(POILogger.DEBUG,
                    "Sheet createSheet (existing file) with %",
                    new Integer(recs.size()));
        Sheet     retval             = new Sheet();
        ArrayList records            = new ArrayList(recs.size() / 5);
        boolean   isfirstcell        = true;
        boolean   isfirstrow         = true;
        int       bofEofNestingLevel = 0;

        for (int k = offset; k < recs.size(); k++)
        {
            Record rec = ( Record ) recs.get(k);

            if (rec.getSid() == BOFRecord.sid)
            {
                bofEofNestingLevel++;
                if (log.check( POILogger.DEBUG ))
                    log.log(POILogger.DEBUG, "Hit BOF record. Nesting increased to " + bofEofNestingLevel);
            }
            else if (rec.getSid() == EOFRecord.sid)
            {
                --bofEofNestingLevel;
                if (log.check( POILogger.DEBUG ))
                    log.log(POILogger.DEBUG, "Hit EOF record. Nesting decreased to " + bofEofNestingLevel);
                if (bofEofNestingLevel == 0) {
                    records.add(rec);
                    retval.eofLoc = k;
                    break;
                }
            }
            else if (rec.getSid() == DimensionsRecord.sid)
            {
                // Make a columns aggregate if one hasn't ready been created.
                if (retval.columns == null)
                {
                    retval.columns = new ColumnInfoRecordsAggregate();
                    records.add(retval.columns);
                }

                retval.dims    = ( DimensionsRecord ) rec;
                retval.dimsloc = records.size();
            }
            else if (rec.getSid() == MergeCellsRecord.sid)
            {
                retval.mergedRecords.add(rec);
                retval.merged = ( MergeCellsRecord ) rec;
                retval.numMergedRegions += retval.merged.getNumAreas();
            }
            else if (rec.getSid() == ColumnInfoRecord.sid)
            {
                ColumnInfoRecord col = (ColumnInfoRecord)rec;
                if (retval.columns != null)
                {
                    rec = null; //only add the aggregate once
                }
                else
                {
                    rec = retval.columns = new ColumnInfoRecordsAggregate();
                }
                retval.columns.insertColumn(col);
            }
            else if (rec.getSid() == DefaultColWidthRecord.sid)
            {
                retval.defaultcolwidth = ( DefaultColWidthRecord ) rec;
            }
            else if (rec.getSid() == DefaultRowHeightRecord.sid)
            {
                retval.defaultrowheight = ( DefaultRowHeightRecord ) rec;
            }
            else if ( rec.isValue() && bofEofNestingLevel == 1 )
            {
                if ( isfirstcell )
                {
                    retval.cells = new ValueRecordsAggregate();
                    rec = retval.cells;
                    retval.cells.construct( k, recs );
                    isfirstcell = false;
                }
                else
                {
                    rec = null;
                }
            }
            else if ( rec.getSid() == StringRecord.sid )
            {
                rec = null;
            }
            else if ( rec.getSid() == RowRecord.sid )
            {
                RowRecord row = (RowRecord)rec;
                if (!isfirstrow) rec = null; //only add the aggregate once

                if ( isfirstrow )
                {
                    retval.rows = new RowRecordsAggregate();
                    rec = retval.rows;                    
                    isfirstrow = false;
                }
                retval.rows.insertRow(row);
            }
            else if ( rec.getSid() == PrintGridlinesRecord.sid )
            {
                retval.printGridlines = (PrintGridlinesRecord) rec;
            }
            else if ( rec.getSid() == GridsetRecord.sid )
            {
                retval.gridset = (GridsetRecord) rec;
            }            
            else if ( rec.getSid() == HeaderRecord.sid && bofEofNestingLevel == 1)
            {
                retval.header = (HeaderRecord) rec;
            }
            else if ( rec.getSid() == FooterRecord.sid && bofEofNestingLevel == 1)
            {
                retval.footer = (FooterRecord) rec;
            }
            else if ( rec.getSid() == PrintSetupRecord.sid && bofEofNestingLevel == 1)
            {
                retval.printSetup = (PrintSetupRecord) rec;
            }
            else if ( rec.getSid() == LeftMarginRecord.sid)
            {
                retval.getMargins()[LeftMargin] = (LeftMarginRecord) rec;
            }
            else if ( rec.getSid() == RightMarginRecord.sid)
            {
                retval.getMargins()[RightMargin] = (RightMarginRecord) rec;
            }
            else if ( rec.getSid() == TopMarginRecord.sid)
            {
                retval.getMargins()[TopMargin] = (TopMarginRecord) rec;
            }
            else if ( rec.getSid() == BottomMarginRecord.sid)
            {
                retval.getMargins()[BottomMargin] = (BottomMarginRecord) rec;
            }
            else if ( rec.getSid() == SelectionRecord.sid )
            {
                retval.selection = (SelectionRecord) rec;
            }
            else if ( rec.getSid() == WindowTwoRecord.sid )
            {
                retval.windowTwo = (WindowTwoRecord) rec;
            }
            else if ( rec.getSid() == DBCellRecord.sid )
            {
                rec = null;
            }
            else if ( rec.getSid() == IndexRecord.sid )
            {
                rec = null;
            }
            
			else if ( rec.getSid() == ProtectRecord.sid )
			{
				retval.protect = (ProtectRecord) rec;
			} 
			else if ( rec.getSid() == ObjectProtectRecord.sid )
			{
				retval.objprotect = (ObjectProtectRecord) rec;
			} 
			else if ( rec.getSid() == ScenarioProtectRecord.sid )
			{
				retval.scenprotect = (ScenarioProtectRecord) rec;
			} 
			else if ( rec.getSid() == PasswordRecord.sid )
			{
				retval.password = (PasswordRecord) rec;
			} 
			else if (rec.getSid() == PageBreakRecord.HORIZONTAL_SID) 
			{	
				retval.rowBreaks = (PageBreakRecord)rec;				
			}
			else if (rec.getSid() == PageBreakRecord.VERTICAL_SID) 
			{	
				retval.colBreaks = (PageBreakRecord)rec;				
			}
            
            if (rec != null)
            {
                records.add(rec);
            }
        }
        retval.records = records;
//        if (retval.rows == null)
//        {
//            retval.rows = new RowRecordsAggregate();
//        }
        retval.checkCells();
        retval.checkRows();
//        if (retval.cells == null)
//        {
//            retval.cells = new ValueRecordsAggregate();
//        }
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "sheet createSheet (existing file) exited");
        return retval;
    }

    /**
     * Clones the low level records of this sheet and returns the new sheet instance.
     * This method is implemented by adding methods for deep cloning to all records that
     * can be added to a sheet. The <b>Record</b> object does not implement cloneable. 
     * When adding a new record, implement a public clone method if and only if the record
     * belongs to a sheet. 
     */
    public Sheet cloneSheet()
    {
      ArrayList clonedRecords = new ArrayList(this.records.size());
      for (int i=0; i<this.records.size();i++) {
        Record rec = (Record)((Record)this.records.get(i)).clone();
        //Need to pull out the Row record and the Value records from their
        //Aggregates.
        //This is probably the best way to do it since we probably dont want the createSheet
        //To cater for these artificial Record types
        if (rec instanceof RowRecordsAggregate) {
          RowRecordsAggregate rrAgg = (RowRecordsAggregate)rec;
          for (Iterator rowIter = rrAgg.getIterator();rowIter.hasNext();) {
            Record rowRec = (Record)rowIter.next();
            clonedRecords.add(rowRec);
          }
        } else if (rec instanceof ValueRecordsAggregate) {
          ValueRecordsAggregate vrAgg = (ValueRecordsAggregate)rec;
          for (Iterator cellIter = vrAgg.getIterator();cellIter.hasNext();) {
            Record valRec = (Record)cellIter.next();
            
            if (valRec instanceof FormulaRecordAggregate) {
                FormulaRecordAggregate fmAgg = (FormulaRecordAggregate)valRec;
                Record fmAggRec = fmAgg.getFormulaRecord();
                if (fmAggRec != null)
                  clonedRecords.add(fmAggRec);
                fmAggRec =   fmAgg.getStringRecord();
                if (fmAggRec != null)
                  clonedRecords.add(fmAggRec);
              } else {
                clonedRecords.add(valRec);
              }
          }
        } else if (rec instanceof FormulaRecordAggregate) {  //Is this required now??
          FormulaRecordAggregate fmAgg = (FormulaRecordAggregate)rec;
          Record fmAggRec = fmAgg.getFormulaRecord();
          if (fmAggRec != null)
            clonedRecords.add(fmAggRec);
          fmAggRec =   fmAgg.getStringRecord();
          if (fmAggRec != null)
            clonedRecords.add(fmAggRec);
        } else {
          clonedRecords.add(rec);
        }
      }
      return createSheet(clonedRecords, 0, 0);
    }


    /**
     * read support  (offset = 0) Same as createSheet(Record[] recs, int, int)
     * only the record offset is assumed to be 0.
     *
     * @param records  array containing those records in the sheet in sequence (normally obtained from RecordFactory)
     * @param sheetnum integer specifying the sheet's number (0,1 or 2 in this release)
     * @return Sheet object
     */

    public static Sheet createSheet(List records, int sheetnum)
    {
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG,
                    "Sheet createSheet (exisiting file) assumed offset 0");
        return createSheet(records, sheetnum, 0);
    }

    /**
     * Creates a sheet with all the usual records minus values and the "index"
     * record (not required).  Sets the location pointer to where the first value
     * records should go.  Use this to create a sheet from "scratch".
     *
     * @return Sheet object with all values set to defaults
     */

    public static Sheet createSheet()
    {
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "Sheet createsheet from scratch called");
        Sheet     retval  = new Sheet();
        ArrayList records = new ArrayList(30);

        records.add(retval.createBOF());

        // records.add(retval.createIndex());
        records.add(retval.createCalcMode());
        records.add(retval.createCalcCount() );
        records.add( retval.createRefMode() );
        records.add( retval.createIteration() );
        records.add( retval.createDelta() );
        records.add( retval.createSaveRecalc() );
        records.add( retval.createPrintHeaders() );
        retval.printGridlines = (PrintGridlinesRecord) retval.createPrintGridlines();
        records.add( retval.printGridlines );
        retval.gridset = (GridsetRecord) retval.createGridset();
        records.add( retval.gridset );
        records.add( retval.createGuts() );
        retval.defaultrowheight =
                (DefaultRowHeightRecord) retval.createDefaultRowHeight();
        records.add( retval.defaultrowheight );
        records.add( retval.createWSBool() );

        retval.rowBreaks = new PageBreakRecord(PageBreakRecord.HORIZONTAL_SID);
        records.add(retval.rowBreaks);
        retval.colBreaks = new PageBreakRecord(PageBreakRecord.VERTICAL_SID);
        records.add(retval.colBreaks);
        
        retval.header = (HeaderRecord) retval.createHeader();
        records.add( retval.header );        
        retval.footer = (FooterRecord) retval.createFooter();
        records.add( retval.footer );
        records.add( retval.createHCenter() );
        records.add( retval.createVCenter() );
        retval.printSetup = (PrintSetupRecord) retval.createPrintSetup();
        records.add( retval.printSetup );
        retval.defaultcolwidth =
                (DefaultColWidthRecord) retval.createDefaultColWidth();
        records.add( retval.defaultcolwidth);
        ColumnInfoRecordsAggregate columns = new ColumnInfoRecordsAggregate();
        records.add( columns );
        retval.columns = columns;
        retval.dims    = ( DimensionsRecord ) retval.createDimensions();
        records.add(retval.dims);
        retval.dimsloc = records.size()-1;
        records.add(retval.windowTwo = retval.createWindowTwo());
        retval.setLoc(records.size() - 1);
        retval.selection = 
                (SelectionRecord) retval.createSelection();
        records.add(retval.selection);
		retval.protect = (ProtectRecord) retval.createProtect();
		records.add(retval.protect);
        records.add(retval.createEOF());


        retval.records = records;
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "Sheet createsheet from scratch exit");
        return retval;
    }

    private void checkCells()
    {
        if (cells == null)
        {
            cells = new ValueRecordsAggregate();
            records.add(getDimsLoc() + 1, cells);
        }
    }

    private void checkRows()
    {
        if (rows == null)
        {
            rows = new RowRecordsAggregate();
            records.add(getDimsLoc() + 1, rows);
        }
    }

    //public int addMergedRegion(short rowFrom, short colFrom, short rowTo,
    public int addMergedRegion(int rowFrom, short colFrom, int rowTo,
                               short colTo)
    {
        if (merged == null || merged.getNumAreas() == 1027)
        {
            merged = ( MergeCellsRecord ) createMergedCells();
            mergedRecords.add(merged);            
            records.add(records.size() - 1, merged);
        }
        merged.addArea(rowFrom, colFrom, rowTo, colTo);
        return numMergedRegions++; 
    }

    public void removeMergedRegion(int index)
    {
        //safety checks
        if (index >= numMergedRegions || mergedRecords.size() == 0)
           return;
            
        int pos = 0;
        int startNumRegions = 0;
        
        //optimisation for current record
        if (numMergedRegions - index < merged.getNumAreas())
        {
            pos = mergedRecords.size() - 1;
            startNumRegions = numMergedRegions - merged.getNumAreas(); 
        }
        else
        {
            for (int n = 0; n < mergedRecords.size(); n++)
            {
                MergeCellsRecord record = (MergeCellsRecord) mergedRecords.get(n);
                if (startNumRegions + record.getNumAreas() > index)
                {
                    pos = n;
                    break;
                }
                startNumRegions += record.getNumAreas(); 
            }
        }

        MergeCellsRecord rec = (MergeCellsRecord) mergedRecords.get(pos);
        rec.removeAreaAt(index - startNumRegions);
        numMergedRegions--;
        if (rec.getNumAreas() == 0)
        {
			mergedRecords.remove(pos);
			//get rid of the record from the sheet
			records.remove(merged);            
            if (merged == rec) {
            	//pull up the LAST record for operations when we finally
            	//support continue records for mergedRegions
            	if (mergedRecords.size() > 0) {
            		merged = (MergeCellsRecord) mergedRecords.get(mergedRecords.size() - 1);
            	} else {
            		merged = null;
            	}
            }
        }
    }

    public MergeCellsRecord.MergedRegion getMergedRegionAt(int index)
    {
        //safety checks
        if (index >= numMergedRegions || mergedRecords.size() == 0)
            return null;
            
        int pos = 0;
        int startNumRegions = 0;
        
        //optimisation for current record
        if (numMergedRegions - index < merged.getNumAreas())
        {
            pos = mergedRecords.size() - 1;
            startNumRegions = numMergedRegions - merged.getNumAreas();
        }
        else
        {
            for (int n = 0; n < mergedRecords.size(); n++)
            {
                MergeCellsRecord record = (MergeCellsRecord) mergedRecords.get(n);
                if (startNumRegions + record.getNumAreas() > index)
                {
                    pos = n;
                    break;
                }
                startNumRegions += record.getNumAreas(); 
            }
        }
        return ((MergeCellsRecord) mergedRecords.get(pos)).getAreaAt(index - startNumRegions);
    }

    public int getNumMergedRegions()
    {
        return numMergedRegions;
    }

    /**
     * Returns the number of low level binary records in this sheet.  This adjusts things for the so called
     * AgregateRecords.
     *
     * @see loci.poi.hssf.record.Record
     */

    public int getNumRecords()
    {
        checkCells();
        checkRows();
        if (log.check( POILogger.DEBUG ))
        {
            log.log(POILogger.DEBUG, "Sheet.getNumRecords");
            log.logFormatted(POILogger.DEBUG, "returning % + % + % - 2 = %", new int[]
            {
                records.size(), cells.getPhysicalNumberOfCells(),
                rows.getPhysicalNumberOfRows(),
                records.size() + cells.getPhysicalNumberOfCells()
                + rows.getPhysicalNumberOfRows() - 2
            });
        }
        return records.size() + cells.getPhysicalNumberOfCells()
               + rows.getPhysicalNumberOfRows() - 2;
    }

    /**
     * Per an earlier reported bug in working with Andy Khan's excel read library.  This
     * sets the values in the sheet's DimensionsRecord object to be correct.  Excel doesn't
     * really care, but we want to play nice with other libraries.
     *
     * @see loci.poi.hssf.record.DimensionsRecord
     */

    //public void setDimensions(short firstrow, short firstcol, short lastrow,
    public void setDimensions(int firstrow, short firstcol, int lastrow,
                              short lastcol)
    {
        if (log.check( POILogger.DEBUG ))
        {
            log.log(POILogger.DEBUG, "Sheet.setDimensions");
            log.log(POILogger.DEBUG,
                    (new StringBuffer("firstrow")).append(firstrow)
                        .append("firstcol").append(firstcol).append("lastrow")
                        .append(lastrow).append("lastcol").append(lastcol)
                        .toString());
        }
        dims.setFirstCol(firstcol);
        dims.setFirstRow(firstrow);
        dims.setLastCol(lastcol);
        dims.setLastRow(lastrow);
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "Sheet.setDimensions exiting");
    }

    /**
     * set the locator for where we should look for the next value record.  The
     * algorythm will actually start here and find the correct location so you
     * can set this to 0 and watch performance go down the tubes but it will work.
     * After a value is set this is automatically advanced.  Its also set by the
     * create method.  So you probably shouldn't mess with this unless you have
     * a compelling reason why or the help for the method you're calling says so.
     * Check the other methods for whether they care about
     * the loc pointer.  Many of the "modify" and "remove" methods re-initialize this
     * to "dimsloc" which is the location of the Dimensions Record and presumably the
     * start of the value section (at or around 19 dec).
     *
     * @param loc the record number to start at
     *
     */

    public void setLoc(int loc)
    {
        valueRecIterator = null;
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "sheet.setLoc(): " + loc);
        this.loc = loc;
    }

    /**
     * Returns the location pointer to the first record to look for when adding rows/values
     *
     */

    public int getLoc()
    {
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "sheet.getLoc():" + loc);
        return loc;
    }

    /**
     * Set the preoffset when using DBCELL records (currently unused) - this is
     * the position of this sheet within the whole file.
     *
     * @param offset the offset of the sheet's BOF within the file.
     */

    public void setPreOffset(int offset)
    {
        this.preoffset = offset;
    }

    /**
     * get the preoffset when using DBCELL records (currently unused) - this is
     * the position of this sheet within the whole file.
     *
     * @return offset the offset of the sheet's BOF within the file.
     */

    public int getPreOffset()
    {
        return preoffset;
    }

    /**
     * Serializes all records in the sheet into one big byte array.  Use this to write
     * the sheet out.
     *
     * @param offset to begin write at
     * @param data   array containing the binary representation of the records in this sheet
     *
     */

    public int serialize(int offset, byte [] data)
    {
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "Sheet.serialize using offsets");

        int pos       = offset;
        boolean haveSerializedIndex = false;

        for (int k = 0; k < records.size(); k++)
        {
            Record record = (( Record ) records.get(k));
            
            //Once the rows have been found in the list of records, start
            //writing out the blocked row information. This includes the DBCell references
            if (record instanceof RowRecordsAggregate) {
              pos += ((RowRecordsAggregate)record).serialize(pos, data, cells);   // rec.length;
            } else if (record instanceof ValueRecordsAggregate) {
              //Do nothing here. The records were serialized during the RowRecordAggregate block serialization
            } else {
              pos += record.serialize(pos, data );   // rec.length;
            }
            //If the BOF record was just serialized then add the IndexRecord
            if (record.getSid() == BOFRecord.sid) {
              //Can there be more than one BOF for a sheet? If not then we can
              //remove this guard. So be safe it is left here.
              if (rows != null && !haveSerializedIndex) {
                haveSerializedIndex = true;
                pos += serializeIndexRecord(k, pos, data);
              }
            }

            //// uncomment to test record sizes ////
//            System.out.println( record.getClass().getName() );
//            byte[] data2 = new byte[record.getRecordSize()];
//            record.serialize(0, data2 );   // rec.length;
//            if (LittleEndian.getUShort(data2, 2) != record.getRecordSize() - 4
//                    && record instanceof RowRecordsAggregate == false
//                    && record instanceof ValueRecordsAggregate == false
//                    && record instanceof EscherAggregate == false)
//            {
//                throw new RuntimeException("Blah!!!  Size off by " + ( LittleEndian.getUShort(data2, 2) - record.getRecordSize() - 4) + " records.");
//            }

//asd:            int len = record.serialize(pos + offset, data );

            /////  DEBUG BEGIN /////
//asd:            if (len != record.getRecordSize())
//asd:                throw new IllegalStateException("Record size does not match serialized bytes.  Serialized size = " + len + " but getRecordSize() returns " + record.getRecordSize() + ". Record object is " + record.getClass());
            /////  DEBUG END /////

//asd:            pos += len;   // rec.length;

        }
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "Sheet.serialize returning ");
        return pos-offset;
    }
    
    private int serializeIndexRecord(final int BOFRecordIndex, final int offset, byte[] data) {
      IndexRecord index = new IndexRecord();
      index.setFirstRow(rows.getFirstRowNum());
      index.setLastRowAdd1(rows.getLastRowNum()+1);
      //Calculate the size of the records from the end of the BOF
      //and up to the RowRecordsAggregate...
      int sheetRecSize = 0;
      for (int j = BOFRecordIndex+1; j < records.size(); j++)
      {
        Record tmpRec = (( Record ) records.get(j));
        if (tmpRec instanceof RowRecordsAggregate)
          break;
        sheetRecSize+= tmpRec.getRecordSize();
      }
      //Add the references to the DBCells in the IndexRecord (one for each block)
      int blockCount = rows.getRowBlockCount();
      //Calculate the size of this IndexRecord
      int indexRecSize = IndexRecord.getRecordSizeForBlockCount(blockCount);

      int rowBlockOffset = 0;
      int cellBlockOffset = 0;
      int dbCellOffset = 0;
      for (int block=0;block<blockCount;block++) {
        rowBlockOffset += rows.getRowBlockSize(block);
        cellBlockOffset += null == cells ? 0 : cells.getRowCellBlockSize(rows.getStartRowNumberForBlock(block),
                                                     rows.getEndRowNumberForBlock(block));
        //Note: The offsets are relative to the Workbook BOF. Assume that this is
        //0 for now.....
        index.addDbcell(offset + indexRecSize + sheetRecSize + dbCellOffset + rowBlockOffset + cellBlockOffset);
        //Add space required to write the dbcell record(s) (whose references were just added).
        dbCellOffset += (8 + (rows.getRowCountForBlock(block) * 2));
      }
      return index.serialize(offset, data);
    }
    

    /**
     * Create a row record.  (does not add it to the records contained in this sheet)
     *
     * @param row number
     * @return RowRecord created for the passed in row number
     * @see loci.poi.hssf.record.RowRecord
     */

    public RowRecord createRow(int row)
    {
        return RowRecordsAggregate.createRow( row );
    }

    /**
     * Create a LABELSST Record (does not add it to the records contained in this sheet)
     *
     * @param row the row the LabelSST is a member of
     * @param col the column the LabelSST defines
     * @param index the index of the string within the SST (use workbook addSSTString method)
     * @return LabelSSTRecord newly created containing your SST Index, row,col.
     * @see loci.poi.hssf.record.SSTRecord
     */

    //public LabelSSTRecord createLabelSST(short row, short col, int index)
    public LabelSSTRecord createLabelSST(int row, short col, int index)
    {
        log.logFormatted(POILogger.DEBUG, "create labelsst row,col,index %,%,%",
                         new int[]
        {
            row, col, index
        });
        LabelSSTRecord rec = new LabelSSTRecord();

        rec.setRow(row);
        rec.setColumn(col);
        rec.setSSTIndex(index);
        rec.setXFIndex(( short ) 0x0f);
        return rec;
    }

    /**
     * Create a NUMBER Record (does not add it to the records contained in this sheet)
     *
     * @param row the row the NumberRecord is a member of
     * @param col the column the NumberRecord defines
     * @param value for the number record
     *
     * @return NumberRecord for that row, col containing that value as added to the sheet
     */

    //public NumberRecord createNumber(short row, short col, double value)
    public NumberRecord createNumber(int row, short col, double value)
    {
        log.logFormatted(POILogger.DEBUG, "create number row,col,value %,%,%",
                         new double[]
        {
            row, col, value
        });
        NumberRecord rec = new NumberRecord();

        //rec.setRow(( short ) row);
        rec.setRow(row);
        rec.setColumn(col);
        rec.setValue(value);
        rec.setXFIndex(( short ) 0x0f);
        return rec;
    }

    /**
     * create a BLANK record (does not add it to the records contained in this sheet)
     *
     * @param row - the row the BlankRecord is a member of
     * @param col - the column the BlankRecord is a member of
     */

    //public BlankRecord createBlank(short row, short col)
    public BlankRecord createBlank(int row, short col)
    {
        //log.logFormatted(POILogger.DEBUG, "create blank row,col %,%", new short[]
        log.logFormatted(POILogger.DEBUG, "create blank row,col %,%", new int[]
        {
            row, col
        });
        BlankRecord rec = new BlankRecord();

        //rec.setRow(( short ) row);
        rec.setRow(row);
        rec.setColumn(col);
        rec.setXFIndex(( short ) 0x0f);
        return rec;
    }

    /**
     * Attempts to parse the formula into PTGs and create a formula record
     * DOES NOT WORK YET
     *
     * @param row - the row for the formula record
     * @param col - the column of the formula record
     * @param formula - a String representing the formula.  To be parsed to PTGs
     * @return bogus/useless formula record
     */

    //public FormulaRecord createFormula(short row, short col, String formula)
    public FormulaRecord createFormula(int row, short col, String formula)
    {
        log.logFormatted(POILogger.DEBUG, "create formula row,col,formula %,%,%",
                         //new short[]
                         new int[]
        {
            row, col
        }, formula);
        FormulaRecord rec = new FormulaRecord();

        rec.setRow(row);
        rec.setColumn(col);
        rec.setOptions(( short ) 2);
        rec.setValue(0);
        rec.setXFIndex(( short ) 0x0f);
        FormulaParser fp = new FormulaParser(formula,null); //fix - do we need this method?
        fp.parse();
        Ptg[] ptg  = fp.getRPNPtg();
        int   size = 0;

        for (int k = 0; k < ptg.length; k++)
        {
            size += ptg[ k ].getSize();
            rec.pushExpressionToken(ptg[ k ]);
        }
        rec.setExpressionLength(( short ) size);
        return rec;
    }

    /**
     * Adds a value record to the sheet's contained binary records
     * (i.e. LabelSSTRecord or NumberRecord).
     * <P>
     * This method is "loc" sensitive.  Meaning you need to set LOC to where you
     * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
     * When adding several rows you can just start at the last one by leaving loc
     * at what this sets it to.
     *
     * @param row the row to add the cell value to
     * @param col the cell value record itself.
     */

    //public void addValueRecord(short row, CellValueRecordInterface col)
    public void addValueRecord(int row, CellValueRecordInterface col)
    {
        checkCells();
        if(log.check(POILogger.DEBUG))
        {
          log.logFormatted(POILogger.DEBUG, "add value record  row,loc %,%", new int[]
          {
              row, loc
          });
        }
        DimensionsRecord d = ( DimensionsRecord ) records.get(getDimsLoc());

        if (col.getColumn() > d.getLastCol())
        {
            d.setLastCol(( short ) (col.getColumn() + 1));
        }
        if (col.getColumn() < d.getFirstCol())
        {
            d.setFirstCol(col.getColumn());
        }
        cells.insertCell(col);

        /*
         * for (int k = loc; k < records.size(); k++)
         * {
         *   Record rec = ( Record ) records.get(k);
         *
         *   if (rec.getSid() == RowRecord.sid)
         *   {
         *       RowRecord rowrec = ( RowRecord ) rec;
         *
         *       if (rowrec.getRowNumber() == col.getRow())
         *       {
         *           records.add(k + 1, col);
         *           loc = k;
         *           if (rowrec.getLastCol() <= col.getColumn())
         *           {
         *               rowrec.setLastCol((( short ) (col.getColumn() + 1)));
         *           }
         *           break;
         *       }
         *   }
         * }
         */
    }

    /**
     * remove a value record from the records array.
     *
     * This method is not loc sensitive, it resets loc to = dimsloc so no worries.
     *
     * @param row - the row of the value record you wish to remove
     * @param col - a record supporting the CellValueRecordInterface.
     * @see loci.poi.hssf.record.CellValueRecordInterface
     */

    //public void removeValueRecord(short row, CellValueRecordInterface col)
    public void removeValueRecord(int row, CellValueRecordInterface col)
    {
        checkCells();
        log.logFormatted(POILogger.DEBUG, "remove value record row,dimsloc %,%",
                         new int[]{row, dimsloc} );
        loc = dimsloc;
        cells.removeCell(col);

        /*
         * for (int k = loc; k < records.size(); k++)
         * {
         *   Record rec = ( Record ) records.get(k);
         *
         *   // checkDimsLoc(rec,k);
         *   if (rec.isValue())
         *   {
         *       CellValueRecordInterface cell =
         *           ( CellValueRecordInterface ) rec;
         *
         *       if ((cell.getRow() == col.getRow())
         *               && (cell.getColumn() == col.getColumn()))
         *       {
         *           records.remove(k);
         *           break;
         *       }
         *   }
         * }
         */
    }

    /**
     * replace a value record from the records array.
     *
     * This method is not loc sensitive, it resets loc to = dimsloc so no worries.
     *
     * @param newval - a record supporting the CellValueRecordInterface.  this will replace
     *                the cell value with the same row and column.  If there isn't one, one will
     *                be added.
     */

    public void replaceValueRecord(CellValueRecordInterface newval)
    {
        checkCells();
        setLoc(dimsloc);
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "replaceValueRecord ");
        //The ValueRecordsAggregate use a tree map underneath.
        //The tree Map uses the CellValueRecordInterface as both the
        //key and the value, if we dont do a remove, then
        //the previous instance of the key is retained, effectively using 
        //double the memory
        cells.removeCell(newval);
        cells.insertCell(newval);

        /*
         * CellValueRecordInterface oldval = getNextValueRecord();
         *
         * while (oldval != null)
         * {
         *   if (oldval.isEqual(newval))
         *   {
         *       records.set(( short ) (getLoc() - 1), newval);
         *       return;
         *   }
         *   oldval = getNextValueRecord();
         * }
         * addValueRecord(newval.getRow(), newval);
         * setLoc(dimsloc);
         */
    }

    /**
     * Adds a row record to the sheet
     *
     * <P>
     * This method is "loc" sensitive.  Meaning you need to set LOC to where you
     * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
     * When adding several rows you can just start at the last one by leaving loc
     * at what this sets it to.
     *
     * @param row the row record to be added
     * @see #setLoc(int)
     */

    public void addRow(RowRecord row)
    {
        checkRows();
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "addRow ");
        DimensionsRecord d = ( DimensionsRecord ) records.get(getDimsLoc());

        if (row.getRowNumber() >= d.getLastRow())
        {
            d.setLastRow(row.getRowNumber() + 1);
        }
        if (row.getRowNumber() < d.getFirstRow())
        {
            d.setFirstRow(row.getRowNumber());
        }
        //IndexRecord index = null;
         //If the row exists remove it, so that any cells attached to the row are removed
         RowRecord existingRow = rows.getRow(row.getRowNumber());
         if (existingRow != null)
           rows.removeRow(existingRow);

        rows.insertRow(row);

        /*
         * for (int k = loc; k < records.size(); k++)
         * {
         *   Record rec = ( Record ) records.get(k);
         *
         *   if (rec.getSid() == IndexRecord.sid)
         *   {
         *       index = ( IndexRecord ) rec;
         *   }
         *   if (rec.getSid() == RowRecord.sid)
         *   {
         *       RowRecord rowrec = ( RowRecord ) rec;
         *
         *       if (rowrec.getRowNumber() > row.getRowNumber())
         *       {
         *           records.add(k, row);
         *           loc = k;
         *           break;
         *       }
         *   }
         *   if (rec.getSid() == WindowTwoRecord.sid)
         *   {
         *       records.add(k, row);
         *       loc = k;
         *       break;
         *   }
         * }
         * if (index != null)
         * {
         *   if (index.getLastRowAdd1() <= row.getRowNumber())
         *   {
         *       index.setLastRowAdd1(row.getRowNumber() + 1);
         *   }
         * }
         */
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "exit addRow");
    }

    /**
     * Removes a row record
     *
     * This method is not loc sensitive, it resets loc to = dimsloc so no worries.
     *
     * @param row  the row record to remove
     */

    public void removeRow(RowRecord row)
    {
        checkRows();
        // IndexRecord index = null;

        setLoc(getDimsLoc());
        rows.removeRow(row);

        /*
         * for (int k = loc; k < records.size(); k++)
         * {
         *   Record rec = ( Record ) records.get(k);
         *
         *   // checkDimsLoc(rec,k);
         *   if (rec.getSid() == RowRecord.sid)
         *   {
         *       RowRecord rowrec = ( RowRecord ) rec;
         *
         *       if (rowrec.getRowNumber() == row.getRowNumber())
         *       {
         *           records.remove(k);
         *           break;
         *       }
         *   }
         *   if (rec.getSid() == WindowTwoRecord.sid)
         *   {
         *       break;
         *   }
         * }
         */
    }

    /**
     * get the NEXT value record (from LOC).  The first record that is a value record
     * (starting at LOC) will be returned.
     *
     * <P>
     * This method is "loc" sensitive.  Meaning you need to set LOC to where you
     * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
     * When adding several rows you can just start at the last one by leaving loc
     * at what this sets it to.  For this method, set loc to dimsloc to start with,
     * subsequent calls will return values in (physical) sequence or NULL when you get to the end.
     *
     * @return CellValueRecordInterface representing the next value record or NULL if there are no more
     * @see #setLoc(int)
     */

    public CellValueRecordInterface getNextValueRecord()
    {
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "getNextValue loc= " + loc);
        if (valueRecIterator == null)
        {
            valueRecIterator = cells.getIterator();
        }
        if (!valueRecIterator.hasNext())
        {
            return null;
        }
        return ( CellValueRecordInterface ) valueRecIterator.next();

        /*
         *      if (this.getLoc() < records.size())
         *     {
         *         for (int k = getLoc(); k < records.size(); k++)
         *         {
         *             Record rec = ( Record ) records.get(k);
         *
         *             this.setLoc(k + 1);
         *             if (rec instanceof CellValueRecordInterface)
         *             {
         *                 return ( CellValueRecordInterface ) rec;
         *             }
         *         }
         *     }
         *     return null;
         */
    }

    /**
     * get the NEXT RowRecord or CellValueRecord(from LOC).  The first record that
     * is a Row record or CellValueRecord(starting at LOC) will be returned.
     * <P>
     * This method is "loc" sensitive.  Meaning you need to set LOC to where you
     * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
     * When adding several rows you can just start at the last one by leaving loc
     * at what this sets it to.  For this method, set loc to dimsloc to start with.
     * subsequent calls will return rows in (physical) sequence or NULL when you get to the end.
     *
     * @return RowRecord representing the next row record or CellValueRecordInterface
     *  representing the next cellvalue or NULL if there are no more
     * @see #setLoc(int)
     *
     */

/*    public Record getNextRowOrValue()
    {
        POILogger.DEBUG((new StringBuffer("getNextRow loc= ")).append(loc)
            .toString());
        if (this.getLoc() < records.size())
        {
            for (int k = this.getLoc(); k < records.size(); k++)
            {
                Record rec = ( Record ) records.get(k);

                this.setLoc(k + 1);
                if (rec.getSid() == RowRecord.sid)
                {
                    return rec;
                }
                else if (rec.isValue())
                {
                    return rec;
                }
            }
        }
        return null;
    }
 */

    /**
     * get the NEXT RowRecord (from LOC).  The first record that is a Row record
     * (starting at LOC) will be returned.
     * <P>
     * This method is "loc" sensitive.  Meaning you need to set LOC to where you
     * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
     * When adding several rows you can just start at the last one by leaving loc
     * at what this sets it to.  For this method, set loc to dimsloc to start with.
     * subsequent calls will return rows in (physical) sequence or NULL when you get to the end.
     *
     * @return RowRecord representing the next row record or NULL if there are no more
     * @see #setLoc(int)
     *
     */

    public RowRecord getNextRow()
    {
        if (log.check( POILogger.DEBUG ))
            log.log(POILogger.DEBUG, "getNextRow loc= " + loc);
        if (rowRecIterator == null)
        {
            rowRecIterator = rows.getIterator();
        }
        if (!rowRecIterator.hasNext())
        {
            return null;
        }
        return ( RowRecord ) rowRecIterator.next();

/*        if (this.getLoc() < records.size())
        {…

Large files files are truncated, but you can click here to view the full file