Open Source Repository

Home /excel/jxl-2.6.12 | Repository Home



jxl/read/biff/SheetReader.java
/*********************************************************************
*
*      Copyright (C) 2002 Andrew Khan
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
***************************************************************************/

package jxl.read.biff;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import jxl.common.Assert;
import jxl.common.Logger;

import jxl.Cell;
import jxl.CellFeatures;
import jxl.CellReferenceHelper;
import jxl.CellType;
import jxl.DateCell;
import jxl.HeaderFooter;
import jxl.Range;
import jxl.SheetSettings;
import jxl.WorkbookSettings;
import jxl.biff.AutoFilter;
import jxl.biff.AutoFilterInfoRecord;
import jxl.biff.AutoFilterRecord;
import jxl.biff.ConditionalFormat;
import jxl.biff.ConditionalFormatRangeRecord;
import jxl.biff.ConditionalFormatRecord;
import jxl.biff.ContinueRecord;
import jxl.biff.DataValidation;
import jxl.biff.DataValidityListRecord;
import jxl.biff.DataValiditySettingsRecord;
import jxl.biff.FilterModeRecord;
import jxl.biff.FormattingRecords;
import jxl.biff.Type;
import jxl.biff.WorkspaceInformationRecord;
import jxl.biff.drawing.Button;
import jxl.biff.drawing.Chart;
import jxl.biff.drawing.CheckBox;
import jxl.biff.drawing.ComboBox;
import jxl.biff.drawing.Comment;
import jxl.biff.drawing.Drawing;
import jxl.biff.drawing.Drawing2;
import jxl.biff.drawing.DrawingData;
import jxl.biff.drawing.DrawingDataException;
import jxl.biff.drawing.MsoDrawingRecord;
import jxl.biff.drawing.NoteRecord;
import jxl.biff.drawing.ObjRecord;
import jxl.biff.drawing.TextObjectRecord;
import jxl.biff.formula.FormulaException;
import jxl.format.PageOrder;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;

/**
 * Reads the sheet.  This functionality was originally part of the
 * SheetImpl class, but was separated out in order to simplify the former
 * class
 */
final class SheetReader
{
  /**
   * The logger
   */
  private static Logger logger = Logger.getLogger(SheetReader.class);

  /**
   * The excel file
   */
  private File excelFile;

  /**
   * A handle to the shared string table
   */
  private SSTRecord sharedStrings;

  /**
   * A handle to the sheet BOF record, which indicates the stream type
   */
  private BOFRecord sheetBof;

  /**
   * A handle to the workbook BOF record, which indicates the stream type
   */
  private BOFRecord workbookBof;

  /**
   * A handle to the formatting records
   */
  private FormattingRecords formattingRecords;

  /**
   * The  number of rows
   */
  private int numRows;

  /**
   * The number of columns
   */
  private int numCols;

  /**
   * The cells
   */
  private Cell[][] cells;

  /**
   * Any cells which are out of the defined bounds
   */
  private ArrayList outOfBoundsCells;

  /**
   * The start position in the stream of this sheet
   */
  private int startPosition;

  /**
   * The list of non-default row properties
   */
  private ArrayList rowProperties;

  /**
   * An array of column info records.  They are held this way before
   * they are transferred to the more convenient array
   */
  private ArrayList columnInfosArray;

  /**
   * A list of shared formula groups
   */
  private ArrayList sharedFormulas;

  /**
   * A list of hyperlinks on this page
   */
  private ArrayList hyperlinks;

  /**
   * The list of conditional formats on this page
   */
  private ArrayList conditionalFormats;

  /**
   * The autofilter information
   */
  private AutoFilter autoFilter;

  /**
   * A list of merged cells on this page
   */
  private Range[] mergedCells;

  /**
   * The list of data validations on this page
   */
  private DataValidation dataValidation;

  /**
   * The list of charts on this page
   */
  private ArrayList charts;

  /**
   * The list of drawings on this page
   */
  private ArrayList drawings;

  /**
   * The drawing data for the drawings
   */
  private DrawingData drawingData;

  /**
   * Indicates whether or not the dates are based around the 1904 date system
   */
  private boolean nineteenFour;

  /**
   * The PLS print record
   */
  private PLSRecord plsRecord;

  /**
   * The property set record associated with this workbook
   */
  private ButtonPropertySetRecord buttonPropertySet;

  /**
   * The workspace options
   */
  private WorkspaceInformationRecord workspaceOptions;

  /**
   * The horizontal page breaks contained on this sheet
   */
  private int[] rowBreaks;

  /**
   * The vertical page breaks contained on this sheet
   */
  private int[] columnBreaks;

  /**
   * The maximum row outline level
   */
  private int maxRowOutlineLevel;

  /**
   * The maximum column outline level
   */
  private int maxColumnOutlineLevel;

  /**
   * The sheet settings
   */
  private SheetSettings settings;

  /**
   * The workbook settings
   */
  private WorkbookSettings workbookSettings;

  /**
   * A handle to the workbook which contains this sheet.  Some of the records
   * need this in order to reference external sheets
   */
  private WorkbookParser workbook;

  /**
   * A handle to the sheet
   */
  private SheetImpl sheet;

  /**
   * Constructor
   *
   @param fr the formatting records
   @param sst the shared string table
   @param f the excel file
   @param sb the bof record which indicates the start of the sheet
   @param wb the bof record which indicates the start of the sheet
   @param wp the workbook which this sheet belongs to
   @param sp the start position of the sheet bof in the excel file
   @param sh the sheet
   @param nf 1904 date record flag
   @exception BiffException
   */
  SheetReader(File f,
              SSTRecord sst,
              FormattingRecords fr,
              BOFRecord sb,
              BOFRecord wb,
              boolean nf,
              WorkbookParser wp,
              int sp,
              SheetImpl sh)
  {
    excelFile = f;
    sharedStrings = sst;
    formattingRecords = fr;
    sheetBof = sb;
    workbookBof = wb;
    columnInfosArray = new ArrayList();
    sharedFormulas = new ArrayList();
    hyperlinks = new ArrayList();
    conditionalFormats = new ArrayList();
    rowProperties = new ArrayList(10);
    charts = new ArrayList();
    drawings = new ArrayList();
    outOfBoundsCells = new ArrayList();
    nineteenFour = nf;
    workbook = wp;
    startPosition = sp;
    sheet = sh;
    settings = new SheetSettings(sh);
    workbookSettings = workbook.getSettings();
  }

