sheetdata2.pas

一个经典的读写Excel的控件

     //: @exclude
     procedure IntWriteError         (Col,Row: integer; FormatIndex: word; Value: TCellError);
     //: @exclude
     procedure IntWriteNumber        (Col,Row: integer; FormatIndex: word; Value: double);

     //: @exclude
     procedure IntWriteSSTStringIndex(Col,Row: integer; FormatIndex: word; Value: integer);
     //: @exclude
     procedure IntWriteSSTString     (Col,Row: integer; FormatIndex: word; Value: WideString);

     //: @exclude
     procedure WriteRawNumFormula    (Col,Row: integer; FormatIndex: integer; Data: PByteArray; Len: integer; Value: double);
     //: @exclude
     procedure WriteRawStrFormula    (Col,Row: integer; FormatIndex: integer; Data: PByteArray; Len: integer; Value: WideString);
     //: @exclude
     procedure WriteRawBoolFormula   (Col,Row: integer; FormatIndex: integer; Data: PByteArray; Len: integer; Value: boolean);
     //: @exclude
     procedure WriteRawErrFormula    (Col,Row: integer; FormatIndex: integer; Data: PByteArray; Len: integer; Value: byte);
     //: @exclude
     procedure WriteRawArrayFormula  (Col,Row: integer; FormatIndex: integer; Data: PByteArray; Len: integer; Value: double; Arr: PByteArray; ArrLen: integer);

     // Used by other formulas.
     //: @exclude
     function  XCalculate(Col, Row: integer): TFormulaValue;

     //: @exclude
     procedure StreamWriteCells      (Version: TExcelVersion; Stream: TXLSStream);
     //: @exclude
     procedure StreamWriteMergedCells(Version: TExcelVersion; Stream: TXLSStream);

     //: @exclude
     procedure AfterFileRead;

     //: @exclude
     property  StorageCells: TCellStorage read FCells;

     //: @exclude
     property  Records: TRecordStorageSheet read FRecords;
     //: @exclude
     property  EscherDrawing: TEscherDrawing read FEscherDrawing;
     //: @exclude
     property HasDefaultRecords: boolean read FHasDefaultRecords write FHasDefaultRecords;

     // **********************************************
     // *********** End internal use only. ***********
     // **********************************************
     //:# Clears the worksheet of all cell data.
     procedure ClearWorksheet;
     //:# Sets column width automatically.
     //: Use AutoColWidth to calculate the column width automatically for column
     //: Col. The width is set to the max string width for text or numbers in
     //: the column.
     function  AutoWidthCol(Col: longword): integer;
     //:# Sets column width automatically.
     //: Use AutoColWidth to calculate the column width automatically for column
     //: Col1 to Col2. The width is set to the max string width for text or
     //: numbers in the columns.
     procedure AutoWidthCols(Col1,Col2: longword);
     //:# Calculate the dimensions of the worksheet.
     //: CalcDimensions will calculate the first and last, first and last column.
     procedure CalcDimensions;
     //:# Calculate the dimensions of the worksheet.
     //: CalcDimensions will calculate the first and last, first and last column.
     //: Only cells with values are included. Cells with only formatting (blank)
     //: are excluded.
     procedure CalcDimensionsEx;
     //:# Calculates a formula cell. @see(CalculateEx).
     function  Calculate(Col, Row: integer): Variant;
     //:# Calculates a formula cell. The result of the calculation is assigned
     //:# to the cell.
     function  CalculateEx(Col, Row: integer; CalculateOptions: TCalculateOptions): Variant;
     //:# Calculates the entire sheet.
     procedure CalculateSheet;
     procedure Calculated(IsCalculated: boolean);
     //:# Deletes a cell.
     procedure DeleteCell(Col, Row: integer);
     //:# Deletes the cells in the area from Col1, Row1 to Col2, Row2.
     procedure DeleteCells(Col1, Row1, Col2, Row2: integer);
     //:# Paint the cell on a canvas.
     //: Use PaintCell to draw the contents of the cell at ACol and ARow on Canvas. ARect is a clipping rectangle, nothing is painted outside it.
     //: @Example
     //: This example shows how to handle the DrawCell event on a TDrawGrid object:<br>
     //: <br>
     //: procedure TForm1.DrawGrid1DrawCell(Sender: TObject; ACol, ARow: Integer; Rect: TRect; State: TGridDrawState);<br>
     //: begin<br>
     //:  if (ACol > 0) and (ARow > 0) then<br>
     //:    XLSReadWrite1.Sheets[0].PaintCell(DrawGrid1.Canvas,Rect,ACol - 1,ARow - 1);<br>
     //:  end;<br>
     procedure PaintCell(Canvas: TCanvas; ARect: TRect; ACol,ARow: integer);
     //:# Returns True if the worksheet don't have any cells.
     function  IsEmpty: boolean;
     //:# Group rows.
     //: GroupRows will group the rows between Row1,Row2. Set Collapsed to
     //: True if the rows shall be collapsed. There can be max 7 group levels.
     procedure GroupRows(Row1,Row2: integer; Collapsed: boolean = False);
     //:# Adds a chart to the workbook.
     function  AddChart: TDrwChart;
     //:# Reads a Rich Text (RTF) file into a cell. Set AllText to True if
     //:# all text in the file shall be read. When False, only the body text
     //:# is read.
     procedure RichTextLoadFromFile(Col, Row: integer;  Filename: WideString; AllText: boolean = True);
     //:# Reads a Rich Text (RTF) stream into a cell. Set AllText to True if
     //:# all text in the stream shall be read. When False, only the body text
     //:# is read.
     procedure RichTextLoadFromStream(Col, Row: integer; Stream: TStream; AllText: boolean = True);
     //:# Saves a cell value to a Rich Text (RTF) file.
     procedure RichTextSaveToFile(Col, Row: integer; Filename: WideString);
     //:# Saves a cell value to a Rich Text (RTF) stream.
     procedure RichTextSaveToStream(Col, Row: integer; Stream: TStream);
     //:# Copies a cell value to a TRichEdit control.
     procedure CopyToRichEdit(Col,Row: integer; var RichEdit: TRichEdit);

     //:# The first column of the worksheet with a cell value.
     property FirstCol: word read GetFirstCol write SetFirstCol;
     //:# The lats column of the worksheet with a cell value.
     property LastCol: word read GetLastCol write SetLastCol;
     //:# The first row of the worksheet with a cell value.
     property FirstRow: word read GetFirstRow write SetFirstRow;
     //:# The last row of the worksheet with a cell value.
     property LastRow: word read GetLastRow write SetLastRow;

     //:# Access a cell as a blank cell value.
     //: Use AsBlank to read or write the cell at Col and Row as a blank cell
     //: value. If there is no cell at the position, a blank cell will be
     //: inserted, when the property is written to. If there already is a cell
     //: there, it will be converted to a blank cell. If the previous cell was
     //: formatted, the formatting will be copied to the inserted cell.
     property AsBlank[Col,Row: integer]: boolean read GetAsBlank write SetAsBlank; 
     //:# Access a cell as a integer cell value.
     //: Use AsInteger to read or write the cell at Col and Row as a integer cell
     //: value. If there is no cell at the position, a integer cell will be
     //: inserted, when the property is written to. If there already is a cell
     //: there, it will be deleted, and a integer cell will be inserted. If the
     //: previous cell was formatted, the formatting will be copied to the
     //: inserted cell. When the property is read, the value of the cell will be
     //: returned. If there is no cell at the position, zero will be returned.
     property AsInteger[Col,Row: integer]: integer read GetAsInteger write SetAsInteger;
     //:# Access a cell as a float cell value.
     //: Use AsFloat to read or write the cell at Col and Row as a float cell
     //: value. If there is no cell at the position, a float cell will be
     //: inserted, when the property is written to. If there already is a cell
     //: there, it will be deleted, and a float cell will be inserted. If the
     //: previous cell was formatted, the formatting will be copied to the
     //: inserted cell. When the property is read, the value of the cell will be
     //: returned. If there is no cell at the position, zero will be returned.
     property AsFloat[Col,Row: integer]: double read GetAsFloat write SetAsFloat;
     //:# Access a cell as a wide string cell value.
     //: Use AsWideString to read or write the cell at Col and Row as a wide
     //: string cell value. If there is no cell at the position, a string cell
     //: will be inserted, when the property is written to. If there already is
     //: a cell there, it will be deleted, and an string cell will be inserted.
     //: If the previous cell was formatted, the formatting will be copied to
     //: the inserted cell. When the property is read, the value of the cell
     //: will be returned. If there is no cell at the position,
     //: an empty string will be returned.
     property AsString[Col,Row: integer]: WideString read GetAsString write SetAsString;
     //:# For backward compatibility. Use @link(AsString).
     property AsWideString[Col,Row: integer]: WideString read GetAsWideString write SetAsWideString;
     // A Rich Text document is always 7-bit ascii, and shall therefore not be
     // a WideString.

     //:# Access a cell as a Rich Text value.
     //: Use AsRichText to read or write the cell at Col and Row as a Rich
     //: Text cell value. If there is no cell at the position, a string cell
     //: will be inserted, when the property is written to. If there already is
     //: a cell there, it will be deleted, and a string cell will be inserted.
     //: If the previous cell was formatted, the formatting will be copied to
     //: the inserted cell. When the property is read, the value of the cell
     //: will be returned.
     property AsRichText[Col,Row: integer]: string read GetAsRichText write SetAsRichText;
     //:# Returns the cell value as a string. If the cell is formatted the
     //:# string will be formatted according to that.
     //: Use AsFmtString to read or write the cell at Col and Row as a formatted
     //: string value. When the cell is formatted, formatting rules will be used.
     //: If there is no cell at the position, an empty string will be returned.
     property AsFmtString[Col,Row: integer]: WideString read GetFmtAsString;
     property AsHTML[Col,Row: integer]: string read GetAsHTML;
     //:# Access a cell as a boolean cell value.
     //: Use AsBoolean to read or write the cell at Col and Row as a boolean
     //: cell value. If there is no cell at the position, a boolean cell will
     //: be inserted, when the property is written to. If there already is a cell
     //: there, it will be deleted, and a boolean cell will be inserted.
     //: If the previous cell was formatted, the formatting will be copied to
     //: the inserted cell. When the property is read, the value of the cell
     //: will be returned. If there is no cell at the position, False will be
     //: returned.
     property AsBoolean[Col,Row: integer]: boolean read GetAsBoolean write SetAsBoolean;
     //:# Access a cell as an error cell value.
     //: Use AsError to read or write the cell at Col and Row as an error cell
     //: value. If there is no cell at the position, an error cell will be
     //: inserted, when the property is written to. If there already is a cell
     //: there, it will be deleted, and an error cell will be inserted. If the
     //: previous cell was formatted, the formatting will be copied to the
     //: inserted cell. When the property is read, the value of the cell will be
     //: returned. If there is no cell at the position, zero will be returned.
     property AsError[Col,Row: integer]: TCellError read GetAsError write SetAsError;
     //:# Access a cell as a formula cell value.
     //: Use AsFormula to read or write the cell at Col and Row as a formula
     //: cell value. If there is no cell at the position, a formula cell will be
     //: inserted, when the property is written to. If there already is a cell
     //: there, it will be deleted, and a formula cell will be inserted. If the
     //: previous cell was formatted, the formatting will be copied to the
     //: inserted cell. When the property is read, the value of the cell will be
     //: returned. If there is no cell at the position, an empty string will be
     //: returned. The value inserted into the cell must be a valid Excel formula.
     property AsFormula[Col,Row: integer]: string read GetAsFormula write SetAsFormula;
     //:# Access the value of a number formula cell.
     //: Use AsNumFormulaValue to read or write the value of a numeric formula
     //: cell at Col and Row. If the cell not is a number formula cell, or there
     //: is no cell at the position, an exception will be raised. To read or
     //: write the formula, use AsFormula
     property AsNumFormulaValue[Col,Row: integer]: double read GetAsNumFormulaValue write SetAsNumFormulaValue;
     //:# Access the value of a string formula cell.
     //: Use AsStrFormulaValue to read or write the value of a string formula
     //: cell at Col and Row. If the cell not is a string formula cell, or there
     //: is no cell at the position, an exception will be raised. To read or
     //: write the formula, use AsFormula
     property AsStrFormulaValue[Col,Row: integer]: WideString read GetAsStrFormulaValue write SetAsStrFormulaValue;
     //:# Access the value of a boolean formula cell.
     //: Use AsBoolFormulaValue to read or write the value of a boolean formula
     //: cell at Col and Row. If the cell not is a boolean formula cell, or there
     //: is no cell at the position, an exception will be raised. To read or
     //: write the formula, use AsFormula
     property AsBoolFormulaValue[Col,Row: integer]: boolean read GetAsBoolFormulaValue write SetAsBoolFormulaValue;
     //:# Access a cell as a variant value.
     //: Use AsVariant to read or write the cell at Col and Row as a variant
     //: value. If there is no cell at the position, a cell will be inserted,
     //: according to the type of variant, when the property is written to.
     //: If there already is a cell
     //: there, it will be deleted. If the
     //: previous cell was formatted, the formatting will be copied to the
     //: inserted cell. When the property is read, the value of the cell will be
     //: returned. If there is no cell at the position, zero will be returned.
     property AsVariant[Col,Row: integer]: Variant read GetAsVaraiant write SetAsVariant;
     property AsFormulaValue[Col,Row: integer]: TFormulaValue read GetAsFormulaValue write SetAsFormulaValue;

     property AsBlankRef[ARef: WideString]: boolean read GetAsBlankRef write SetAsBlankRef;
     property AsIntegerRef[ARef: WideString]: integer read GetAsIntegerRef write SetAsIntegerRef;
     property AsFloatRef[ARef: WideString]: double read GetAsFloatRef write SetAsFloatRef;
     property AsStringRef[ARef: WideString]: WideString read GetAsStringRef write SetAsStringRef;
     property AsRichTextRef[ARef: WideString]: string read GetAsRichTextRef write SetAsRichTextRef;
     property AsFmtStringRef[ARef: WideString]: WideString read GetFmtAsStringRef;
     property AsHTMLRef[ARef: WideString]: string read GetAsHTMLRef;
     property AsBooleanRef[ARef: WideString]: boolean read GetAsBooleanRef write SetAsBooleanRef;
     property AsErrorRef[ARef: WideString]: TCellError read GetAsErrorRef write SetAsErrorRef;
     property AsFormulaRef[ARef: WideString]: string read GetAsFormulaRef write SetAsFormulaRef;
     property AsNumFormulaValueRef[ARef: WideString]: double read GetAsNumFormulaValueRef write SetAsNumFormulaValueRef;
     property AsStrFormulaValueRef[ARef: WideString]: WideString read GetAsStrFormulaValueRef write SetAsStrFormulaValueRef;
     property AsBoolFormulaValueRef[ARef: WideString]: boolean read GetAsBoolFormulaValueRef write SetAsBoolFormulaValueRef;
     property AsVariantRef[ARef: WideString]: Variant read GetAsVaraiantRef write SetAsVariantRef;

     //:# Reads the cell as a TCell object.
     //: Use Cell to read the cell at Col and Row as a TCell object. You may
     //: use the Cell property in order apply formatting to the cell.
     //: If there are no cell at the location, an exception is raised.
     property Cell[Col,Row: integer]: TCell read GetCell;
     //:# Use CellType to read the cell type of the cell at Col and Row.
     property CellType[Col,Row: integer]: TCellType read GetCellType;
     //:# List of rows in the worksheet.
     property Rows: TXLSRows read FRows;
     //:# List of columns in the worksheet.
     property Columns: TXLSColumns read FColumns write FColumns;
     //:# List of charts in the worksheet.
     property Charts: TDrwCharts read FCharts write FCharts;
     //:# Apply format is used to apply formatting to an area of cells.
     property ApplyFormat: TApplyFormat read FApplyFormat;

