sheetimpl.java

jxtl API Java中Excel的生成与导入解析参考文档

/*********************************************************************
*
*      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.Iterator;

import common.Logger;
import common.Assert;

import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.Hyperlink;
import jxl.Image;
import jxl.LabelCell;
import jxl.Range;
import jxl.Sheet;
import jxl.SheetSettings;
import jxl.WorkbookSettings;
import jxl.biff.BuiltInName;
import jxl.biff.AutoFilter;
import jxl.biff.CellReferenceHelper;
import jxl.biff.ConditionalFormat;
import jxl.biff.DataValidation;
import jxl.biff.EmptyCell;
import jxl.biff.FormattingRecords;
import jxl.biff.Type;
import jxl.biff.WorkspaceInformationRecord;
import jxl.biff.drawing.Chart;
import jxl.biff.drawing.Drawing;
import jxl.biff.drawing.DrawingData;
import jxl.biff.drawing.DrawingGroupObject;
import jxl.format.CellFormat;

/**
 * Represents a sheet within a workbook.  Provides a handle to the individual
 * cells, or lines of cells (grouped by Row or Column)
 * In order to simplify this class due to code bloat, the actual reading
 * logic has been delegated to the SheetReaderClass.  This class' main
 * responsibility is now to implement the API methods declared in the
 * Sheet interface
 */
