[分享]代码控制表格文档**

alang

表格文档是TSheetDoc
TSheetDoc = class(TExportableDoc)
protected
  function CreateDocViewer(AMode: TDocMode): Business.Forms.TWinControl; override;
  procedure DefineProperties(Filer: Business.System.TFiler); override;
  procedure DoClose; override;
  procedure DoOpen(AMode: TDocMode; AContainer: Business.Forms.TWinControl); override;
  function GetExporterClass: Business.Forms.TExporterClass; override;
  function GetPageInfoClass: Business.Forms.TPageInfoClass; override;
  procedure InitDocViewer(AMode: TDocMode); override;
  procedure InitOutput; override;
  procedure Loaded; override;
  procedure Modified; override;
  procedure SetReadOnly(Value: Boolean); override;
  property Info: TInfo; readonly;
public
  constructor create(AContext: TContext);
  procedure BeginUpdate;
  function CalcColWidth(ACol: Longint): Longint;
  function CalcRowHeight(ARow: Longint): Longint;
  procedure Delete(ACol: Longint; ARow: Longint; ADeleteType: Business.Forms.TSheetOperate; AColCount: Longint; ARowCount: Longint);
  procedure Destroy; override;
  procedure EndUpdate;
  function EnumCells(AArea: Business.System.TRect; ACreate: Boolean; AEnumProc: Business.Forms.TCellEnumProc; AData: Longint): Longint;
  function EnumCoords(AArea: Business.System.TRect; AEnumProc: Business.Forms.TSheetCoordEnumProc; AData: Longint; AIncX: Boolean; AIncY: Boolean): Longint;
  procedure GetUsingDataSetList(AList: TBizDataSetList); override;
  procedure Insert(ACol: Longint; ARow: Longint; AInsertType: Business.Forms.TSheetOperate; AColCount: Longint; ARowCount: Longint);
  procedure LoadFromFile(AFileName: string);
  procedure LoadFromStream(AStream: Business.System.TStream);
  procedure SaveToFile(AFileName: string);
  procedure SaveToStream(AStream: Business.System.TStream);
  procedure Zap;
  property Areas: Business.Forms.TAreaList;
  property AvailableBounds: Business.System.TRect;
  property Booleans[Longint, Longint]: Boolean;
  property CellClasses[Longint, Longint]: Business.Forms.TCellClass;
  property Cells[Longint, Longint]: Business.Forms.TSheetCell;
  property ColCount: Longint;
  property ColDefWidth: Longint;
  property ColMaxWidth: Longint;
  property ColWidths[Longint]: Longint;
  property Currencies[Longint, Longint]: Int64;
  property DateTimes[Longint, Longint]: Double;
  property DefaultMode: TDocMode;
  property Direction: Business.Forms.TExtentDirection;
  property Doubles[Longint, Longint]: Double;
  property EditOptions: Business.Forms.TEditOptions;
  property Integers[Longint, Longint]: Longint;
  property Objects[Longint, Longint]: Business.System.TPersistent;
  property OutputOptions: Business.Forms.TOutputOptions;
  property PeekCells[Longint, Longint]: Business.Forms.TSheetCell;
  property RowCount: Longint;
  property RowDefHeight: Longint;
  property RowHeights[Longint]: Longint;
  property RowMaxHeight: Longint;
  property Sections[Business.Forms.TSheetSector]: Business.Forms.TSheetSection;
  property Sheet: Business.Forms.TSheet;
  property State: Business.Forms.TSheetState;
  property Strings[Longint, Longint]: string;
  property Styler: Business.Forms.TSheetStyler;
  property Values[Longint, Longint]: TObject;
end;

alang

表格文档中定义的是 TDataSheet
TDataSheet = class(TSheet)
protected
  procedure DefineProperties(Filer: Business.System.TFiler); override;
  procedure SizeChanged(AAxes: TSheetAxes; AClient: Boolean); override;
