Measure/Units/ExportExcel.pas

unit ExportExcel;

interface

uses
  Classes, ZjGrid, ScXlsOutput, ScXlsCustomUD, Windows, StdCtrls,
  sdIDTree, sdDB, Graphics, SysUtils, ProgressHintFrm, Forms, Controls,
  OExport, OExport_Vcl, OExport_VclForms;

type
  TExcelExportor = class
  private
    FOExport: TOExport;
    FGrid: TZJGrid;
    FTempFile: string;
    FFileName: string;

    procedure InitialPage(AGrid: TZJGrid; ASheet: TExportWorkSheet);
  protected
    procedure BeforeExport;
    procedure EndExport;
  public
    constructor Create;
    destructor Destroy; override;

    procedure ExportToSheet(AGrid: TZJGrid; ASheet: TExportWorkSheet);
    procedure ExportToFile(AGrid: TZJGrid; const AFileName: string);
  end;

  PColInfo = ^TColInfo;

  TColInfo = record
    // 字段名
    FieldName: string;
    // 查询字段名，应用于联合几个数据库的情况，参照dataset的Lookup方式
    KeyField: string;
    LookupKeyField: string;
    // 查询数据库ID
    LookupDataSetIndex: Integer;
    // 列名
    TitleCaption: string;
    // 列宽
    Width: Integer;
    // 对齐方式
    HorTextAlign: TCellHAlignment;
    //VerTextAlign: TUDVTextAlign;
  end;

  PColInfos = ^TColInfos;

  TColInfos = array [0..30] of TColInfo;

  // 仿照DataSet的Lookup以及数据库的AutoUpdate, 以达到关于sdIDTree导出数据至Excel的普适性
  // 导出前须根据所需列信息，以及查询数据库（列信息须与查询数据库对等，否则将会报错，并不检查列与数据库是否匹配）
  TIDTreeExcelExportor = class
  private
    FOExport: TOExport;
    FDataSetList: TList;
    FColInfos: PColInfos;
    FColCount: Integer;
    FHasLevelCode: Boolean;
    FTree: TsdIDTree;
    FTempFile: string;

    // 当清单数超过3w3k行时，使用Variant会内存溢出
    function GetCellValue(ANode: TsdIDTreeNode; ColInfo: TColInfo): Variant;
    // 故换成直接使用String
    function GetCellStr(ANode: TsdIDTreeNode; ColInfo: TColInfo): string;
    procedure ExportNodeData(ANode: TsdIDTreeNode; ASheet: TExportWorkSheet; const ALevelCode: string);
    procedure ExportTreeNode(ANode: TsdIDTreeNode; ASheet: TExportWorkSheet; const ALevelCode: string);
    procedure DefineHeader(ASheet: TExportWorkSheet);
  protected
    procedure BeforeExport;
    procedure AfterExport;
  public
    constructor Create;
    destructor Destroy; override;

    procedure AddLookupDataSet(ADataSet: TsdDataSet);
    procedure DefineCol(AColInfos: PColInfos; AColCount: Integer);

    procedure ExportToSheet(ATree: TsdIDTree; ASheet: TExportWorkSheet);
    procedure ExportToFile(ATree: TsdIDTree; const AFileName: string);

    property HasLevelCode: Boolean read FHasLevelCode write FHasLevelCode;
  end;

  TMasterExcelExportor = class
  private
    FOExport: TOExport;
    FColInfos: PColInfos;
    FRelaColInfos: PColInfos;
    FColCount: Integer;
    FTempFile: string;

    FMasterDataSet: TsdDataSet;
    FKeyFieldName: string;
    FRelaDataSet: TsdDataSet;
    FMasterFieldName: string;

    function GetCellValue(ARec: TsdDataRecord; ColInfo: TColInfo): Variant;
    procedure ExportRecord(ARec: TsdDataRecord; ASheet: TExportWorkSheet; AColInfos: PColInfos);
    procedure ExportData(ASheet: TExportWorkSheet);
    procedure DefineHeader(ASheet: TExportWorkSheet);
  protected
    procedure BeforeExport;
    procedure AfterExport;
  public
    constructor Create;
    destructor Destroy; override;

    procedure DefineCol(AColInfos: PColInfos; AColCount: Integer; ARelaColInfo: PColInfos = nil);
    procedure DefineMasterDataSet(ADataSet: TsdDataSet; const AKeyFieldName: string);
    procedure DefineRelaDataSet(ADataSet: TsdDataSet; const AMasterFieldName: string);

    procedure ExportToSheet(ASheet: TExportWorkSheet);
    procedure ExportToFile(const AFileName: string);
  end;

