sheetdata2.pas

一个经典的读写Excel的控件

unit SheetData2;

{
********************************************************************************
******* 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.                                                     **
********************************************************************************
}

{$I XLSRWII2.inc}        

{$B-}
{$Q-}


interface

uses SysUtils, Classes, Windows, Dialogs, BIFFRecsII2, XLSUtils2, XLSStream2,
     Cell2, EncodeFormulaII2, ExcelMaskII2, CellStorage2, Graphics,
{$ifndef OLD_COMPILER}
     Variants,
{$endif}
     CellFormats2, SST2, Validate2, Math, XLSRWIIResourceStrings2, Hyperlink2,
     RecordStorage2, Escher2, EscherTypes2, DrawingObj2, ControlObj2, XLSFonts2,
     ApplyFormat, FormulaHandler2, DrawingObjChart2, MergedCells2, CondFmt2,
     CellAreas2, Columns2, Rows2, RTFReadWrite2, ComCtrls;

type TPaneType = (ptNone,ptFrozen,ptSplit);

type
//:# Printing options.
//: psoLeftToRight = Print over, and then down.<br>
//: psoPortrait = Portrait mode if set. Otherwise landscape mode.<br>
//: psoNoColor = Print in black and white.<br>
//: psoDraftQuality = Print draft quality.<br>
//: psoNotes = Print cell notes.<br>
//: psoRowColHeading = Print row and column headings.<br>
//: psoGridlines = Print grid lines.<br>
//: psoHorizCenter = Center sheet between horizontal margins.<br>
//: psoVertCenter = Center sheet between vertical margins.<br>
     TPrintSetupOption = (psoLeftToRight,psoPortrait,psoNoColor,psoDraftQuality,psoNotes,psoRowColHeading,psoGridlines,psoHorizCenter,psoVertCenter);