  /**
   * Adds the cell to the array
   *
   @param cell the cell to add
   */
  private void addCell(Cell cell)
  {
    // Sometimes multiple cells (eg. MULBLANK) can exceed the
    // column/row boundaries.  Ignore these
    if (cell.getRow() < numRows && cell.getColumn() < numCols)
    {
      if (cells[cell.getRow()][cell.getColumn()] != null)
      {
        StringBuffer sb = new StringBuffer();
        CellReferenceHelper.getCellReference
          (cell.getColumn(), cell.getRow(), sb);
        logger.warn("Cell " + sb.toString() +
                    " already contains data");
      }
      cells[cell.getRow()][cell.getColumn()] = cell;
    }
    else
    {
      outOfBoundsCells.add(cell);
      /*
        logger.warn("Cell " +
        CellReferenceHelper.getCellReference
        (cell.getColumn(), cell.getRow()) +
        " exceeds defined cell boundaries in Dimension record " +
        "(" + numCols + "x" + numRows + ")");
      */
    }
  }

  /**
   * Reads in the contents of this sheet
   */
  final void read()
  {
    Record r = null;
    BaseSharedFormulaRecord sharedFormula = null;
    boolean sharedFormulaAdded = false;

    boolean cont = true;

    // Set the position within the file
    excelFile.setPos(startPosition);

    // Handles to the last drawing and obj records
    MsoDrawingRecord msoRecord = null;
    ObjRecord objRecord = null;
    boolean firstMsoRecord = true;
    
    // Handle to the last conditional format record
    ConditionalFormat condFormat = null;

    // Handle to the autofilter records
    FilterModeRecord filterMode = null;
    AutoFilterInfoRecord autoFilterInfo = null;

    // A handle to window2 record
    Window2Record window2Record = null;

    // A handle to printgridlines record
    PrintGridLinesRecord printGridLinesRecord = null;

    // A handle to printheaders record
    PrintHeadersRecord printHeadersRecord = null;

    // Hash map of comments, indexed on objectId.  As each corresponding
    // note record is encountered, these are removed from the array
    HashMap comments = new HashMap();

    // A list of object ids - used for cross referencing
    ArrayList objectIds = new ArrayList();

    // A handle to a continue record read in
    ContinueRecord continueRecord = null;

    while (cont)
    {
      r = excelFile.next();
      Type type = r.getType();

      if (type == Type.UNKNOWN && r.getCode() == 0)
      {
        logger.warn("Biff code zero found");

        // Try a dimension record
        if (r.getLength() == 0xa)
        {
          logger.warn("Biff code zero found - trying a dimension record.");
          r.setType(Type.DIMENSION);
        }
        else
        {
          logger.warn("Biff code zero found - Ignoring.");
        }
      }

      if (type == Type.DIMENSION)
      {
        DimensionRecord dr = null;

        if (workbookBof.isBiff8())
        {
          dr = new DimensionRecord(r);
        }
        else
        {
          dr = new DimensionRecord(r, DimensionRecord.biff7);
        }
        numRows = dr.getNumberOfRows();
        numCols = dr.getNumberOfColumns();
        cells = new Cell[numRows][numCols];
      }
      else if (type == Type.LABELSST)
      {
        LabelSSTRecord label = new LabelSSTRecord(r,
                                                  sharedStrings,
                                                  formattingRecords,
                                                  sheet);
        addCell(label);
      }
      else if (type == Type.RK || type == Type.RK2)
      {
        RKRecord rkr = new RKRecord(r, formattingRecords, sheet);

        if (formattingRecords.isDate(rkr.getXFIndex()))
        {
          DateCell dc = new DateRecord
            (rkr, rkr.getXFIndex(), formattingRecords, nineteenFour, sheet);
          addCell(dc);
        }
        else
        {
          addCell(rkr);
        }
      }
      else if (type == Type.HLINK)
      {
        HyperlinkRecord hr = new HyperlinkRecord(r, sheet, workbookSettings);
        hyperlinks.add(hr);
      }
      else if (type == Type.MERGEDCELLS)
      {
        MergedCellsRecord  mc = new MergedCellsRecord(r, sheet);
        if (mergedCells == null)
        {
          mergedCells = mc.getRanges();
        }
        else
        {
          Range[] newMergedCells =
            new Range[mergedCells.length + mc.getRanges().length];
          System.arraycopy(mergedCells, 0, newMergedCells, 0,
                           mergedCells.length);
          System.arraycopy(mc.getRanges(),
                           0,
                           newMergedCells, mergedCells.length,
                           mc.getRanges().length);
          mergedCells = newMergedCells;
        }
      }
      else if (type == Type.MULRK)
      {
        MulRKRecord mulrk = new MulRKRecord(r);

        // Get the individual cell records from the multiple record
        int num = mulrk.getNumberOfColumns();
        int ixf = 0;
        for (int i = 0; i < num; i++)
        {
          ixf = mulrk.getXFIndex(i);

          NumberValue nv = new NumberValue
            (mulrk.getRow(),
             mulrk.getFirstColumn() + i,
             RKHelper.getDouble(mulrk.getRKNumber(i)),
             ixf,
             formattingRecords,
             sheet);


          if (formattingRecords.isDate(ixf))
          {
            DateCell dc = new DateRecord(nv, 
                                         ixf, 
                                         formattingRecords,
                                         nineteenFour, 
                                         sheet);
            addCell(dc);
          }
          else
          {
            nv.setNumberFormat(formattingRecords.getNumberFormat(ixf));
            addCell(nv);
          }
        }
      }
      else if (type == Type.NUMBER)
      {
        NumberRecord nr = new NumberRecord(r, formattingRecords, sheet);

        if (formattingRecords.isDate(nr.getXFIndex()))
        {
          DateCell dc = new DateRecord(nr,
                                       nr.getXFIndex(),
                                       formattingRecords,
                                       nineteenFour, sheet);
          addCell(dc);
        }
        else
        {
          addCell(nr);
        }
      }
      else if (type == Type.BOOLERR)
      {
        BooleanRecord br = new BooleanRecord(r, formattingRecords, sheet);

        if (br.isError())
        {
          ErrorRecord er = new ErrorRecord(br.getRecord(), formattingRecords,
                                           sheet);
          addCell(er);
        }
        else
        {
          addCell(br);
        }
      }
      else if (type == Type.PRINTGRIDLINES)
      {
        printGridLinesRecord = new PrintGridLinesRecord(r);
        settings.setPrintGridLines(printGridLinesRecord.getPrintGridLines());
      }
      else if (type == Type.PRINTHEADERS)
      {
        printHeadersRecord = new PrintHeadersRecord(r);
        settings.setPrintHeaders(printHeadersRecord.getPrintHeaders());
      }
      else if (type == Type.WINDOW2)
      {
        window2Record = null;

        if (workbookBof.isBiff8())
        {
          window2Record = new Window2Record(r);
        }
        else
        {
          window2Record = new Window2Record(r, Window2Record.biff7);
        }

        settings.setShowGridLines(window2Record.getShowGridLines());
        settings.setDisplayZeroValues(window2Record.getDisplayZeroValues());
        settings.setSelected(true);
        settings.setPageBreakPreviewMode(window2Record.isPageBreakPreview());
      }
      else if (type == Type.PANE)
      {
        PaneRecord pr = new PaneRecord(r);

        if (window2Record != null &&
            window2Record.getFrozen())
        {
          settings.setVerticalFreeze(pr.getRowsVisible());
          settings.setHorizontalFreeze(pr.getColumnsVisible());
        }
      }
      else if (type == Type.CONTINUE)
      {
        // don't know what this is for, but keep hold of it anyway
        continueRecord = new ContinueRecord(r);
      }
      else if (type == Type.NOTE)
      {
        if (!workbookSettings.getDrawingsDisabled())
        {
          NoteRecord nr = new NoteRecord(r);

          // Get the comment for the object id
          Comment comment = (Commentcomments.remove
            (new Integer(nr.getObjectId()));

          if (comment == null)
          {
            logger.warn(" cannot find comment for note id " +
                        nr.getObjectId() "...ignoring");
          }
          else
          {
            comment.setNote(nr);

            drawings.add(comment);

            addCellComment(comment.getColumn(),
                           comment.getRow(),
                           comment.getText(),
                           comment.getWidth(),
                           comment.getHeight());
          }
        }
      }
      else if (type == Type.ARRAY)
      {
        ;
      }
      else if (type == Type.PROTECT)
      {
        ProtectRecord pr = new ProtectRecord(r);
        settings.setProtected(pr.isProtected());
      }
      else if (type == Type.SHAREDFORMULA)
      {
        if (sharedFormula == null)
        {
          logger.warn("Shared template formula is null - " +
                      "trying most recent formula template");
          SharedFormulaRecord lastSharedFormula =
            (SharedFormulaRecordsharedFormulas.get(sharedFormulas.size() 1);

          if (lastSharedFormula != null)
          {
            sharedFormula = lastSharedFormula.getTemplateFormula();
          }
        }

        SharedFormulaRecord sfr = new SharedFormulaRecord
          (r, sharedFormula, workbook, workbook, sheet);
        sharedFormulas.add(sfr);
        sharedFormula = null;
      }
      else if (type == Type.FORMULA || type == Type.FORMULA2)
      {
        FormulaRecord fr = new FormulaRecord(r,
                                             excelFile,
                                             formattingRecords,
                                             workbook,
                                             workbook,
                                             sheet,
                                             workbookSettings);

        if (fr.isShared())
        {
          BaseSharedFormulaRecord prevSharedFormula = sharedFormula;
          sharedFormula = (BaseSharedFormulaRecordfr.getFormula();

          // See if it fits in any of the shared formulas
          sharedFormulaAdded = addToSharedFormulas(sharedFormula);

          if (sharedFormulaAdded)
          {
            sharedFormula = prevSharedFormula;
          }

          // If we still haven't added the previous base shared formula,
          // revert it to an ordinary formula and add it to the cell
          if (!sharedFormulaAdded && prevSharedFormula != null)
          {
            // Do nothing.  It's possible for the biff file to contain the
            // record sequence
            // FORMULA-SHRFMLA-FORMULA-SHRFMLA-FORMULA-FORMULA-FORMULA
            // ie. it first lists all the formula templates, then it
            // lists all the individual formulas
            addCell(revertSharedFormula(prevSharedFormula));
          }
        }
        else
        {
          Cell cell = fr.getFormula();
          try
          {
            // See if the formula evaluates to date
            if (fr.getFormula().getType() == CellType.NUMBER_FORMULA)
            {
              NumberFormulaRecord nfr = (NumberFormulaRecordfr.getFormula();
              if (formattingRecords.isDate(nfr.getXFIndex()))
              {
                cell = new DateFormulaRecord(nfr,
                                             formattingRecords,
                                             workbook,
                                             workbook,
                                             nineteenFour,
                                             sheet);
              }
            }
            
            addCell(cell);
          }
          catch (FormulaException e)
          {
            // Something has gone wrong trying to read the formula data eg. it
            // might be unsupported biff7 data
            logger.warn
              (CellReferenceHelper.getCellReference
               (cell.getColumn(), cell.getRow()) " " + e.getMessage());
          }
        }
      }
      else if (type == Type.LABEL)
      {
        LabelRecord lr = null;

        if (workbookBof.isBiff8())
        {
          lr = new LabelRecord(r, formattingRecords, sheet, workbookSettings);
        }
        else
        {
          lr = new LabelRecord(r, formattingRecords, sheet, workbookSettings,
                               LabelRecord.biff7);
        }
        addCell(lr);
      }
      else if (type == Type.RSTRING)
      {
        RStringRecord lr = null;

        // RString records are obsolete in biff 8
        Assert.verify(!workbookBof.isBiff8());
        lr = new RStringRecord(r, formattingRecords,
                               sheet, workbookSettings,
                               RStringRecord.biff7);
        addCell(lr);
      }
      else if (type == Type.NAME)
      {
        ;
      }
      else if (type == Type.PASSWORD)
      {
        PasswordRecord pr = new PasswordRecord(r);
        settings.setPasswordHash(pr.getPasswordHash());
      }
      else if (type == Type.ROW)
      {
        RowRecord rr = new RowRecord(r);

        // See if the row has anything funny about it
        if (!rr.isDefaultHeight() ||
            !rr.matchesDefaultFontHeight() ||
            rr.isCollapsed() ||
            rr.hasDefaultFormat() || 
            rr.getOutlineLevel() != 0)
        {
          rowProperties.add(rr);
        }
      }
      else if (type == Type.BLANK)
      {
        if (!workbookSettings.getIgnoreBlanks())
        {
          BlankCell bc = new BlankCell(r, formattingRecords, sheet);
          addCell(bc);
        }
      }
      else if (type == Type.MULBLANK)
      {
        if (!workbookSettings.getIgnoreBlanks())
        {
          MulBlankRecord mulblank = new MulBlankRecord(r);

          // Get the individual cell records from the multiple record
          int num = mulblank.getNumberOfColumns();

          for (int i = 0; i < num; i++)
          {
            int ixf = mulblank.getXFIndex(i);

            MulBlankCell mbc = new MulBlankCell
              (mulblank.getRow(),
               mulblank.getFirstColumn() + i,
               ixf,
               formattingRecords,
               sheet);
            
            addCell(mbc);
          }
        }
      }
      else if (type == Type.SCL)
      {
        SCLRecord scl = new SCLRecord(r);
        settings.setZoomFactor(scl.getZoomFactor());
      }
      else if (type == Type.COLINFO)
      {
        ColumnInfoRecord cir = new ColumnInfoRecord(r);
        columnInfosArray.add(cir);
      }
      else if (type == Type.HEADER)
      {
        HeaderRecord hr = null;
        if (workbookBof.isBiff8())
        {
          hr = new HeaderRecord(r, workbookSettings);
        }
        else
        {
          hr = new HeaderRecord(r, workbookSettings, HeaderRecord.biff7);
        }

        HeaderFooter header = new HeaderFooter(hr.getHeader());
        settings.setHeader(header);
      }
      else if (type == Type.FOOTER)
      {
        FooterRecord fr = null;
        if (workbookBof.isBiff8())
        {
          fr = new FooterRecord(r, workbookSettings);
        }
        else
        {
          fr = new FooterRecord(r, workbookSettings, FooterRecord.biff7);
        }

        HeaderFooter footer = new HeaderFooter(fr.getFooter());
        settings.setFooter(footer);
      }
      else if (type == Type.SETUP)
      {
        SetupRecord sr = new SetupRecord(r);
        
        // If the setup record has its not initialized bit set, then
        // use the sheet settings default values
        if (sr.getInitialized())
        {
          if (sr.isPortrait())
          {
            settings.setOrientation(PageOrientation.PORTRAIT);
          }
          else
          {
            settings.setOrientation(PageOrientation.LANDSCAPE);
          }
          if (sr.isRightDown())
          {
              settings.setPageOrder(PageOrder.RIGHT_THEN_DOWN);
          }
          else
          {
              settings.setPageOrder(PageOrder.DOWN_THEN_RIGHT);
          }
          settings.setPaperSize(PaperSize.getPaperSize(sr.getPaperSize()));
          settings.setHeaderMargin(sr.getHeaderMargin());
          settings.setFooterMargin(sr.getFooterMargin());
          settings.setScaleFactor(sr.getScaleFactor());
          settings.setPageStart(sr.getPageStart());
          settings.setFitWidth(sr.getFitWidth());
          settings.setFitHeight(sr.getFitHeight());
          settings.setHorizontalPrintResolution
            (sr.getHorizontalPrintResolution());
          settings.setVerticalPrintResolution(sr.getVerticalPrintResolution());
          settings.setCopies(sr.getCopies());

          if (workspaceOptions != null)
          {
            settings.setFitToPages(workspaceOptions.getFitToPages());
          }
        }
      }
      else if (type == Type.WSBOOL)
      {
        workspaceOptions = new WorkspaceInformationRecord(r);
      }
      else if (type == Type.DEFCOLWIDTH)
      {
        DefaultColumnWidthRecord dcwr = new DefaultColumnWidthRecord(r);
        settings.setDefaultColumnWidth(dcwr.getWidth());
      }
      else if (type == Type.DEFAULTROWHEIGHT)
      {
        DefaultRowHeightRecord drhr = new DefaultRowHeightRecord(r);
        if (drhr.getHeight() != 0)
        {
          settings.setDefaultRowHeight(drhr.getHeight());
        }
      }
      else if (type == Type.CONDFMT)
      {
        ConditionalFormatRangeRecord cfrr = 
          new ConditionalFormatRangeRecord(r);
        condFormat = new ConditionalFormat(cfrr);
        conditionalFormats.add(condFormat);
      }
      else if (type == Type.CF)
      {
        ConditionalFormatRecord cfr = new ConditionalFormatRecord(r);
        condFormat.addCondition(cfr);
      }
      else if (type == Type.FILTERMODE)
      {
        filterMode = new FilterModeRecord(r);
      }
      else if (type == Type.AUTOFILTERINFO)
      {
        autoFilterInfo = new AutoFilterInfoRecord(r);
      
      else if (type == Type.AUTOFILTER)
      {
        if (!workbookSettings.getAutoFilterDisabled())
        {
          AutoFilterRecord af = new AutoFilterRecord(r);

          if (autoFilter == null)
          {
            autoFilter = new AutoFilter(filterMode, autoFilterInfo);
            filterMode = null;
            autoFilterInfo = null;
          }

          autoFilter.add(af);
        }
      }
      else if (type == Type.LEFTMARGIN)
      {
        MarginRecord m = new LeftMarginRecord(r);
        settings.setLeftMargin(m.getMargin());
      }
      else if (type == Type.RIGHTMARGIN)
      {
        MarginRecord m = new RightMarginRecord(r);
        settings.setRightMargin(m.getMargin());
      }
      else if (type == Type.TOPMARGIN)
      {
        MarginRecord m = new TopMarginRecord(r);
        settings.setTopMargin(m.getMargin());
      }
      else if (type == Type.BOTTOMMARGIN)
      {
        MarginRecord m = new BottomMarginRecord(r);
        settings.setBottomMargin(m.getMargin());
      }
      else if (type == Type.HORIZONTALPAGEBREAKS)
      {
        HorizontalPageBreaksRecord dr = null;

        if (workbookBof.isBiff8())
        {
          dr = new HorizontalPageBreaksRecord(r);
        }
        else
        {
          dr = new HorizontalPageBreaksRecord
            (r, HorizontalPageBreaksRecord.biff7);
        }
        rowBreaks = dr.getRowBreaks();
      }
      else if (type == Type.VERTICALPAGEBREAKS)
      {
        VerticalPageBreaksRecord dr = null;

        if (workbookBof.isBiff8())
        {
          dr = new VerticalPageBreaksRecord(r);
        }
        else
        {
          dr = new VerticalPageBreaksRecord
            (r, VerticalPageBreaksRecord.biff7);
        }
        columnBreaks = dr.getColumnBreaks();
      }
      else if (type == Type.PLS)
      {
        plsRecord = new PLSRecord(r);

        // Check for Continue records
        while (excelFile.peek().getType() == Type.CONTINUE)
        {
          r.addContinueRecord(excelFile.next());
        }
      }
      else if (type == Type.DVAL)
      {
        if (!workbookSettings.getCellValidationDisabled())
        {
          DataValidityListRecord dvlr = new DataValidityListRecord(r);
          if (dvlr.getObjectId() == -1)
          {
            if (msoRecord != null && objRecord == null)
            {
              // there is a drop down associated with this data validation
              if (drawingData == null)
              {
                drawingData = new DrawingData();
              }

              Drawing2 d2 = new Drawing2(msoRecord, drawingData, 
                                         workbook.getDrawingGroup());
              drawings.add(d2);
              msoRecord = null;

              dataValidation = new DataValidation(dvlr);
            }
            else
            {
              // no drop down
              dataValidation = new DataValidation(dvlr);
            }
          }
          else if (objectIds.contains(new Integer(dvlr.getObjectId())))
          {
            dataValidation = new DataValidation(dvlr);
          }
          else
          {
            logger.warn("object id " + dvlr.getObjectId() " referenced " +
                        " by data validity list record not found - ignoring");
          }
        }
      }
      else if (type == Type.HCENTER)
      {
        CentreRecord hr = new CentreRecord(r);
        settings.setHorizontalCentre(hr.isCentre());
      
      else if (type == Type.VCENTER)
      {
        CentreRecord vc = new CentreRecord(r);
        settings.setVerticalCentre(vc.isCentre());
      }
      else if (type == Type.DV)
      {
        if (!workbookSettings.getCellValidationDisabled())
        {
          DataValiditySettingsRecord dvsr = 
            new DataValiditySettingsRecord(r, 
                                           workbook, 
                                           workbook,
                                           workbook.getSettings());
          if (dataValidation != null)
          {
            dataValidation.add(dvsr);
            addCellValidation(dvsr.getFirstColumn(),
                              dvsr.getFirstRow(),
                              dvsr.getLastColumn(),
                              dvsr.getLastRow()
                              dvsr);
          }
          else
          {
            logger.warn("cannot add data validity settings");
          }
        }
      }
      else if (type == Type.OBJ)
      {
        objRecord = new ObjRecord(r);

        if (!workbookSettings.getDrawingsDisabled())
        {
          // sometimes excel writes out continue records instead of drawing
          // records, so forcibly hack the stashed continue record into
          // a drawing record
          if (msoRecord == null && continueRecord != null)
          {
            logger.warn("Cannot find drawing record - using continue record");
            msoRecord = new MsoDrawingRecord(continueRecord.getRecord());
            continueRecord = null;
          }
          handleObjectRecord(objRecord, msoRecord, comments);
          objectIds.add(new Integer(objRecord.getObjectId()));
        }

        // Save chart handling until the chart BOF record appears
        if (objRecord.getType() != ObjRecord.CHART)
        {
          objRecord = null;
          msoRecord = null;
        }
      }
      else if (type == Type.MSODRAWING)
      {
        if (!workbookSettings.getDrawingsDisabled())
        {
          if (msoRecord != null)
          {
            // For form controls, a rogue MSODRAWING record can crop up
            // after the main one.  Add these into the drawing data
            drawingData.addRawData(msoRecord.getData());
          }
          msoRecord = new MsoDrawingRecord(r);

          if (firstMsoRecord)
          {
            msoRecord.setFirst();
            firstMsoRecord = false;
          }
        }
      }
      else if (type == Type.BUTTONPROPERTYSET)
      {
        buttonPropertySet = new ButtonPropertySetRecord(r);
      }
      else if (type == Type.CALCMODE)
      {
        CalcModeRecord cmr = new CalcModeRecord(r);
        settings.setAutomaticFormulaCalculation(cmr.isAutomatic());
      }
      else if (type == Type.SAVERECALC)
      {
        SaveRecalcRecord cmr = new SaveRecalcRecord(r);
        settings.setRecalculateFormulasBeforeSave(cmr.getRecalculateOnSave());
      }
      else if (type == Type.GUTS)
      {
        GuttersRecord gr = new GuttersRecord(r);
        maxRowOutlineLevel = 
          gr.getRowOutlineLevel() ? gr.getRowOutlineLevel() 0;
        maxColumnOutlineLevel = 
          gr.getColumnOutlineLevel() ? gr.getRowOutlineLevel() 0;
      }
      else if (type == Type.BOF)
      {
        BOFRecord br = new BOFRecord(r);
        Assert.verify(!br.isWorksheet());

        int startpos = excelFile.getPos() - r.getLength() 4;

        // Skip to the end of the nested bof
        // Thanks to Rohit for spotting this
        Record r2 = excelFile.next();
        while (r2.getCode() != Type.EOF.value)
        {
          r2 = excelFile.next();
        }

        if (br.isChart())
        {
          if (!workbook.getWorkbookBof().isBiff8())
          {
            logger.warn("only biff8 charts are supported");
          }
          else
          {
            if (drawingData == null)
            {
              drawingData = new DrawingData();
            }
          
            if (!workbookSettings.getDrawingsDisabled())
            {
              Chart chart = new Chart(msoRecord, objRecord, drawingData,
                                      startpos, excelFile.getPos(),
                                      excelFile, workbookSettings);
              charts.add(chart);

              if (workbook.getDrawingGroup() != null)
              {
                workbook.getDrawingGroup().add(chart);
              }
            }
          }

          // Reset the drawing records
          msoRecord = null;
          objRecord = null;
        }

        // If this worksheet is just a chart, then the EOF reached
        // represents the end of the sheet as well as the end of the chart
        if (sheetBof.isChart())
        {
          cont = false;
        }
      }
      else if (type == Type.EOF)
      {
        cont = false;
      }
    }

    // Restore the file to its accurate position
    excelFile.restorePos();

    // Add any out of bounds cells
    if (outOfBoundsCells.size() 0)
    {
      handleOutOfBoundsCells();
    }

    // Add all the shared formulas to the sheet as individual formulas
    Iterator i = sharedFormulas.iterator();

    while (i.hasNext())
    {
      SharedFormulaRecord sfr = (SharedFormulaRecordi.next();

      Cell[] sfnr = sfr.getFormulas(formattingRecords, nineteenFour);

      for (int sf = 0; sf < sfnr.length; sf++)
      {
        addCell(sfnr[sf]);
      }
    }

    // If the last base shared formula wasn't added to the sheet, then
    // revert it to an ordinary formula and add it
    if (!sharedFormulaAdded && sharedFormula != null)
    {
      addCell(revertSharedFormula(sharedFormula));
    }

    // If there is a stray msoDrawing record, then flag to the drawing group
    // that one has been omitted
    if (msoRecord != null && workbook.getDrawingGroup() != null)
    {
      workbook.getDrawingGroup().setDrawingsOmitted(msoRecord, objRecord);
    }
      
    // Check that the comments hash is empty
    if (!comments.isEmpty())
    {
      logger.warn("Not all comments have a corresponding Note record");
    }
  }

  /**
   * Sees if the shared formula belongs to any of the shared formula
   * groups
   *
   @param fr the candidate shared formula
   @return TRUE if the formula was added, FALSE otherwise
   */
  private boolean addToSharedFormulas(BaseSharedFormulaRecord fr)
  {
    boolean added = false;
    SharedFormulaRecord sfr = null;

    for (int i=0, size=sharedFormulas.size(); i<size && !added; ++i
    {
      sfr = (SharedFormulaRecord sharedFormulas.get(i);
      added = sfr.add(fr);
    }

    return added;
  }

  /**
   * Reverts the shared formula passed in to an ordinary formula and adds
   * it to the list
   *
   @param f the formula
   @return the new formula
   @exception FormulaException
   */
  private Cell revertSharedFormula(BaseSharedFormulaRecord f
  {
    // String formulas look for a STRING record soon after the formula
    // occurred.  Temporarily the position in the excel file back
    // to the point immediately after the formula record
    int pos = excelFile.getPos();
    excelFile.setPos(f.getFilePos());

    FormulaRecord fr = new FormulaRecord(f.getRecord(),
                                         excelFile,
                                         formattingRecords,
                                         workbook,
                                         workbook,
                                         FormulaRecord.ignoreSharedFormula,
                                         sheet,
                                         workbookSettings);

    try
    {
    Cell cell = fr.getFormula();

    // See if the formula evaluates to date
    if (fr.getFormula().getType() == CellType.NUMBER_FORMULA)
    {
      NumberFormulaRecord nfr = (NumberFormulaRecordfr.getFormula();
      if (formattingRecords.isDate(fr.getXFIndex()))
      {
        cell = new DateFormulaRecord(nfr,
                                     formattingRecords,
                                     workbook,
                                     workbook,
                                     nineteenFour,
                                     sheet);
      }
    }

    excelFile.setPos(pos);
    return cell;
    }
    catch (FormulaException e)
    {
      // Something has gone wrong trying to read the formula data eg. it
      // might be unsupported biff7 data
      logger.warn
        (CellReferenceHelper.getCellReference(fr.getColumn(), fr.getRow()) 
         " " + e.getMessage());

      return null;
    }
  }


  /**
   * Accessor
   *
   @return the number of rows
   */
  final int getNumRows()
  {
    return numRows;
  }

  /**
   * Accessor
   *
   @return the number of columns
   */
  final int getNumCols()
  {
    return numCols;
  }

  /**
   * Accessor
   *
   @return the cells
   */
  final Cell[][] getCells()
  {
    return cells;
  }

  /**
   * Accessor
   *
   @return the row properties
   */
  final ArrayList    getRowProperties()
  {
    return rowProperties;
  }

  /**
   * Accessor
   *
   @return the column information
   */
  final ArrayList getColumnInfosArray()
  {
    return columnInfosArray;
  }

  /**
   * Accessor
   *
   @return the hyperlinks
   */
  final ArrayList getHyperlinks()
  {
    return hyperlinks;
  }

  /**
   * Accessor
   *
   @return the conditional formatting
   */
  final ArrayList getConditionalFormats()
  {
    return conditionalFormats;
  }

  /**
   * Accessor
   *
   @return the autofilter
   */
  final AutoFilter getAutoFilter()
  {
    return autoFilter;
  }

  /**
   * Accessor
   *
   @return the charts
   */
  final ArrayList getCharts()
  {
    return charts;
  }

  /**
   * Accessor
   *
   @return the drawings
   */
  final ArrayList getDrawings()
  {
    return drawings;
  }

  /**
   * Accessor
   *
   @return the data validations
   */
  final DataValidation getDataValidation()
  {
    return dataValidation;
  }

  /**
   * Accessor
   *
   @return the ranges
   */
  final Range[] getMergedCells()
  {
    return mergedCells;
  }

  /**
   * Accessor
   *
   @return the sheet settings
   */
  final SheetSettings getSettings()
  {
    return settings;
  }

  /**
   * Accessor
   *
   @return the row breaks
   */
  final int[] getRowBreaks()
  {
    return rowBreaks;
  }

  /**
   * Accessor
   *
   @return the column breaks
   */
  final int[] getColumnBreaks()
  {
    return columnBreaks;
  }

  /**
   * Accessor
   *
   @return the workspace options
   */
  final WorkspaceInformationRecord getWorkspaceOptions()
  {
    return workspaceOptions;
  }

  /**
   * Accessor
   *
   @return the environment specific print record
   */
  final PLSRecord getPLS()
  {
    return plsRecord;
  }

  /**
   * Accessor for the button property set, used during copying
   *
   @return the button property set
   */
  final ButtonPropertySetRecord getButtonPropertySet()
  {
    return buttonPropertySet;
  }

  /**
   * Adds a cell comment to a cell just read in
   *
   @param col the column for the comment
   @param row the row for the comment
   @param text the comment text
   @param width the width of the comment text box
   @param height the height of the comment text box
   */
  private void addCellComment(int col, 
                              int row, 
                              String text, 
                              double width,
                              double height)
  {
    Cell c = cells[row][col];
    if (c == null)
    {
      logger.warn("Cell at " + CellReferenceHelper.getCellReference(col, row+
                  " not present - adding a blank");
      MulBlankCell mbc = new MulBlankCell(row,
                                          col,
                                          0,
                                          formattingRecords,
                                          sheet);
      CellFeatures cf = new CellFeatures();
      cf.setReadComment(text, width, height);
      mbc.setCellFeatures(cf);
      addCell(mbc);

      return;
    }

    if (instanceof CellFeaturesAccessor)
    {
      CellFeaturesAccessor cv = (CellFeaturesAccessorc;
      CellFeatures cf = cv.getCellFeatures();

      if (cf == null)
      {
        cf = new CellFeatures();
        cv.setCellFeatures(cf);
      }

      cf.setReadComment(text, width ,height);
    }
    else
    {
      logger.warn("Not able to add comment to cell type " +
                  c.getClass().getName() +
                  " at " + CellReferenceHelper.getCellReference(col, row));
    }
  }

  /**
   * Adds a cell comment to a cell just read in
   *
   @param col1 the column for the comment
   @param row1 the row for the comment
   @param col2 the row for the comment
   @param row2 the row for the comment
   @param dvsr the validation settings
   */
  private void addCellValidation(int col1, 
                                 int row1, 
                                 int col2,
                                 int row2,
                                 DataValiditySettingsRecord dvsr)
  {
    for (int row = row1; row <= row2; row++)
    {
      for (int col = col1; col <= col2; col++)
      {
        Cell c = null;

        if (cells.length > row && cells[row].length > col)
        {
          c = cells[row][col];
        }

        if (c == null)
        {
          MulBlankCell mbc = new MulBlankCell(row,
                                              col,
                                              0,
                                              formattingRecords,
                                              sheet);
          CellFeatures cf = new CellFeatures();
          cf.setValidationSettings(dvsr);
          mbc.setCellFeatures(cf);
          addCell(mbc);
        }
        else if (instanceof CellFeaturesAccessor)
        {          // Check to see if the cell already contains a comment
          CellFeaturesAccessor cv = (CellFeaturesAccessorc;
          CellFeatures cf = cv.getCellFeatures();

          if (cf == null)
          {
            cf = new CellFeatures();
            cv.setCellFeatures(cf);
          }

          cf.setValidationSettings(dvsr);
        }
        else
        {
          logger.warn("Not able to add comment to cell type " +
                      c.getClass().getName() +
                      " at " + CellReferenceHelper.getCellReference(col, row));
        }
      }
    }
  }

  /**
   * Reads in the object record
   *
   @param objRecord the obj record
   @param msoRecord the mso drawing record read in earlier
   @param comments the hash map of comments
   */
  private void handleObjectRecord(ObjRecord objRecord,
                                  MsoDrawingRecord msoRecord,
                                  HashMap comments)
  {
    if (msoRecord == null)
    {
      logger.warn("Object record is not associated with a drawing " +
                  " record - ignoring");
      return;
    }
    
    try
    {
      // Handle images
      if (objRecord.getType() == ObjRecord.PICTURE)
      {
        if (drawingData == null)
        {
          drawingData = new DrawingData();
        }

        Drawing drawing = new Drawing(msoRecord,
                                      objRecord,
                                      drawingData,
                                      workbook.getDrawingGroup(),
                                      sheet);
        drawings.add(drawing);
        return;
      }

      // Handle comments
      if (objRecord.getType() == ObjRecord.EXCELNOTE)
      {
        if (drawingData == null)
        {
          drawingData = new DrawingData();
        }

        Comment comment = new Comment(msoRecord,
                                      objRecord,
                                      drawingData,
                                      workbook.getDrawingGroup(),
                                      workbookSettings);

        // Sometimes Excel writes out Continue records instead of drawing
        // records, so forcibly hack all of these into a drawing record
        Record r2 = excelFile.next();
        if (r2.getType() == Type.MSODRAWING || r2.getType() == Type.CONTINUE)
        {
          MsoDrawingRecord mso = new MsoDrawingRecord(r2);
          comment.addMso(mso);
          r2 = excelFile.next();
        }
        Assert.verify(r2.getType() == Type.TXO);
        TextObjectRecord txo = new TextObjectRecord(r2);
        comment.setTextObject(txo);

        r2 = excelFile.next();
        Assert.verify(r2.getType() == Type.CONTINUE);
        ContinueRecord text = new ContinueRecord(r2);
        comment.setText(text);

        r2 = excelFile.next();
        if (r2.getType() == Type.CONTINUE)
        {
          ContinueRecord formatting = new ContinueRecord(r2);
          comment.setFormatting(formatting);
        }

        comments.put(new Integer(comment.getObjectId()), comment);
        return;
      }

      // Handle combo boxes
      if (objRecord.getType() == ObjRecord.COMBOBOX)
      {
        if (drawingData == null)
        {
          drawingData = new DrawingData();
        }

        ComboBox comboBox = new ComboBox(msoRecord,
                                         objRecord,
                                         drawingData,
                                         workbook.getDrawingGroup(),
                                         workbookSettings);
        drawings.add(comboBox);
        return;
      }
    
      // Handle check boxes
      if (objRecord.getType() == ObjRecord.CHECKBOX)
      {
        if (drawingData == null)
        {
          drawingData = new DrawingData();
        }

        CheckBox checkBox = new CheckBox(msoRecord,
                                         objRecord,
                                         drawingData,
                                         workbook.getDrawingGroup(),
                                         workbookSettings);

        Record r2 = excelFile.next();
        Assert.verify(r2.getType() == Type.MSODRAWING || 
                      r2.getType() == Type.CONTINUE);
        if (r2.getType() == Type.MSODRAWING || r2.getType() == Type.CONTINUE)
        {
          MsoDrawingRecord mso = new MsoDrawingRecord(r2);
          checkBox.addMso(mso);
          r2 = excelFile.next();
        }

        Assert.verify(r2.getType() == Type.TXO);
        TextObjectRecord txo = new TextObjectRecord(r2);
        checkBox.setTextObject(txo);

        if (txo.getTextLength() == 0)
        {
          return;
        }

        r2 = excelFile.next();
        Assert.verify(r2.getType() == Type.CONTINUE);
        ContinueRecord text = new ContinueRecord(r2);
        checkBox.setText(text);

        r2 = excelFile.next();
        if (r2.getType() == Type.CONTINUE)
        {
          ContinueRecord formatting = new ContinueRecord(r2);
          checkBox.setFormatting(formatting);
        }

        drawings.add(checkBox);

        return;
      }

      // Handle form buttons
      if (objRecord.getType() == ObjRecord.BUTTON)
      {
        if (drawingData == null)
        {
          drawingData = new DrawingData();
        }

        Button button = new Button(msoRecord,
                                   objRecord,
                                   drawingData,
                                   workbook.getDrawingGroup(),
                                   workbookSettings);

        Record r2 = excelFile.next();
        Assert.verify(r2.getType() == Type.MSODRAWING || 
                      r2.getType() == Type.CONTINUE);
        if (r2.getType() == Type.MSODRAWING || 
            r2.getType() == Type.CONTINUE)
        {
          MsoDrawingRecord mso = new MsoDrawingRecord(r2);
          button.addMso(mso);
          r2 = excelFile.next();
        }

        Assert.verify(r2.getType() == Type.TXO);
        TextObjectRecord txo = new TextObjectRecord(r2);
        button.setTextObject(txo);

        r2 = excelFile.next();
        Assert.verify(r2.getType() == Type.CONTINUE);
        ContinueRecord text = new ContinueRecord(r2);
        button.setText(text);

        r2 = excelFile.next();
        if (r2.getType() == Type.CONTINUE)
        {
          ContinueRecord formatting = new ContinueRecord(r2);
          button.setFormatting(formatting);
        }

        drawings.add(button);

        return;
      }

      // Non-supported types which have multiple record types
      if (objRecord.getType() == ObjRecord.TEXT)
      {
        logger.warn(objRecord.getType() " Object on sheet \"" +
                    sheet.getName() +
                    "\" not supported - omitting");

        // Still need to add the drawing data to preserve the hierarchy
        if (drawingData == null)
        {
          drawingData = new DrawingData();

        }
        drawingData.addData(msoRecord.getData());

        Record r2 = excelFile.next();
        Assert.verify(r2.getType() == Type.MSODRAWING || 
                      r2.getType() == Type.CONTINUE);
        if (r2.getType() == Type.MSODRAWING || 
            r2.getType() == Type.CONTINUE)
        {
          MsoDrawingRecord mso = new MsoDrawingRecord(r2);
          drawingData.addRawData(mso.getData());
          r2 = excelFile.next();
        }

        Assert.verify(r2.getType() == Type.TXO);

        if (workbook.getDrawingGroup() != null// can be null for Excel 95
        {
          workbook.getDrawingGroup().setDrawingsOmitted(msoRecord,
                                                        objRecord);
        }

        return;
      }

      // Handle other types
      if (objRecord.getType() != ObjRecord.CHART)
      {
        logger.warn(objRecord.getType() " Object on sheet \"" +
                    sheet.getName() +
                    "\" not supported - omitting");

        // Still need to add the drawing data to preserve the hierarchy
        if (drawingData == null)
        {
          drawingData = new DrawingData();
        }

        drawingData.addData(msoRecord.getData());

        if (workbook.getDrawingGroup() != null// can be null for Excel 95
        {
          workbook.getDrawingGroup().setDrawingsOmitted(msoRecord,
                                                        objRecord);
        }

        return;
      }
    }
    catch (DrawingDataException e)
    {
      logger.warn(e.getMessage() 
                  "...disabling drawings for the remainder of the workbook");
      workbookSettings.setDrawingsDisabled(true);
    }
  }

  /**
   * Gets the drawing data - for use as part of the Escher debugging tool
   */
  DrawingData getDrawingData()
  {
    return drawingData;
  }

  /**
   * Handle any cells which fall outside of the bounds specified within
   * the dimension record
   */
  private void handleOutOfBoundsCells()
  {
    int resizedRows = numRows;
    int resizedCols = numCols;

    // First, determine the new bounds
    for (Iterator i = outOfBoundsCells.iterator() ; i.hasNext() ;)
    {
      Cell cell = (Celli.next();
      resizedRows = Math.max(resizedRows, cell.getRow() 1);
      resizedCols = Math.max(resizedCols, cell.getColumn() 1);
    }
    
    // There used to be a warning here about exceeding the sheet dimensions,
    // but removed it when I added the ability to perform data validation
    // on entire rows or columns - in which case it would blow out any
    // existing dimensions

    // Resize the columns, if necessary
    if (resizedCols > numCols)
    {
      for (int r = ; r < numRows ; r++)
      {
        Cell[] newRow = new Cell[resizedCols];
        Cell[] oldRow = cells[r];
        System.arraycopy(oldRow, 0, newRow, 0, oldRow.length);
        cells[r= newRow;
      }
    }

    // Resize the rows, if necessary
    if (resizedRows > numRows)
    {
      Cell[][] newCells = new Cell[resizedRows][];
      System.arraycopy(cells, 0, newCells, 0, cells.length);
      cells = newCells;

      // Create the new rows
      for (int i = numRows; i < resizedRows; i++)
      {
        newCells[inew Cell[resizedCols];
      }
    }

    numRows = resizedRows;
    numCols = resizedCols;

    // Now add all the out of bounds cells into the new cells
    for (Iterator i = outOfBoundsCells.iterator(); i.hasNext())
    {
      Cell cell = (Celli.next();
      addCell(cell);
    }

    outOfBoundsCells.clear();
  }

  /** 
   * Accessor for the maximum column outline level
   *
   @return the maximum column outline level, or 0 if no outlines/groups
   */
  public int getMaxColumnOutlineLevel() 
  {
    return maxColumnOutlineLevel;
  }

  /** 
   * Accessor for the maximum row outline level
   *
   @return the maximum row outline level, or 0 if no outlines/groups
   */
  public int getMaxRowOutlineLevel() 
  {
    return maxRowOutlineLevel;
  }
}