tmsuxlssheet.pas

TMS Component Pack V5.0包含了超过 280 个为 Delphi 以及 C++Builder 设计的 TMS 生产控件

unit tmsUXlsSheet;
{$INCLUDE ..\FLXCOMPILER.INC}

interface
uses Classes, SysUtils, tmsUXlsBaseRecords, tmsUXlsBaseRecordLists, tmsUXlsOtherRecords, tmsUXlsChart,
     tmsUXlsSST, tmsXlsMessages, tmsUXlsSections, tmsUXlsCondFmt, tmsUXlsRowColEntries, tmsUXlsEscher,
     tmsUXlsRangeRecords, tmsUEscherRecords, tmsUXlsWorkbookGlobals, tmsUXlsNotes, tmsUXlsBaseList,
     tmsUFlxMessages, tmsUXlsCellRecords, tmsUXlsFormula, tmsUXlsPageBreaks,
     {$IFDEF WIN32}Windows, {$ENDIF} //On linux resources is defined on system
     {$IFDEF FLX_NEEDSTYPES} Types,{$ENDIF} //Delphi 6 or above
     

     tmsUXlsColInfo, tmsUXlsHyperLink, tmsUOle2Impl;

const
     RESOURCE_EMPTYSHEET = 'TMSFLXEMPTYSHEET';

