xlsreadwriteii2.pas

一个经典的读写Excel的控件

unit XLSReadWriteII2;

{
********************************************************************************
******* XLSReadWriteII V2.00                                             *******
*******                                                                  *******
******* Copyright(C) 1999,2004 Lars Arvidsson, Axolot Data               *******
*******                                                                  *******
******* email: [e-mail]                                     *******
******* URL:   http://www.axolot.com                                     *******
********************************************************************************
** Users of the XLSReadWriteII component must accept the following            **
** disclaimer of warranty:                                                    **
**                                                                            **
** XLSReadWriteII is supplied as is. The author disclaims all warranties,     **
** expressedor implied, including, without limitation, the warranties of      **
** merchantability and of fitness for any purpose. The author assumes no      **
** liability for damages, direct or consequential, which may result from the  **
** use of XLSReadWriteII.                                                     **
********************************************************************************
}

{$B-}

// @exclude
// {$define SHAREWARE}

interface

uses Windows, WinSpool, Classes, SysUtils, Dialogs, BIFFRecsII2, XLSUtils2,
     CellFormats2, SheetData2, XLSStream2, XLSFonts2, EncodeFormulaII2,
     ExcelMaskII2, Graphics, Cell2, Printers, XLSRWIIResourceStrings2,
     Forms, RecordStorage2, ComObj, ActiveX, XLSNames2, ExcelFuncII2,
     Escher2, FormulaHandler2, DrawingObjChart2;

const CurrentVersionNumber = '2.00.14';

type TWorkbookOption = (woHidden,   //: The window is hidden.
                        woIconized, //: The window is displayed as an icon.
                        woHScroll,  //: The horizontal scroll bar is displayed.
                        woVScroll,  //: The vertical scroll bar is displayed.
                        woTabs      //: The workbook tabs are displayed.
                        );
     TWorkbookOptions = set of TWorkbookOption;

type TShowObjects = (soShowAll,soPlaceholders,soHideAll);

type TCalcMode = (cmManual,cmAutomatic,cmAutoExTables);

// Options when copying and moving cells.
type TCopyCellsOption = (ccoAdjustCells,  // Adjust relative cells according to the new location.
                         ccoLockStartRow, // Don't adjust the first row.
                         ccoForceAdjust,  // Adjust absolute cell references as well.
                         ccoCopyValues,   // Copy cell values.
                         ccoCopyShapes,   // Copy shape objects.
                         ccoCopyNotes,    // Copy cell notes.
                         ccoCopyCondFmt,  // Copy conditional formats.
                         ccoCopyValidations, // Copy cell validations.
                         ccoCopyMerged       // Copy merged cells.
                         );
     TCopyCellsOptions = set of TCopyCellsOption;

const CopyAllCells = [ccoCopyValues,ccoCopyShapes,ccoCopyNotes,ccoCopyCondFmt,ccoCopyValidations,ccoCopyMerged];

type TStyles = class(TList)
private
     function GetItems(Index: integer): PRecSTYLE;
public
     destructor Destroy; override;
     procedure Clear; override;
     procedure Add(Style: PRecSTYLE);

     property Items[Index: integer]: PRecSTYLE read GetItems; default;
     end;

type TRowOptions = (roZeroHeight,roFormatted);

type TRowData = record
     Row: word;
     Height: word;
     FormatIndex: word;
     Oprtions: TRowOptions;
     end;

type TWorkbookData = class(TPersistent)
private
     FRecords: TRecordStorageGlobals;

     function  ReadHeight: word;
     function  ReadLeft: word;
     function  ReadOptions: TWorkbookOptions;
     function  ReadSelectedTab: word;
     function  ReadTop: word;
     function  ReadWidth: word;
     procedure WriteHeight(const Value: word);
     procedure WriteLeft(const Value: word);
     procedure WriteOptions(const Value: TWorkbookOptions);
     procedure WriteSelectedTab(const Value: word);
     procedure WriteTop(const Value: word);
     procedure WriteWidth(const Value: word);
public
     constructor Create(Records: TRecordStorageGlobals);
published
     property Left: word read ReadLeft write WriteLeft;
     property Top: word read ReadTop write WriteTop;
     property Width: word read ReadWidth write WriteWidth;
     property Height: word read ReadHeight write WriteHeight;
     property SelectedTab: word read ReadSelectedTab write WriteSelectedTab;
     property Options: TWorkbookOptions read ReadOptions write WriteOptions;
     end;

