writablesheetimpl.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.write.biff;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Iterator;
import java.util.TreeSet;

import common.Assert;
import common.Logger;

import jxl.BooleanCell;
import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.DateCell;
import jxl.HeaderFooter;
import jxl.Hyperlink;
import jxl.Image;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Range;
import jxl.Sheet;
import jxl.SheetSettings;
import jxl.WorkbookSettings;
import jxl.biff.AutoFilter;
import jxl.biff.BuiltInName;
import jxl.biff.CellReferenceHelper;
import jxl.biff.ConditionalFormat;
import jxl.biff.DataValidation;
import jxl.biff.EmptyCell;
import jxl.biff.FormattingRecords;
import jxl.biff.FormulaData;
import jxl.biff.IndexMapping;
import jxl.biff.NumFormatRecordsException;
import jxl.biff.SheetRangeImpl;
import jxl.biff.WorkspaceInformationRecord;
import jxl.biff.XFRecord;
import jxl.biff.drawing.Chart;
import jxl.biff.drawing.ComboBox;
import jxl.biff.drawing.Drawing;
import jxl.biff.drawing.DrawingGroupObject;
import jxl.format.CellFormat;
import jxl.format.Font;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.write.Blank;
import jxl.write.Boolean;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * A writable sheet.  This class contains implementation of all the
 * writable sheet methods which may be invoke by the API
 */
class WritableSheetImpl implements WritableSheet
{
  /**
   * The logger
   */
  private static Logger logger = Logger.getLogger(WritableSheetImpl.class);
    
  /**
   * The name of this sheet
   */
  private String name;
  /**
   * A handle to the output file which the binary data is written to
   */
  private File outputFile;
  /**
   * The rows within this sheet
   */
  private RowRecord[] rows;
  /**
   * A handle to workbook format records
   */
  private FormattingRecords formatRecords;
  /**
   * A handle to the shared strings used by this workbook
   */
  private SharedStrings     sharedStrings;

  /**
   * The list of non-default column formats
   */
  private TreeSet columnFormats;

  /**
   * The list of autosized columns
   */
  private TreeSet autosizedColumns;

  /**
   * The list of hyperlinks
   */
  private ArrayList hyperlinks;

  /**
   * The list of merged ranged
   */
  private MergedCells mergedCells;

  /**
   * A number of rows.  This is a count of the maximum row number + 1
   */
  private int numRows;

  /**
   * The number of columns.  This is a count of the maximum column number + 1
   */
  private int numColumns;

  /**
   * The environment specific print record, copied from the read spreadsheet
   */
  private PLSRecord plsRecord;

  /**
   * The buttons property set
   */
  private ButtonPropertySetRecord buttonPropertySet;

  /**
   * A flag indicating that this sheet is a chart only
   */
  private boolean chartOnly;

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

  /**
   * Array of row page breaks
   */
  private ArrayList rowBreaks;

  /**
   * Array of column page breaks
   */
  private ArrayList columnBreaks;

  /**
   * The drawings on this sheet
   */
  private ArrayList drawings;

  /**
   * The images on this sheet.  This is a subset of the drawings list
   */
  private ArrayList images;

  /**
   * The conditional formats on this sheet
   */
  private ArrayList conditionalFormats;

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

  /**
   * The writable cells on this sheet which may have validation added
   * to them
   */
  private ArrayList validatedCells;

  /**
   * The combo box object used for list validations on this sheet
   */
  private ComboBox comboBox;

  /**
   * Drawings modified flag.  Set to true if the drawings list has
   * been modified
   */
  private boolean drawingsModified;

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

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

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

  /**
   * The sheet writer engine
   */
  private SheetWriter sheetWriter;

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

  /**
   * The workbook
   */
  private WritableWorkbookImpl workbook;

  /**
   * The amount by which to grow the rows array
   */
  private final static int rowGrowSize = 10;

  /**
   * The maximum number of rows excel allows in a worksheet
   */
  private final static int numRowsPerSheet = 65536;

  /**
   * The maximum number of characters permissible for a sheet name
   */ 
  private final static int maxSheetNameLength = 31;

  /**
   * The illegal characters for a sheet name
   */
  private final static char[] illegalSheetNameCharacters = 
    new char[] {'*', ':', '?', '\\'}; 

  /**
   * The supported file types
   */
  private static final String[] imageTypes = new String[] {"png"};

