unit1.~pas

针对银行还款账单的处理工具

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, Grids, DBGrids, StdCtrls, Buttons, GridsEh, DBGridEh,
  WinSkinData;

type
  TForm1 = class(TForm)
    Label1: TLabel;
    Edit1: TEdit;
    Button1: TButton;
    GroupBox1: TGroupBox;
    BitBtn1: TBitBtn;
    Button2: TButton;
    Label2: TLabel;
    GroupBox2: TGroupBox;
    DBGridEh1: TDBGridEh;
    DBGridEh2: TDBGridEh;
    edit2: TEdit;
    ADOTable1: TADOTable;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    ADOTable2: TADOTable;
    OpenDialog1: TOpenDialog;
    OpenDialog2: TOpenDialog;
    Button3: TButton;
    SaveDialog1: TSaveDialog;
    SkinData1: TSkinData;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}  uses DBGridEhImpExp;

procedure TForm1.Button1Click(Sender: TObject);
var i:integer;
begin
opendialog1.FileName:='';
opendialog1.Filter:='Excel files(*.xls)|*.xls' ;
adotable1.Close;
if opendialog1.Execute then
begin
edit1.Text:=opendialog1.FileName;
adotable1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+ trim(edit1.Text) + ';'
+'Extended Properties="Excel 8.0;HDR=YES;IMEX=1";Persist Security Info=False';
AdoTable1.TableDirect := True;
adotable1.TableName:='sheet1$';
try
    AdoTable1.Open;
    groupbox1.Caption := '共有'+inttostr(AdoTable1.RecordCount)+'条记录';
    except
      Application.MessageBox(PChar('打开文件 ' + edit1.Text + ' 中的表 Sheet1$ 失败！'), '系统提示');
      Exit;
    end;
   for i := 0 to adotable1.FieldCount-1 do
    begin
    dbgrideh1.Columns[i].Title.Color:=clinfobk;
    dbgrideh1.Columns[i].Width:=90;
    dbgrideh1.Columns[i].Title.Alignment:=tacenter;
    dbgrideh1.Columns[i].Alignment:=tacenter;
    //dbgrid1.Columns[i].Alignment:=tacenter;
    next;
    end;
end
else
messagebox(0,'您没有打开任何Excel文件！','提示',MB_ICONINFORMATION);
exit;


end;

procedure TForm1.Button2Click(Sender: TObject);
//var i:integer;
begin
opendialog2.FileName:='';
opendialog2.Filter:='Excel files(*.xls)|*.xls' ;
adotable2.Close;
if opendialog2.Execute then
begin
edit2.Text:=opendialog2.FileName;
adotable2.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+ trim(edit2.Text) + ';'
+'Extended Properties="Excel 8.0;HDR=YES";Persist Security Info=False';
AdoTable2.TableDirect := True;
adotable2.TableName:='sheet1$';
try
    AdoTable2.Open;
    dbgrideh2.Columns[0].FieldName:='帐号';
    dbgrideh2.Columns[1].FieldName:='委案月份';
    dbgrideh2.Columns[2].FieldName:='委托单编号';
    dbgrideh2.Columns[3].FieldName:='委外公司';
    dbgrideh2.Columns[4].FieldName:='城市';
    dbgrideh2.Columns[5].FieldName:='帐户产品';
    dbgrideh2.Columns[6].FieldName:='币种';
    dbgrideh2.Columns[7].FieldName:='逾期状态';
    dbgrideh2.Columns[8].FieldName:='费率';
    dbgrideh2.Columns[9].FieldName:='帐户状态码';
    dbgrideh2.Columns[10].FieldName:='月结日';
    dbgrideh2.Columns[11].FieldName:='还款时间';
    dbgrideh2.Columns[12].FieldName:='委案月份（此处是说委给贵司的月份，即第一或第二、三月）';
    dbgrideh2.Columns[13].FieldName:='有效还款';
    except
      Application.MessageBox(PChar('打开文件 ' + edit2.Text + ' 中的表 Sheet1$ 失败！'), '系统提示');
      Exit;
    end;
    {for i := 0 to adotable2.FieldCount-1 do
    begin
    dbgrid2.Columns[i].Title.Color:=clinfobk;
    dbgrid2.Columns[i].Width:=90;
    dbgrid2.Columns[i].Title.Alignment:=tacenter;
    dbgrid2.Columns[i].Alignment:=tacenter;
    //dbgrid1.Columns[i].Alignment:=tacenter;
    next;
    end;}
end
else
messagebox(0,'您没有打开任何Excel文件！','提示',MB_ICONINFORMATION);
exit;

end;