public
  constructor create(AOwner: Business.System.TComponent);
  procedure AddDataSet(ADataSet: Business.Data.TDataSet);
  procedure Delete(ACol: Longint; ARow: Longint; ADeleteType: TSheetOperate; AColCount: Longint; ARowCount: Longint); override;
  procedure Destroy; override;
  function FindDataSet(ADataSetID: string): Business.Data.TDataSet;
  function FindDataSource(ADataSetID: string): Business.Data.TDataSource;
  function FindSheetArea(ACol: Longint; ARow: Longint): TSheetArea;
  procedure GetAdvisableColRow(ACol: Longint; ARow: Longint); override;
  procedure GetUsingDataSetList(AList: Business.System.TList);
  procedure Insert(ACol: Longint; ARow: Longint; AInsertType: TSheetOperate; AColCount: Longint; ARowCount: Longint); override;
  procedure RemoveDataSet(ADataSet: Business.Data.TDataSet);
  procedure Zap; override;
  property DataSources: TDataSourceList;
  property FieldsDict: Business.System.TStrings;
end;

TSheet = class(TCustomSheet)
protected
  procedure AreasChanged(AArea: TSheetArea);
  procedure DefineProperties(Filer: Business.System.TFiler); override;
  procedure Loaded; override;
  procedure Notification(AComponent: Business.System.TComponent; Operation: Business.System.TOperation); override;
  function SaveStyler: Boolean; virtual;
  procedure SizeChanged(AAxes: TSheetAxes; AClient: Boolean); override;
  function UnpackURL: Boolean; virtual;
public
  constructor create(AOwner: Business.System.TComponent);
  procedure AssignSize(ASrcSheet: TSheet);
  procedure AssignStyler(AStyler: TSheetStyler);
  function CalcHeight(FromRow: Longint; ToRow: Longint; AScale: Longint): Longint;
  function CalcWidth(FromCol: Longint; ToCol: Longint; AScale: Longint): Longint;
  procedure ClearAreaCache;
  procedure ClearCell(ACoord: Business.System.TPoint; ASizes: TCellSides);
  procedure ClearCols(ACol: Longint; ACount: Longint);
  procedure ClearRows(ARow: Longint; ACount: Longint);
  procedure ClearSection(ARect: Business.System.TRect; ClearOptions: TClearOptions; Param: Longint);
  procedure CopyClientSection(ASrcSheet: TSheet; ARect: Business.System.TRect; ACol: Longint; ARow: Longint; AutoSize: Boolean; CopyOnly: Boolean; OutProp: TOutProp; AssignStyle: Boolean);
  procedure CopyCols(ASrcCol: Longint; ADstCol: Longint; ACount: Longint);
  procedure CopyRows(ASrcRow: Longint; ADstRow: Longint; ACount: Longint);
  procedure CopySheet(ASrcSheet: TSheet; ARect: Business.System.TRect);
  procedure Delete(ACol: Longint; ARow: Longint; ADeleteType: TSheetOperate; AColCount: Longint; ARowCount: Longint); virtual;
  procedure Destroy; override;
  procedure DoAll(ADoAllProc: TDoAllProc);
  function GetSeemlyScale(AWidth: Longint): Longint;
  procedure Insert(ACol: Longint; ARow: Longint; AInsertType: TSheetOperate; AColCount: Longint; ARowCount: Longint); virtual;
  procedure PageSetup; virtual;
  procedure Preview; virtual;
  procedure Print; virtual;
  property Areas: TAreaList;
  property AutoTitle: Boolean;
  property Background: Business.System.TPicture;
  property BaseClientStyle: TSheetBaseStyle;
  property BaseTitleStyle: TSheetBaseStyle;
  property CellSizes[Longint, Longint]: Business.System.TRect;
  property ColCount: Longint;
  property ColDefWidth: Longint;
  property ColMaxWidth: Longint;
  property EditOptions: TEditOptions;
  property GridCount: Longint; readonly;
  property Grids[Longint]: TSheetGrid; readonly;
  property OnAreasChanged: Business.System.TNotifyEvent;
  property OnCellChanged: TSheetGridEvent;
  property OnCellClick: TSheetGridEvent;
  property OnCellDblClick: TSheetGridEvent;
  property OnGetCellControl: TGetCellControl;
  property OnGetCellHint: TCellTextEvent;
  property OnModified: Business.System.TNotifyEvent;
  property OnPageSetup: Business.System.TNotifyEvent;
  property OnPreview: Business.System.TNotifyEvent;
  property OnPrint: Business.System.TNotifyEvent;
  property OnSizing: TSheetSizingEvent;
  property OnStylerChanged: Business.System.TNotifyEvent;
  property OutputOptions: TOutputOptions;
  property Params: TSheetParams;
  property ReadOnly: Boolean;
  property RowCount: Longint;
  property RowDefHeight: Longint;
  property RowMaxHeight: Longint;
  property SheetArea: TSheetArea;
  property SheetProperty: TSheetProperty;
  property Styler: TSheetStyler;
  property Title: string;
  property TitleCols: Longint;
  property TitleRows: Longint;
