workbookparser.cs

Excel的操作,其中可以读取及写入Excel 文件

using System;
using System.Collections;

using Microsoft.Fawvw.Components.NExcel.ExcelCommon;
using Microsoft.Fawvw.Components.NExcel.Biff;
using Microsoft.Fawvw.Components.NExcel.Biff.Formula;
using Microsoft.Fawvw.Components.NExcel.Biff.Drawing;

namespace Microsoft.Fawvw.Components.NExcel.Read.Biff
{
	
	/// <summary> Parses the biff file passed in, and builds up an internal representation of
	/// the spreadsheet
	/// </summary>
	public class WorkbookParser:Workbook, ExternalSheet, WorkbookMethods
	{
		/// <summary> 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
		/// 
		/// </summary>
		/// <returns> an array of the individual sheets
		/// </returns>
		override public Sheet[] Sheets
		{
			get
			{
				Sheet[] sheetArray = new Sheet[NumberOfSheets];
				
				for (int i = 0; i < NumberOfSheets; i++)
				{
					sheetArray[i] = (Sheet) sheets[i];
				}
				return sheetArray;
			}
			
		}
		/// <summary> Gets the sheet names
		/// 
		/// </summary>
		/// <returns> an array of strings containing the sheet names
		/// </returns>
		override public string[] SheetNames
		{
			get
			{
				string[] names = new string[boundsheets.Count];
				
				BoundsheetRecord br = null;
				for (int i = 0; i < names.Length; i++)
				{
					br = (BoundsheetRecord) boundsheets[i];
					names[i] = br.Name;
				}
				
				return names;
			}
			
		}
		/// <summary> Returns the number of sheets in this workbook
		/// 
		/// </summary>
		/// <returns> the number of sheets in this workbook
		/// </returns>
		override public int NumberOfSheets
		{
			get
			{
				return sheets.Count;
			}
			
		}
		/// <summary> Accessor for the formattingRecords, used by the WritableWorkbook
		/// when creating a copy of this
		/// 
		/// </summary>
		/// <returns> the formatting records
		/// </returns>
		virtual public FormattingRecords FormattingRecords
		{
			get
			{
				return formattingRecords;
			}
			
		}
		/// <summary> Accessor for the externSheet, used by the WritableWorkbook
		/// when creating a copy of this
		/// 
		/// </summary>
		/// <returns> the external sheet record
		/// </returns>
		virtual public ExternalSheetRecord ExternalSheetRecord
		{
			get
			{
				return externSheet;
			}
			
		}
		/// <summary> Accessor for the MsoDrawingGroup, used by the WritableWorkbook
		/// when creating a copy of this
		/// 
		/// </summary>
		/// <returns> the Mso Drawing Group record
		/// </returns>
		virtual public MsoDrawingGroupRecord MsoDrawingGroupRecord
		{
			get
			{
				return msoDrawingGroup;
			}
			
		}
		/// <summary> Accessor for the supbook records, used by the WritableWorkbook
		/// when creating a copy of this
		/// 
		/// </summary>
		/// <returns> the supbook records
		/// </returns>
		virtual public SupbookRecord[] SupbookRecords
		{
			get
			{
				SupbookRecord[] sr = new SupbookRecord[supbooks.Count];
				
				for (int i = 0; i < sr.Length; i++)
				{
					sr[i] = (SupbookRecord) supbooks[i];
				}
				
				return sr;
			}
			
		}
		/// <summary> Accessor for the name records.  Used by the WritableWorkbook when
		/// creating a copy of this
		/// 
		/// </summary>
		/// <returns> the array of names
		/// </returns>
		virtual public NameRecord[] NameRecords
		{
			get
			{
				NameRecord[] na = new NameRecord[nameTable.Count];
				
				for (int i = 0; i < nameTable.Count; i++)
				{
					na[i] = (NameRecord) nameTable[i];
				}
				
				return na;
			}
			
		}
		/// <summary> Accessor for the fonts, used by the WritableWorkbook
		/// when creating a copy of this
		/// </summary>
		/// <returns> the fonts used in this workbook
		/// </returns>
		virtual public Fonts Fonts
		{
			get
			{
				return fonts;
			}
			
		}
		/// <summary> Gets the named ranges
		/// 
		/// </summary>
		/// <returns> the list of named cells within the workbook
		/// </returns>
		override public string[] RangeNames
		{
			get
			{
				ArrayList keylist = new ArrayList(namedRecords.Keys);
				System.Object[] keys = keylist.ToArray();
				string[] names = new string[keys.Length];
				Array.Copy(keys, 0, names, 0, keys.Length);
				
				return names;
			}
			
		}
		/// <summary> Method used when parsing formulas to make sure we are trying
		/// to parse a supported biff version
		/// 
		/// </summary>
		/// <returns> the BOF record
		/// </returns>
		virtual public BOFRecord WorkbookBof
		{
			get
			{
				return workbookBof;
			}
			
		}
		/// <summary> Determines whether the sheet is protected
		/// 
		/// </summary>
		/// <returns> whether or not the sheet is protected
		/// </returns>
		override public bool Protected
		{
			get
			{
				return wbProtected;
			}
			
		}
		/// <summary> Accessor for the settings
		/// 
		/// </summary>
		/// <returns> the workbook settings
		/// </returns>
		virtual public WorkbookSettings Settings
		{
			get
			{
				return settings;
			}
			
		}
		/// <summary> Accessor for the drawing group</summary>
		virtual public DrawingGroup DrawingGroup
		{
			get
			{
				return drawingGroup;
			}
			
		}
		/// <summary> The logger</summary>
		private static Logger logger;
		
