1    
2    /* ====================================================================
3     * The Apache Software License, Version 1.1
4     *
5     * Copyright (c) 2002 The Apache Software Foundation.  All rights
6     * reserved.
7     *
8     * Redistribution and use in source and binary forms, with or without
9     * modification, are permitted provided that the following conditions
10    * are met:
11    *
12    * 1. Redistributions of source code must retain the above copyright
13    *    notice, this list of conditions and the following disclaimer.
14    *
15    * 2. Redistributions in binary form must reproduce the above copyright
16    *    notice, this list of conditions and the following disclaimer in
17    *    the documentation and/or other materials provided with the
18    *    distribution.
19    *
20    * 3. The end-user documentation included with the redistribution,
21    *    if any, must include the following acknowledgment:
22    *       "This product includes software developed by the
23    *        Apache Software Foundation (http://www.apache.org/)."
24    *    Alternately, this acknowledgment may appear in the software itself,
25    *    if and wherever such third-party acknowledgments normally appear.
26    *
27    * 4. The names "Apache" and "Apache Software Foundation" and
28    *    "Apache POI" must not be used to endorse or promote products
29    *    derived from this software without prior written permission. For
30    *    written permission, please contact apache@apache.org.
31    *
32    * 5. Products derived from this software may not be called "Apache",
33    *    "Apache POI", nor may "Apache" appear in their name, without
34    *    prior written permission of the Apache Software Foundation.
35    *
36    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
37    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
38    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
39    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
40    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
41    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
42    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
43    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
44    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
45    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
46    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
47    * SUCH DAMAGE.
48    * ====================================================================
49    *
50    * This software consists of voluntary contributions made by many
51    * individuals on behalf of the Apache Software Foundation.  For more
52    * information on the Apache Software Foundation, please see
53    * <http://www.apache.org/>.
54    */
55   
56   package org.apache.poi.hssf.model;
57   
58   import java.util.List;
59   import java.util.ArrayList;
60   import java.util.Iterator;
61   
62   import org.apache.poi.hssf
63       .record.*;       // normally I don't do this, buy we literally mean ALL
64   import org.apache.poi.hssf.record.formula.Ptg;
65   import org.apache.poi.util.*;
66   import org.apache.poi.hssf.record
67       .aggregates.*;   // normally I don't do this, buy we literally mean ALL
68   
69   /**
70    * Low level model implementation of a Sheet (one workbook contains many sheets)
71    * This file contains the low level binary records starting at the sheets BOF and
72    * ending with the sheets EOF.  Use HSSFSheet for a high level representation.
73    * <P>
74    * The structures of the highlevel API use references to this to perform most of their
75    * operations.  Its probably unwise to use these low level structures directly unless you
76    * really know what you're doing.  I recommend you read the Microsoft Excel 97 Developer's
77    * Kit (Microsoft Press) and the documentation at http://sc.openoffice.org/excelfileformat.pdf
78    * before even attempting to use this.
79    * <P>
80    * @author  Andrew C. Oliver (acoliver at apache dot org)
81    * @author  Glen Stampoultzis (glens at apache.org)
82    * @author  Shawn Laubach (laubach at acm.org) Just Gridlines, Headers, Footers, and PrintSetup
83    * @author Jason Height (jheight at chariot dot net dot au) Clone support
84    *
85    * @see org.apache.poi.hssf.model.Workbook
86    * @see org.apache.poi.hssf.usermodel.HSSFSheet
87    * @version 1.0-pre
88    */
89   
90   public class Sheet
91       extends java.lang.java.lang.Objectlic static final short   LeftMargin = 0;
92       public static final short   RightMargin = 1;
93       public static final short   TopMargin = 2;
94       public static final short   BottomMargin = 3;
95   
96       protected ArrayList                 records        = null;
97       int                                 preoffset      = 0;      // offset of the sheet in a new file
98       int                                 loc            = 0;
99       protected boolean                   containsLabels = false;
100      protected int                       dimsloc        = 0;
101      protected DimensionsRecord          dims;
102      protected DefaultColWidthRecord     defaultcolwidth  = null;
103      protected DefaultRowHeightRecord    defaultrowheight = null;
104      protected GridsetRecord             gridset          = null;
105      protected PrintSetupRecord          printSetup       = null;
106      protected HeaderRecord              header           = null;
107      protected FooterRecord              footer           = null;
108      protected PrintGridlinesRecord      printGridlines   = null;
109      protected MergeCellsRecord          merged           = null;
110      protected int                       mergedloc        = 0;
111      private static POILogger            log              = POILogFactory.getLogger(Sheet.class);
112      private ArrayList                   columnSizes      = null;  // holds column info
113      protected ValueRecordsAggregate     cells            = null;
114      protected RowRecordsAggregate       rows             = null;
115      private Iterator                    valueRecIterator = null;
116      private Iterator                    rowRecIterator   = null;
117      protected int                       eofLoc           = 0;
118  
119      /**
120       * Creates new Sheet with no intialization --useless at this point
121       * @see #createSheet(List,int,int)
122       */
123  
124      public Sheet()
125      {
126      }
127  
128      /**
129       * read support  (offset used as starting point for search) for low level
130       * API.  Pass in an array of Record objects, the sheet number (0 based) and
131       * a record offset (should be the location of the sheets BOF record).  A Sheet
132       * object is constructed and passed back with all of its initialization set
133       * to the passed in records and references to those records held. This function
134       * is normally called via Workbook.
135       *
136       * @param recs array containing those records in the sheet in sequence (normally obtained from RecordFactory)
137       * @param sheetnum integer specifying the sheet's number (0,1 or 2 in this release)
138       * @param offset of the sheet's BOF record
139       *
140       * @return Sheet object with all values set to those read from the file
141       *
142       * @see org.apache.poi.hssf.model.Workbook
143       * @see org.apache.poi.hssf.record.Record
144       */
145      public static Sheet createSheet(List recs, int sheetnum, int offset)
146      {
147          log.logFormatted(log.DEBUG,
148                           "Sheet createSheet (existing file) with %",
149                           new Integer(recs.size()));
150          Sheet     retval             = new Sheet();
151          ArrayList records            = new ArrayList(recs.size() / 5);
152          boolean   isfirstcell        = true;
153          boolean   isfirstrow         = true;
154          int       bofEofNestingLevel = 0;
155  
156          for (int k = offset; k < recs.size(); k++)
157          {
158              Record rec = ( Record ) recs.get(k);
159  
160              if (rec.getSid() == LabelRecord.sid)
161              {
162                  log.log(log.DEBUG, "Hit label record.");
163                  retval.containsLabels = true;
164              }
165              else if (rec.getSid() == BOFRecord.sid)
166              {
167                  bofEofNestingLevel++;
168                  log.log(log.DEBUG, "Hit BOF record. Nesting increased to " + bofEofNestingLevel);
169              }
170              else if (rec.getSid() == EOFRecord.sid)
171              {
172                  --bofEofNestingLevel;
173                  log.log(log.DEBUG, "Hit EOF record. Nesting decreased to " + bofEofNestingLevel);
174                  if (bofEofNestingLevel == 0) {
175                      records.add(rec);
176                      retval.eofLoc = k;
177                      break;
178                  }
179              }
180              else if (rec.getSid() == DimensionsRecord.sid)
181              {
182                  retval.dims    = ( DimensionsRecord ) rec;
183                  retval.dimsloc = records.size();
184              }
185              else if (rec.getSid() == MergeCellsRecord.sid)
186              {
187                  retval.merged    = ( MergeCellsRecord ) rec;
188                  retval.mergedloc = k - offset;
189              }
190              else if (rec.getSid() == ColumnInfoRecord.sid)
191              {
192                  if (retval.columnSizes == null)
193                  {
194                      retval.columnSizes = new ArrayList();
195                  }
196                  retval.columnSizes.add(( ColumnInfoRecord ) rec);
197              }
198              else if (rec.getSid() == DefaultColWidthRecord.sid)
199              {
200                  retval.defaultcolwidth = ( DefaultColWidthRecord ) rec;
201              }
202              else if (rec.getSid() == DefaultRowHeightRecord.sid)
203              {
204                  retval.defaultrowheight = ( DefaultRowHeightRecord ) rec;
205              }
206              else if ( rec.isValue() && bofEofNestingLevel == 1 )
207              {
208                  if ( isfirstcell )
209                  {
210                      retval.cells = new ValueRecordsAggregate();
211                      rec = retval.cells;
212                      retval.cells.construct( k, recs );
213                      isfirstcell = false;
214                  }
215                  else
216                  {
217                      rec = null;
218                  }
219              }
220              else if ( rec.getSid() == StringRecord.sid )
221              {
222                  rec = null;
223              }
224              else if ( rec.getSid() == RowRecord.sid )
225              {
226                  if ( isfirstrow )
227                  {
228                      retval.rows = new RowRecordsAggregate();
229                      rec = retval.rows;
230                      retval.rows.construct( k, recs );
231                      isfirstrow = false;
232                  }
233                  else
234                  {
235                      rec = null;
236                  }
237              }
238              else if ( rec.getSid() == PrintGridlinesRecord.sid )
239              {
240                  retval.printGridlines = (PrintGridlinesRecord) rec;
241              }
242              else if ( rec.getSid() == HeaderRecord.sid )
243              {
244                  retval.header = (HeaderRecord) rec;
245              }
246              else if ( rec.getSid() == FooterRecord.sid )
247              {
248                  retval.footer = (FooterRecord) rec;
249              }
250              else if ( rec.getSid() == PrintSetupRecord.sid )
251              {
252                  retval.printSetup = (PrintSetupRecord) rec;
253              }
254  
255              if (rec != null)
256              {
257                  records.add(rec);
258              }
259          }
260          retval.records = records;
261          if (retval.rows == null)
262          {
263              retval.rows = new RowRecordsAggregate();
264          }
265          if (retval.cells == null)
266          {
267              retval.cells = new ValueRecordsAggregate();
268          }
269          log.log(log.DEBUG, "sheet createSheet (existing file) exited");
270          return retval;
271      }
272  
273      /**
274       * Clones the low level records of this sheet and returns the new sheet instance.
275       */
276      public Sheet cloneSheet()
277      {
278        ArrayList clonedRecords = new ArrayList(this.records.size());
279        for (int i=0; i<this.records.size();i++) {
280          Record rec = (Record)((Record)this.records.get(i)).clone();
281          //Need to pull out the Row record and the Value records from their
282          //Aggregates.
283          //This is probably the best way to do it since we probably dont want the createSheet
284          //To cater for these artificial Record types
285          if (rec instanceof RowRecordsAggregate) {
286            RowRecordsAggregate rrAgg = (RowRecordsAggregate)rec;
287            for (Iterator rowIter = rrAgg.getIterator();rowIter.hasNext();) {
288              Record rowRec = (Record)rowIter.next();
289              clonedRecords.add(rowRec);
290            }
291          } else if (rec instanceof ValueRecordsAggregate) {
292            ValueRecordsAggregate vrAgg = (ValueRecordsAggregate)rec;
293            for (Iterator cellIter = vrAgg.getIterator();cellIter.hasNext();) {
294              Record valRec = (Record)cellIter.next();
295              clonedRecords.add(valRec);
296            }
297          } else if (rec instanceof FormulaRecordAggregate) {
298            FormulaRecordAggregate fmAgg = (FormulaRecordAggregate)rec;
299            Record fmAggRec = fmAgg.getFormulaRecord();
300            if (fmAggRec != null)
301              clonedRecords.add(fmAggRec);
302            fmAggRec =   fmAgg.getStringRecord();
303            if (fmAggRec != null)
304              clonedRecords.add(fmAggRec);
305          } else {
306            clonedRecords.add(rec);
307          }
308        }
309        return createSheet(clonedRecords, 0, 0);
310      }
311  
312  
313      /**
314       * read support  (offset = 0) Same as createSheet(Record[] recs, int, int)
315       * only the record offset is assumed to be 0.
316       *
317       * @param records  array containing those records in the sheet in sequence (normally obtained from RecordFactory)
318       * @param sheetnum integer specifying the sheet's number (0,1 or 2 in this release)
319       * @return Sheet object
320       */
321  
322      public static Sheet createSheet(List records, int sheetnum)
323      {
324          log.log(log.DEBUG,
325                  "Sheet createSheet (exisiting file) assumed offset 0");
326          return createSheet(records, sheetnum, 0);
327      }
328  
329      /**
330       * Creates a sheet with all the usual records minus values and the "index"
331       * record (not required).  Sets the location pointer to where the first value
332       * records should go.  Use this to create a sheet from "scratch".
333       *
334       * @return Sheet object with all values set to defaults
335       */
336  
337      public static Sheet createSheet()
338      {
339          log.log(log.DEBUG, "Sheet createsheet from scratch called");
340          Sheet     retval  = new Sheet();
341          ArrayList records = new ArrayList(30);
342  
343          records.add(retval.createBOF());
344  
345          // records.add(retval.createIndex());
346          records.add(retval.createCalcMode());
347          records.add(retval.createCalcCount() );
348          records.add( retval.createRefMode() );
349          records.add( retval.createIteration() );
350          records.add( retval.createDelta() );
351          records.add( retval.createSaveRecalc() );
352          records.add( retval.createPrintHeaders() );
353          retval.printGridlines = (PrintGridlinesRecord) retval.createPrintGridlines();
354          records.add( retval.printGridlines );
355          retval.gridset = (GridsetRecord) retval.createGridset();
356          records.add( retval.gridset );
357          records.add( retval.createGuts() );
358          retval.defaultrowheight =
359                  (DefaultRowHeightRecord) retval.createDefaultRowHeight();
360          records.add( retval.defaultrowheight );
361          records.add( retval.createWSBool() );
362          retval.header = (HeaderRecord) retval.createHeader();
363          records.add( retval.header );
364          retval.footer = (FooterRecord) retval.createFooter();
365          records.add( retval.footer );
366          records.add( retval.createHCenter() );
367          records.add( retval.createVCenter() );
368          retval.printSetup = (PrintSetupRecord) retval.createPrintSetup();
369          records.add( retval.printSetup );
370          retval.defaultcolwidth =
371                  (DefaultColWidthRecord) retval.createDefaultColWidth();
372          records.add( retval.defaultcolwidth);
373          retval.dims    = ( DimensionsRecord ) retval.createDimensions();
374          retval.dimsloc = 19;
375          records.add(retval.dims);
376          records.add(retval.createWindowTwo());
377          retval.setLoc(records.size() - 1);
378          records.add(retval.createSelection());
379          records.add(retval.createEOF());
380          retval.records = records;
381          log.log(log.DEBUG, "Sheet createsheet from scratch exit");
382          return retval;
383      }
384  
385      private void checkCells()
386      {
387          if (cells == null)
388          {
389              cells = new ValueRecordsAggregate();
390              records.add(getDimsLoc() + 1, cells);
391          }
392      }
393  
394      private void checkRows()
395      {
396          if (rows == null)
397          {
398              rows = new RowRecordsAggregate();
399              records.add(getDimsLoc() + 1, rows);
400          }
401      }
402  
403      //public int addMergedRegion(short rowFrom, short colFrom, short rowTo,
404      public int addMergedRegion(int rowFrom, short colFrom, int rowTo,
405                                 short colTo)
406      {
407          if (merged == null)
408          {
409              merged    = ( MergeCellsRecord ) createMergedCells();
410              mergedloc = records.size() - 1;
411              records.add(records.size() - 1, merged);
412          }
413          return merged.addArea(rowFrom, colFrom, rowTo, colTo);
414      }
415  
416      public void removeMergedRegion(int index)
417      {
418          merged.removeAreaAt(index);
419          if (merged.getNumAreas() == 0)
420          {
421              merged = null;
422              records.remove(mergedloc);
423              mergedloc = 0;
424          }
425      }
426  
427      public MergeCellsRecord.MergedRegion getMergedRegionAt(int index)
428      {
429          return merged.getAreaAt(index);
430      }
431  
432      public int getNumMergedRegions()
433      {
434          return merged.getNumAreas();
435      }
436  
437      /**
438       * This is basically a kludge to deal with the now obsolete Label records.  If
439       * you have to read in a sheet that contains Label records, be aware that the rest
440       * of the API doesn't deal with them, the low level structure only provides read-only
441       * semi-immutable structures (the sets are there for interface conformance with NO
442       * impelmentation).  In short, you need to call this function passing it a reference
443       * to the Workbook object.  All labels will be converted to LabelSST records and their
444       * contained strings will be written to the Shared String tabel (SSTRecord) within
445       * the Workbook.
446       *
447       * @param wb sheet's matching low level Workbook structure containing the SSTRecord.
448       * @see org.apache.poi.hssf.record.LabelRecord
449       * @see org.apache.poi.hssf.record.LabelSSTRecord
450       * @see org.apache.poi.hssf.record.SSTRecord
451       */
452  
453      public void convertLabelRecords(Workbook wb)
454      {
455          log.log(log.DEBUG, "convertLabelRecords called");
456          if (containsLabels)
457          {
458              for (int k = 0; k < records.size(); k++)
459              {
460                  Record rec = ( Record ) records.get(k);
461  
462                  if (rec.getSid() == LabelRecord.sid)
463                  {
464                      LabelRecord oldrec = ( LabelRecord ) rec;
465  
466                      records.remove(k);
467                      LabelSSTRecord newrec   = new LabelSSTRecord();
468                      int            stringid =
469                          wb.addSSTString(oldrec.getValue());
470  
471                      newrec.setRow(oldrec.getRow());
472                      newrec.setColumn(oldrec.getColumn());
473                      newrec.setXFIndex(oldrec.getXFIndex());
474                      newrec.setSSTIndex(stringid);
475                            records.add(k, newrec);
476                  }
477              }
478          }
479          log.log(log.DEBUG, "convertLabelRecords exit");
480      }
481  
482      /**
483       * Returns the number of low level binary records in this sheet.  This adjusts things for the so called
484       * AgregateRecords.
485       *
486       * @see org.apache.poi.hssf.record.Record
487       */
488  
489      public int getNumRecords()
490      {
491          checkCells();
492          checkRows();
493          log.log(log.DEBUG, "Sheet.getNumRecords");
494          log.logFormatted(log.DEBUG, "returning % + % + % - 2 = %", new int[]
495          {
496              records.size(), cells.getPhysicalNumberOfCells(),
497              rows.getPhysicalNumberOfRows(),
498              records.size() + cells.getPhysicalNumberOfCells()
499              + rows.getPhysicalNumberOfRows() - 2
500          });
501          return records.size() + cells.getPhysicalNumberOfCells()
502                 + rows.getPhysicalNumberOfRows() - 2;
503      }
504  
505      /**
506       * Per an earlier reported bug in working with Andy Khan's excel read library.  This
507       * sets the values in the sheet's DimensionsRecord object to be correct.  Excel doesn't
508       * really care, but we want to play nice with other libraries.
509       *
510       * @see org.apache.poi.hssf.record.DimensionsRecord
511       */
512  
513      //public void setDimensions(short firstrow, short firstcol, short lastrow,
514      public void setDimensions(int firstrow, short firstcol, int lastrow,
515                                short lastcol)
516      {
517          log.log(log.DEBUG, "Sheet.setDimensions");
518          log.log(log.DEBUG,
519                  (new StringBuffer("firstrow")).append(firstrow)
520                      .append("firstcol").append(firstcol).append("lastrow")
521                      .append(lastrow).append("lastcol").append(lastcol)
522                      .toString());
523          dims.setFirstCol(firstcol);
524          dims.setFirstRow(firstrow);
525          dims.setLastCol(lastcol);
526          dims.setLastRow(lastrow);
527          log.log(log.DEBUG, "Sheet.setDimensions exiting");
528      }
529  
530      /**
531       * set the locator for where we should look for the next value record.  The
532       * algorythm will actually start here and find the correct location so you
533       * can set this to 0 and watch performance go down the tubes but it will work.
534       * After a value is set this is automatically advanced.  Its also set by the
535       * create method.  So you probably shouldn't mess with this unless you have
536       * a compelling reason why or the help for the method you're calling says so.
537       * Check the other methods for whether they care about
538       * the loc pointer.  Many of the "modify" and "remove" methods re-initialize this
539       * to "dimsloc" which is the location of the Dimensions Record and presumably the
540       * start of the value section (at or around 19 dec).
541       *
542       * @param loc the record number to start at
543       *
544       */
545  
546      public void setLoc(int loc)
547      {
548          valueRecIterator = null;
549          log.log(log.DEBUG, "sheet.setLoc(): " + loc);
550          this.loc = loc;
551      }
552  
553      /**
554       * Returns the location pointer to the first record to look for when adding rows/values
555       *
556       */
557  
558      public int getLoc()
559      {
560          log.log(log.DEBUG, "sheet.getLoc():" + loc);
561          return loc;
562      }
563  
564      /**
565       * Set the preoffset when using DBCELL records (currently unused) - this is
566       * the position of this sheet within the whole file.
567       *
568       * @param offset the offset of the sheet's BOF within the file.
569       */
570  
571      public void setPreOffset(int offset)
572      {
573          this.preoffset = offset;
574      }
575  
576      /**
577       * get the preoffset when using DBCELL records (currently unused) - this is
578       * the position of this sheet within the whole file.
579       *
580       * @return offset the offset of the sheet's BOF within the file.
581       */
582  
583      public int getPreOffset()
584      {
585          return preoffset;
586      }
587  
588      /**
589       * Serializes all records in the sheet into one big byte array.  Use this to write
590       * the sheet out.
591       *
592       * @return byte[] array containing the binary representation of the records in this sheet
593       *
594       */
595  
596      public byte [] serialize()
597      {
598          log.log(log.DEBUG, "Sheet.serialize");
599  
600          // addDBCellRecords();
601          byte[] retval    = null;
602  
603          // ArrayList bytes     = new ArrayList(4096);
604          int    arraysize = getSize();
605          int    pos       = 0;
606  
607          // for (int k = 0; k < records.size(); k++)
608          // {
609          // bytes.add((( Record ) records.get(k)).serialize());
610          //
611          // }
612          // for (int k = 0; k < bytes.size(); k++)
613          // {
614          // arraysize += (( byte [] ) bytes.get(k)).length;
615          // log.debug((new StringBuffer("arraysize=")).append(arraysize)
616          // .toString());
617          // }
618          retval = new byte[ arraysize ];
619          for (int k = 0; k < records.size(); k++)
620          {
621  
622              // byte[] rec = (( byte [] ) bytes.get(k));
623              // System.arraycopy(rec, 0, retval, pos, rec.length);
624              pos += (( Record ) records.get(k)).serialize(pos,
625                      retval);   // rec.length;
626          }
627          log.log(log.DEBUG, "Sheet.serialize returning " + retval);
628          return retval;
629      }
630  
631      /**
632       * Serializes all records in the sheet into one big byte array.  Use this to write
633       * the sheet out.
634       *
635       * @param offset to begin write at
636       * @param data   array containing the binary representation of the records in this sheet
637       *
638       */
639  
640      public int serialize(int offset, byte [] data)
641      {
642          log.log(log.DEBUG, "Sheet.serialize using offsets");
643  
644          // addDBCellRecords();
645          // ArrayList bytes     = new ArrayList(4096);
646          // int arraysize = getSize();   // 0;
647          int pos       = 0;
648  
649          // for (int k = 0; k < records.size(); k++)
650          // {
651          // bytes.add((( Record ) records.get(k)).serialize());
652          //
653          // }
654          // for (int k = 0; k < bytes.size(); k++)
655          // {
656          // arraysize += (( byte [] ) bytes.get(k)).length;
657          // log.debug((new StringBuffer("arraysize=")).append(arraysize)
658          // .toString());
659          // }
660          for (int k = 0; k < records.size(); k++)
661          {
662  //             byte[] rec = (( byte [] ) bytes.get(k));
663              // System.arraycopy(rec, 0, data, offset + pos, rec.length);
664              Record record = (( Record ) records.get(k));
665  
666              //uncomment to test record sizes
667  //            byte[] data2 = new byte[record.getRecordSize()];
668  //            record.serialize(0, data2 );   // rec.length;
669  //            if (LittleEndian.getUShort(data2, 2) != record.getRecordSize() - 4
670  //                    && record instanceof RowRecordsAggregate == false && record instanceof ValueRecordsAggregate == false)
671  //                throw new RuntimeException("Blah!!!");
672  
673              pos += record.serialize(pos + offset, data );   // rec.length;
674  
675          }
676          log.log(log.DEBUG, "Sheet.serialize returning ");
677          return pos;
678      }
679  
680      /**
681       * Create a row record.  (does not add it to the records contained in this sheet)
682       *
683       * @param row number
684       * @return RowRecord created for the passed in row number
685       * @see org.apache.poi.hssf.record.RowRecord
686       */
687  
688      public RowRecord createRow(int row)
689      {
690          log.log(log.DEBUG, "create row number " + row);
691          RowRecord rowrec = new RowRecord();
692  
693          //rowrec.setRowNumber(( short ) row);
694          rowrec.setRowNumber(row);
695          rowrec.setHeight(( short ) 0xff);
696          rowrec.setOptimize(( short ) 0x0);
697          rowrec.setOptionFlags(( short ) 0x0);
698          rowrec.setXFIndex(( short ) 0x0);
699          return rowrec;
700      }
701  
702      /**
703       * Create a LABELSST Record (does not add it to the records contained in this sheet)
704       *
705       * @param row the row the LabelSST is a member of
706       * @param col the column the LabelSST defines
707       * @param index the index of the string within the SST (use workbook addSSTString method)
708       * @return LabelSSTRecord newly created containing your SST Index, row,col.
709       * @see org.apache.poi.hssf.record.SSTRecord
710       */
711  
712      //public LabelSSTRecord createLabelSST(short row, short col, int index)
713      public LabelSSTRecord createLabelSST(int row, short col, int index)
714      {
715          log.logFormatted(log.DEBUG, "create labelsst row,col,index %,%,%",
716                           new int[]
717          {
718              row, col, index
719          });
720          LabelSSTRecord rec = new LabelSSTRecord();
721  
722          rec.setRow(row);
723          rec.setColumn(col);
724          rec.setSSTIndex(index);
725          rec.setXFIndex(( short ) 0x0f);
726          return rec;
727      }
728  
729      /**
730       * Create a NUMBER Record (does not add it to the records contained in this sheet)
731       *
732       * @param row the row the NumberRecord is a member of
733       * @param col the column the NumberRecord defines
734       * @param value for the number record
735       *
736       * @return NumberRecord for that row, col containing that value as added to the sheet
737       */
738  
739      //public NumberRecord createNumber(short row, short col, double value)
740      public NumberRecord createNumber(int row, short col, double value)
741      {
742          log.logFormatted(log.DEBUG, "create number row,col,value %,%,%",
743                           new double[]
744          {
745              row, col, value
746          });
747          NumberRecord rec = new NumberRecord();
748  
749          //rec.setRow(( short ) row);
750          rec.setRow(row);
751          rec.setColumn(col);
752          rec.setValue(value);
753          rec.setXFIndex(( short ) 0x0f);
754          return rec;
755      }
756  
757      /**
758       * create a BLANK record (does not add it to the records contained in this sheet)
759       *
760       * @param row - the row the BlankRecord is a member of
761       * @param col - the column the BlankRecord is a member of
762       */
763  
764      //public BlankRecord createBlank(short row, short col)
765      public BlankRecord createBlank(int row, short col)
766      {
767          //log.logFormatted(log.DEBUG, "create blank row,col %,%", new short[]
768          log.logFormatted(log.DEBUG, "create blank row,col %,%", new int[]
769          {
770              row, col
771          });
772          BlankRecord rec = new BlankRecord();
773  
774          //rec.setRow(( short ) row);
775          rec.setRow(row);
776          rec.setColumn(col);
777          rec.setXFIndex(( short ) 0x0f);
778          return rec;
779      }
780  
781      /**
782       * Attempts to parse the formula into PTGs and create a formula record
783       * DOES NOT WORK YET
784       *
785       * @param row - the row for the formula record
786       * @param col - the column of the formula record
787       * @param formula - a String representing the formula.  To be parsed to PTGs
788       * @return bogus/useless formula record
789       */
790  
791      //public FormulaRecord createFormula(short row, short col, String formula)
792      public FormulaRecord createFormula(int row, short col, String formula)
793      {
794          log.logFormatted(log.DEBUG, "create formula row,col,formula %,%,%",
795                           //new short[]
796                           new int[]
797          {
798              row, col
799          }, formula);
800          FormulaRecord rec = new FormulaRecord();
801  
802          rec.setRow(row);
803          rec.setColumn(col);
804          rec.setOptions(( short ) 2);
805          rec.setValue(0);
806          rec.setXFIndex(( short ) 0x0f);
807          FormulaParser fp = new FormulaParser(formula,null); //fix - do we need this method?
808          fp.parse();
809          Ptg[] ptg  = fp.getRPNPtg();
810          int   size = 0;
811  
812          for (int k = 0; k < ptg.length; k++)
813          {
814              size += ptg[ k ].getSize();
815              rec.pushExpressionToken(ptg[ k ]);
816          }
817          rec.setExpressionLength(( short ) size);
818          return rec;
819      }
820  
821      /**
822       * Adds a value record to the sheet's contained binary records
823       * (i.e. LabelSSTRecord or NumberRecord).
824       * <P>
825       * This method is "loc" sensitive.  Meaning you need to set LOC to where you
826       * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
827       * When adding several rows you can just start at the last one by leaving loc
828       * at what this sets it to.
829       *
830       * @param row the row to add the cell value to
831       * @param col the cell value record itself.
832       */
833  
834      //public void addValueRecord(short row, CellValueRecordInterface col)
835      public void addValueRecord(int row, CellValueRecordInterface col)
836      {
837          checkCells();
838          log.logFormatted(log.DEBUG, "add value record  row,loc %,%", new int[]
839          {
840              row, loc
841          });
842          DimensionsRecord d = ( DimensionsRecord ) records.get(getDimsLoc());
843  
844          if (col.getColumn() > d.getLastCol())
845          {
846              d.setLastCol(( short ) (col.getColumn() + 1));
847          }
848          if (col.getColumn() < d.getFirstCol())
849          {
850              d.setFirstCol(col.getColumn());
851          }
852          cells.insertCell(col);
853  
854          /*
855           * for (int k = loc; k < records.size(); k++)
856           * {
857           *   Record rec = ( Record ) records.get(k);
858           *
859           *   if (rec.getSid() == RowRecord.sid)
860           *   {
861           *       RowRecord rowrec = ( RowRecord ) rec;
862           *
863           *       if (rowrec.getRowNumber() == col.getRow())
864           *       {
865           *           records.add(k + 1, col);
866           *           loc = k;
867           *           if (rowrec.getLastCol() <= col.getColumn())
868           *           {
869           *               rowrec.setLastCol((( short ) (col.getColumn() + 1)));
870           *           }
871           *           break;
872           *       }
873           *   }
874           * }
875           */
876      }
877  
878      /**
879       * remove a value record from the records array.
880       *
881       * This method is not loc sensitive, it resets loc to = dimsloc so no worries.
882       *
883       * @param row - the row of the value record you wish to remove
884       * @param col - a record supporting the CellValueRecordInterface.
885       * @see org.apache.poi.hssf.record.CellValueRecordInterface
886       */
887  
888      //public void removeValueRecord(short row, CellValueRecordInterface col)
889      public void removeValueRecord(int row, CellValueRecordInterface col)
890      {
891          checkCells();
892          log.logFormatted(log.DEBUG, "remove value record row,dimsloc %,%",
893                           new int[]
894          {
895              row, dimsloc
896          });
897          loc = dimsloc;
898          cells.removeCell(col);
899  
900          /*
901           * for (int k = loc; k < records.size(); k++)
902           * {
903           *   Record rec = ( Record ) records.get(k);
904           *
905           *   // checkDimsLoc(rec,k);
906           *   if (rec.isValue())
907           *   {
908           *       CellValueRecordInterface cell =
909           *           ( CellValueRecordInterface ) rec;
910           *
911           *       if ((cell.getRow() == col.getRow())
912           *               && (cell.getColumn() == col.getColumn()))
913           *       {
914           *           records.remove(k);
915           *           break;
916           *       }
917           *   }
918           * }
919           */
920      }
921  
922      /**
923       * replace a value record from the records array.
924       *
925       * This method is not loc sensitive, it resets loc to = dimsloc so no worries.
926       *
927       * @param newval - a record supporting the CellValueRecordInterface.  this will replace
928       *                the cell value with the same row and column.  If there isn't one, one will
929       *                be added.
930       */
931  
932      public void replaceValueRecord(CellValueRecordInterface newval)
933      {
934          checkCells();
935          setLoc(dimsloc);
936          log.log(log.DEBUG, "replaceValueRecord ");
937          cells.insertCell(newval);
938  
939          /*
940           * CellValueRecordInterface oldval = getNextValueRecord();
941           *
942           * while (oldval != null)
943           * {
944           *   if (oldval.isEqual(newval))
945           *   {
946           *       records.set(( short ) (getLoc() - 1), newval);
947           *       return;
948           *   }
949           *   oldval = getNextValueRecord();
950           * }
951           * addValueRecord(newval.getRow(), newval);
952           * setLoc(dimsloc);
953           */
954      }
955  
956      /**
957       * Adds a row record to the sheet
958       *
959       * <P>
960       * This method is "loc" sensitive.  Meaning you need to set LOC to where you
961       * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
962       * When adding several rows you can just start at the last one by leaving loc
963       * at what this sets it to.
964       *
965       * @param row the row record to be added
966       * @see #setLoc(int)
967       */
968  
969      public void addRow(RowRecord row)
970      {
971          checkRows();
972          log.log(log.DEBUG, "addRow ");
973          DimensionsRecord d = ( DimensionsRecord ) records.get(getDimsLoc());
974  
975          if (row.getRowNumber() > d.getLastRow())
976          {
977              d.setLastRow(row.getRowNumber() + 1);
978          }
979          if (row.getRowNumber() < d.getFirstRow())
980          {
981              d.setFirstRow(row.getRowNumber());
982          }
983          //IndexRecord index = null;
984  
985          rows.insertRow(row);
986  
987          /*
988           * for (int k = loc; k < records.size(); k++)
989           * {
990           *   Record rec = ( Record ) records.get(k);
991           *
992           *   if (rec.getSid() == IndexRecord.sid)
993           *   {
994           *       index = ( IndexRecord ) rec;
995           *   }
996           *   if (rec.getSid() == RowRecord.sid)
997           *   {
998           *       RowRecord rowrec = ( RowRecord ) rec;
999           *
1000          *       if (rowrec.getRowNumber() > row.getRowNumber())
1001          *       {
1002          *           records.add(k, row);
1003          *           loc = k;
1004          *           break;
1005          *       }
1006          *   }
1007          *   if (rec.getSid() == WindowTwoRecord.sid)
1008          *   {
1009          *       records.add(k, row);
1010          *       loc = k;
1011          *       break;
1012          *   }
1013          * }
1014          * if (index != null)
1015          * {
1016          *   if (index.getLastRowAdd1() <= row.getRowNumber())
1017          *   {
1018          *       index.setLastRowAdd1(row.getRowNumber() + 1);
1019          *   }
1020          * }
1021          */
1022         log.log(log.DEBUG, "exit addRow");
1023     }
1024 
1025     /**
1026      * Removes a row record
1027      *
1028      * This method is not loc sensitive, it resets loc to = dimsloc so no worries.
1029      *
1030      * @param row  the row record to remove
1031      */
1032 
1033     public void removeRow(RowRecord row)
1034     {
1035         checkRows();
1036         // IndexRecord index = null;
1037 
1038         setLoc(getDimsLoc());
1039         rows.removeRow(row);
1040 
1041         /*
1042          * for (int k = loc; k < records.size(); k++)
1043          * {
1044          *   Record rec = ( Record ) records.get(k);
1045          *
1046          *   // checkDimsLoc(rec,k);
1047          *   if (rec.getSid() == RowRecord.sid)
1048          *   {
1049          *       RowRecord rowrec = ( RowRecord ) rec;
1050          *
1051          *       if (rowrec.getRowNumber() == row.getRowNumber())
1052          *       {
1053          *           records.remove(k);
1054          *           break;
1055          *       }
1056          *   }
1057          *   if (rec.getSid() == WindowTwoRecord.sid)
1058          *   {
1059          *       break;
1060          *   }
1061          * }
1062          */
1063     }
1064 
1065     /**
1066      * get the NEXT value record (from LOC).  The first record that is a value record
1067      * (starting at LOC) will be returned.
1068      *
1069      * <P>
1070      * This method is "loc" sensitive.  Meaning you need to set LOC to where you
1071      * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
1072      * When adding several rows you can just start at the last one by leaving loc
1073      * at what this sets it to.  For this method, set loc to dimsloc to start with,
1074      * subsequent calls will return values in (physical) sequence or NULL when you get to the end.
1075      *
1076      * @return CellValueRecordInterface representing the next value record or NULL if there are no more
1077      * @see #setLoc(int)
1078      */
1079 
1080     public CellValueRecordInterface getNextValueRecord()
1081     {
1082         log.log(log.DEBUG, "getNextValue loc= " + loc);
1083         if (valueRecIterator == null)
1084         {
1085             valueRecIterator = cells.getIterator();
1086         }
1087         if (!valueRecIterator.hasNext())
1088         {
1089             return null;
1090         }
1091         return ( CellValueRecordInterface ) valueRecIterator.next();
1092 
1093         /*
1094          *      if (this.getLoc() < records.size())
1095          *     {
1096          *         for (int k = getLoc(); k < records.size(); k++)
1097          *         {
1098          *             Record rec = ( Record ) records.get(k);
1099          *
1100          *             this.setLoc(k + 1);
1101          *             if (rec instanceof CellValueRecordInterface)
1102          *             {
1103          *                 return ( CellValueRecordInterface ) rec;
1104          *             }
1105          *         }
1106          *     }
1107          *     return null;
1108          */
1109     }
1110 
1111     /**
1112      * get the NEXT RowRecord or CellValueRecord(from LOC).  The first record that
1113      * is a Row record or CellValueRecord(starting at LOC) will be returned.
1114      * <P>
1115      * This method is "loc" sensitive.  Meaning you need to set LOC to where you
1116      * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
1117      * When adding several rows you can just start at the last one by leaving loc
1118      * at what this sets it to.  For this method, set loc to dimsloc to start with.
1119      * subsequent calls will return rows in (physical) sequence or NULL when you get to the end.
1120      *
1121      * @return RowRecord representing the next row record or CellValueRecordInterface
1122      *  representing the next cellvalue or NULL if there are no more
1123      * @see #setLoc(int)
1124      *
1125      */
1126 
1127 /*    public Record getNextRowOrValue()
1128     {
1129         log.debug((new StringBuffer("getNextRow loc= ")).append(loc)
1130             .toString());
1131         if (this.getLoc() < records.size())
1132         {
1133             for (int k = this.getLoc(); k < records.size(); k++)
1134             {
1135                 Record rec = ( Record ) records.get(k);
1136 
1137                 this.setLoc(k + 1);
1138                 if (rec.getSid() == RowRecord.sid)
1139                 {
1140                     return rec;
1141                 }
1142                 else if (rec.isValue())
1143                 {
1144                     return rec;
1145                 }
1146             }
1147         }
1148         return null;
1149     }
1150  */
1151 
1152     /**
1153      * get the NEXT RowRecord (from LOC).  The first record that is a Row record
1154      * (starting at LOC) will be returned.
1155      * <P>
1156      * This method is "loc" sensitive.  Meaning you need to set LOC to where you
1157      * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
1158      * When adding several rows you can just start at the last one by leaving loc
1159      * at what this sets it to.  For this method, set loc to dimsloc to start with.
1160      * subsequent calls will return rows in (physical) sequence or NULL when you get to the end.
1161      *
1162      * @return RowRecord representing the next row record or NULL if there are no more
1163      * @see #setLoc(int)
1164      *
1165      */
1166 
1167     public RowRecord getNextRow()
1168     {
1169         log.log(log.DEBUG, "getNextRow loc= " + loc);
1170         if (rowRecIterator == null)
1171         {
1172             rowRecIterator = rows.getIterator();
1173         }
1174         if (!rowRecIterator.hasNext())
1175         {
1176             return null;
1177         }
1178         return ( RowRecord ) rowRecIterator.next();
1179 
1180 /*        if (this.getLoc() < records.size())
1181         {
1182             for (int k = this.getLoc(); k < records.size(); k++)
1183             {
1184                 Record rec = ( Record ) records.get(k);
1185 
1186                 this.setLoc(k + 1);
1187                 if (rec.getSid() == RowRecord.sid)
1188                 {
1189                     return ( RowRecord ) rec;
1190                 }
1191             }
1192         }*/
1193     }
1194 
1195     /**
1196      * get the NEXT (from LOC) RowRecord where rownumber matches the given rownum.
1197      * The first record that is a Row record (starting at LOC) that has the
1198      * same rownum as the given rownum will be returned.
1199      * <P>
1200      * This method is "loc" sensitive.  Meaning you need to set LOC to where you
1201      * want it to start searching.  If you don't know do this: setLoc(getDimsLoc).
1202      * When adding several rows you can just start at the last one by leaving loc
1203      * at what this sets it to.  For this method, set loc to dimsloc to start with.
1204      * subsequent calls will return rows in (physical) sequence or NULL when you get to the end.
1205      *
1206      * @param rownum   which row to return (careful with LOC)
1207      * @return RowRecord representing the next row record or NULL if there are no more
1208      * @see #setLoc(int)
1209      *
1210      */
1211 
1212     //public RowRecord getRow(short rownum)
1213     public RowRecord getRow(int rownum)
1214     {
1215         log.log(log.DEBUG, "getNextRow loc= " + loc);
1216         return rows.getRow(rownum);
1217 
1218         /*
1219          * if (this.getLoc() < records.size())
1220          * {
1221          *   for (int k = this.getLoc(); k < records.size(); k++)
1222          *   {
1223          *       Record rec = ( Record ) records.get(k);
1224          *
1225          *       this.setLoc(k + 1);
1226          *       if (rec.getSid() == RowRecord.sid)
1227          *       {
1228          *           if ((( RowRecord ) rec).getRowNumber() == rownum)
1229          *           {
1230          *               return ( RowRecord ) rec;
1231          *           }
1232          *       }
1233          *   }
1234          * }
1235          */
1236 
1237         // return null;
1238     }
1239 
1240     /**
1241      * Not currently used method to calculate and add dbcell records
1242      *
1243      */
1244 
1245     public void addDBCellRecords()
1246     {
1247         int         offset        = 0;
1248         int         recnum        = 0;
1249         int         rownum        = 0;
1250         //int         lastrow       = 0;
1251         //long        lastrowoffset = 0;
1252         IndexRecord index         = null;
1253 
1254         // ArrayList rowOffsets = new ArrayList();
1255         IntList     rowOffsets    = new IntList();
1256 
1257         for (recnum = 0; recnum < records.size(); recnum++)
1258         {
1259             Record rec = ( Record ) records.get(recnum);
1260 
1261             if (rec.getSid() == IndexRecord.sid)
1262             {
1263                 index = ( IndexRecord ) rec;
1264             }
1265             if (rec.getSid() != RowRecord.sid)
1266             {
1267                 offset += rec.serialize().length;
1268             }
1269             else
1270             {
1271                 break;
1272             }
1273         }
1274 
1275         // First Row Record
1276         for (; recnum < records.size(); recnum++)
1277         {
1278             Record rec = ( Record ) records.get(recnum);
1279 
1280             if (rec.getSid() == RowRecord.sid)
1281             {
1282                 rownum++;
1283                 rowOffsets.add(offset);
1284                 if ((rownum % 32) == 0)
1285                 {
1286 
1287                     // if this is the last rec in a  dbcell block
1288                     // find the next row or last value record
1289                     for (int rn = recnum; rn < records.size(); rn++)
1290                     {
1291                         rec = ( Record ) records.get(rn);
1292                         if ((!rec.isInValueSection())
1293                                 || (rec.getSid() == RowRecord.sid))
1294                         {
1295 
1296                             // here is the next row or last value record
1297                             records.add(rn,
1298                                         createDBCell(offset, rowOffsets,
1299                                                      index));
1300                             recnum = rn;
1301                             break;
1302                         }
1303                     }
1304                 }
1305                 else
1306                 {
1307                 }
1308             }
1309             if (!rec.isInValueSection())
1310             {
1311                 records.add(recnum, createDBCell(offset, rowOffsets, index));
1312                 break;
1313             }
1314             offset += rec.serialize().length;
1315         }
1316     }
1317 
1318     /** not currently used */
1319 
1320     private DBCellRecord createDBCell(int offset, IntList rowoffsets,
1321                                       IndexRecord index)
1322     {
1323         DBCellRecord rec = new DBCellRecord();
1324 
1325         rec.setRowOffset(offset - rowoffsets.get(0));
1326 
1327         // test hack
1328         rec.addCellOffset(( short ) 0x0);
1329 
1330         // end test hack
1331         addDbCellToIndex(offset, index);
1332         return rec;
1333     }
1334 
1335     /** not currently used */
1336 
1337     private void addDbCellToIndex(int offset, IndexRecord index)
1338     {
1339         int numdbcells = index.getNumDbcells() + 1;
1340 
1341         index.addDbcell(offset + preoffset);
1342 
1343         // stupid but whenever we add an offset that causes everything to be shifted down 4
1344         for (int k = 0; k < numdbcells; k++)
1345         {
1346             int dbval = index.getDbcellAt(k);
1347 
1348             index.setDbcell(k, dbval + 4);
1349         }
1350     }
1351 
1352     /**
1353      * creates the BOF record
1354      * @see org.apache.poi.hssf.record.BOFRecord
1355      * @see org.apache.poi.hssf.record.Record
1356      * @return record containing a BOFRecord
1357      */
1358 
1359     protected Record createBOF()
1360     {
1361         BOFRecord retval = new BOFRecord();
1362 
1363         retval.setVersion(( short ) 0x600);
1364         retval.setType(( short ) 0x010);
1365 
1366         // retval.setBuild((short)0x10d3);
1367         retval.setBuild(( short ) 0x0dbb);
1368         retval.setBuildYear(( short ) 1996);
1369         retval.setHistoryBitMask(0xc1);
1370         retval.setRequiredVersion(0x6);
1371         return retval;
1372     }
1373 
1374     /**
1375      * creates the Index record  - not currently used
1376      * @see org.apache.poi.hssf.record.IndexRecord
1377      * @see org.apache.poi.hssf.record.Record
1378      * @return record containing a IndexRecord
1379      */
1380 
1381     protected Record createIndex()
1382     {
1383         IndexRecord retval = new IndexRecord();
1384 
1385         retval.setFirstRow(0);   // must be set explicitly
1386         retval.setLastRowAdd1(0);
1387         return retval;
1388     }
1389 
1390     /**
1391      * creates the CalcMode record and sets it to 1 (automatic formula caculation)
1392      * @see org.apache.poi.hssf.record.CalcModeRecord
1393      * @see org.apache.poi.hssf.record.Record
1394      * @return record containing a CalcModeRecord
1395      */
1396 
1397     protected Record createCalcMode()
1398     {
1399         CalcModeRecord retval = new CalcModeRecord();
1400 
1401         retval.setCalcMode(( short ) 1);
1402         return retval;
1403     }
1404 
1405     /**
1406      * creates the CalcCount record and sets it to 0x64 (default number of iterations)
1407      * @see org.apache.poi.hssf.record.CalcCountRecord
1408      * @see org.apache.poi.hssf.record.Record
1409      * @return record containing a CalcCountRecord
1410      */
1411 
1412     protected Record createCalcCount()
1413     {
1414         CalcCountRecord retval = new CalcCountRecord();
1415 
1416         retval.setIterations(( short ) 0x64);   // default 64 iterations
1417         return retval;
1418     }
1419 
1420     /**
1421      * creates the RefMode record and sets it to A1 Mode (default reference mode)
1422      * @see org.apache.poi.hssf.record.RefModeRecord
1423      * @see org.apache.poi.hssf.record.Record
1424      * @return record containing a RefModeRecord
1425      */
1426 
1427     protected Record createRefMode()
1428     {
1429         RefModeRecord retval = new RefModeRecord();
1430 
1431         retval.setMode(retval.USE_A1_MODE);
1432         return retval;
1433     }
1434 
1435     /**
1436      * creates the Iteration record and sets it to false (don't iteratively calculate formulas)
1437      * @see org.apache.poi.hssf.record.IterationRecord
1438      * @see org.apache.poi.hssf.record.Record
1439      * @return record containing a IterationRecord
1440      */
1441 
1442     protected Record createIteration()
1443     {
1444         IterationRecord retval = new IterationRecord();
1445 
1446         retval.setIteration(false);
1447         return retval;
1448     }
1449 
1450     /**
1451      * creates the Delta record and sets it to 0.0010 (default accuracy)
1452      * @see org.apache.poi.hssf.record.DeltaRecord
1453      * @see org.apache.poi.hssf.record.Record
1454      * @return record containing a DeltaRecord
1455      */
1456 
1457     protected Record createDelta()
1458     {
1459         DeltaRecord retval = new DeltaRecord();
1460 
1461         retval.setMaxChange(0.0010);
1462         return retval;
1463     }
1464 
1465     /**
1466      * creates the SaveRecalc record and sets it to true (recalculate before saving)
1467      * @see org.apache.poi.hssf.record.SaveRecalcRecord
1468      * @see org.apache.poi.hssf.record.Record
1469      * @return record containing a SaveRecalcRecord
1470      */
1471 
1472     protected Record createSaveRecalc()
1473     {
1474         SaveRecalcRecord retval = new SaveRecalcRecord();
1475 
1476         retval.setRecalc(true);
1477         return retval;
1478     }
1479 
1480     /**
1481      * creates the PrintHeaders record and sets it to false (we don't create headers yet so why print them)
1482      * @see org.apache.poi.hssf.record.PrintHeadersRecord
1483      * @see org.apache.poi.hssf.record.Record
1484      * @return record containing a PrintHeadersRecord
1485      */
1486 
1487     protected Record createPrintHeaders()
1488     {
1489         PrintHeadersRecord retval = new PrintHeadersRecord();
1490 
1491         retval.setPrintHeaders(false);
1492         return retval;
1493     }
1494 
1495     /**
1496      * creates the PrintGridlines record and sets it to false (that makes for ugly sheets).  As far as I can
1497      * tell this does the same thing as the GridsetRecord
1498      *
1499      * @see org.apache.poi.hssf.record.PrintGridlinesRecord
1500      * @see org.apache.poi.hssf.record.Record
1501      * @return record containing a PrintGridlinesRecord
1502      */
1503 
1504     protected Record createPrintGridlines()
1505     {
1506         PrintGridlinesRecord retval = new PrintGridlinesRecord();
1507 
1508         retval.setPrintGridlines(false);
1509         return retval;
1510     }
1511 
1512     /**
1513      * creates the Gridset record and sets it to true (user has mucked with the gridlines)
1514      * @see org.apache.poi.hssf.record.GridsetRecord
1515      * @see org.apache.poi.hssf.record.Record
1516      * @return record containing a GridsetRecord
1517      */
1518 
1519     protected Record createGridset()
1520     {
1521         GridsetRecord retval = new GridsetRecord();
1522 
1523         retval.setGridset(true);
1524         return retval;
1525     }
1526 
1527     /**
1528      * creates the Guts record and sets leftrow/topcol guttter and rowlevelmax/collevelmax to 0
1529      * @see org.apache.poi.hssf.record.GutsRecord
1530      * @see org.apache.poi.hssf.record.Record
1531      * @return record containing a GutsRecordRecord
1532      */
1533 
1534     protected Record createGuts()
1535     {
1536         GutsRecord retval = new GutsRecord();
1537 
1538         retval.setLeftRowGutter(( short ) 0);
1539         retval.setTopColGutter(( short ) 0);
1540         retval.setRowLevelMax(( short ) 0);
1541         retval.setColLevelMax(( short ) 0);
1542         return retval;
1543     }
1544 
1545     /**
1546      * creates the DefaultRowHeight Record and sets its options to 0 and rowheight to 0xff
1547      * @see org.apache.poi.hssf.record.DefaultRowHeightRecord
1548      * @see org.apache.poi.hssf.record.Record
1549      * @return record containing a DefaultRowHeightRecord
1550      */
1551 
1552     protected Record createDefaultRowHeight()
1553     {
1554         DefaultRowHeightRecord retval = new DefaultRowHeightRecord();
1555 
1556         retval.setOptionFlags(( short ) 0);
1557         retval.setRowHeight(( short ) 0xff);
1558         return retval;
1559     }
1560 
1561     /**
1562      * creates the WSBoolRecord and sets its values to defaults
1563      * @see org.apache.poi.hssf.record.WSBoolRecord
1564      * @see org.apache.poi.hssf.record.Record
1565      * @return record containing a WSBoolRecord
1566      */
1567 
1568     protected Record createWSBool()
1569     {
1570         WSBoolRecord retval = new WSBoolRecord();
1571 
1572         retval.setWSBool1(( byte ) 0x4);
1573         retval.setWSBool2(( byte ) 0xffffffc1);
1574         return retval;
1575     }
1576 
1577     /**
1578      * creates the Header Record and sets it to nothing/0 length
1579      * @see org.apache.poi.hssf.record.HeaderRecord
1580      * @see org.apache.poi.hssf.record.Record
1581      * @return record containing a HeaderRecord
1582      */
1583 
1584     protected Record createHeader()
1585     {
1586         HeaderRecord retval = new HeaderRecord();
1587 
1588         retval.setHeaderLength(( byte ) 0);
1589         retval.setHeader(null);
1590         return retval;
1591     }
1592 
1593     /**
1594      * creates the Footer Record and sets it to nothing/0 length
1595      * @see org.apache.poi.hssf.record.FooterRecord
1596      * @see org.apache.poi.hssf.record.Record
1597      * @return record containing a FooterRecord
1598      */
1599 
1600     protected Record createFooter()
1601     {
1602         FooterRecord retval = new FooterRecord();
1603 
1604         retval.setFooterLength(( byte ) 0);
1605         retval.setFooter(null);
1606         return retval;
1607     }
1608 
1609     /**
1610      * creates the HCenter Record and sets it to false (don't horizontally center)
1611      * @see org.apache.poi.hssf.record.HCenterRecord
1612      * @see org.apache.poi.hssf.record.Record
1613      * @return record containing a HCenterRecord
1614      */
1615 
1616     protected Record createHCenter()
1617     {
1618         HCenterRecord retval = new HCenterRecord();
1619 
1620         retval.setHCenter(false);
1621         return retval;
1622     }
1623 
1624     /**
1625      * creates the VCenter Record and sets it to false (don't horizontally center)
1626      * @see org.apache.poi.hssf.record.VCenterRecord
1627      * @see org.apache.poi.hssf.record.Record
1628      * @return record containing a VCenterRecord
1629      */
1630 
1631     protected Record createVCenter()
1632     {
1633         VCenterRecord retval = new VCenterRecord();
1634 
1635         retval.setVCenter(false);
1636         return retval;
1637     }
1638 
1639     /**
1640      * creates the PrintSetup Record and sets it to defaults and marks it invalid
1641      * @see org.apache.poi.hssf.record.PrintSetupRecord
1642      * @see org.apache.poi.hssf.record.Record
1643      * @return record containing a PrintSetupRecord
1644      */
1645 
1646     protected Record createPrintSetup()
1647     {
1648         PrintSetupRecord retval = new PrintSetupRecord();
1649 
1650         retval.setPaperSize(( short ) 1);
1651         retval.setScale(( short ) 100);
1652         retval.setPageStart(( short ) 1);
1653         retval.setFitWidth(( short ) 1);
1654         retval.setFitHeight(( short ) 1);
1655         retval.setOptions(( short ) 2);
1656         retval.setHResolution(( short ) 300);
1657         retval.setVResolution(( short ) 300);
1658         retval.setHeaderMargin( 0.5);
1659         retval.setFooterMargin( 0.5);
1660         retval.setCopies(( short ) 0);
1661         return retval;
1662     }
1663 
1664     /**
1665      * creates the DefaultColWidth Record and sets it to 8
1666      * @see org.apache.poi.hssf.record.DefaultColWidthRecord
1667      * @see org.apache.poi.hssf.record.Record
1668      * @return record containing a DefaultColWidthRecord
1669      */
1670 
1671     protected Record createDefaultColWidth()
1672     {
1673         DefaultColWidthRecord retval = new DefaultColWidthRecord();
1674 
1675         retval.setColWidth(( short ) 8);
1676         return retval;
1677     }
1678 
1679     /**
1680      * creates the ColumnInfo Record and sets it to a default column/width
1681      * @see org.apache.poi.hssf.record.ColumnInfoRecord
1682      * @return record containing a ColumnInfoRecord
1683      */
1684 
1685     protected Record createColInfo()
1686     {
1687         ColumnInfoRecord retval = new ColumnInfoRecord();
1688 
1689         retval.setColumnWidth(( short ) 0x8);
1690         retval.setOptions(( short ) 6);
1691         retval.setXFIndex(( short ) 0x0f);
1692         return retval;
1693     }
1694 
1695     /**
1696      * get the default column width for the sheet (if the columns do not define their own width)
1697      * @return default column width
1698      */
1699 
1700     public short getDefaultColumnWidth()
1701     {
1702         return defaultcolwidth.getColWidth();
1703     }
1704 
1705     /**
1706      * get whether gridlines are printed.
1707      * @return true if printed
1708      */
1709 
1710     public boolean isGridsPrinted()
1711     {
1712         return !gridset.getGridset();
1713     }
1714 
1715     /**
1716      * set whether gridlines printed or not.
1717      * @param value     True if gridlines printed.
1718      */
1719 
1720     public void setGridsPrinted(boolean value)
1721     {
1722         gridset.setGridset(!value);
1723     }
1724 
1725     /**
1726      * set the default column width for the sheet (if the columns do not define their own width)
1727      * @param dcw  default column width
1728      */
1729 
1730     public void setDefaultColumnWidth(short dcw)
1731     {
1732         defaultcolwidth.setColWidth(dcw);
1733     }
1734 
1735     /**
1736      * set the default row height for the sheet (if the rows do not define their own height)
1737      */
1738 
1739     public void setDefaultRowHeight(short dch)
1740     {
1741         defaultrowheight.setRowHeight(dch);
1742     }
1743 
1744     /**
1745      * get the default row height for the sheet (if the rows do not define their own height)
1746      * @return  default row height
1747      */
1748 
1749     public short getDefaultRowHeight()
1750     {
1751         return defaultrowheight.getRowHeight();
1752     }
1753 
1754     /**
1755      * get the width of a given column in units of 1/20th of a point width (twips?)
1756      * @param column index
1757      * @see org.apache.poi.hssf.record.DefaultColWidthRecord
1758      * @see org.apache.poi.hssf.record.ColumnInfoRecord
1759      * @see #setColumnWidth(short,short)
1760      * @return column width in units of 1/20th of a point (twips?)
1761      */
1762 
1763     public short getColumnWidth(short column)
1764     {
1765         short            retval = 0;
1766         ColumnInfoRecord ci     = null;
1767         int              k      = 0;
1768 
1769         if (columnSizes != null)
1770         {
1771             for (k = 0; k < columnSizes.size(); k++)
1772             {
1773                 ci = ( ColumnInfoRecord ) columnSizes.get(k);
1774                 if ((ci.getFirstColumn() >= column)
1775                         && (column <= ci.getLastColumn()))
1776                 {
1777                     break;
1778                 }
1779                 ci = null;
1780             }
1781         }
1782         if (ci != null)
1783         {
1784             retval = ci.getColumnWidth();
1785         }
1786         else
1787         {
1788             retval = defaultcolwidth.getColWidth();
1789         }
1790         return retval;
1791     }
1792 
1793     /**
1794      * set the width for a given column in 1/20th of a character width units
1795      * @param column - the column number
1796      * @param width (in units of 1/20th of a character width)
1797      */
1798 
1799     public void setColumnWidth(short column, short width)
1800     {
1801         ColumnInfoRecord ci = null;
1802         int              k  = 0;
1803 
1804         if (columnSizes == null)
1805         {
1806             columnSizes = new ArrayList();
1807         }
1808         //int cioffset = getDimsLoc() - columnSizes.size();
1809 
1810         for (k = 0; k < columnSizes.size(); k++)
1811         {
1812             ci = ( ColumnInfoRecord ) columnSizes.get(k);
1813             if ((ci.getFirstColumn() >= column)
1814                     && (column <= ci.getLastColumn()))
1815             {
1816                 break;
1817             }
1818             ci = null;
1819         }
1820         if (ci != null)
1821         {
1822             if (ci.getColumnWidth() == width)
1823             {
1824 
1825                 // do nothing...the cell's width is equal to what we're setting it to.
1826             }
1827             else if ((ci.getFirstColumn() == column)
1828                      && (ci.getLastColumn() == column))
1829             {                               // if its only for this cell then
1830                 ci.setColumnWidth(width);   // who cares, just change the width
1831             }
1832             else if ((ci.getFirstColumn() == column)
1833                      || (ci.getLastColumn() == column))
1834             {
1835 
1836                 // okay so the width is different but the first or last column == the column we'return setting
1837                 // we'll just divide the info and create a new one
1838                 if (ci.getFirstColumn() == column)
1839                 {
1840                     ci.setFirstColumn(( short ) (column + 1));
1841                 }
1842                 else
1843                 {
1844                     ci.setLastColumn(( short ) (column - 1));
1845                 }
1846                 ColumnInfoRecord nci = ( ColumnInfoRecord ) createColInfo();
1847 
1848                 nci.setFirstColumn(column);
1849                 nci.setLastColumn(column);
1850                 nci.setOptions(ci.getOptions());
1851                 nci.setXFIndex(ci.getXFIndex());
1852                 nci.setColumnWidth(width);
1853                 columnSizes.add(k, nci);
1854                 records.add((1 + getDimsLoc() - columnSizes.size()) + k, nci);
1855                 dimsloc++;
1856             }
1857         }
1858         else
1859         {
1860 
1861             // okay so there ISN'T a column info record that cover's this column so lets create one!
1862             ColumnInfoRecord nci = ( ColumnInfoRecord ) createColInfo();
1863 
1864             nci.setFirstColumn(column);
1865             nci.setLastColumn(column);
1866             nci.setColumnWidth(width);
1867             columnSizes.add(k, nci);
1868             records.add((1 + getDimsLoc() - columnSizes.size()) + k, nci);
1869             dimsloc++;
1870         }
1871     }
1872 
1873     /**
1874      * creates the Dimensions Record and sets it to bogus values (you should set this yourself
1875      * or let the high level API do it for you)
1876      * @see org.apache.poi.hssf.record.DimensionsRecord
1877      * @see org.apache.poi.hssf.record.Record
1878      * @return record containing a DimensionsRecord
1879      */
1880 
1881     protected Record createDimensions()
1882     {
1883         DimensionsRecord retval = new DimensionsRecord();
1884 
1885         retval.setFirstCol(( short ) 0);
1886         retval.setLastRow(1);             // one more than it is
1887         retval.setFirstRow(0);
1888         retval.setLastCol(( short ) 1);   // one more than it is
1889         return retval;
1890     }
1891 
1892     /**
1893      * creates the WindowTwo Record and sets it to:  <P>
1894      * options        = 0x6b6 <P>
1895      * toprow         = 0 <P>
1896      * leftcol        = 0 <P>
1897      * headercolor    = 0x40 <P>
1898      * pagebreakzoom  = 0x0 <P>
1899      * normalzoom     = 0x0 <p>
1900      * @see org.apache.poi.hssf.record.WindowTwoRecord
1901      * @see org.apache.poi.hssf.record.Record
1902      * @return record containing a WindowTwoRecord
1903      */
1904 
1905     protected Record createWindowTwo()
1906     {
1907         WindowTwoRecord retval = new WindowTwoRecord();
1908 
1909         retval.setOptions(( short ) 0x6b6);
1910         retval.setTopRow(( short ) 0);
1911         retval.setLeftCol(( short ) 0);
1912         retval.setHeaderColor(0x40);
1913         retval.setPageBreakZoom(( short ) 0);
1914         retval.setNormalZoom(( short ) 0);
1915         return retval;
1916     }
1917 
1918     /**
1919      * Creates the Selection record and sets it to nothing selected
1920      *
1921      * @see org.apache.poi.hssf.record.SelectionRecord
1922      * @see org.apache.poi.hssf.record.Record
1923      * @return record containing a SelectionRecord
1924      */
1925 
1926     protected Record createSelection()
1927     {
1928         SelectionRecord retval = new SelectionRecord();
1929 
1930         retval.setPane(( byte ) 0x3);
1931         retval.setActiveCellCol(( short ) 0x0);
1932         retval.setActiveCellRow(( short ) 0x0);
1933         retval.setNumRefs(( short ) 0x0);
1934         return retval;
1935     }
1936 
1937     protected Record createMergedCells()
1938     {
1939         MergeCellsRecord retval = new MergeCellsRecord();
1940 
1941         retval.setNumAreas(( short ) 0);
1942         return retval;
1943     }
1944 
1945     /**
1946      * creates the EOF record
1947      * @see org.apache.poi.hssf.record.EOFRecord
1948      * @see org.apache.poi.hssf.record.Record
1949      * @return record containing a EOFRecord
1950      */
1951 
1952     protected Record createEOF()
1953     {
1954         return new EOFRecord();
1955     }
1956 
1957     /**
1958      * get the location of the DimensionsRecord (which is the last record before the value section)
1959      * @return location in the array of records of the DimensionsRecord
1960      */
1961 
1962     public int getDimsLoc()
1963     {
1964         log.log(log.DEBUG, "getDimsLoc dimsloc= " + dimsloc);
1965         return dimsloc;
1966     }
1967 
1968     /**
1969      * in the event the record is a dimensions record, resets both the loc index and dimsloc index
1970      */
1971 
1972     public void checkDimsLoc(Record rec, int recloc)
1973     {
1974         if (rec.getSid() == DimensionsRecord.sid)
1975         {
1976             loc     = recloc;
1977             dimsloc = recloc;
1978         }
1979     }
1980 
1981     public int getSize()
1982     {
1983         int retval = 0;
1984 
1985         for (int k = 0; k < records.size(); k++)
1986         {
1987             retval += (( Record ) records.get(k)).getRecordSize();
1988         }
1989         return retval;
1990     }
1991 
1992     public List getRecords()
1993     {
1994         return records;
1995     }
1996 
1997     /**
1998      * Gets the gridset record for this sheet.
1999      */
2000 
2001     public GridsetRecord getGridsetRecord()
2002     {
2003         return gridset;
2004     }
2005 
2006     /**
2007      * Returns the first occurance of a record matching a particular sid.
2008      */
2009 
2010     public Record findFirstRecordBySid(short sid)
2011     {
2012         for (Iterator iterator = records.iterator(); iterator.hasNext(); )
2013         {
2014             Record record = ( Record ) iterator.next();
2015 
2016             if (record.getSid() == sid)
2017             {
2018                 return record;
2019             }
2020         }
2021         return null;
2022     }
2023 
2024     /**
2025      * Returns the HeaderRecord.
2026      * @return HeaderRecord for the sheet.
2027      */
2028     public HeaderRecord getHeader ()
2029     {
2030 	return header;
2031     }
2032 
2033     /**
2034      * Sets the HeaderRecord.
2035      * @param newHeader The new HeaderRecord for the sheet.
2036      */
2037     public void setHeader (HeaderRecord newHeader)
2038     {
2039 	header = newHeader;
2040     }
2041 
2042     /**
2043      * Returns the FooterRecord.
2044      * @return FooterRecord for the sheet.
2045      */
2046     public FooterRecord getFooter ()
2047     {
2048 	return footer;
2049     }
2050 
2051     /**
2052      * Sets the FooterRecord.
2053      * @param newFooter The new FooterRecord for the sheet.
2054      */
2055     public void setFooter (FooterRecord newFooter)
2056     {
2057 	footer = newFooter;
2058     }
2059 
2060     /**
2061      * Returns the PrintSetupRecord.
2062      * @return PrintSetupRecord for the sheet.
2063      */
2064     public PrintSetupRecord getPrintSetup ()
2065     {
2066 	return printSetup;
2067     }
2068 
2069     /**
2070      * Sets the PrintSetupRecord.
2071      * @param newPrintSetup The new PrintSetupRecord for the sheet.
2072      */
2073     public void setPrintSetup (PrintSetupRecord newPrintSetup)
2074     {
2075 	printSetup = newPrintSetup;
2076     }
2077 
2078     /**
2079      * Returns the PrintGridlinesRecord.
2080      * @return PrintGridlinesRecord for the sheet.
2081      */
2082     public PrintGridlinesRecord getPrintGridlines ()
2083     {
2084 	return printGridlines;
2085     }
2086 
2087     /**
2088      * Sets the PrintGridlinesRecord.
2089      * @param newPrintGridlines The new PrintGridlinesRecord for the sheet.
2090      */
2091     public void setPrintGridlines (PrintGridlinesRecord newPrintGridlines)
2092     {
2093 	printGridlines = newPrintGridlines;
2094     }
2095 
2096     /**
2097      * Sets whether the sheet is selected
2098      * @param sel True to select the sheet, false otherwise.
2099      */
2100     public void setSelected(boolean sel) {
2101 	WindowTwoRecord windowTwo = (WindowTwoRecord) findFirstRecordBySid(WindowTwoRecord.sid);
2102 	windowTwo.setSelected(sel);
2103     }
2104 
2105      /**
2106       * Gets the size of the margin in inches.
2107       * @param margin which margin to get
2108       * @return the size of the margin
2109       */
2110      public double getMargin(short margin) {
2111        Margin m;
2112        switch (margin) {
2113        case LeftMargin :
2114            m = (Margin)findFirstRecordBySid(LeftMarginRecord.sid);
2115            if (m == null)
2116                return .75;
2117            break;
2118        case RightMargin :
2119            m = (Margin)findFirstRecordBySid(RightMarginRecord.sid);
2120            if (m == null)
2121                return .75;
2122            break;
2123        case TopMargin :
2124            m = (Margin)findFirstRecordBySid(TopMarginRecord.sid);
2125            if (m == null)
2126                return 1.0;
2127            break;
2128        case BottomMargin :
2129            m = (Margin)findFirstRecordBySid(BottomMarginRecord.sid);
2130            if (m == null)
2131                return 1.0;
2132            break;
2133        default : throw new RuntimeException("Unknown margin constant:  " + margin);
2134        }
2135        return m.getMargin();
2136      }
2137 
2138      /**
2139       * Sets the size of the margin in inches.
2140       * @param margin which margin to get
2141       * @param size the size of the margin
2142       */
2143      public void setMargin(short margin, double size) {
2144        Margin m;
2145        switch (margin) {
2146        case LeftMargin :
2147            m = (Margin)findFirstRecordBySid(LeftMarginRecord.sid);
2148            if (m == null) {
2149                m = new LeftMarginRecord();
2150                records.add(getDimsLoc() + 1, (Record)m);
2151            }
2152            break;
2153        case RightMargin :
2154            m = (Margin)findFirstRecordBySid(RightMarginRecord.sid);
2155            if (m == null) {
2156                m = new RightMarginRecord();
2157                records.add(getDimsLoc() + 1, (Record)m);
2158            }
2159            break;
2160        case TopMargin :
2161            m = (Margin)findFirstRecordBySid(TopMarginRecord.sid);
2162            if (m == null) {
2163                m = new TopMarginRecord();
2164                records.add(getDimsLoc() + 1, (Record)m);
2165            }
2166            break;
2167        case BottomMargin :
2168            m = (Margin)findFirstRecordBySid(BottomMarginRecord.sid);
2169            if (m == null) {
2170                m = new BottomMarginRecord();
2171                records.add(getDimsLoc() + 1, (Record)m);
2172            }
2173            break;
2174        default : throw new RuntimeException("Unknown margin constant:  " + margin);
2175        }
2176        m.setMargin(size);
2177      }
2178 
2179     public int getEofLoc()
2180     {
2181         return eofLoc;
2182     }
2183 
2184 }
2185