type
  TSheet= class (TBaseSection)
  private
    function GetShowGridLines: boolean;
    procedure SetShowGridLines(const Value: boolean);
    function GetShowGridHeaders: boolean;
    procedure SetShowGridHeaders(const Value: boolean);
    function GetPrintGridLines: boolean;
    procedure SetPrintGridLines(const Value: boolean);
    function GetPageFooter: UTF16String;
    function GetPageHeader: UTF16String;
    procedure SetPageFooter(const Value: UTF16String);
    procedure SetPageHeader(const Value: UTF16String);
    function GetMargins: TXlsMargins;
    procedure SetMargins(const Value: TXlsMargins);
    procedure AddMargin(var Margin: TMarginRecord; const aId: integer; const Value: extended);
    function GetSheetZoom: integer;
    procedure SetSheetZoom(const Value: integer);
    function GetCodeName: UTF16String;
    procedure SetCodeName(const Value: UTF16String);
    function GetIsR1C1: boolean;
    procedure SetIsR1C1(const Value: boolean);
    function GetWindow2Frozen: Boolean;
    procedure SetWindow2Frozen(const value: Boolean);
  protected
    FWorkbookGlobals: TWorkbookGlobals;
    FWindow2: TWindow2Record;
    FPageHeader: TPageHeaderRecord;
    FPageFooter: TPageFooterRecord;
    FPrintGridLines: TPrintGridLinesRecord;
    FLeftMargin, FRightMargin, FTopMargin, FBottomMargin: TMarginRecord;
    FSetup: TSetupRecord;
    FPrinterDriverSettings: TPlsRecord;
    FWSBool: TWSBoolRecord;
    FHCenter, FVCenter: TPrintCenteredRecord;
    FZoom: TSCLRecord;
    FGuts: TGutsRecord;
    FPane: TPaneRecord;
    FAutoFilterInfo: TAutoFilterInfoRecord;


    FRefMode: TRefModeRecord;

    FPrintRecords: TBaseRecordList;

    FCodeName: TCodeNameRecord;

    procedure LoadCachePointers(const R: TBaseRecord);
    procedure FixCachePointers; virtual;
    function GetSelected: boolean;
    procedure SetSelected(const Value: boolean);
    procedure SetPageHeaderFooter(const P: TPageHeaderFooterRecord; const s: UTF16String);virtual;abstract;
    procedure AddZoomRecord; virtual;abstract;
    procedure AddOrRemovePane(const Add: boolean);virtual;

    property Window2Frozen: boolean read GetWindow2Frozen write SetWindow2Frozen;

  public
    OriginalDimensions: TDimensionsRec;

    function CopyTo: TSheet; //This method can't be virtual
    function DoCopyTo: TSheet; virtual;
    procedure InsertAndCopyRowsAndCols(const FirstRow, LastRow, DestRow, aRowCount,FirstCol, LastCol, DestCol, aColCount: integer; const SheetInfo: TSheetInfo; const OnlyFormulas: boolean);virtual;abstract;
    procedure DeleteRowsAndCols(const aRow, aRowCount, aCol, aColCount: word;const SheetInfo: TSheetInfo);virtual; abstract;
    procedure ArrangeInsertRowsAndCols(const InsRowPos, InsRowCount, InsColPos, InsColCount: integer ; const SheetInfo: TSheetInfo);virtual;abstract;
    procedure ArrangeCopySheet(const SheetInfo: TSheetInfo); virtual;

    procedure Clear; override;

    procedure DeleteHPageBreak(const aRow: word);virtual;
    procedure DeleteVPageBreak(const aCol: word);virtual;
    procedure InsertHPageBreak(const aRow: word);virtual;
    procedure InsertVPageBreak(const aCol: word);virtual;

    constructor Create(const aWorkbookGlobals: TWorkbookGlobals);virtual;

    property Selected: boolean read GetSelected write SetSelected;
    property ShowGridLines: boolean read GetShowGridLines write SetShowGridLines;
    property ShowGridHeaders: boolean read GetShowGridHeaders write SetShowGridHeaders;
    property PrintGridLines: boolean read GetPrintGridLines write SetPrintGridLines;

    property PageHeader: UTF16String read GetPageHeader write SetPageHeader;
    property PageFooter: UTF16String read GetPageFooter write SetPageFooter;
    property Margins: TXlsMargins read GetMargins write SetMargins; //Margins are in inches

    property SheetZoom: integer read GetSheetZoom write SetSheetZoom;

    property IsR1C1: boolean read GetIsR1C1 write SetIsR1C1;

    procedure FreezePanes(const row, col: integer);
    procedure GetFrozenPanes(out row, col: integer);
    procedure GetSplitWindow(out xOffset, yOffset: integer);
    procedure SplitWindow(const xOffset, yOffset: integer);

    property CodeName: UTF16String read GetCodeName write SetCodeName;

    function TotalSize:int64; override;
    function FixTotalSize(const NeedsRecalc: boolean): int64;
    function TotalRangeSize(const SheetIndex: integer; const CellRange: TXlsCellRange):int64; override;

   	procedure RestoreObjectCoords; virtual;

    procedure FixRows; virtual;
  end;

  ClassOfTSheet= class of TSheet;

  TFlxChart = class (TSheet)
  private
    FChartRecords: TChartRecordList;
    procedure LoadSubChart(const DataStream: TOle2File; var RecordHeader: TRecordHeader; const SST: TSST; const Level: integer);
  protected
    procedure SetPageHeaderFooter(const P: TPageHeaderFooterRecord; const s: UTF16String);override;
    procedure AddZoomRecord; override;
    procedure FixCachePointers; override;

  public
    RemainingData: TBaseRecord;

    constructor Create(const aWorkbookGlobals: TWorkbookGlobals);override;
    destructor Destroy;override;
    function DoCopyTo: TSheet; override;

    function TotalSize:int64; override;
    function TotalRangeSize(const SheetIndex: integer; const CellRange: TXlsCellRange):int64; override;
    procedure LoadFromStream(const DataStream: TOle2File; var RecordHeader: TRecordHeader; const First: TBOFRecord; const SST: TSST);override;
    procedure SaveToStream(const DataStream: TOle2File);override;
    procedure SaveRangeToStream(const DataStream: TOle2File; const SheetIndex: integer; const CellRange: TXlsCellRange);override;
    procedure Clear; override;
    procedure ArrangeCopyRowsAndCols(const RowOffset, ColOffset: integer);
    procedure ArrangeCopySheet(const SheetInfo: TSheetInfo);override;
    procedure ArrangeInsertRowsAndCols(const InsRowPos, InsRowCount, InsColPos, InsColCount: integer; const SheetInfo: TSheetInfo);override;
    procedure InsertAndCopyRowsAndCols(const FirstRow, LastRow, DestRow, aRowCount,FirstCol, LastCol, DestCol, aColCount: integer; const SheetInfo: TSheetInfo; const OnlyFormulas: boolean);override;
    procedure DeleteRowsAndCols(const aRow, aRowCount, aCol, aColCount: word; const SheetInfo: TSheetInfo);override;

  end;

  TSheetRecordList=class(TBaseRecordList)
  end;

  TChartList = class(TBaseList) //records are TFlxChart
    {$INCLUDE TChartListHdr.inc}
    procedure SaveToStream(const DataStream: TOle2File);
    procedure ArrangeInsertRowsAndCols(const InsRowPos, InsRowCount, InsColPos, InsColCount: integer; const SheetInfo: TSheetInfo);
  end;

  TFlxUnsupportedSheet = class (TSheet)
  private
    FSheetRecords: TSheetRecordList;
  protected
    procedure SetPageHeaderFooter(const P: TPageHeaderFooterRecord; const s: UTF16String);override;
    procedure AddZoomRecord; override;
    procedure FixCachePointers; override;

  public

    constructor Create(const aWorkbookGlobals: TWorkbookGlobals);override;
    destructor Destroy;override;
    function DoCopyTo: TSheet; override;

    function TotalSize:int64; override;
    function TotalRangeSize(const SheetIndex: integer; const CellRange: TXlsCellRange):int64; override;
    procedure LoadFromStream(const DataStream: TOle2File; var RecordHeader: TRecordHeader; const First: TBOFRecord; const SST: TSST);override;
    procedure SaveToStream(const DataStream: TOle2File);override;
    procedure SaveRangeToStream(const DataStream: TOle2File; const SheetIndex: integer; const CellRange: TXlsCellRange);override;
    procedure Clear; override;
    procedure ArrangeCopySheet(const SheetInfo: TSheetInfo);override;
    procedure ArrangeInsertRowsAndCols(const InsRowPos, InsRowCount, InsColPos, InsColCount: integer; const SheetInfo: TSheetInfo);override;
    procedure InsertAndCopyRowsAndCols(const FirstRow, LastRow, DestRow, aRowCount,FirstCol, LastCol, DestCol, aColCount: integer; const SheetInfo: TSheetInfo; const OnlyFormulas: boolean);override;
    procedure DeleteRowsAndCols(const aRow, aRowCount, aCol, aColCount: word; const SheetInfo: TSheetInfo);override;

  end;

  TWorkSheet = class (TSheet)
  private
    FMiscRecords1: TBaseRecordList;
    FMiscRecords2: TBaseRecordList;
    FHPageBreaks: THPageBreakList;
    FVPageBreaks: TVPageBreakList;
    FDrawing: TDrawing;
    FCells: TCells;
    FRanges: TRangeList;
    FNotes: TNoteList;
    FColumns: TColInfoList;
    FHLinks: THLinkList;

    FDefRowHeight: Longint;
    FDefColWidth:  integer;

    function GetDrawingRow(index: integer): integer;
    function GetDrawingName(index: integer): UTF16String;
    function GetPrintNumberOfHorizontalPages: word;
    function GetPrintNumberOfVerticalPages: word;
    function GetPrintScale: integer;
    function GetPrintToFit: boolean;
    procedure SetPrintNumberOfHorizontalPages(const Value: word);
    procedure SetPrintNumberOfVerticalPages(const Value: word);
    procedure SetPrintScale(const Value: integer);
    procedure SetPrintToFit(const Value: boolean);
    function GetPrintOptions: word;
    procedure SetPrintOptions(const Value: word);

    function GetPrintCopies: integer;
    function GetPrinterDriverSettings: TPrinterDriverSettings;
    function GetPrintPaperSize: TExcelPaperSize;
    function GetPrintXResolution: integer;
    function GetPrintYResolution: integer;
    procedure SetPrintCopies(const Value: integer);
    procedure SetPrinterDriverSettings(
      const Value: TPrinterDriverSettings);
    procedure SetPrintPaperSize(const Value: TExcelPaperSize);
    procedure SetPrintXResolution(const Value: integer);
    procedure SetPrintYResolution(const Value: integer);

    function GetColOutlineLevel(col: integer): integer;
    function GetRowOutlineLevel(row: integer): integer;
    procedure SetColOulineLevel(col: integer; const Value: integer);
    procedure SetRowOulineLevel(row: integer; const Value: integer);

    procedure EnsureGuts;
    procedure AddPrinterDriverRecord(const aPlsRecord: TPlsRecord; const FRecords: TBaseRecordList);
    procedure RemovePrinterDriverRecord;overload;
    procedure RemovePrinterDriverRecord(const FRecords: TBaseRecordList);overload;
    function GetOutlineSummaryColsRightOfDetail: boolean;
    function GetOutlineSummaryRowsBelowDetail: boolean;
    function GetOutlineAutomaticStyles: boolean;
    procedure SetOutlineSummaryColsRightOfDetail(const value: boolean);
    procedure SetOutlineSummaryRowsBelowDetail(const value: boolean);
    procedure SetOutlineAutomaticStyles(const value: boolean);
    function GetPrintHCentered: boolean;
    function GetPrintVCentered: boolean;
    procedure SetPrintHCentered(const Value: boolean);
    procedure SetPrintVCentered(const Value: boolean);
  protected
    procedure AddZoomRecord; override;
    procedure SetPageHeaderFooter(const P: TPageHeaderFooterRecord; const s: UTF16String);override;
    procedure FixCachePointers; override;
    procedure AddOrRemovePane(const Add: boolean);override;
  public
    constructor Create(const aWorkbookGlobals: TWorkbookGlobals);override;
    constructor CreateFromData(const aWorkbookGlobals: TWorkbookGlobals; const SST: TSST);
    destructor Destroy;override;
    function DoCopyTo: TSheet; override;

    function TotalSize:int64; override;
    function TotalRangeSize(const SheetIndex: integer; const CellRange: TXlsCellRange): int64;override;
    procedure LoadFromStream(const DataStream: TOle2File; var RecordHeader: TRecordHeader; const First: TBOFRecord; const SST: TSST);override;
    procedure SaveToStream(const DataStream: TOle2File);override;
    procedure SaveRangeToStream(const DataStream: TOle2File; const SheetIndex: integer; const CellRange: TXlsCellRange);override;
    procedure Clear; override;

    procedure InsertAndCopyRowsAndCols(const FirstRow, LastRow, DestRow, aRowCount,FirstCol, LastCol, DestCol, aColCount: integer; const SheetInfo: TSheetInfo; const OnlyFormulas: boolean);override;
    procedure DeleteRowsAndCols(const aRow, aRowCount, aCol, aColCount: word; const SheetInfo: TSheetInfo);override;

    procedure ArrangeInsertRowsAndCols(const InsRowPos, InsRowCount, InsColPos, InsColCount: integer; const SheetInfo: TSheetInfo);override;
    procedure ArrangeCopySheet(const SheetInfo: TSheetInfo); override;

    procedure ClearValues;

    property Notes: TNoteList read FNotes;
    property Cells: TCells read FCells;
    property HLinks: THLinkList read FHLinks;

    function DrawingCount: integer;
    procedure AssignDrawing(const Index: integer; const Data: ByteArray; const DataType: TXlsImgTypes);
    procedure GetDrawingFromStream(const Index: integer; const Data: TStream; var DataType: TXlsImgTypes);
    property DrawingRow[index: integer]: integer read GetDrawingRow;
    property DrawingName[index: integer]: UTF16String read GetDrawingName;
    function GetAnchor(const Index: integer): TClientAnchor;
    procedure SetAnchor(const Index: integer; const aAnchor: TClientAnchor);

    procedure DeleteImage(const Index: integer);
    procedure ClearImage(const Index: integer);
    procedure AddImage(const Data: ByteArray; const DataType: TXlsImgTypes; const Properties: TImageProperties;const Anchor: TFlxAnchorType);

    procedure AddNewComment(const Row, Col: integer; const Txt: UTF16String; const Properties: TImageProperties);

    procedure DeleteHPageBreak(const aRow: word);override;
    procedure DeleteVPageBreak(const aCol: word);override;
    procedure InsertHPageBreak(const aRow: word);override;
    procedure InsertVPageBreak(const aCol: word);override;

    function GetRowHeight(const aRow: integer): integer;overload;
    function GetRowHeight(const aRow: integer; const HiddenIsZero: boolean): integer;overload;
    function GetColWidth(const aCol: Word): integer;overload;
    function GetColWidth(const aCol: Word; const HiddenIsZero: boolean): integer;overload;
    procedure SetRowHeight(const aRow: integer; const Value: integer);
    procedure SetColWidth(const aCol: Word; const Value: integer);

    function GetRowHidden(const aRow: integer): boolean;
    function GetColHidden(const aCol: Word): boolean;
    procedure SetRowHidden(const aRow: integer; const Value: boolean);
    procedure SetColHidden(const aCol: Word; const Value: boolean);


    property DefRowHeight: Longint read FDefRowHeight;
    property DefColWidth:  integer read FDefColWidth;

    function GetRowFormat(const aRow: integer): integer;
    function GetColFormat(const aCol: integer): integer;
    procedure SetRowFormat(const aRow: integer; const Value: integer);
    procedure SetColFormat(const aCol: integer; const Value: integer);

    function CellMergedBounds(const aRow, aCol: integer): TXlsCellRange;
    function CellMergedList(const index: integer): TXlsCellRange;
    function CellMergedListCount: integer;
    procedure MergeCells(aRow1, aCol1, aRow2, aCol2: integer);
    procedure UnMergeCells(aRow1, aCol1, aRow2, aCol2: integer);

    function HasHPageBreak(const Row: integer): boolean;
    function HasVPageBreak(const Col: integer): boolean;

    property PrintToFit: boolean read GetPrintToFit write SetPrintToFit;
    property PrintHCentered: boolean read GetPrintHCentered write SetPrintHCentered;
    property PrintVCentered: boolean read GetPrintVCentered write SetPrintVCentered;

    property PrintScale: integer read GetPrintScale write SetPrintScale;
    property PrintNumberOfHorizontalPages: word read GetPrintNumberOfHorizontalPages write SetPrintNumberOfHorizontalPages;
    property PrintNumberOfVerticalPages: word read GetPrintNumberOfVerticalPages write SetPrintNumberOfVerticalPages;
    property PrintOptions: word read GetPrintOptions write SetPrintOptions;

    property PrintPaperSize: TExcelPaperSize read GetPrintPaperSize write SetPrintPaperSize;
    property PrintCopies: integer read GetPrintCopies write SetPrintCopies;
    property PrintXResolution: integer read GetPrintXResolution write SetPrintXResolution;
    property PrintYResolution: integer read GetPrintYResolution write SetPrintYResolution;

    property PrinterDriverSettings: TPrinterDriverSettings read GetPrinterDriverSettings write SetPrinterDriverSettings;

    constructor DoCreateFromData(const SST: TSST);

    property RowOutlineLevel[row: integer]: integer read GetRowOutlineLevel write SetRowOulineLevel;
    property ColOutlineLevel[col: integer]: integer read GetColOutlineLevel write SetColOulineLevel;
    property OutlineSummaryRowsBelowDetail: boolean read GetOutlineSummaryRowsBelowDetail write SetOutlineSummaryRowsBelowDetail;
    property OutlineSummaryColsRightOfDetail: boolean read GetOutlineSummaryColsRightOfDetail write SetOutlineSummaryColsRightOfDetail;
    property OutlineAutomaticStyles: boolean read GetOutlineAutomaticStyles write SetOutlineAutomaticStyles;

  	procedure RestoreObjectCoords; override;

    procedure RecalcRowHeights(const Workbook: pointer; const Row1, Row2: Integer; const Forced: Boolean; const KeepAutoFit: Boolean; const Adjustment: extended);
    procedure RecalcColWidths(const Workbook: pointer; const Col1, Col2: Integer; const IgnoreStrings: Boolean; const Adjustment: extended);

    procedure SetAutoFilter(const SheetIndex: Int32; const Row: Int32; const Col1: Int32; const Col2: Int32);
    procedure RemoveAutoFilter();
    function HasAutoFilter(): Boolean;overload;
    function HasAutoFilter(const sheet: Int32; const row: Int32; const col: Int32): Boolean;overload;
    function GetAutoFilterRange(const Sheet: Int32): TXlsCellRange;

    procedure FixRows; override;
    function FixTotalSize(const NeedsRecalc: boolean): int64;

  end;

implementation
{$INCLUDE TChartListImp.inc}

{ TSheet }

function TSheet.CopyTo: TSheet;
begin
  if Self= nil then Result:=nil else Result:= DoCopyTo;
end;

constructor TSheet.Create(const aWorkbookGlobals: TWorkbookGlobals);
begin
  FWorkbookGlobals:=aWorkbookGlobals;
end;

function TSheet.DoCopyTo: TSheet;
begin
  Result:= ClassOfTSheet(ClassType).Create(FWorkbookGlobals);
  Result.sBOF:= sBOF.CopyTo as TBOFRecord;
  Result.sEOF:= sEOF.CopyTo as TEOFRecord;
  Result.FCodeName:= nil;
end;

function TSheet.GetSelected: boolean;
begin
  if (FWindow2<>nil) then Result:=FWindow2.Selected else Result:=false;