procedure TForm1.BitBtn1Click(Sender: TObject);
var i:integer;
begin
if not adotable2.Active then
begin
showmessage('请打开模板文件！');
exit;
end
else
if not adotable1.Active then
begin
showmessage('请打开源文件！');
exit;
end
else
if dbgrideh1.DataSource.DataSet.RecordCount<=0  then
begin
showmessage('没有您要转换的源数据！');
exit;
end
else
adotable2.Edit;
try
for i:=0 to adotable1.RecordCount-1 do
begin
adotable2.Append;
{adotable2.Fields[0].AsString:=copy(trim(adotable1.Fields[0].AsString),4,length(trim(adotable1.Fields[0].AsString)));
adotable2.Fields[1].AsString:=adotable1.Fields[1].AsString;
adotable2.Fields[2].AsString:=adotable1.Fields[2].AsString;
adotable2.Fields[3].AsString:=adotable1.Fields[3].AsString;
adotable2.Fields[4].AsString:=adotable1.Fields[4].AsString;
adotable2.Fields[5].AsString:=adotable1.Fields[5].AsString;
adotable2.Fields[6].AsString:=adotable1.Fields[6].AsString;
adotable2.Fields[7].AsString:=adotable1.Fields[7].AsString;
adotable2.Fields[8].AsString:=adotable1.Fields[8].AsString;
adotable2.Fields[9].AsString:=adotable1.Fields[9].AsString;
adotable2.Fields[10].AsString:=adotable1.Fields[10].AsString;
adotable2.Fields[11].AsString:=adotable1.Fields[11].AsString;
adotable2.Fields[12].AsString:=adotable1.Fields[12].AsString;
adotable2.Fields[13].AsString:=adotable1.Fields[13].AsString;}
adotable2.FieldByName('帐号').AsString:=copy(trim(adotable1.FieldByName('acctnum').AsString),4,length(trim(adotable1.FieldByName('acctnum').AsString)));
adotable2.FieldByName('委案月份').AsString:=adotable1.FieldByName('oa_month').AsString;
adotable2.FieldByName('委托单编号').AsString:=adotable1.FieldByName('serial').AsString;
adotable2.FieldByName('委外公司').AsString:=adotable1.FieldByName('agent_name').AsString;
adotable2.FieldByName('城市').AsString:=adotable1.FieldByName('city').AsString;
adotable2.FieldByName('帐户产品').AsString:=adotable1.FieldByName('prodnum').AsString;
adotable2.FieldByName('币种').AsString:=adotable1.FieldByName('base_curr').AsString;
adotable2.FieldByName('逾期状态').AsString:=adotable1.FieldByName('BUCKET').AsString;
adotable2.FieldByName('费率').AsString:=adotable1.FieldByName('fee_rate').AsString;
adotable2.FieldByName('帐户状态码').AsString:=adotable1.FieldByName('status_code').AsString;
adotable2.FieldByName('月结日').AsString:=adotable1.FieldByName('cyc_day').AsString;
adotable2.FieldByName('还款时间').AsString:=adotable1.FieldByName('UPDATE_DATE').AsString;
adotable2.FieldByName('委案月份（此处是说委给贵司的月份，即第一或第二、三月）').AsString:=adotable1.FieldByName('agent_month').AsString;
adotable2.FieldByName('有效还款').AsString:=adotable1.FieldByName('eff_pymt').AsString;

adotable1.Next;
end;
showmessage('转换数据成功！请仔细再检查一下!');
groupbox2.Caption := '共有'+inttostr(AdoTable1.RecordCount)+'条记录被转换';
except
showmessage('转换失败！');
exit;
end;
end;

procedure TForm1.Button3Click(Sender: TObject);
 var save_xls_name:string;
    //i:integer;
begin
if not adotable2.Active then
begin
messagebox(0,'对不起，请先查询再进行导出！','提示',mb_ok);
exit;
end
else
if dbgrideh2.DataSource.DataSet.RecordCount-1=0 then
begin
messagebox(0,'对不起，系统中没有您要导出的数据！','提示',mb_ok);
exit;
end
else
savedialog1.FileName:='还款数据导出'+ datetostr(date);
savedialog1.Filter:='Excel files (*.xls)|*.xls';
if savedialog1.Execute then
begin
save_xls_name:=savedialog1.FileName+'.xls';
SaveDBGridEhToExportFile( TDBGridEhExportAsXLS, DBGridEh2, save_xls_name, true);
//rzgroupbox2.Caption:='一共有 '+inttostr(dbgrideh1.DataSource.DataSet.RecordCount)+' 条记录';
end
else
messagebox(0,'您没有进行导出操作！','提示',mb_ok);
exit;
end;

end.