//     property Count: integer read FCellCount;

published
     //:# Default column width.
     //: The width is in units of 1/256s of a character width.
     property DefaultColWidth: word read GetDefaultColWidth write SetDefaultColWidth;
     //:# Default row height.
     //: Default row height measured in 1/20th of a character point, based on
     //: the selected font for the worksheet.
     property DefaultRowHeight: word read GetDefaultRowHeight write SetDefaultRowHeight;
     //:# Options when printing a worksheet.
     property PrintSettings: TPrintSettings read FPrintSettings write FPrintSettings;
     //:# List of merged cells.
     property MergedCells: TMergedCells read FMergedCells write FMergedCells;
     //:# Worksheet options.
     property Options: TSheetOptions read GetOptions write SetOptions;
     //:# Workspace options.
     property WorkspaceOptions: TWorkspaceOptions read GetWorkspaceOptions write SetWorkspaceOptions;
     //:# Zoom magnification.
     property Zoom: word read GetZoom write SetZoom;
     //:# Zoom magnification in page break preview.
     property ZoomPreview: word read GetZoomPreview write SetZoomPreview;
     //:# If formulas shall be recalced when the file is opened by Excel.
     //: Set this property to true if formulas shall be marked for recalculation.
     //: As TXLSReadWriteII2 don't calculate formulas, this is advised. Default is True.
     property RecalcFormulas: boolean read FRecalcFormulas write FRecalcFormulas;
     //:# List with data validations.
     property Validations: TDataValidations read FValidations write FValidations;
     //:# List with drawing objects.
     property DrawingObjects: TDrawingObjects read FDrawingObjects write FDrawingObjects;
     //:# List with control objects.
     property ControlsObjects: TControlObjects read FControlObjects write FControlObjects;
     //:# Pane settings for the worksheet.
     property Pane: TPane read FPane;
     //:# List with hyperlinks.
     property Hyperlinks: THyperlinks read FHyperlinks write FHyperlinks;