sheetreader.java

JAVA读取excel表格里的数据,与jdbc功能相当,是一个包

/*********************************************************************
*
*      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 common.Assert;
import 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.ContinueRecord;
import jxl.biff.DataValidation;
import jxl.biff.DataValidityListRecord;
import jxl.biff.DataValiditySettingsRecord;
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.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.MsoDrawingRecord;
import jxl.biff.drawing.NoteRecord;
import jxl.biff.drawing.ObjRecord;
import jxl.biff.drawing.TextObjectRecord;
import jxl.biff.formula.FormulaException;
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;

  /**
   * 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 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();
    rowProperties = new ArrayList(10);
    charts = new ArrayList();
    drawings = new ArrayList();
    outOfBoundsCells = new ArrayList();
    nineteenFour = nf;
    workbook = wp;
    startPosition = sp;
    sheet = sh;
    settings = new SheetSettings();
    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;

    // 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();

    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 = new Window2Record(r);

        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)
      {
        ;
      }
      else if (type == Type.NOTE)
      {
        if (!workbookSettings.getDrawingsDisabled())
        {
          NoteRecord nr = new NoteRecord(r);

          // Get the comment for the object id
          Comment comment = (Comment) comments.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)