sheetdata2.pas

一个经典的读写Excel的控件

     procedure SetMarginTopCm(const Value: double);
protected
     function  GetOwner: TPersistent; override;
public
     constructor Create(Parent: TSheet);
     destructor Destroy; override;
     procedure Clear;
published
     //:# Number of copies to print.
     property Copies: word read GetCopies write SetCopies;
     //:# Footer text.
     //: In order to format text, the following codes can be used:
     //: &&=The "&" character.<br>
     //: &L=Align left.<br>
     //: &C=Align center.<br>
     //: &R=Align right.<br>
     //: &P=Page number.<br>
     //: &N=Page count.<br>
     //: &D=Current date.<br>
     //: &T=Current time.<br>
     //: &A=Sheet name.<br>
     //: &F=Filename without path.<br>
     //: &Z=Filepath without filename (Excel 2000 and later).<br>
     //: &B=Bold on/off.<br>
     //: &I=Italic on/off.<br>
     //: &U=Underlining on/off.<br>
     //: &E=Double underlining on/off.<br>
     //: &S=Strikeout on/off.<br>
     //: &X=Superscript on/off.<br>
     //: &Y=Subscript on/off.<br>
     //: &NN=Set font size to NN points.<br>
     //: &"Fontname"=Set font to Fontname. It is also possible to set the font
     //: to bold weight, but this is localized, ie the word used for "Bold" must
     //: be in the same language as the windows version which is used! English
     //: example: &"Arial,Bold". Swedish example: &"Arial,Fet".
     property Footer: WideString read FFooter write FFooter;
     property FooterMargin: double read GetFooterMargin write SetFooterMargin;
     //:# Header text.
     //: In order to format text, the following codes can be used:
     //: &&=The "&" character.<br>
     //: &L=Align left.<br>
     //: &C=Align center.<br>
     //: &R=Align right.<br>
     //: &P=Page number.<br>
     //: &N=Page count.<br>
     //: &D=Current date.<br>
     //: &T=Current time.<br>
     //: &A=Sheet name.<br>
     //: &F=Filename without path.<br>
     //: &Z=Filepath without filename (Excel 2000 and later).<br>
     //: &B=Bold on/off.<br>
     //: &I=Italic on/off.<br>
     //: &U=Underlining on/off.<br>
     //: &E=Double underlining on/off.<br>
     //: &S=Strikeout on/off.<br>
     //: &X=Superscript on/off.<br>
     //: &Y=Subscript on/off.<br>
     //: &NN=Set font size to NN points.<br>
     //: &"Fontname"=Set font to Fontname. It is also possible to set the font
     //: to bold weight, but this is localized, ie the word used for "Bold" must
     //: be in the same language as the windows version which is used! English
     //: example: &"Arial,Bold". Swedish example: &"Arial,Fet".
     property Header: WideString read FHeader write FHeader;
     //:# Header margin in inches.
     property HeaderMargin: double read GetHeaderMargin write SetHeaderMargin;
     //:# Bottom margin in inches.
     property MarginBottom: double read FMarginBottom write FMarginBottom;
     //:# Left margin in inches.
     property MarginLeft: double read FMarginLeft write FMarginLeft;
     //:# Rightmargin in inches.
     property MarginRight: double read FMarginRight write FMarginRight;
     //:# Top margin in inches.
     property MarginTop: double read FMarginTop write FMarginTop;
     //:# Bottom margin in centimeters.
     property MarginBottomCm: double read GetMarginBottomCm write SetMarginBottomCm;
     //:# Left margin in centimeters.
     property MarginLeftCm: double read GetMarginLeftCm write SetMarginLeftCm;
     //:# Right margin in centimeters.
     property MarginRightCm: double read GetMarginRightCm write SetMarginRightCm;
     //:# Top margin in centimeters.
     property MarginTopCm: double read GetMarginTopCm write SetMarginTopCm;
     //:#Options.
     property Options: TPrintSetupOptions read GetOptions write SetOptions;
     //:# Paper size.
     //: PaperSize is taken from TXLSReadWriteII2.DefaultPaperSize, which in
     //: in turn is taken the paper size from the system settings.
     property PaperSize: TPaperSize read GetPaperSize write SetPaperSize;
     //:# Scaling factor (zoom).
     //: The default scaling factor is 100%.
     property ScalingFactor: word read GetScalingFactor write SetScalingFactor;
     //:# First page to print.
     property StartingPage: word read GetStartingPage write SetStartingPage;
{    Implemented in InternalNames.
     property RowsOnEachPage: WideString read GetRowsOnEachPage write SetRowsOnEachPage;
     property ColsOnEachPage: WideString read GetColsOnEachPage write SetColsOnEachPage;
}
     //:# List of horizontal page breaks.
     property HorizPagebreaks: THorizPagebreaks read FHorizPagebreaks write FHorizPagebreaks;
     //:# List of vertical page breaks.
     property VertPagebreaks: TVertPagebreaks read FVertPagebreaks write FVertPagebreaks;
     //:# Printer resolution in dpi.
     property Resolution: integer read GetResolution write SetResolution;
     end;