// The TOptionsDialog contains options for the workbook. Please referrer to the
// Excel documentation for the meaning of the options.
type TOptionsDialog = class(TPersistent)
private
     FRecords: TRecordStorageGlobals;
     FCalcCount: word;
     FCalcMode: TCalcMode;
     FDelta: double;
     FIteration: boolean;
     FR1C1Mode: boolean;
     FRecalcBeforeSave: boolean;
     FUncalced: boolean;
     FSaveRecalc: boolean;

     function  GetShowObjects: TShowObjects;
     procedure SetShowObjects(const Value: TShowObjects);
     function  GetPrecisionAsDisplayed: boolean;
     procedure SetPrecisionAsDisplayed(const Value: boolean);
     function  GetSaveExtLinkVal: boolean;
     procedure SetSaveExtLinkVal(const Value: boolean);
published
     constructor Create(Records: TRecordStorageGlobals);
     property SaveExtLinkVal: boolean read GetSaveExtLinkVal write SetSaveExtLinkVal;
     property CalcCount: word read FCalcCount write FCalcCount;
     property CalcMode: TCalcMode read FCalcMode write FCalcMode;
     property Delta: double read FDelta write FDelta;
     property ShowObjects: TShowObjects read GetShowObjects write SetShowObjects;
     property Iteration: boolean read FIteration write FIteration;
     property PrecisionAsDisplayed: boolean read GetPrecisionAsDisplayed write SetPrecisionAsDisplayed;
     property R1C1Mode: boolean read FR1C1Mode write FR1C1Mode;
     property RecalcBeforeSave: boolean read FRecalcBeforeSave write FRecalcBeforeSave;
     property Uncalced: boolean read FUncalced write FUncalced;
     property SaveRecalc: boolean read FSaveRecalc write FSaveRecalc;
     end;

type TXLSReadWriteII2 = class(TComponent)
private
      FFilename: WideString;
      FVersion: TExcelVersion;
      FFormats: TCellFormats;
      FDefaultFormat: TCellFormat;
      FSheets: TSheets;
      FWorkbookData: TWorkbookData;
      FOptionsDialog: TOptionsDialog;
      FFonts: TXFonts;
      FMaxBuffsize: integer;
      FStrTRUE: WideString;
      FStrFALSE: WideString;
      FIsMac: boolean;
      FWriteDefaultData: boolean;
      FDefaultCountryIndex: integer;
      FWinIniCountry: integer;
      FShowFormulas: boolean;
      FAfterLoad: TNotifyEvent;
      FRowHeightEvent: TColRowSizeEvent;
      FColWidthEvent: TColRowSizeEvent;
      FRows: TList;
      FFormulaHandler: TFormulaHandler;
      FProgressEvent: TIntegerEvent;
      FFunctionEvent: TFunctionEvent;
      FPasswordEvent: TPasswordEvent;
      FDevMode: PDeviceModeW;
      FStyles: TStyles;
{$ifdef SHAREWARE}
      FNagMsgShown: boolean;
{$endif}
      FRecords: TRecordStorageGlobals;
      FExtraObjects: TExtraObjects;
      FMSOPictures: TMSOPictures;
      FSheetCharts: TSheetCharts;
      FPreserveMacros: boolean;
      FWritePassword: WideString;
      FDefaultPaperSize: TPaperSize;
      FCellCount: integer;
      FProgressCount: integer;

      procedure SetVersion(Value: TExcelVersion);
      procedure SetFont(F: TXFont);
      function  GetFont: TXFont;
      procedure SetCodepage(Value: word);
      procedure FormulaHandlerSheetName(Name: WideString; var Index,Count: integer);
      function  FormulaHandlerSheetData(DataType: TSheetDataType; SheetIndex,Col,Row: integer): WideString;
      function  GetCodepage: word;
      function  GetUserName: string;
      procedure SetUserName(const Value: string);
      function  GetBookProtected: boolean;
      procedure SetBookProtected(const Value: boolean);
      function  GetBackup: boolean;
      procedure SetBackup(const Value: boolean);
      function  GetRefreshAll: boolean;
      procedure SetRefreshAll(const Value: boolean);
      function  GetNameAsFloat(AName: string): double;
      procedure SetNameAsFloat(AName: string; const Value: double);
      function  GetNameAsBoolean(AName: string): boolean;
      function  GetNameAsBoolFormulaValue(AName: string): boolean;
      function  GetNameAsError(AName: string): TCellError;
      function  GetNameAsFmtString(AName: string): WideString;
      function  GetNameAsFormula(AName: string): string;
      function  GetNameAsNumFormulaValue(AName: string): double;
      function  GetNameAsStrFormulaValue(AName: string): WideString;
      function  GetNameAsString(AName: string): WideString;
      procedure SetNameAsBoolean(AName: string; const Value: boolean);
      procedure SetNameAsBoolFormulaValue(AName: string; const Value: boolean);
      procedure SetNameAsError(AName: string; const Value: TCellError);
      procedure SetNameAsFormula(AName: string; const Value: string);
      procedure SetNameAsNumFormulaValue(AName: string; const Value: double);
      procedure SetNameAsStrFormulaValue(AName: string; const Value: WideString);
      procedure SetNameAsString(AName: string; const Value: WideString);
      function  GetVersionNumber: string;
      procedure SetVerionNumber(const Value: string);
      function  GetInternalNames: TInternalNames;
      procedure SetInternalNames(const Value: TInternalNames);