type TPrintSetupOptions = set of TPrintSetupOption;
type
//:# Paper size.
//:  psNone = No paper size selected<br>
//:  psLetter = Letter 8 12 x 11 in<br>
//:  psLetterSmall = Letter Small 8 12 x 11 in<br>
//:  psTabloid = Tabloid 11 x 17 in<br>
//:  psLedger = Ledger 17 x 11 in<br>
//:  psLegal = Legal 8 12 x 14 in<br>
//:  psStatement = Statement 5 12 x 8 12 in<br>
//:  psExecutive = Executive 7 14 x 10 12 in<br>
//:  psA3 = A3 297 x 420 mm<br>
//:  psA4 = A4 210 x 297 mm<br>
//:  psA4Small = A4 Small 210 x 297 mm<br>
//:  psA5 = A5 148 x 210 mm<br>
//:  psB4 = B4 (JIS) 250 x 354<br>
//:  psB5 = B5 (JIS) 182 x 257 mm<br>
//:  psFolio = Folio 8 12 x 13 in<br>
//:  psQuarto = Quarto 215 x 275 mm<br>
//:  ps10X14 = 10x14 in<br>
//:  ps11X17 = 11x17 in<br>
//:  psNote = Note 8 12 x 11 in<br>
//:  psEnv9 = Envelope #9 3 78 x 8 78<br>
//:  psEnv10 = Envelope #10 4 18 x 9 12<br>
//:  psEnv11 = Envelope #11 4 12 x 10 38<br>
//:  psEnv12 = Envelope #12 4 \276 x 11<br>
//:  psEnv14 = Envelope #14 5 x 11 12<br>
//:  psCSheet = C size sheet<br>
//:  psDSheet = D size sheet<br>
//:  psESheet = E size sheet<br>
//:  psEnvDL = Envelope DL 110 x 220mm<br>
//:  psEnvC5 = Envelope C5 162 x 229 mm<br>
//:  psEnvC3 = Envelope C3  324 x 458 mm<br>
//:  psEnvC4 = Envelope C4  229 x 324 mm<br>
//:  psEnvC6 = Envelope C6  114 x 162 mm<br>
//:  psEnvC65 = Envelope C65 114 x 229 mm<br>
//:  psEnvB4 = Envelope B4  250 x 353 mm<br>
//:  psEnvB5 = Envelope B5  176 x 250 mm<br>
//:  psEnvB6 = Envelope B6  176 x 125 mm<br>
//:  psEnvItaly = Envelope 110 x 230 mm<br>
//:  psEnvMonarch = Envelope Monarch 3.875 x 7.5 in<br>
//:  psEnvPersonal = 6 34 Envelope 3 58 x 6 12 in<br>
//:  psFanfoldUS = US Std Fanfold 14 78 x 11 in<br>
//:  psFanfoldStdGerman = German Std Fanfold 8 12 x 12 in<br>
//:  psFanfoldLglGerman = German Legal Fanfold 8 12 x 13 in<br>
//:  psISO_B4 = B4 (ISO) 250 x 353 mm<br>
//:  psJapanesePostcard = Japanese Postcard 100 x 148 mm<br>
//:  ps9X11 = 9 x 11 in<br>
//:  ps10X11 = 10 x 11 in<br>
//:  ps15X11 = 15 x 11 in<br>
//:  psEnvInvite = Envelope Invite 220 x 220 mm<br>
//:  psReserved48 = RESERVED--DO NOT USE<br>
//:  psReserved49 = RESERVED--DO NOT USE<br>
//:  psLetterExtra = Letter Extra 9 \275 x 12 in<br>
//:  psLegalExtra = Legal Extra 9 \275 x 15 in<br>
//:  psTabloidExtra = Tabloid Extra 11.69 x 18 in<br>
//:  psA4Extra = A4 Extra 9.27 x 12.69 in<br>
//:  psLetterTransverse = Letter Transverse 8 \275 x 11 in<br>
//:  psA4Transverse = A4 Transverse 210 x 297 mm<br>
//:  psLetterExtraTransverse = Letter Extra Transverse 9\275 x 12 in<br>
//:  psAPlus = SuperASuperAA4 227 x 356 mm<br>
//:  psBPlus = SuperBSuperBA3 305 x 487 mm<br>
//:  psLetterPlus = Letter Plus 8.5 x 12.69 in<br>
//:  psA4Plus = A4 Plus 210 x 330 mm<br>
//:  psA5Transverse = A5 Transverse 148 x 210 mm<br>
//:  psB5transverse = B5 (JIS) Transverse 182 x 257 mm<br>
//:  psA3Extra = A3 Extra 322 x 445 mm<br>
//:  psA5Extra = A5 Extra 174 x 235 mm<br>
//:  psB5Extra = B5 (ISO) Extra 201 x 276 mm<br>
//:  psA2 = A2 420 x 594 mm<br>
//:  psA3Transverse = A3 Transverse 297 x 420 mm<br>
//:  psA3ExtraTransvers = A3 Extra Transverse 322 x 445 mm<br>
  TPaperSize = (
  psNone,
  psLetter,                { Letter 8 12 x 11 in                 }
  psLetterSmall,           { Letter Small 8 12 x 11 in           }
  psTabloid,               { Tabloid 11 x 17 in                  }
  psLedger,                { Ledger 17 x 11 in                   }
  psLegal,                 { Legal 8 12 x 14 in                  }
  psStatement,             { Statement 5 12 x 8 12 in            }
  psExecutive,             { Executive 7 14 x 10 12 in           }
  psA3,                    { A3 297 x 420 mm                     }
  psA4,                    { A4 210 x 297 mm                     }
  psA4Small,               { A4 Small 210 x 297 mm               }
  psA5,                    { A5 148 x 210 mm                     }
  psB4,                    { B4 (JIS) 250 x 354                  }
  psB5,                    { B5 (JIS) 182 x 257 mm               }
  psFolio,                 { Folio 8 12 x 13 in                  }
  psQuarto,                { Quarto 215 x 275 mm                 }
  ps10X14,                 { 10x14 in                            }
  ps11X17,                 { 11x17 in                            }
  psNote,                  { Note 8 12 x 11 in                   }
  psEnv9,                  { Envelope #9 3 78 x 8 78             }
  psEnv10,                 { Envelope #10 4 18 x 9 12            }
  psEnv11,                 { Envelope #11 4 12 x 10 38           }
  psEnv12,                 { Envelope #12 4 \276 x 11            }
  psEnv14,                 { Envelope #14 5 x 11 12              }
  psCSheet,                { C size sheet                        }
  psDSheet,                { D size sheet                        }
  psESheet,                { E size sheet                        }
  psEnvDL,                 { Envelope DL 110 x 220mm             }
  psEnvC5,                 { Envelope C5 162 x 229 mm            }
  psEnvC3,                 { Envelope C3  324 x 458 mm           }
  psEnvC4,                 { Envelope C4  229 x 324 mm           }
  psEnvC6,                 { Envelope C6  114 x 162 mm           }
  psEnvC65,                { Envelope C65 114 x 229 mm           }
  psEnvB4,                 { Envelope B4  250 x 353 mm           }
  psEnvB5,                 { Envelope B5  176 x 250 mm           }
  psEnvB6,                 { Envelope B6  176 x 125 mm           }
  psEnvItaly,              { Envelope 110 x 230 mm               }
  psEnvMonarch,            { Envelope Monarch 3.875 x 7.5 in     }
  psEnvPersonal,           { 6 34 Envelope 3 58 x 6 12 in        }
  psFanfoldUS,             { US Std Fanfold 14 78 x 11 in        }
  psFanfoldStdGerman,      { German Std Fanfold 8 12 x 12 in     }
  psFanfoldLglGerman,      { German Legal Fanfold 8 12 x 13 in   }
  psISO_B4,                { B4 (ISO) 250 x 353 mm               }
  psJapanesePostcard,      { Japanese Postcard 100 x 148 mm      }
  ps9X11,                  { 9 x 11 in                           }
  ps10X11,                 { 10 x 11 in                          }
  ps15X11,                 { 15 x 11 in                          }
  psEnvInvite,             { Envelope Invite 220 x 220 mm        }
  psReserved48,            { RESERVED--DO NOT USE                }
  psReserved49,            { RESERVED--DO NOT USE                }
  psLetterExtra,           { Letter Extra 9 \275 x 12 in         }
  psLegalExtra,            { Legal Extra 9 \275 x 15 in          }
  psTabloidExtra,          { Tabloid Extra 11.69 x 18 in         }
  psA4Extra,               { A4 Extra 9.27 x 12.69 in            }
  psLetterTransverse,      { Letter Transverse 8 \275 x 11 in    }
  psA4Transverse,          { A4 Transverse 210 x 297 mm          }
  psLetterExtraTransverse, { Letter Extra Transverse 9\275 x 12 in  }
  psAPlus,                 { SuperASuperAA4 227 x 356 mm         }
  psBPlus,                 { SuperBSuperBA3 305 x 487 mm         }
  psLetterPlus,            { Letter Plus 8.5 x 12.69 in          }
  psA4Plus,                { A4 Plus 210 x 330 mm                }
  psA5Transverse,          { A5 Transverse 148 x 210 mm          }
  psB5transverse,          { B5 (JIS) Transverse 182 x 257 mm    }
  psA3Extra,               { A3 Extra 322 x 445 mm               }
  psA5Extra,               { A5 Extra 174 x 235 mm               }
  psB5Extra,               { B5 (ISO) Extra 201 x 276 mm         }
  psA2,                    { A2 420 x 594 mm                     }
  psA3Transverse,          { A3 Transverse 297 x 420 mm          }
  psA3ExtraTransverse      { A3 Extra Transverse 322 x 445 mm    }
  );

type TSheet = class;

//:# Defines a horizontal page break.
//: THorizPagebreak defines a horizontal pagebreak when printing the worksheet.
     THorizPagebreak = class(TCollectionItem)
private
     FRow: integer;
     FCol1,FCol2: integer;
public
     constructor Create(Collection: TCollection); override;
published
     //:# Row for the horizontal pagebreak.
     //: Set Row to the row for the horizontal page break.
     property Row: integer read FRow write FRow;
     //:# First column for the horizontal pagebreak.
     //: Set Col1 to the first column for the horizontal page break.
     property Col1: integer read FCol1 write FCol1;
     //:# Last column for the horizontal pagebreak.
     //: Set Col2 to the Last column for the horizontal page break. If all
     //: columns shall be included, set it to 255.
     property Col2: integer read FCol2 write FCol2;
     end;

//:# List of horizontal page breaks.
     THorizPagebreaks = class(TCollection)
private
     FOwner: TPersistent;
     function GetItem(Index: integer): THorizPagebreak;
protected
     function  GetOwner: TPersistent; override;
public
     constructor Create(AOwner: TPersistent);
     //:# Add a new THorizPagebreak object.
     function  Add: THorizPagebreak;
     //:# The THorizPagebreak objects in the list.
     property Items[Index: integer]: THorizPagebreak read GetItem; default;
     end;

//:# Defines a vertical page break.
//: TVertPagebreak defines a vertical pagebreak when printing the worksheet.
     TVertPagebreak = class(TCollectionItem)
private
     FCol: integer;
     FRow1,FRow2: integer;
public
     constructor Create(Collection: TCollection); override;
published
     //:# Column for vertical pagebreak.
     //: Set Col to the column for the vertical page break.
     property Col: integer read FCol write FCol;
     //:# First row for vertical pagebreak.
     //: Set Row1 to the first row for the vertical page break.
     property Row1: integer read FRow1 write FRow1;
     //:# Last row for vertical pagebreak.
     //: Set Row2 to the last row for the vertical page break. If all rows
     //: shall be included, set it to 65535.
     property Row2: integer read FRow2 write FRow2;
     end;

     TVertPagebreaks = class(TCollection)
private
     FOwner: TPersistent;
     function GetItem(Index: integer): TVertPagebreak;
protected
     function  GetOwner: TPersistent; override;
public
     constructor Create(AOwner: TPersistent);
     //:# Add a new TVertPagebreak object.
     function  Add: TVertPagebreak;
     //:# The TVertPagebreak objects in the list.
     property Items[Index: integer]: TVertPagebreak read GetItem; default;
     end;

//:# Options for printing the worksheet.
     TPrintSettings = class(TPersistent)
private
     FParent: TSheet;
     FHeader,FFooter: WideString;
     FMarginLeft,FMarginRight,FMarginTop,FMarginBottom: double;
     FHorizPagebreaks: THorizPagebreaks;
     FVertPagebreaks: TVertPagebreaks;

     function  GetOptions: TPrintSetupOptions;
     procedure SetOptions(const Value: TPrintSetupOptions);
     function  GetCopies: word;
     function  GetFooterMargin: double;
     function  GetHeaderMargin: double;
     function  GetPaperSize: TPaperSize;
     function  GetScalingFactor: word;
     function  GetStartingPage: word;
     procedure SetCopies(const Value: word);
     procedure SetFooterMargin(const Value: double);
     procedure SetHeaderMargin(const Value: double);
     procedure SetPaperSize(const Value: TPaperSize);
     procedure SetScalingFactor(const Value: word);
     procedure SetStartingPage(const Value: word);
     function  GetResolution: integer;
     procedure SetResolution(const Value: integer);
     function  GetMarginBottomCm: double;
     function  GetMarginLeftCm: double;
     function  GetMarginRightCm: double;
     function  GetMarginTopCm: double;
     procedure SetMarginBottomCm(const Value: double);
     procedure SetMarginLeftCm(const Value: double);
     procedure SetMarginRightCm(const Value: double);