		/// <summary> The excel file</summary>
		private File excelFile;
		/// <summary> The number of open bofs</summary>
		private int bofs;
		/// <summary> Indicates whether or not the dates are based around the 1904 date system</summary>
		private bool nineteenFour;
		/// <summary> The shared string table</summary>
		private SSTRecord sharedStrings;
		/// <summary> The names of all the worksheets</summary>
		private ArrayList boundsheets;
		/// <summary> The xf records</summary>
		private FormattingRecords formattingRecords;
		/// <summary> The fonts used by this workbook</summary>
		private Fonts fonts;
		
		/// <summary> The sheets contained in this workbook</summary>
		private ArrayList sheets;
		
		/// <summary> The last sheet accessed</summary>
		private SheetImpl lastSheet;
		
		/// <summary> The index of the last sheet retrieved</summary>
		private int lastSheetIndex;
		
		/// <summary> The named records found in this workbook</summary>
		private Hashtable namedRecords;
		
		/// <summary> The list of named records</summary>
		private ArrayList nameTable;
		
		/// <summary> The external sheet record.  Used by formulas, and names</summary>
		private ExternalSheetRecord externSheet;
		
		/// <summary> The list of supporting workbooks - used by formulas</summary>
		private ArrayList supbooks;
		
		/// <summary> The bof record for this workbook</summary>
		private BOFRecord workbookBof;
		
		/// <summary> The Mso Drawing Group record for this workbook</summary>
		private MsoDrawingGroupRecord msoDrawingGroup;
		
		/// <summary> Workbook protected flag</summary>
		private bool wbProtected;
		
		/// <summary> The workbook settings</summary>
		private WorkbookSettings settings;
		
		/// <summary> The drawings contained in this workbook</summary>
		private DrawingGroup drawingGroup;
		
		/// <summary> Constructs this object from the raw excel data
		/// 
		/// </summary>
		/// <param name="f">the excel 97 biff file
		/// </param>
		/// <param name="s">the workbook settings
		/// </param>
		public WorkbookParser(File f, WorkbookSettings s):base()
		{
			excelFile = f;
			boundsheets = new ArrayList(10);
			fonts = new Fonts();
			formattingRecords = new FormattingRecords(fonts);
			sheets = new ArrayList(10);
			supbooks = new ArrayList(10);
			namedRecords = new Hashtable();
			lastSheetIndex = - 1;
			wbProtected = false;
			settings = s;
		}
		
		/// <summary> Interface method from WorkbookMethods - gets the specified
		/// sheet within this workbook
		/// 
		/// </summary>
		/// <param name="index">the zero based index of the required sheet
		/// </param>
		/// <returns> The sheet specified by the index
		/// </returns>
		public virtual Sheet getReadSheet(int index)
		{
			return getSheet(index);
		}
		
		/// <summary> Gets the specified sheet within this workbook
		/// 
		/// </summary>
		/// <param name="index">the zero based index of the required sheet
		/// </param>
		/// <returns> The sheet specified by the index
		/// </returns>
		public override 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.GCDisabled)
				{
					System.GC.Collect();
				}
			}
			
			lastSheet = (SheetImpl) sheets[index];
			lastSheetIndex = index;
			lastSheet.readSheet();
			
			return lastSheet;
		}
		
		/// <summary> Gets the sheet with the specified name from within this workbook
		/// 
		/// </summary>
		/// <param name="name">the sheet name
		/// </param>
		/// <returns> The sheet with the specified name, or null if it is not found
		/// </returns>
		public override Sheet getSheet(string name)
		{
			// Iterate through the boundsheet records
			int pos = 0;
			bool found = false;
			
			foreach(BoundsheetRecord br in boundsheets)
			{
				if (found) break;
			
				if (br.Name.Equals(name))
				{
					found = true;
				}
				else
				{
					pos++;
				}
			}
			
			return found?getSheet(pos):null;
		}
		
		
		/// <summary> 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
		/// 
		/// </summary>
		/// <param name="index">the external sheet reference
		/// </param>
		/// <returns> the actual sheet index
		/// </returns>
		public virtual 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;
		}
		
		/// <summary> 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
		/// 
		/// </summary>
		/// <param name="index">the external sheet reference
		/// </param>
		/// <returns> the actual sheet index
		/// </returns>
		public virtual 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;
		}
		
		/// <summary> Gets the name of the external sheet specified by the index
		/// 
		/// </summary>
		/// <param name="index">the external sheet index
		/// </param>
		/// <returns> the name of the external sheet
		/// </returns>
		public virtual 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[index];
				
				return br.Name;
			}
			
			int supbookIndex = externSheet.getSupbookIndex(index);
			SupbookRecord sr = (SupbookRecord) supbooks[supbookIndex];
			
			int firstTab = externSheet.getFirstTabIndex(index);
			
			if (sr.Type == SupbookRecord.INTERNAL)
			{
				// It's an internal reference - get the name from the boundsheets list
				BoundsheetRecord br = (BoundsheetRecord) boundsheets[firstTab];
				
				return br.Name;
			}
			else if (sr.Type == SupbookRecord.EXTERNAL)
			{
				// External reference - get the sheet name from the supbook record
				System.Text.StringBuilder sb = new System.Text.StringBuilder();
				sb.Append('[');
				sb.Append(sr.FileName);