  /**
   * The comparator for column info record
   */
  private static class ColumnInfoComparator implements Comparator
  {
    /**
     * Equals method
     * 
     * @param o the object to compare
     * @return TRUE if equal, FALSE otherwise
     */
    public boolean equals(Object o)
    {
      return o == this;
    }

    /**
     * Comparison function for to ColumnInfoRecords
     * 
     * @param o2 first object to compare
     * @param o1 second object to compare
     * @return the result of the comparison
     */
    public int compare(Object o1, Object o2)
    {
      if (o1 == o2)
      {
        return 0;
      }

      Assert.verify(o1 instanceof ColumnInfoRecord);
      Assert.verify(o2 instanceof ColumnInfoRecord);
      
      ColumnInfoRecord ci1 = (ColumnInfoRecord) o1;
      ColumnInfoRecord ci2 = (ColumnInfoRecord) o2;

      return ci1.getColumn() - ci2.getColumn();
    }
  }

  /**
   * Constructor
   * 
   * @param fr the formatting records used by the workbook
   * @param of the output file to write the binary data
   * @param f the fonts used by the workbook
   * @param n the name of this sheet
   * @param ss the shared strings used by the workbook
   * @param ws the workbook settings
   */
  public WritableSheetImpl(String n, 
                           File of, 
                           FormattingRecords fr, 
                           SharedStrings ss,
                           WorkbookSettings ws,
                           WritableWorkbookImpl ww)
  {
    name = validateName(n);
    outputFile = of;
    rows = new RowRecord[0];
    numRows = 0;
    numColumns = 0;
    chartOnly = false;
    workbook = ww;

    formatRecords      = fr;
    sharedStrings      = ss;
    workbookSettings   = ws;
    drawingsModified   = false;
    columnFormats      = new TreeSet(new ColumnInfoComparator());
    autosizedColumns   = new TreeSet();
    hyperlinks         = new ArrayList();
    mergedCells        = new MergedCells(this);
    rowBreaks          = new ArrayList();
    columnBreaks       = new ArrayList();
    drawings           = new ArrayList();
    images             = new ArrayList();
    conditionalFormats = new ArrayList();
    validatedCells     = new ArrayList();
    settings           = new SheetSettings(this);


    sheetWriter = new SheetWriter(outputFile,
                                  this, 
                                  workbookSettings);
  }

  /**
   * 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 column the column number
   * @param row the row number
   * @return the cell at the specified co-ordinates
   */
  public Cell getCell(int column, int row)
  {
    return getWritableCell(column, row);
  }

  /**
   * Returns the cell for the specified location eg. "A4".  Note that this
   * method is identical to calling getCell(CellReferenceHelper.getColumn(loc),
   * CellReferenceHelper.getRow(loc)) and its implicit performance
   * overhead for string parsing.  As such,this method should therefore
   * be used sparingly
   *
   * @param loc the cell reference
   * @return the cell at the specified co-ordinates
   */
  public WritableCell getWritableCell(String loc)
  {
    return getWritableCell(CellReferenceHelper.getColumn(loc),
                           CellReferenceHelper.getRow(loc));
  }

  /**
   * Returns the cell specified at this row and at this column
   * 
   * @param column the column number
   * @param row the row number
   * @return the cell at the specified co-ordinates
   */
  public WritableCell getWritableCell(int column, int row)
  {
    WritableCell c = null;

    if (row < rows.length && rows[row] != null)
    {
      c = rows[row].getCell(column);
    }

    if (c == null)
    {
      c = new EmptyCell(column, row);
    }
    
    return c;
  }

  /**
   * Returns the number of rows in this sheet
   * 
   * @return the number of rows in this sheet
   */
  public int getRows()
  {
    return numRows;
  }

  /**
   * Returns the number of columns in this sheet
   * 
   * @return the number of columns in this sheet
   */
  public int getColumns()
  {
    return numColumns;
  }

  /**
   * 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 parameter, null if not found
   */
  public Cell findCell(String contents)
  {
    Cell cell = null;
    boolean found = false;
    
    for (int i = 0 ; i < getRows() && found == false; i++)
    {
      Cell[] row = getRow(i);
      for (int j = 0 ; j < row.length && found == false; 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 == false; i++)
    {
      Cell[] row = getRow(i);
      for (int j = 0 ; j < row.length && found == false; 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;
  }

  /**
   * Gets all the cells on the specified row