protected
      procedure GetCommonSheetData;
      procedure SetCommonSheetData;
public
      constructor Create(AOwner: TComponent); override;
      destructor Destroy; override;
      {*@desc Clears all cell values in all sheets.}
      procedure ClearCells;
      {*@desc Clears all data from the workbook.}
      procedure Clear;
      {*@desc Reads the file given by @link(Filename)}
      procedure Read;
      {*@desc Writes the file given by @link(Filename). Please note that if
       the file allready is opened by another application, such as Excel, it
       is not possible to write to it, as Excel locks the file for exclusive
       use.}
      procedure Write;
      {*@desc Writes the workbook to the stream given by the Stream value;}
      procedure WriteToStream(Stream: TStream);
      {*@desc Reads the workbook from the stream given by the Stream value;}
      procedure LoadFromStream(Stream: TStream);
      {*@desc The max number of rows there can be in a worksheet.}
      function  MaxRowCount: integer;
      {*@desc Copies the cells in area Col1,Row1 - Col2,Row2 on sheet
       SrcSheet to DestCol and DestRow on sheet DestSheet. Which type of
       cells and data that are copied can be set with CopyOptions. The
       copying is carried out correctly even if the areas overlap. /n
        See also @link(TCopyCellsOptions)}
      procedure CopyCells(SrcSheet,Col1,Row1,Col2,Row2,DestSheet,DestCol,DestRow: integer; CopyOptions: TCopyCellsOptions = [ccoAdjustCells] + CopyAllCells);
      {*@desc Moves the cells in area Col1,Row1 - Col2,Row2 on sheet SrcSheet
       to DestCol and DestRow on sheet DestSheet. Which type of cells and
       data that are moved can be set with CopyOptions. /n
        See also @link(TCopyCellsOptions)}
      procedure MoveCells(SrcSheet,Col1,Row1,Col2,Row2,DestSheet,DestCol,DestRow: integer; CopyOptions: TCopyCellsOptions = [ccoAdjustCells] + CopyAllCells);
      {*@desc Deletes the cells in area Col1,Row1 - Col2,Row2 on sheet Sheet.}
      procedure DeleteCells(Sheet, Col1, Row1, Col2, Row2: integer);

      {*@desc Copy columns Col1 to Col2 on sheet SrcSheet to column DestCol
       on sheet DestSheet. Cell values are copied as well. Any existing data
       at the destination is overwritten. For only copying column widths, see
       @link(TColumns).}
      procedure CopyColumns(SrcSheet,Col1,Col2,DestSheet,DestCol: integer; DoCopyCells: boolean = True);
      //: Moves columns Col1 to Col2 on sheet SrcSheet to column DestCol on
      //: sheet DestSheet. Cell values are moved as well.
      //: Any existing data at the destination is overwritten, and the source
      //: columns are cleared.
      //: For only moving column widths, see @link(TColumns).
      procedure MoveColumns(SrcSheet,Col1,Col2,DestSheet,DestCol: integer; DoMoveCells: boolean = True);
      //: Deletes columns Col1 to Col2 on sheet Sheet.
      //: Columns to the right of Col2 are shifted left.
      procedure DeleteColumns(Sheet,Col1,Col2: integer);
      //: Clears columns Col1 to Col2 on sheet Sheet.
      procedure ClearColumns(Sheet,Col1,Col2: integer);
      //: Inserts ColCount empty columns at column Col in sheet Sheet.
      procedure InsertColumns(Sheet,Col,ColCount: integer);

      //: Copy rows Row1 to Row2 on sheet SrcSheet to row DestRow on
      //: sheet DestSheet. Cell values are copied as well.
      //: Any existing data at the destination is overwritten.
      //: For only copying row heights, see @link(TRows).
      procedure CopyRows(SrcSheet,Row1,Row2,DestSheet,DestRow: integer; DoCopyCells: boolean = True);
      //: Moves rows Row1 to Row2 on sheet SrcSheet to row DestRow on
      //: sheet DestSheet. Cell values are copied as well.
      //: Any existing data at the destination is overwritten.
      //: For only moving row heights, see @link(TRows).
      procedure MoveRows(SrcSheet,Row1,Row2,DestSheet,DestRow: integer; DoMoveCells: boolean = True);
      //: Deletes rows Row1 to Row2 on sheet Sheet.
      //: Rows below Row2 are shifted uppwards.
      procedure DeleteRows(Sheet,Row1,Row2: integer);
      //: Clears rows Row1 to Row2 on sheet Sheet.
      procedure ClearRows(Sheet,Row1,Row2: integer);
      //: Inserts RowCount empty Rows at row Row in sheet Sheet.
      procedure InsertRows(Sheet,Row,RowCount: integer);
      procedure CopySheet(SrcSheet,DestSheet: integer);
      //: @exclude
      function  GetDEVMODE: PDeviceModeW;
      //: @exclude
      function  HasDEVMODE: boolean;
      //: Calculates all sheets in the workbook.
      //: See also @link(TSheet.Calculate)
      procedure Calculate;
      //: Use BeginUpdate when you do a lot of changes to the workbook.
      //: BeginUpdate is most usefull when adding many string cells.
      //: EndUpdate must be called after the changes are done.
      //: See also @link(EndUpdate)
      procedure BeginUpdate;
      //: Writes any pending changes since BeginUpdate was called.
      //: See also @link(BeginUpdate)
      procedure EndUpdate;

      //: The NameAsXXX properties reads or writes a cell value by giving a
      //: previous defined name as parameter.
      //: See also @link(InternalNames)
      property NameAsFloat           [AName: string]: double read GetNameAsFloat write SetNameAsFloat;
      property NameAsString          [AName: string]: WideString read GetNameAsString write SetNameAsString;
      property NameAsFmtString       [AName: string]: WideString read GetNameAsFmtString;
      property NameAsBoolean         [AName: string]: boolean read GetNameAsBoolean write SetNameAsBoolean;
      property NameAsError           [AName: string]: TCellError read GetNameAsError write SetNameAsError;
      property NameAsFormula         [AName: string]: string read GetNameAsFormula write SetNameAsFormula;
      property NameAsNumFormulaValue [AName: string]: double read GetNameAsNumFormulaValue write SetNameAsNumFormulaValue;
      property NameAsStrFormulaValue [AName: string]: WideString read GetNameAsStrFormulaValue write SetNameAsStrFormulaValue;
      property NameAsBoolFormulaValue[AName: string]: boolean read GetNameAsBoolFormulaValue write SetNameAsBoolFormulaValue;

      property DefaultPaperSize: TPaperSize read FDefaultPaperSize write FDefaultPaperSize;

      //: @exclude
      { ********************************************* }
      { ********** For internal use only. *********** }
      { ********************************************* }

      //: Set to False when loading an Excel file, as there is default data in the file.
      property WriteDefaultData: boolean read FWriteDefaultData write FWriteDefaultData;
      //: @exclude
      property MaxBuffSize: integer read FMaxBuffsize;
      //: @exclude
      property Styles: TStyles read FStyles;
      //: @exclude
      property OnAfterLoad: TNotifyEvent read FAfterLoad write FAfterLoad;
      //: @exclude
      property OnRowHeight: TColRowSizeEvent read FRowHeightEvent write FRowHeightEvent;
      //: @exclude
      property OnColWidth: TColRowSizeEvent read FColWidthEvent write FColWidthEvent;

      //: @exclude
      property Records: TRecordStorageGlobals read FRecords;
      //: @exclude
      property ExtraObjects: TExtraObjects read FExtraObjects;
      //: @exclude
      property Formats: TCellFormats read FFormats write FFormats;
      //: @exclude
      property Fonts: TXFonts read FFonts write FFonts;
      //: @exclude
      property DefaultFormat: TCellFormat read FDefaultFormat;

      //: @exclude
      function  GetNameValue(Index, Col, Row: integer): TFormulaValue;
      //: @exclude
      function  GetExternNameValue(NameIndex,SheetIndex: integer): TFormulaValue;
      //: @exclude
      //: Add number formats in file read.
      procedure AddNumberFormat(Format: string; Index: integer);
      //: @exclude
      procedure WrittenNCells;
      //: @exclude
      //: *********************************************
      //: ********** End internal use only. ***********
      //: *********************************************
      //: @link(TInternalNames)}
      property InternalNames: TInternalNames read GetInternalNames write SetInternalNames;
      //: Charts that are on it's own sheet.
      //: See also @link(TSheetCharts)
      property SheetCharts: TSheetCharts read FSheetCharts;