workbookparser.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.HashMap;
import java.util.Iterator;

import common.Assert;
import common.Logger;

import jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.biff.BuiltInName;
import jxl.biff.CellReferenceHelper;
import jxl.biff.EmptyCell;
import jxl.biff.FontRecord;
import jxl.biff.Fonts;
import jxl.biff.FormatRecord;
import jxl.biff.FormattingRecords;
import jxl.biff.NumFormatRecordsException;
import jxl.biff.PaletteRecord;
import jxl.biff.RangeImpl;
import jxl.biff.StringHelper;
import jxl.biff.Type;
import jxl.biff.WorkbookMethods;
import jxl.biff.XFRecord;
import jxl.biff.drawing.DrawingGroup;
import jxl.biff.drawing.MsoDrawingGroupRecord;
import jxl.biff.drawing.Origin;
import jxl.biff.formula.ExternalSheet;

/**
 * Parses the biff file passed in, and builds up an internal representation of
 * the spreadsheet
 */
public class WorkbookParser extends Workbook
  implements ExternalSheet, WorkbookMethods
{
  /**
   * The logger
   */
  private static Logger logger = Logger.getLogger(WorkbookParser.class);

  /**
   * The excel file
   */
  private File excelFile;
  /**
   * The number of open bofs
   */
  private int bofs;
  /**
   * Indicates whether or not the dates are based around the 1904 date system
   */
  private boolean nineteenFour;
  /**
   * The shared string table
   */
  private SSTRecord sharedStrings;
  /**
   * The names of all the worksheets
   */
  private ArrayList boundsheets;
  /**
   * The xf records
   */
  private FormattingRecords formattingRecords;
  /**
   * The fonts used by this workbook
   */
  private Fonts fonts;

  /**
   * The sheets contained in this workbook
   */
  private ArrayList sheets;

  /**
   * The last sheet accessed
   */
  private SheetImpl lastSheet;

  /**
   * The index of the last sheet retrieved
   */
  private int lastSheetIndex;

  /**
   * The named records found in this workbook
   */
  private HashMap namedRecords;

  /**
   * The list of named records
   */
  private ArrayList nameTable;

  /**
   * The list of add in functions
   */
  private ArrayList addInFunctions;

  /**
   * The external sheet record.  Used by formulas, and names
   */
  private ExternalSheetRecord externSheet;

  /**
   * The list of supporting workbooks - used by formulas
   */
  private ArrayList supbooks;

  /**
   * The bof record for this workbook
   */
  private BOFRecord workbookBof;

  /**
   * The Mso Drawing Group record for this workbook
   */
  private MsoDrawingGroupRecord msoDrawingGroup;

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

  /**
   * Workbook protected flag
   */
  private boolean wbProtected;

  /**
   * Contains macros flag
   */
  private boolean containsMacros;

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

  /**
   * The drawings contained in this workbook
   */
  private DrawingGroup drawingGroup;

  /**
   * The country record (containing the language and regional settings)
   * for this workbook
   */
  private CountryRecord countryRecord;

  /**
   * Constructs this object from the raw excel data
   *
   * @param f the excel 97 biff file
   * @param s the workbook settings
   */
  public WorkbookParser(File f, WorkbookSettings s)
  {
    super();
    excelFile = f;
    boundsheets = new ArrayList(10);
    fonts = new Fonts();
    formattingRecords = new FormattingRecords(fonts);
    sheets = new ArrayList(10);
    supbooks = new ArrayList(10);
    namedRecords = new HashMap();
    lastSheetIndex = -1;
    wbProtected = false;
    containsMacros = false;
    settings = s;
  }

 /**
   * Gets the sheets within this workbook.
   * NOTE:  Use of this method for
   * very large worksheets can cause performance and out of memory problems.
   * Use the alternative method getSheet() to retrieve each sheet individually
   *
   * @return an array of the individual sheets
   */
  public Sheet[] getSheets()
  {
    Sheet[] sheetArray = new Sheet[getNumberOfSheets()];
    return (Sheet[]) sheets.toArray(sheetArray);
  }

  /**
   * Interface method from WorkbookMethods - gets the specified
   * sheet within this workbook
   *
   * @param index the zero based index of the required sheet
   * @return The sheet specified by the index
   */
  public Sheet getReadSheet(int index)
  {
    return getSheet(index);
  }

  /**
   * Gets the specified sheet within this workbook
   *
   * @param index the zero based index of the required sheet
   * @return The sheet specified by the index
   */
  public Sheet getSheet(int index)
  {
    // First see if the last sheet index is the same as this sheet index.
    // If so, then the same sheet is being re-requested, so simply
    // return it instead of rereading it
    if ((lastSheet != null) && lastSheetIndex == index)
    {
      return lastSheet;
    }

    // Flush out all of the cached data in the last sheet
    if (lastSheet != null)
    {
      lastSheet.clear();

      if (!settings.getGCDisabled())
      {
        System.gc();
      }
    }

    lastSheet = (SheetImpl) sheets.get(index);
    lastSheetIndex = index;
    lastSheet.readSheet();

    return lastSheet;
  }

  /**
   * Gets the sheet with the specified name from within this workbook
   *
   * @param name the sheet name
   * @return The sheet with the specified name, or null if it is not found
   */
  public Sheet getSheet(String name)
  {
    // Iterate through the boundsheet records
    int pos = 0;
    boolean found = false;
    Iterator i = boundsheets.iterator();
    BoundsheetRecord br = null;

    while (i.hasNext() && !found)
    {
      br = (BoundsheetRecord) i.next();

      if (br.getName().equals(name))
      {
        found = true;
      }
      else
      {
        pos++;
      }
    }

    return found ? getSheet(pos) : null;
  }

  /**
   * Gets the sheet names
   *
   * @return an array of strings containing the sheet names
   */
  public String[] getSheetNames()
  {
    String[] names = new String[boundsheets.size()];

    BoundsheetRecord br = null;
    for (int i = 0; i < names.length; i++)
    {
      br = (BoundsheetRecord) boundsheets.get(i);
      names[i] = br.getName();
    }

    return names;
  }


  /**
   * Package protected function which gets the real internal sheet index
   * based upon  the external sheet reference.  This is used for extern sheet
   * references  which are specified in formulas
   *
   * @param index the external sheet reference
   * @return the actual sheet index
   */
  public int getExternalSheetIndex(int index)
  {
    // For biff7, the whole external reference thing works differently
    // Hopefully for our purposes sheet references will all be local
    if (workbookBof.isBiff7())
    {
      return index;
    }

    Assert.verify(externSheet != null);

    int firstTab = externSheet.getFirstTabIndex(index);

    return firstTab;
  }

  /**
   * Package protected function which gets the real internal sheet index
   * based upon  the external sheet reference.  This is used for extern sheet
   * references  which are specified in formulas
   *
   * @param index the external sheet reference
   * @return the actual sheet index
   */
  public int getLastExternalSheetIndex(int index)
  {
    // For biff7, the whole external reference thing works differently
    // Hopefully for our purposes sheet references will all be local
    if (workbookBof.isBiff7())
    {
      return index;
    }

    Assert.verify(externSheet != null);

    int lastTab = externSheet.getLastTabIndex(index);

    return lastTab;
  }

  /**
   * Gets the name of the external sheet specified by the index
   *
   * @param index the external sheet index
   * @return the name of the external sheet
   */
  public String getExternalSheetName(int index)
  {
    // For biff7, the whole external reference thing works differently
    // Hopefully for our purposes sheet references will all be local
    if (workbookBof.isBiff7())
    {
      BoundsheetRecord br = (BoundsheetRecord) boundsheets.get(index);

      return br.getName();
    }

    int supbookIndex = externSheet.getSupbookIndex(index);
    SupbookRecord sr = (SupbookRecord) supbooks.get(supbookIndex);

    int firstTab = externSheet.getFirstTabIndex(index);
    int lastTab  = externSheet.getLastTabIndex(index);
    String firstTabName = "";
    String lastTabName = "";

    if (sr.getType() == SupbookRecord.INTERNAL)
    {
      // It's an internal reference - get the name from the boundsheets list
      if (firstTab == 65535)
      {
         firstTabName = "#REF";
      }
      else
      {
        BoundsheetRecord br = (BoundsheetRecord) boundsheets.get(firstTab);
        firstTabName = br.getName();
      }
 
      if (lastTab==65535)
      {
        lastTabName = "#REF";
      }
      else
      {
        BoundsheetRecord br = (BoundsheetRecord) boundsheets.get(lastTab);
        lastTabName = br.getName();
      }

      String sheetName = (firstTab == lastTab) ? firstTabName : 
        firstTabName + ':' + lastTabName;

      // if the sheet name contains apostrophes then escape them
      sheetName = sheetName.indexOf('\'') == -1 ? sheetName :
        StringHelper.replace(sheetName, "\'", "\'\'");

      
      // if the sheet name contains spaces, then enclose in quotes