//:# TPane stores settings for window panes.
     TPane = class(TPersistent)
private
     FPaneType: TPaneType;
     FSplitColX: integer;
     FSplitRowY: integer;
     FLeftCol: integer;
     FTopRow: integer;
     FActivePane: byte;
     FSelections: TBaseRecordStorage;

     procedure SetActivePane(const Value: byte);
public
     constructor Create;
     destructor Destroy; override;
     procedure Clear;
     //: @exclude
     property Selections: TBaseRecordStorage read FSelections;
     //:# Which pane that is active, i.e the pane with the cursor.
     property ActivePane: byte read FActivePane write SetActivePane;
published
     //:# Pane style.
     property PaneType: TPaneType read FPaneType write FPaneType;
     //:# Vertical split.
     //: Split pane (PaneType = ptSplit): width of left pane in units of 1/20
     //: of a point.<br>
     //: Frozen pane (PaneType = ptFrozen): Number of visible columns in left pane.<br>
     //: If there not shall be a vertical split, set SplitColX to zero.
     property SplitColX: integer read FSplitColX write FSplitColX;
     //:# Horizontal split.
     //: Split pane (PaneType = ptSplit): height of top pane in units of 1/20
     //: of a point.<br>
     //: Frozen pane (PaneType = ptFrozen): Number of visible rows in top pane.
     //: If there not shall be a horizontal split, set SplitColY to zero.
     property SplitRowY: integer read FSplitRowY write FSplitRowY;
     //:# First visible column in right pane.
     property LeftCol: integer read FLeftCol write FLeftCol;
     //:# First visible row in bottom pane.
     property TopRow: integer read FTopRow write FTopRow;
     end;

     TSheetOption = (soGridlines,soRowColHeadings,soProtected,soR1C1Mode,soIteration,soShowFormulas,soFrozenPanes,soShowZeros);
     TSheetOptions = set of TSheetOption;

     TWorkspaceOption = (woShowAutoBreaks,woApplyStyles,woRowSumsBelow,woColSumsRight,woFitToPage,woOutlineSymbols);
     TWorkspaceOptions = set of TWorkspaceOption;

//:# Base class for worksheets.
     TBasicSheet = class(TCollectionItem)
protected
     FName: WideString;

     procedure SetName(Value: WideString);
     function  GetName: WideString;
public
     constructor Create(Collection: TCollection); override;
     destructor Destroy; override;

published
     property Name: WideString read GetName write SetName;
     end;

     TSheets = class;

//:# Normal worksheet.
     TSheet = class(TBasicSheet)
private
     FCells: TCellStorage;
     FRecords: TRecordStorageSheet;
     FHasDefaultRecords: boolean;

     FPrintSettings: TPrintSettings;
     FMergedCells: TMergedCells;
     FRecalcFormulas: boolean;
     FColumns: TXLSColumns;
     FValidations: TDataValidations;
     FRows: TXLSRows;
     FPane: TPane;
     FDrawingObjects: TDrawingObjects;
     FControlObjects: TControlObjects;
     FCharts: TDrwCharts;
     FEscherDrawing: TEscherDrawing;
     FApplyFormat: TApplyFormat;
     FHyperlinks: THyperlinks;
     FConditionalFormats: TConditionalFormats;
     FFormats: TCellFormats;

     function  GetAsBoolFormulaValue(Col, Row: integer): boolean;
     function  GetAsNumFormulaValue(Col, Row: integer): double;
     function  GetAsStrFormulaValue(Col, Row: integer): WideString;
     procedure SetAsBoolFormulaValue(Col, Row: integer; const Value: boolean);
     procedure SetAsNumFormulaValue(Col, Row: integer; const Value: double);
     procedure SetAsStrFormulaValue(Col, Row: integer; const Value: WideString);
     procedure SetAsErrFormulaValue(Col, Row: integer;  const Value: TCellError);
     function  GetDefaultRowHeight: word;
     procedure SetDefaultRowHeight(const Value: word);
     function  GetWorkspaceOptions: TWorkspaceOptions;
     procedure SetWorkspaceOptions(const Value: TWorkspaceOptions);
     function  GetDefaultColWidth: word;
     procedure SetDefaultColWidth(const Value: word);
     function  GetFirstCol: word;
     function  GetFirstRow: word;
     function  GetLastCol: word;
     function  GetLastRow: word;
     procedure SetFirstCol(const Value: word);
     procedure SetFirstRow(const Value: word);
     procedure SetLastCol(const Value: word);
     procedure SetLastRow(const Value: word);
     function  GetOptions: TSheetOptions;
     function  GetZoom: word;
     function  GetZoomPreview: word;
     procedure SetOptions(const Value: TSheetOptions);
     procedure SetZoom(const Value: word);
     procedure SetZoomPreview(const Value: word);
     function  GetAsVaraiant(Col, Row: integer): Variant;
     procedure SetAsVariant(Col, Row: integer; const Value: Variant);
     function  GetAsFormulaValue(Col, Row: integer): TFormulaValue;
     procedure SetAsFormulaValue(Col, Row: integer; const Value: TFormulaValue);
     function  GetAsRichText(Col, Row: integer): string;
     procedure SetAsRichText(Col, Row: integer; const Value: string);
     function  GetAsBlankRef(ARef: WideString): boolean;
     function  GetAsBooleanRef(ARef: WideString): boolean;
     function  GetAsBoolFormulaValueRef(ARef: WideString): boolean;
     function  GetAsErrorRef(ARef: WideString): TCellError;
     function  GetAsFloatRef(ARef: WideString): double;
     function  GetAsFormulaRef(ARef: WideString): string;
     function  GetAsHTMLRef(ARef: WideString): string;
     function  GetAsIntegerRef(ARef: WideString): integer;
     function  GetAsNumFormulaValueRef(ARef: WideString): double;
     function  GetAsRichTextRef(ARef: WideString): string;
     function  GetAsStrFormulaValueRef(ARef: WideString): WideString;
     function  GetAsStringRef(ARef: WideString): WideString;
     function  GetAsVaraiantRef(ARef: WideString): Variant;
     function  GetFmtAsStringRef(ARef: WideString): WideString;
     procedure SetAsBlankRef(ARef: WideString; const Value: boolean);
     procedure SetAsBooleanRef(ARef: WideString; const Value: boolean);
     procedure SetAsBoolFormulaValueRef(ARef: WideString; const Value: boolean);
     procedure SetAsErrorRef(ARef: WideString; const Value: TCellError);
     procedure SetAsFloatRef(ARef: WideString; const Value: double);
     procedure SetAsFormulaRef(ARef: WideString; const Value: string);
     procedure SetAsIntegerRef(ARef: WideString; const Value: integer);
     procedure SetAsNumFormulaValueRef(ARef: WideString; const Value: double);
     procedure SetAsRichTextRef(ARef: WideString; const Value: string);
     procedure SetAsStrFormulaValueRef(ARef: WideString; const Value: WideString);
     procedure SetAsStringRef(ARef: WideString; const Value: WideString);
     procedure SetAsVariantRef(ARef: WideString; const Value: Variant);
