workbookparser.java

一个非常有用的操作MCRSOFT EXCEL文件的工具。可以用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.Iterator;
import java.util.HashMap;

import common.Assert;
import common.Logger;

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

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

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

    for (int i = 0; i < getNumberOfSheets(); i++)
    {
      sheetArray[i] = (Sheet) sheets.get(i);
    }
    return 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);

    if (sr.getType() == SupbookRecord.INTERNAL)
    {
      // It's an internal reference - get the name from the boundsheets list
      BoundsheetRecord br = (BoundsheetRecord) boundsheets.get(firstTab);

      return br.getName();
    }
    else if (sr.getType() == SupbookRecord.EXTERNAL)
    {
      // External reference - get the sheet name from the supbook record
      StringBuffer sb = new StringBuffer();
      sb.append('[');
      sb.append(sr.getFileName());
      sb.append(']');
      sb.append(sr.getSheetName(firstTab));
      return sb.toString();
    }

    // An unknown supbook - return unkown
    return "[UNKNOWN]";
  }

  /**
   * 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 getLastExternalSheetName(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 lastTab = externSheet.getLastTabIndex(index);

    if (sr.getType() == SupbookRecord.INTERNAL)
    {
      // It's an internal reference - get the name from the boundsheets list
      BoundsheetRecord br = (BoundsheetRecord) boundsheets.get(lastTab);

      return br.getName();
    }
    else if (sr.getType() == SupbookRecord.EXTERNAL)
    {
      // External reference - get the sheet name from the supbook record
      StringBuffer sb = new StringBuffer();
      sb.append('[');
      sb.append(sr.getFileName());
      sb.append(']');
      sb.append(sr.getSheetName(lastTab));
      return sb.toString();
    }

    // An unknown supbook - return unkown
    return "[UNKNOWN]";
  }

  /**
   * Returns the number of sheets in this workbook
   *
   * @return the number of sheets in this workbook
   */
  public int getNumberOfSheets()
  {
    return sheets.size();
  }

  /**
   * Closes this workbook, and frees makes any memory allocated available
   * for garbage collection
   */
  public void close()
  {
    if (lastSheet != null)
    {
      lastSheet.clear();
    }
    excelFile.clear();

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

  /**
   * Adds the sheet to the end of the array
   *
   * @param s the sheet to add
   */
  final void addSheet(Sheet s)
  {
    sheets.add(s);
  }

  /**
   * Does the hard work of building up the object graph from the excel bytes
   *
   * @exception BiffException
   * @exception PasswordException if the workbook is password protected
   */
  protected void parse() throws BiffException, PasswordException
  {
    Record r = null;

    BOFRecord bof = new BOFRecord(excelFile.next());
    workbookBof = bof;
    bofs++;

    if (!bof.isBiff8() && !bof.isBiff7())
    {
      throw new BiffException(BiffException.unrecognizedBiffVersion);
    }

    if (!bof.isWorkbookGlobals())
    {
      throw new BiffException(BiffException.expectedGlobals);
    }
    ArrayList continueRecords = new ArrayList();
    nameTable = new ArrayList();

    // Skip to the first worksheet
    while (bofs == 1)
    {
      r = excelFile.next();

      if (r.getType() == Type.SST)
      {
        continueRecords.clear();
        Record nextrec = excelFile.peek();
        while (nextrec.getType() == Type.CONTINUE)
        {
          continueRecords.add(excelFile.next());
          nextrec = excelFile.peek();
        }

        // cast the array
        Object[] rec = continueRecords.toArray();
        Record[] records = new Record[rec.length];
        System.arraycopy(rec, 0, records, 0, rec.length);

        sharedStrings = new SSTRecord(r, records, settings);
      }
      else if (r.getType() == Type.FILEPASS)
      {
        throw new PasswordException();
      }
      else if (r.getType() == Type.NAME)
      {
        NameRecord nr = null;

        if (bof.isBiff8())
        {
          nr = new NameRecord(r, settings, namedRecords.size());
        }