ufrmexportbusinessdata.pas

抽象三层访问数据库示例

  btnExecute.Enabled := false;
  PageControl1.Enabled := false;
  try
    GetExportBusinessData(mmBusinessNameOrID.Text);
    ExportBusinessData(mmBusinessNameOrID.Text, edtExportFile.Text);
    application.MessageBox('导出流程权限设置数据成功完成', '系统提示', MB_ICONINFORMATION);
  finally
    cdsBusinessGrant.Close;
    cdsBusinessConfig.Close;
    cdsProcessConfig.Close;
    cdsProcessOperates.Close;
    btnExecute.Enabled := true;
    PageControl1.Enabled := true;
  end;
end;

procedure TfrmExportBusinessData.cdsBusinessGrantAfterOpen(
  DataSet: TDataSet);
var
  i: integer;
begin
  for i := 0 to DataSet.FieldCount - 1 do
    DataSet.Fields[i].DisplayWidth := 10;
end;

procedure TfrmExportBusinessData.ExportBusinessData(ABusinessNameOrIDs,
  AFileName: string);
var
  i, intRow, intBaseCol, intBaseRow: integer;
  intBusinessStartRow, intProcessStartRow, intRoleStartRow: integer;
  tmpXLSReadWriteAgent: TXLSReadWriteAgent;
  xc: TExcelColor;