const
  ciLedger: array [0..8] of TColInfo =(
    (FieldName: 'Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '项目节编号'; Width: 120; HorTextAlign: cahLeft),
    (FieldName: 'B_Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单编号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'Name'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '名称'; Width: 200; HorTextAlign: cahLeft),
    (FieldName: 'Units'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单位'; Width: 40; HorTextAlign: cahCenter),
    (FieldName: 'Price'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单价'; Width: 60; HorTextAlign: cahRight),
    (FieldName: 'OrgQuantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '数量'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'OrgTotalPrice'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '金额'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'DrawingCode'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '图号'; Width: 100; HorTextAlign: cahLeft),
    (FieldName: 'MemoStr'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '备注'; Width: 80; HorTextAlign: cahLeft)
  );

  ciLedgerWithMis: array [0..10] of TColInfo =(
    (FieldName: 'Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '项目节编号'; Width: 120; HorTextAlign: cahLeft),
    (FieldName: 'B_Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单编号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'Name'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '名称'; Width: 200; HorTextAlign: cahLeft),
    (FieldName: 'Units'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单位'; Width: 40; HorTextAlign: cahCenter),
    (FieldName: 'Price'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单价'; Width: 60; HorTextAlign: cahRight),
    (FieldName: 'OrgQuantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '施工图数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'MisQuantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '设计错漏数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'OthQuantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '其他错漏数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'TotalPrice'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '金额'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'DrawingCode'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '图号'; Width: 100; HorTextAlign: cahLeft),
    (FieldName: 'MemoStr'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '备注'; Width: 80; HorTextAlign: cahLeft)
  );

  ciFxBills: array [0..10] of TColInfo =(
    (FieldName: 'Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '预算项目节'; Width: 120; HorTextAlign: cahLeft),
    (FieldName: 'B_Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单子目号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'Name'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '名称'; Width: 200; HorTextAlign: cahLeft),
    (FieldName: 'Units'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单位'; Width: 40; HorTextAlign: cahCenter),
    (FieldName: 'OrgQuantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单数量'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'DgnQuantity1'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '设计数量1'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'DgnQuantity2'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '设计数量2'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'Price'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单价'; Width: 60; HorTextAlign: cahRight),
    (FieldName: 'OrgTotalPrice'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '合价'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'DrawingCode'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '图号'; Width: 100; HorTextAlign: cahLeft),
    (FieldName: 'MemoStr'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '备注'; Width: 80; HorTextAlign: cahLeft)
  );

  ciFxBillsWithMis: array [0..12] of TColInfo =(
    (FieldName: 'Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '预算项目节'; Width: 120; HorTextAlign: cahLeft),
    (FieldName: 'B_Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单子目号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'Name'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '名称'; Width: 200; HorTextAlign: cahLeft),
    (FieldName: 'Units'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单位'; Width: 40; HorTextAlign: cahCenter),
    (FieldName: 'OrgQuantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'MisQuantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '设计错漏数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'OthQuantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '其他错漏数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'DgnQuantity1'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '设计数量1'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'DgnQuantity2'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '设计数量2'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'Price'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单价'; Width: 60; HorTextAlign: cahRight),
    (FieldName: 'TotalPrice'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '合价'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'DrawingCode'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '图号'; Width: 100; HorTextAlign: cahLeft),
    (FieldName: 'MemoStr'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '备注'; Width: 80; HorTextAlign: cahLeft)
  );

  ciTpPegGcl: array [0..9] of TColInfo =(
    (FieldName: 'Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '项目节编号'; Width: 120; HorTextAlign: cahLeft),
    (FieldName: 'B_Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单编号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'Name'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '名称'; Width: 200; HorTextAlign: cahLeft),
    (FieldName: 'Units'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单位'; Width: 40; HorTextAlign: cahCenter),
    (FieldName: 'Price'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单价'; Width: 60; HorTextAlign: cahRight),
    (FieldName: 'Quantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'TotalPrice'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '金额'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'Peg'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '桩号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'DrawingCode'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '图号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'MemoStr'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '备注'; Width: 80; HorTextAlign: cahLeft)
  );

  ciTpGclPeg_Gcl: array [0..9] of TColInfo =(
    (FieldName: 'B_Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单编号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '项目节编号'; Width: 120; HorTextAlign: cahLeft),
    (FieldName: 'Name'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '名称'; Width: 200; HorTextAlign: cahLeft),
    (FieldName: 'Units'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单位'; Width: 40; HorTextAlign: cahCenter),
    (FieldName: 'Price'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单价'; Width: 60; HorTextAlign: cahRight),
    (FieldName: 'Quantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'TotalPrice'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '金额'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'Peg'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '桩号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'DrawingCode'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '图号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'MemoStr'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '备注'; Width: 80; HorTextAlign: cahLeft)
  );

  ciTpGclPeg_Peg: array [0..9] of TColInfo =(
    (FieldName: 'B_Code'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '清单编号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'PegXmjCode'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '项目节编号'; Width: 120; HorTextAlign: cahLeft),
    (FieldName: 'PegXmjName'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '名称'; Width: 200; HorTextAlign: cahLeft),
    (FieldName: 'PegXmjUnits'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单位'; Width: 40; HorTextAlign: cahCenter),
    (FieldName: 'Price'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '单价'; Width: 60; HorTextAlign: cahRight),
    (FieldName: 'Quantity'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '数量'; Width: 90; HorTextAlign: cahRight),
    (FieldName: 'TotalPrice'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '金额'; Width: 80; HorTextAlign: cahRight),
    (FieldName: 'Peg'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '桩号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'DrawingCode'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '图号'; Width: 80; HorTextAlign: cahLeft),
    (FieldName: 'MemoStr'; KeyField: ''; LookupKeyField: ''; LookupDataSetIndex: -1; TitleCaption: '备注'; Width: 80; HorTextAlign: cahLeft)
  );

implementation

uses
  ZhAPI, Variants, UtilMethods, Math;

function GetExportor(const AFileType: string): TOCustomExporter;
begin
  if SameText(AFileType, '.xls') then
    Result := TOCustomExporterXLS.Create
  else if SameText(AFileType, '.xlsx') then
    Result := TOCustomExporterXLSX.Create;
end;

{ TExcelExportor }

procedure TExcelExportor.BeforeExport;
begin
  Screen.Cursor := crHourGlass;
  ShowProgressHint('导出Excel表格数据', FGrid.RowCount);
end;

constructor TExcelExportor.Create;
begin
  FOExport := TOExport.Create;
  FOExport.UseProgress := False;

  FTempFile := GetTempFileName;
end;

destructor TExcelExportor.Destroy;
begin
  if FileExists(FTempFile) then
    DeleteFileOrFolder(FTempFile);
  FOExport.Free;
  inherited;
end;

procedure TExcelExportor.EndExport;
begin
  CloseProgressHint;
  Screen.Cursor := crDefault;
end;

procedure TExcelExportor.ExportToFile(AGrid: TZJGrid;
  const AFileName: string);
var
  vExportor: TOCustomExporter;
begin
  FFileName := AFileName;
  FGrid := AGrid;
  BeforeExport;
  try
    vExportor := GetExportor(ExtractFileExt(AFileName));
    ExportToSheet(AGrid, FOExport.AddWorkSheet);
    FOExport.SaveToFile(FTempFile, vExportor);
    if not FileExists(FFileName) or QuestMessage('存在同名文件，是否替换？') then
      CopyFileOrFolder(FTempFile, FFileName);
  finally
    vExportor.Free;
    EndExport;
  end;
end;

procedure TExcelExportor.ExportToSheet(AGrid: TZJGrid;
  ASheet: TExportWorkSheet);

  procedure SetXlsCellTextAlign(ACell: TExportCell; AGridCell: TzjCell);
  begin
    case AGridCell.TextAlign of
      gaTopLeft:
      begin
        ACell.SetVAlignment(cavTop);
        ACell.SetAlignment(cahLeft);
      end;
      gaTopCenter:
      begin
        ACell.SetVAlignment(cavTop);
        ACell.SetAlignment(cahCenter);
      end;
      gaTopRight:
      begin
        ACell.SetVAlignment(cavTop);
        ACell.SetAlignment(cahRight);
      end;
      gaCenterLeft:
      begin
        ACell.SetVAlignment(cavCenter);
        ACell.SetAlignment(cahLeft);
      end;
      gaCenterCenter:
      begin
        ACell.SetVAlignment(cavCenter);
        ACell.SetAlignment(cahCenter);
      end;
      gaCenterRight:
      begin
        ACell.SetVAlignment(cavCenter);
        ACell.SetAlignment(cahRight);
      end;
      gaBottomLeft:
      begin
        ACell.SetVAlignment(cavBottom);
        ACell.SetAlignment(cahLeft);
      end;
      gaBottomCenter:
      begin
        ACell.SetVAlignment(cavBottom);
        ACell.SetAlignment(cahCenter);
      end;
      gaBottomRight:
      begin
        ACell.SetVAlignment(cavBottom);
        ACell.SetAlignment(cahRight);
      end;
    end;
    if goWarpText in AGridCell.Grid.Options then
      ACell.WrapText := True;
  end;

  procedure ExportGridCell(AGridCell: TzjCell; ARow: TExportRow);
  var
    vCell: TExportCell;
    XlsCell: TXlsCustomCell;
  begin
    if (AGridCell = nil) then Exit;
    if ARow.Cells.Count >= AGridCell.Col + 1 then
      vCell := ARow.Cells[AGridCell.Col]
    else
      vCell := ARow.AddCellString(AGridCell.Text);
    SetXlsCellTextAlign(vCell, AGridCell);
    vCell.Font.Name := AGridCell.Font.Name;
    vCell.Font.Size := AGridCell.Font.Size;
    vCell.RowSpan := AGridCell.Height;
    vCell.ColSpan := AGridCell.Width;
    vCell.Width := FGrid.ColWidths[AGridCell.Col];
    vCell.Height := FGrid.RowHeights[AGridCell.Row];
  end;

var
  iColumn, iRow: Integer;
  vRow: TExportRow;
begin
  for iRow := 0 to AGrid.RowCount - 1 do
  begin
    UpdateProgressHint(Format('导出第%d行数据', [iRow + 1]));
    UpdateProgressHint(1);
    vRow := ASheet.AddRow;
    for iColumn := 0 to AGrid.ColCount - 1 do
      ExportGridCell(AGrid.Cells[iColumn, iRow], vRow);
  end;
end;

procedure TExcelExportor.InitialPage(AGrid: TZJGrid;
  ASheet: TExportWorkSheet);

  procedure InitialColumnWidth;
  var
    iColumn: Integer;
  begin
    for iColumn := 0 to AGrid.ColCount - 1 do
      ASheet.Cols[iColumn].SetWidth(AGrid.ColWidths[iColumn]);
  end;

  procedure InitialRowHeight;
  var
    iRow: Integer;
  begin
    for iRow := 0 to AGrid.RowCount - 1 do
      ASheet.Rows[iRow].SetHeight(AGrid.RowHeights[iRow]);
  end;

begin
  InitialColumnWidth;
  InitialRowHeight;
end;

{ TIDTreeExcelExportor }

constructor TIDTreeExcelExportor.Create;
begin
  FOExport := TOExport.Create;
  FDataSetList := TList.Create;
  FTempFile := GetTempFileName;
end;

destructor TIDTreeExcelExportor.Destroy;
begin
  if FileExists(FTempFile) then
    DeleteFileOrFolder(FTempFile);
  FDataSetList.Free;
  FOExport.Free;
  inherited;
end;

procedure TIDTreeExcelExportor.ExportToFile(ATree: TsdIDTree;
  const AFileName: string);
var
  vExportor: TOCustomExporter;
begin
  FTree := ATree;
  BeforeExport;
  try
    vExportor := GetExportor(ExtractFileExt(AFileName));
    ExportToSheet(ATree, FOExport.AddWorkSheet);
    UpdateProgressHint('保存0号台账Excel数据');
    FOExport.SaveToFile(FTempFile, vExportor);
    if not FileExists(AFileName) or QuestMessage('存在同名文件，是否替换？') then
      CopyFileOrFolder(FTempFile, AFileName);
  finally
    vExportor.Free;
    AfterExport;
  end;
end;

procedure TIDTreeExcelExportor.ExportTreeNode(ANode: TsdIDTreeNode;
  ASheet: TExportWorkSheet; const ALevelCode: string);

  function GetFirstChildLevelCode(const ACode: string): string;
  begin
    Result := ACode + '.1';
  end;

  function GetNextSiblingLevelCode(const ACode: string): string;
  var
    strPreCode, strLastCode: string;
    iNextCode: Integer;
  begin
    if Pos('.', ACode) = 0 then
      Result := IntToStr(StrToIntDef(ACode, 1) + 1)
    else
    begin
      strPreCode := GetPrefixOfCode(ACode, '.');
      strLastCode := GetLastSetmentOfCode(ACode, '.');
      iNextCode := StrToIntDef(strLastCode, 1) + 1;
      Result := strPreCode + '.' + IntToStr(iNextCode);
    end;
  end;

var
  sHint: string;
begin
  if not Assigned(ANode) then Exit;

  if ANode.Rec.ValueByName('Code').AsString <> '' then
    sHint := '正在导出清单 ' + ANode.Rec.ValueByName('Code').AsString
  else if ANode.Rec.ValueByName('B_Code').AsString <> '' then
    sHint := '正在导出清单 ' + ANode.Rec.ValueByName('B_Code').AsString
  else
    sHint := '正在导出清单 ' + ANode.Rec.ValueByName('Name').AsString;
  UpdateProgressHint(sHint);
  UpdateProgressHint(1);

  ExportNodeData(ANode, ASheet, ALevelCode);
  ExportTreeNode(ANode.FirstChild, ASheet, GetFirstChildLevelCode(ALevelCode));
  ExportTreeNode(ANode.NextSibling, ASheet, GetNextSiblingLevelCode(ALevelCode));
end;

procedure TIDTreeExcelExportor.ExportNodeData(ANode: TsdIDTreeNode;
  ASheet: TExportWorkSheet; const ALevelCode: string);
var
  iCol: Integer;
  ColInfo: TColInfo;
  vRow: TExportRow;
  vCell: TExportCell;
  XlsCell: TXlsCustomCell;
  sStr: string;
begin
  if not Assigned(ANode) then Exit;
  vRow := ASheet.AddRow;
  vRow.Height := 20;
  for iCol := 0 to FColCount - 1 do
  begin
    ColInfo := FColInfos[iCol];
    vCell := vRow.AddCellString(GetCellStr(ANode, ColInfo));
    vCell.Alignment := ColInfo.HorTextAlign;
    vCell.Font.Name := 'SmartSimSun';
    vCell.Font.Size := 9;
  end;
  if HasLevelCode then
  begin
    vCell := vRow.AddCellString(ALevelCode);
    vCell.Font.Name := 'SmartSimSun';
    vCell.Font.Size := 9;
  end;
end;

procedure TIDTreeExcelExportor.ExportToSheet(ATree: TsdIDTree;
  ASheet: TExportWorkSheet);
begin
  DefineHeader(ASheet);
  ExportTreeNode(ATree.FirstNode, ASheet, '1');
end;

function TIDTreeExcelExportor.GetCellValue(ANode: TsdIDTreeNode;
  ColInfo: TColInfo): Variant;

  function GetRec: TsdDataRecord;
  var
    DataSet: TsdDataSet;
  begin
    Result := nil;
    DataSet := TsdDataSet(FDataSetList.Items[ColInfo.LookupDataSetIndex]);
    if not Assigned(DataSet) then Exit;
    Result := DataSet.Locate(ColInfo.LookupKeyField, ANode.Rec.ValueByName(ColInfo.KeyField).Value);
  end;

var
  ARec: TsdDataRecord;
begin
  Result := '';
  if not Assigned(ANode) then Exit;
  if ColInfo.LookupDataSetIndex = -1 then
    ARec := ANode.Rec
  else
    ARec := GetRec;
  if Assigned(ARec) then
    Result := ARec.ValueByName(ColInfo.FieldName).Value;
end;

procedure TIDTreeExcelExportor.DefineHeader(ASheet: TExportWorkSheet);
var
  iCol: Integer;
  ColInfo: TColInfo;
  vRow: TExportRow;
  vCell: TExportCell;
  XlsCell: TXlsCustomCell;
begin
  vRow := ASheet.AddRow;
  vRow.Height := 20;
  for iCol := 0 to FColCount - 1 do
  begin
    ColInfo := FColInfos[iCol];
    vCell := vRow.AddCellString(ColInfo.TitleCaption);
    vCell.SetAlignment(cahCenter);
    vCell.SetVAlignment(cavCenter);
    vCell.Font.Name := '黑体';
    vCell.Font.Size := 10;
    vCell.Width := ColInfo.Width;
  end;
  if HasLevelCode then
  begin
    vCell := vRow.AddCellString('层次编号');
    vCell.SetAlignment(cahCenter);
    vCell.SetVAlignment(cavCenter);
    vCell.Font.Name := '黑体';
    vCell.Font.Size := 10;
  end;
end;

procedure TIDTreeExcelExportor.AddLookupDataSet(ADataSet: TsdDataSet);
begin
  FDataSetList.Add(ADataSet);
end;

procedure TIDTreeExcelExportor.DefineCol(AColInfos: PColInfos;
  AColCount: Integer);
begin
  FColInfos := AColInfos;
  FColCount := AColCount;
end;

procedure TIDTreeExcelExportor.BeforeExport;
begin
  Screen.Cursor := crHourGlass;
  ShowProgressHint('导出0号台账Excel数据', FTree.Count);
end;

procedure TIDTreeExcelExportor.AfterExport;
begin
  CloseProgressHint;
  Screen.Cursor := crDefault;
end;

function TIDTreeExcelExportor.GetCellStr(ANode: TsdIDTreeNode;
  ColInfo: TColInfo): string;

  function GetRec: TsdDataRecord;
  var
    DataSet: TsdDataSet;
  begin
    Result := nil;
    DataSet := TsdDataSet(FDataSetList.Items[ColInfo.LookupDataSetIndex]);
    if not Assigned(DataSet) then Exit;
    Result := DataSet.Locate(ColInfo.LookupKeyField, ANode.Rec.ValueByName(ColInfo.KeyField).Value);
  end;

var
  ARec: TsdDataRecord;
begin
  Result := '';
  if not Assigned(ANode) then Exit;
  if ColInfo.LookupDataSetIndex = -1 then
    ARec := ANode.Rec
  else
    ARec := GetRec;
  if Assigned(ARec) then
    Result := ARec.ValueByName(ColInfo.FieldName).AsString;
  if SameText(Result, '0') then
    Result := '';
end;

{ TMasterExcelExportor }

procedure TMasterExcelExportor.BeforeExport;
begin
  Screen.Cursor := crHourGlass;
end;

constructor TMasterExcelExportor.Create;
begin
  FOExport := TOExport.Create;
  FTempFile := GetTempFileName;
end;

procedure TMasterExcelExportor.DefineCol(AColInfos: PColInfos;
  AColCount: Integer; ARelaColInfo: PColInfos);
begin
  FColInfos := AColInfos;
  FColCount := AColCount;
  FRelaColInfos := ARelaColInfo;
end;

procedure TMasterExcelExportor.DefineHeader(ASheet: TExportWorkSheet);
var
  iCol: Integer;
  ColInfo: TColInfo;
  vRow: TExportRow;
  vCell: TExportCell;
begin
  vRow := ASheet.AddRow;
  vRow.Height := 20;
  for iCol := 0 to FColCount - 1 do
  begin
    ColInfo := FColInfos[iCol];
    vCell := vRow.AddCellString(ColInfo.TitleCaption);
    vCell.SetAlignment(cahCenter);
    vCell.SetVAlignment(cavCenter);
    vCell.Font.Name := '黑体';
    vCell.Font.Size := 10;
    vCell.Width := ColInfo.Width;
  end;
end;

procedure TMasterExcelExportor.DefineMasterDataSet(ADataSet: TsdDataSet;
  const AKeyFieldName: string);
begin
  FMasterDataSet := ADataSet;
  FKeyFieldName := AKeyFieldName;
end;

procedure TMasterExcelExportor.DefineRelaDataSet(ADataSet: TsdDataSet;
  const AMasterFieldName: string);
begin
  FRelaDataSet := ADataSet;
  FMasterFieldName := AMasterFieldName;
end;

destructor TMasterExcelExportor.Destroy;
begin
  if FileExists(FTempFile) then
    DeleteFileOrFolder(FTempFile);
  FOExport.Free;
  inherited;
end;

procedure TMasterExcelExportor.AfterExport;
begin
  Screen.Cursor := crDefault;
end;

procedure TMasterExcelExportor.ExportData(ASheet: TExportWorkSheet);
var
  i, j: Integer;
  Rec, RelaRec: TsdDataRecord;
begin
  for i := 0 to FMasterDataSet.RecordCount - 1 do
  begin
    Rec := FMasterDataSet.Records[i];
    ExportRecord(Rec, ASheet, FColInfos);
    for j := 0 to FRelaDataSet.RecordCount - 1 do
    begin
      RelaRec := FRelaDataSet.Records[j];
      if (RelaRec.ValueByName(FMasterFieldName).Value = Rec.ValueByName(FKeyFieldName).Value) then
      begin
        if Assigned(FRelaColInfos) then
          ExportRecord(RelaRec, ASheet, FRelaColInfos)
        else
          ExportRecord(RelaRec, ASheet, FColInfos);
      end;
    end;
  end;
end;

procedure TMasterExcelExportor.ExportRecord(ARec: TsdDataRecord;
  ASheet: TExportWorkSheet; AColInfos: PColInfos);
var
  iCol: Integer;
  ColInfo: TColInfo;
  vRow: TExportRow;
  vCell: TExportCell;
  sStr: string;
begin
  if not Assigned(ARec) then Exit;

  vRow := ASheet.AddRow;
  for iCol := 0 to FColCount - 1 do
  begin
    ColInfo := AColInfos[iCol];
    vCell := vRow.AddCellVariant(GetCellValue(ARec, ColInfo));
    vCell.Font.Name := 'SmartSimSun';
    vCell.Font.Size := 9;
  end;
end;

procedure TMasterExcelExportor.ExportToFile(const AFileName: string);
var
  vExportor: TOCustomExporter;
begin
  if not Assigned(FMasterDataSet) then Exit;

  BeforeExport;
  try
    vExportor := GetExportor(ExtractFileExt(AFileName));
    ExportToSheet(FOExport.AddWorkSheet);
    FOExport.SaveToFile(FTempFile, vExportor);
    if not FileExists(AFileName) or QuestMessage('存在同名文件，是否替换？') then
      CopyFile(PChar(FTempFile), PChar(AFileName), False);
  finally
    vExportor.Free;
    AfterExport;
  end;
end;

procedure TMasterExcelExportor.ExportToSheet(ASheet: TExportWorkSheet);
begin
  if not Assigned(FMasterDataSet) then Exit;

  DefineHeader(ASheet);
  ExportData(ASheet);
end;

function TMasterExcelExportor.GetCellValue(ARec: TsdDataRecord;
  ColInfo: TColInfo): Variant;
var
  Value: TsdValue;
begin
  Result := '';
  if Assigned(ARec) then
  begin
    Value := ARec.ValueByName(ColInfo.FieldName);
    if Assigned(Value) then
      Result := Value.AsVariant;
  end;
end;

end.