end;

alang

这些属性中，经常用到的是：
1  Areas 文档中定义的区域
数据集区域  TDataSheetArea
决策区域   TDCSheetArea
矩形区域  TSheetArea
2  Cells  文档中的每一个单元格
  单元格需要先设置CellType，再定义单元格的其他属性
类型  CellType  类名
标准单元格 ctCell  TCell
字段标签单元格 ctFieldLabel  TFieldLabelCell
字段单元格 ctField  TFieldCell
图片单元格 ctPicture  TPictureCell
SQL单元格 ctSQL  TSQLCell
图表单元格 ctDataChart  TDataChartCell
表达式单元格 ctExpression TExprCell
控件单元格 ctControl  TControlCell
3  Sheet 文档
4  Sheet.PageInfo 文档的页面信息

针对表格文档的例子，可以参考 系统空间\系统功能\公共类库\表格文档库 中的代码

alang

以下就一些典型的用法给出简单的例子
1  设置表格文档自动适应纸张大小（前提是没有超出很多，否则会看不清了）
主要用到TSheetDoc.PageInfo 获取和设置一些页面信息
var
  lWidth, lScale: Integer;
begin
  //设置纸张的单位是象素数
  TSheetDoc(DocView1.Doc).PageInfo.UnitType := Business.System.TUnits.mmPixel;
  //计算可打印宽度
  lWidth := TSheetDoc(DocView1.Doc).PageInfo.Width-TSheetDoc(DocView1.Doc).PageInfo.MarginLeft-TSheetDoc(DocView1.Doc).PageInfo.MarginRight;
  //按照宽度计算比例
  lScale := TSheetDoc(DocView1.Doc).Sheet.GetSeemlyScale(lWidth);
  //设置打印比例
  TSheetDoc(DocView1.Doc).PageInfo.Scale := lScale;
end;

相关的例子，设置DataGrid的打印比例
var
  i: Integer;
  lpgWidth, lWidth, lScale: Integer;
begin
  //设置纸张的单位是象素数
  DataGrid1.Exporter.PageInfo.UnitType := Business.System.TUnits.mmPixel;
  //计算纸张的可打印宽度
  lpgWidth := DataGrid1.Exporter.PageInfo.Width-DataGrid1.Exporter.PageInfo.MarginLeft-DataGrid1.Exporter.PageInfo.MarginRight;
  //计算打印列的宽度
  lWidth := 0;
  for i:=0 to DataGrid1.ColumnCount-1 do
    if DataGrid1.Columns.Visible then
      Inc(lWidth, DataGrid1.Columns.Width);
  //按照宽度计算比例
  lScale := lpgWidth*100 div lWidth;
  //设置打印比例
  DataGrid1.Exporter.PageInfo.Scale := lScale;
end;

alang

2  代码设置表格文档的页眉页脚
这里用到了 增加行，设置行高，设置单元格内容和边框线 等
type
  TMySheetDoc = class(TSheetDoc)
  private
    function GetHeaderFooter: THeaderFooter;
  public
    property HF: THeaderFooter read GetHeaderFooter;
  end;

function TMySheetDoc.GetHeaderFooter: THeaderFooter;
begin
  Result := HeaderFooter;
end;

procedure TMainForm.Button2Click(Sender: TObject);
var
  i, j: Integer;
  b: TCellSideBorder;
  cs: TCellSides;
begin
  //页头插入两行，变成3行页头
  TMySheetDoc(DocView1.Doc).HF.Sheet.Insert(0, 0, TSheetOperate.soAllRow, 0, 2);
  //设置行高
  TMySheetDoc(DocView1.Doc).HF.Sheet.RowHeights[0] := 10;
  TMySheetDoc(DocView1.Doc).HF.Sheet.RowHeights[1] := 20;
  TMySheetDoc(DocView1.Doc).HF.Sheet.RowHeights[2] := 30;
  //设置四个边框线都需要画
  cs := [TCellSide.sLeft, TCellSide.sRight, TCellSide.sTop, TCellSide.sBottom];
  //设置边框线的颜色和线型
  b.Line := TCellSideLine.slMedium;
  b.Color := Business.System.Graphics.clRed;
  for i:=0 to 7 do
    for j:=0 to 7 do
    begin
      //设置页头页尾单元格的内容
      TMySheetDoc(DocView1.Doc).HF.Sheet.Cells[i,j].AsString := SysUtils.Format('i:%d; j:%d', [i, j]);
      //设置页头页尾单元格如何显示边线
      TMySheetDoc(DocView1.Doc).HF.Sheet.Cells[i,j].Borders[cs] := b;
    end;