public class SheetImpl implements Sheet
{
  /**
   * The logger
   */
  private static Logger logger = Logger.getLogger(SheetImpl.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 name of this sheet
   */
  private String name;

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

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

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

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

  /**
   * The list of specified (ie. non default) column widths
   */
  private ColumnInfoRecord[] columnInfos;

  /**
   * The array of row records
   */
  private RowRecord[] rowRecords;

  /**
   * 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 charts on this page
   */
  private ArrayList charts;

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

  /**
   * A list of drawings (as opposed to comments/validation/charts) on this
   * page
   */
  private ArrayList images;

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

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

  /**
   * Indicates whether the columnInfos array has been initialized
   */
  private boolean columnInfosInitialized;

  /**
   * Indicates whether the rowRecords array has been initialized
   */
  private boolean rowRecordsInitialized;

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

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

  /**
   * The hidden flag
   */
  private boolean hidden;

  /**
   * The environment specific print record
   */
  private PLSRecord plsRecord;

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

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

  /**
   * 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 list of local names for this sheet
   */
  private ArrayList localNames;

  /**
   * The list of conditional formats for this sheet
   */
  private ArrayList conditionalFormats;

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

  /**
   * 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 workbook settings
   */
  private WorkbookSettings workbookSettings;

  /**
   * Constructor
   *
   * @param f the excel file
   * @param sst the shared string table
   * @param fr formatting records
   * @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 nf the 1904 flag
   * @param wp the workbook which this sheet belongs to
   * @exception BiffException
   */
  SheetImpl(File f,
            SSTRecord sst,
            FormattingRecords fr,
            BOFRecord sb,
            BOFRecord wb,
            boolean nf,
            WorkbookParser wp)
    throws BiffException
  {
    excelFile = f;
    sharedStrings = sst;
    formattingRecords = fr;
    sheetBof = sb;
    workbookBof = wb;
    columnInfosArray = new ArrayList();
    sharedFormulas = new ArrayList();
    hyperlinks = new ArrayList();
    rowProperties = new ArrayList(10);
    columnInfosInitialized = false;
    rowRecordsInitialized = false;
    nineteenFour = nf;
    workbook = wp;
    workbookSettings = workbook.getSettings();

    // Mark the position in the stream, and then skip on until the end
    startPosition = f.getPos();

    if (sheetBof.isChart())
    {
      // Set the start pos to include the bof so the sheet reader can handle it
      startPosition -= (sheetBof.getLength() + 4);
    }

    Record r = null;
    int bofs = 1;

    while (bofs >= 1)
    {
      r = f.next();

      // use this form for quick performance
      if (r.getCode() == Type.EOF.value)
      {
        bofs--;
      }

      if (r.getCode() == Type.BOF.value)
      {
        bofs++;
      }
    }
  }

  /**
   * Returns the cell for the specified location eg. "A4", using the
   * CellReferenceHelper
   *
   * @param loc the cell reference
   * @return the cell at the specified co-ordinates
   */
  public Cell getCell(String loc)
  {
    return getCell(CellReferenceHelper.getColumn(loc),
                   CellReferenceHelper.getRow(loc));
  }

  /**
   * Returns the cell specified at this row and at this column
   *
   * @param row the row number
   * @param column the column number
   * @return the cell at the specified co-ordinates
   */
  public Cell getCell(int column, int row)
  {
    // just in case this has been cleared, but something else holds
    // a reference to it
    if (cells == null)
    {
      readSheet();
    }

    Cell c = cells[row][column];

    if (c == null)
    {
      c = new EmptyCell(column, row);
      cells[row][column] = c;
    }

    return c;
  }

  /**
   * Gets the cell whose contents match the string passed in.
   * If no match is found, then null is returned.  The search is performed
   * on a row by row basis, so the lower the row number, the more
   * efficiently the algorithm will perform
   *
   * @param  contents the string to match
   * @return the Cell whose contents match the paramter, null if not found
   */
  public Cell findCell(String contents)
  {
    Cell cell = null;
    boolean found = false;

    for (int i = 0; i < getRows() && !found; i++)
    {
      Cell[] row = getRow(i);
      for (int j = 0; j < row.length && !found; j++)
      {
        if (row[j].getContents().equals(contents))
        {
          cell = row[j];
          found = true;
        }
      }
    }

    return cell;
  }

  /**
   * Gets the cell whose contents match the string passed in.
   * If no match is found, then null is returned.  The search is performed
   * on a row by row basis, so the lower the row number, the more
   * efficiently the algorithm will perform.  This method differs
   * from the findCell methods in that only cells with labels are
   * queried - all numerical cells are ignored.  This should therefore
   * improve performance.
   *
   * @param  contents the string to match
   * @return the Cell whose contents match the paramter, null if not found
   */
  public LabelCell findLabelCell(String contents)
  {
    LabelCell cell = null;
    boolean found = false;

    for (int i = 0; i < getRows() && !found; i++)
    {
      Cell[] row = getRow(i);
      for (int j = 0; j < row.length && !found; j++)
      {
        if ((row[j].getType() == CellType.LABEL ||
             row[j].getType() == CellType.STRING_FORMULA) &&
            row[j].getContents().equals(contents))
        {
          cell = (LabelCell) row[j];
          found = true;
        }
      }
    }

    return cell;
  }

  /**
   * Returns the number of rows in this sheet
   *
   * @return the number of rows in this sheet
   */
  public int getRows()
  {
    // just in case this has been cleared, but something else holds
    // a reference to it
    if (cells == null)
    {
      readSheet();
    }

    return numRows;
  }

  /**
   * Returns the number of columns in this sheet
   *
   * @return the number of columns in this sheet
   */
  public int getColumns()
  {
    // just in case this has been cleared, but something else holds
    // a reference to it
    if (cells == null)
    {
      readSheet();
    }

    return numCols;
  }

  /**
   * Gets all the cells on the specified row.  The returned array will
   * be stripped of all trailing empty cells
   *
   * @param row the rows whose cells are to be returned
   * @return the cells on the given row
   */
  public Cell[] getRow(int row)
  {
    // just in case this has been cleared, but something else holds
    // a reference to it
    if (cells == null)
    {
      readSheet();
    }

    // Find the last non-null cell
    boolean found = false;
    int col = numCols - 1;
    while (col >= 0 && !found)
    {
      if (cells[row][col] != null)
      {
        found = true;
      }
      else
      {
        col--;
      }
    }

    // Only create entries for non-null cells
    Cell[] c = new Cell[col + 1];

    for (int i = 0; i <= col; i++)
    {
      c[i] = getCell(i, row);
    }
    return c;
  }

  /**
   * Gets all the cells on the specified column.  The returned array
   * will be stripped of all trailing empty cells
   *
   * @param col the column whose cells are to be returned
   * @return the cells on the specified column
   */
  public Cell[] getColumn(int col)
  {
    // just in case this has been cleared, but something else holds
    // a reference to it
    if (cells == null)
    {
      readSheet();
    }

    // Find the last non-null cell
    boolean found = false;
    int row = numRows - 1;
    while (row >= 0 && !found)
    {
      if (cells[row][col] != null)
      {
        found = true;
      }
      else
      {
        row--;
      }
    }

    // Only create entries for non-null cells
    Cell[] c = new Cell[row + 1];

    for (int i = 0; i <= row; i++)
    {
      c[i] = getCell(col, i);
    }
    return c;
  }

  /**
   * Gets the name of this sheet
   *
   * @return the name of the sheet
   */
  public String getName()
  {
    return name;
  }

  /**
   * Sets the name of this sheet
   *
   * @param s the sheet name
   */
  final void setName(String s)
  {
    name = s;
  }

  /**
   * Determines whether the sheet is hidden
   *
   * @return whether or not the sheet is hidden
   * @deprecated in favour of the getSettings function
   */
  public boolean isHidden()
  {
    return hidden;
  }

  /**
   * Gets the column info record for the specified column.  If no
   * column is specified, null is returned
   *
   * @param col the column
   * @return the ColumnInfoRecord if specified, NULL otherwise
   */
  public ColumnInfoRecord getColumnInfo(int col)
  {
    if (!columnInfosInitialized)
    {
      // Initialize the array
      Iterator i = columnInfosArray.iterator();
      ColumnInfoRecord cir = null;
      while (i.hasNext())
      {
        cir = (ColumnInfoRecord) i.next();

        int startcol = Math.max(0, cir.getStartColumn());
        int endcol = Math.min(columnInfos.length - 1, cir.getEndColumn());

        for (int c = startcol; c <= endcol; c++)
        {
          columnInfos[c] = cir;
        }

        if (endcol < startcol)
        {
          columnInfos[startcol] = cir;
        }
      }

      columnInfosInitialized = true;
    }