begin
  if cdsBusinessGrant.IsEmpty then
    exit;
  if FileExists(AFileName) then
    DeleteFile(AFileName)
  else
    if not DirectoryExists(ExtractFileDir(AFileName)) then
    begin
      ForceDirectories(ExtractFileDir(AFileName));
      if not DirectoryExists(ExtractFileDir(AFileName)) then
        raise Exception.Create('输入的文件名路径无法创建');
    end;
  tmpXLSReadWriteAgent := TXLSReadWriteAgent.Create(self);
  try
    tmpXLSReadWriteAgent.FileName := AFileName;
    tmpXLSReadWriteAgent.ReadFile;
    intBaseCol := 0;
    intBaseRow := 0;
    intRow := intBaseRow + 1;
    ShowExecuteInfo('开始导出流程');
    //标题行内容
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol, intBaseRow, '流程');
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 1, intBaseRow, '过程');
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intBaseRow, '角色');
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 3, intBaseRow, '菜单');
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 4, intBaseRow, '模块');
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 5, intBaseRow, '分区');
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 6, intBaseRow, '权限');
    tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol, intBaseRow, intBaseCol + 6, intBaseRow].FillPatternForeColor := xc26;
    for i := 0 to 6 do
      tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + i, intBaseRow, intBaseCol + i, intBaseRow].BorderOutlineStyle := cbsThin;
    cdsBusinessGrant.First;
    while not cdsBusinessGrant.Eof do
    begin
      if cdsBusinessGrant.FieldByName('data_type').AsString = 'Business' then
      begin
        ShowExecuteInfo(Format('正在导出流程 %s 相关数据', [cdsBusinessGrant.FieldByName('business_name').AsString]));
        if intRow > intBaseRow then
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol, intBusinessStartRow, intBaseCol + 6, intRow - 1].BorderOutlineStyle := cbsThin;
        intBusinessStartRow := intRow;
        tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol, intRow, cdsBusinessGrant.FieldByName('business_name').AsString);
        tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol, intRow].FontStyle := [xfsBold];
        tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol, intRow, intBaseCol + 6, intRow].BorderOutlineStyle := cbsThin;
        tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol, intRow, intBaseCol + 6, intRow].FillPatternForeColor := xc27;
        tmpXLSReadWriteAgent.MergedCells(intBaseCol, intRow, intBaseCol + 6, intRow);
        //导出流程配置
        if cdsBusinessConfig.Locate('business_id', cdsBusinessGrant.FieldByName('business_id').AsString, []) then
        begin
          inc(intRow);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 1, intRow, '办理期限');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 1, intRow].FontStyle := [xfsBold];//xfsItalic, xfsBold
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, cdsBusinessConfig.FieldByName('time_limit').AsString);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 3, intRow, '主办部门');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 3, intRow].FontStyle := [xfsBold];
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 4, intRow, cdsBusinessConfig.FieldByName('organ_name').AsString);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 5, intRow, '主办人员');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 5, intRow].FontStyle := [xfsBold];
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 6, intRow, cdsBusinessConfig.FieldByName('emp_name').AsString);
          //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol, intRow, intBaseCol + 6, intRow].BorderOutlineStyle := cbsThin;
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol, intRow, intBaseCol + 6, intRow].FillPatternForeColor := xcYellow;
        end;
      end
      else if cdsBusinessGrant.FieldByName('data_type').AsString = 'Process' then
      begin
        ShowExecuteInfo(Format('正在导出过程 %s.%s 相关数据',
          [cdsBusinessGrant.FieldByName('business_name').AsString,
          cdsBusinessGrant.FieldByName('process_name').AsString]));
        intProcessStartRow := intRow;
        tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 1, intRow, cdsBusinessGrant.FieldByName('process_name').AsString);
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 1, intRow, intBaseCol + 6, intRow].BorderOutlineStyle := cbsThin;
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 1, intRow, intBaseCol + 6, intRow].FillPatternForeColor := xcPink;//IntToXColor($00D8EBEB);//xcYellow;
        tmpXLSReadWriteAgent.MergedCells(intBaseCol + 1, intRow, intBaseCol + 6, intRow);
        //导出过程配置
        if cdsProcessConfig.Locate('business_id', cdsBusinessGrant.FieldByName('process_id').AsString, []) then
        begin
          inc(intRow);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, '办理期限');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 2, intRow].FontStyle := [xfsBold];
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 3, intRow, cdsProcessConfig.FieldByName('time_limit').AsString);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 4, intRow, '是否可办结');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 4, intRow].FontStyle := [xfsBold];
          if cdsProcessConfig.FieldByName('can_finish').AsString = '0' then
            tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 5, intRow, '否')
          else
            tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 5, intRow, '是');
          inc(intRow);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, '办理部门');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 2, intRow].FontStyle := [xfsBold];
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 3, intRow, cdsProcessConfig.FieldByName('organ_name').AsString);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 4, intRow, '办理角色');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 4, intRow].FontStyle := [xfsBold];
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 5, intRow, cdsProcessConfig.FieldByName('role_name').AsString);
          inc(intRow);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, '办理人员');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 2, intRow].FontStyle := [xfsBold];
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 3, intRow, cdsProcessConfig.FieldByName('emp_name').AsString);
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 1, intRow - 2, intBaseCol + 6, intRow].FillPatternForeColor := xcYellow;
        end;
        //导出过程更多办理部门、角色
        cdsProcessOperates.Filter := 'business_id = ' + QuotedStr(cdsBusinessGrant.FieldByName('process_id').AsString);
        cdsProcessOperates.Filtered := true;
        if not cdsProcessOperates.IsEmpty then
        begin
          inc(intRow);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, '更多的办理部门、角色');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 2, intRow].FontStyle := [xfsBold];
          inc(intRow);
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, '承办类型');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 2, intRow].FontStyle := [xfsBold];
          tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 3, intRow, '承办名称');
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Cell[intBaseCol + 3, intRow].FontStyle := [xfsBold];
          while not cdsProcessOperates.Eof do
          begin
            inc(intRow);
            if cdsProcessOperates.FieldByName('type').AsString = '0' then
              tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, '部门')
            else
              tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, '角色');
            tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 3, intRow, cdsProcessOperates.FieldByName('name').AsString);
            cdsProcessOperates.Next;
          end;
          tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 1, intRow - 2 - cdsProcessOperates.RecordCount, intBaseCol + 6, intRow].FillPatternForeColor := xcYellow;
        end;
      end
      else if cdsBusinessGrant.FieldByName('data_type').AsString = 'Role' then
      begin
        ShowExecuteInfo(Format('正在导出角色 %s.%s.%s 相关数据',
          [cdsBusinessGrant.FieldByName('business_name').AsString,
          cdsBusinessGrant.FieldByName('process_name').AsString,
          cdsBusinessGrant.FieldByName('role_name').AsString]));
        intRoleStartRow := intRow;
        tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 2, intRow, cdsBusinessGrant.FieldByName('role_name').AsString);
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 2, intRow, intBaseCol + 6, intRow].BorderOutlineStyle := cbsThin;
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 2, intRow, intBaseCol + 6, intRow].FillPatternForeColor := IntToXColor($00D8EBEB);//xcYellow;
        tmpXLSReadWriteAgent.MergedCells(intBaseCol + 2, intRow, intBaseCol + 6, intRow);
      end
      else if cdsBusinessGrant.FieldByName('data_type').AsString = 'Menu' then
      begin
        tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 3, intRow, cdsBusinessGrant.FieldByName('menu_name').AsString);
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 3, intRow, intBaseCol + 6, intRow].BorderOutlineStyle := cbsThin;
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 3, intRow, intBaseCol + 6, intRow].FillPatternForeColor := IntToXColor($00D8EBEB);//xcYellow;
        tmpXLSReadWriteAgent.MergedCells(intBaseCol + 3, intRow, intBaseCol + 6, intRow);
      end
      else if cdsBusinessGrant.FieldByName('data_type').AsString = 'Module' then
      begin
        tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 4, intRow, cdsBusinessGrant.FieldByName('module_name').AsString);
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 4, intRow, intBaseCol + 6, intRow].BorderOutlineStyle := cbsThin;
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 4, intRow, intBaseCol + 6, intRow].FillPatternForeColor := IntToXColor($00D8EBEB);//xcYellow;
        tmpXLSReadWriteAgent.MergedCells(intBaseCol + 4, intRow, intBaseCol + 6, intRow);
      end
      else if cdsBusinessGrant.FieldByName('data_type').AsString = 'Block' then
      begin
        tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 5, intRow, cdsBusinessGrant.FieldByName('block_name').AsString);
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 5, intRow, intBaseCol + 6, intRow].BorderOutlineStyle := cbsThin;
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 5, intRow, intBaseCol + 6, intRow].FillPatternForeColor := IntToXColor($00D8EBEB);//xcYellow;
        tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol + 6, intRow, cdsBusinessGrant.FieldByName('block_grant_name').AsString);
        //tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol + 6, intRow, intBaseCol + 6, intRow].BorderOutlineStyle := cbsThin;
      end;
      inc(intRow);
      cdsBusinessGrant.Next;
    end;
    tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol, intBusinessStartRow, intBaseCol + 6, intRow - 1].BorderOutlineStyle := cbsThin;
    inc(intRow);
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol, intRow, '备注：');
    inc(intRow);
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol, intRow, '1.如果单元格出现ID值，表示相应的引用基础数据被删除。如角色列出现ID值表示对应的角色数据已经不存在。');
    inc(intRow);
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol, intRow, '2.“T_Node_”开始的分区为导航树分区。');
    inc(intRow);
    tmpXLSReadWriteAgent.SetCellStringValue(intBaseCol, intRow, '3.隐藏权限的分区未导出。');
    tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[intBaseCol, intRow - 3, intBaseCol, intRow].FontColor := xcGray50;
    {inc(intRow);
    for xc := Low(TExcelColor) to High(TExcelColor) do
    begin
      tmpXLSReadWriteAgent.SetCellStringValue(0, intRow, GetEnumName(TypeInfo(TExcelColor), Ord(xc)));
      tmpXLSReadWriteAgent.XLSReadWrite.Sheet[tmpXLSReadWriteAgent.ActiveSheetIndex].Range.Items[0, intRow, 0, intRow].FillPatternForeColor := xc;
      inc(intRow);
    end;}
    tmpXLSReadWriteAgent.WriteFile;
  finally
    tmpXLSReadWriteAgent.Free;
    ShowExecuteInfo('');
  end;
end;

function TfrmExportBusinessData.GetExcelAgent: IExcelAgent;
begin
  {if FExcelAgent = nil then
    FExcelAgent := TExcelAgentHelper.GetExcelAgentInstance(self);}
  Result := XLSReadWriteAgent;
end;

function TfrmExportBusinessData.GetXLSReadWriteAgent: TXLSReadWriteAgent;
begin
  if FXLSReadWriteAgent = nil then
    FXLSReadWriteAgent := TXLSReadWriteAgent.Create(self);
  Result := FXLSReadWriteAgent;
end;

procedure TfrmExportBusinessData.ShowExecuteInfo(AInfo: string);
begin
  lblInfo.Caption := AInfo;
  lblInfo.Refresh;
  Application.ProcessMessages;
end;

procedure TfrmExportBusinessData.Button1Click(Sender: TObject);
begin
  Close;
end;

procedure TfrmExportBusinessData.sbtnSelEmpClick(Sender: TObject);
begin
  if SaveDialog1.Execute then
    edtExportFile.Text := SaveDialog1.FileName;  
end;

procedure TfrmExportBusinessData.FormDestroy(Sender: TObject);
begin
  FDataAccess := nil;
end;

end.