end;

说明：
   这里因为HeaderFooter是一个Protected的属性，因此继承了一个TMySheetDoc ，利用这个类把HeaderFooter公开出来，让代码中可以访问到

alang

上面的两个例子，一个是通过 PageInfo 设置显示比例，一个是通过HeaderFooter 设置页眉页脚，可以用在表格文档上，也可以用在 DataGrid 上
DataGrid1.Exporter.PageInfo
DataGrid1.Exporter.HFSheet

alang

3  根据数据集生成DataSheet
此例子用到了 定义区域，定义单元格类型，定义边框线，全新创建一个DataSheet
var
  lSheet: TDataSheet;
  lExporter: TSheetExporter;
  lArea: TDataSheetArea;
  lDataSet: TDataSet;
  lCell: TSheetCell;
  i, lCnt: Integer;
  b: TCellSideBorder;
begin
  lDataSet := DataSetBroker1.DataSet;

  lSheet := TDataSheet.Create(Self);
  lExporter := TSheetExporter.Create(Self);
  lExporter.Sheet := lSheet;
  //这里必须定义PageInfo和HFSheet，否则Preview出指针错
  lExporter.PageInfo := TMySheetDoc(DocView1.Doc).PageInfo as TSheetPageInfo;
  lExporter.HFSheet := TMySheetDoc(DocView1.Doc).HF.Sheet;
  lSheet.AddDataSet(lDataSet);
  lSheet.RowCount := 6;

  b.Line := TCellSideLine.slSingle;
  b.Color := Business.System.Graphics.clBlue;
  lCnt := 0;
  for i:=0 to lDataSet.FieldCount-1 do
  begin
    if not lDataSet.Fields.Visible then
      Continue;
    Inc(lCnt);
    lSheet.ColWidths[lCnt] := lDataSet.Fields.DisplayWidth*lSheet.Cells[0,0].Font.Size;

    //必须先定义CellType再定义单元格的其他属性，否则就晚了
    lSheet.CellTypes[lCnt, 1] := TCellType.ctFieldLabel;
    lCell := lSheet.Cells[lCnt, 1];
    TFieldLabelCell(lCell).DataSetID := TSQLDataSet(lDataSet).ID;
    TFieldLabelCell(lCell).FieldName := lDataSet.Fields.FieldName;
    //设置上边框线为粗线
    b.Line := TCellSideLine.slMedium;
    lCell.Border[TCellSide.sTop] := b;
    //设置下边框线为细线
    b.Line := TCellSideLine.slSingle;
    lCell.Border[TCellSide.sBottom] := b;

    lSheet.CellTypes[lCnt, 2] := TCellType.ctField;
    lCell := lSheet.Cells[lCnt, 2];
    TFieldCell(lCell).DataSetID := TSQLDataSet(lDataSet).ID;
    TFieldCell(lCell).FieldName := lDataSet.Fields.FieldName;
    b.Line := TCellSideLine.slMedium;
    lCell.Border[TCellSide.sBottom] := b;
  end;
  //设置外边框是粗线
  b.Line := TCellSideLine.slMedium;
  lCell := lSheet.Cells[1, 1];
  lCell.Border[TCellSide.sLeft] := b;;
  lCell := lSheet.Cells[1, 2];
  lCell.Border[TCellSide.sLeft] := b;
  lCell := lSheet.Cells[lCnt, 1];
  lCell.Border[TCellSide.sRight] := b;
  lCell := lSheet.Cells[lCnt, 2];
  lCell.Border[TCellSide.sRight] := b;

  //定义数据集区域
  lArea := lSheet.Areas.Add(TAreaType.atDBArea) as TDataSheetArea;
  lArea.DataSetID := TSQLDataSet(lDataSet).ID;
  lArea.BoundsRect := Types.Bounds(1, 1, lCnt, 2);
  lArea.TitleRows := 1;
  //DataSheet的列数一定要比实际列数多一列，否则最右边的边框线就没有了
  lSheet.ColCount := lCnt+1;
  lExporter.Preview(nil);
end;