protected
     function  GetDisplayName: string; override;
     procedure WriteBuf(Stream: TXLSStream; RecId,Size: word; P: Pointer);
     procedure CheckFirstLast(ACol,ARow: integer);
     function  GetDefaultWriteFormat(Version: TExcelVersion; FormatIndex: integer): word;
     function  GetDefaultFormat(Col,Row: integer): word;

     function  GetCell(Col, Row: integer): TCell;
     function  GetAsBlank(Col, Row: integer): boolean;
     procedure SetAsBlank(Col, Row: integer; const Value: boolean);
     function  GetAsInteger(Col, Row: integer): integer;
     procedure SetAsInteger(Col, Row: integer; const Value: integer);
     function  GetAsBoolean(Col, Row: integer): boolean;
     function  GetAsError(Col, Row: integer): TCellError;
     procedure SetAsError(Col, Row: integer; Value: TCellError);
     function  GetAsFloat(Col, Row: integer): double;

     function  GetAsFormula(Col, Row: integer): string;

     function  GetAsString(Col, Row: integer): WideString;
     function  GetAsWideString(Col, Row: integer): WideString;
     function  GetFmtAsString(Col, Row: integer): WideString;
     function  GetAsHTML(Col, Row: integer): string;
     procedure SetAsBoolean(Col, Row: integer; const Value: boolean);
     procedure SetAsFloat(Col, Row: integer; const Value: double);

     procedure SetAsFormula(Col, Row: integer; const Value: string);

     procedure SetAsString(Col, Row: integer; const Value: WideString);
     procedure SetAsWideString(Col, Row: integer; const Value: WideString);
     function  GetCellType(Col, Row: integer): TCellType;
     function  EncodeFormula(F: string; CellType: TCellType; RC: integer; FormatIndex: word): TFormulaCell;
     function  GetCellAlignment(Cell: TCell): TCellHorizAlignment;
     function  MakeFormulaCell(CellType: TCellType; Data: PByteArray; Size,RC,FI: integer): TFormulaCell;
     procedure ColFormatChange(Format: TCellFormat; Col: integer);
     procedure RowFormatChange(Format: TCellFormat; Row: integer);

     procedure OnEscherReadShape(Sender: TObject; Shape: TShape);
public
     constructor Create(Collection: TCollection); override;
     destructor Destroy; override;
     //:# Clears (empties) the worksheet of all data.
     procedure ClearData;
     // **********************************************
     // *********** For internal use only. ***********
     // **********************************************
     //: @exclude
     procedure ClearCells;

     //: @exclude
     procedure IntWriteBlank         (Col,Row: integer; FormatIndex: word);
     //: @exclude
     procedure IntWriteBoolean       (Col,Row: integer; FormatIndex: word; Value: boolean);