📄 unit1.pas

📁 本系统在一些大中型企业(跨多达24个区域)一直都在很好的服务过
💻 PAS
📖 第 1 页 / 共 3 页
字号:
12 3 下一页
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, FileCtrl, ComCtrls, Buttons, ExtCtrls, Db, DBTables, Grids,
  DBGrids, GB2BIG, BIG2GB;

type
  TForm1 = class(TForm)
    Label1: TLabel;
    DirectoryListBox1: TDirectoryListBox;
    BitBtn1: TBitBtn;
    Panel1: TPanel;
    Panel2: TPanel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Query1: TQuery;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Memo1: TMemo;
    Memo2: TMemo;
    Memo3: TMemo;
    ClientDataSet1: TTable;
    BatchMove1: TBatchMove;
    GB2BIG: TGB2BIG;
    BIG2GB1: TBIG2GB;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
     MsExcel:Variant;
     MsExcelWorkBook:Variant;
     MsExcelWorkSheet:Variant;
  public
        procedure   CHJ;
    procedure   CHJO;
    procedure   CHJI;
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation
uses comobj;

{$R *.DFM}

   


procedure TForm1.BitBtn1Click(Sender: TObject);
var
sky14:integer;
sky21:integer;
sky25:integer;
sky29:integer;
sky34:integer;
sky38:integer;
X01:integer;
CHANG:integer;
KONKA:integer;
TCl:integer;
HAIER:integer;

i:integer;
j:integer;
c:string;

begin

Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT dept ,sum(sky14) as sky14  ,sum(sky21) as sky21 ,sum(sky25) as sky25 ,sum(sky29) as sky29 ,sum(sky34) as sky34 ,sum(sky38) as sky38 ,101,sum(Chang) as Chang ,sum(Kakon) as Kakon ,sum(TCL) as TCL ,sum(Haier) as Haier  from '+'"'+DirectoryListBox1.Directory+'\db01\main.dbf"  where dateo>'+trim(ComboBox1.text)+' and dateo<'+trim(ComboBox2.text)+'  group by dept ' ) ;
query1.Open;
ClientDataSet1.Close;
deletefile(form1.DirectoryListBox1.Directory+'\temp.dbf');
ClientDataSet1.TableName:=form1.DirectoryListBox1.Directory+'\temp.dbf';
chdir(form1.DirectoryListBox1.Directory);
CHJ;
CHJO;
CHJI;
{=================}
Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT * from '+'"'+DirectoryListBox1.Directory+'\temp.dbf" ') ;
query1.Open;
query1.First;
while not  query1.EOF do
begin
query1.Edit;
query1.FieldByName('X01').value:=query1.FieldByName('sky14').value+query1.FieldByName('sky21').value+query1.FieldByName('sky25').value+query1.FieldByName('sky29').value+query1.FieldByName('sky34').value+query1.FieldByName('sky38').value;
query1.Post;
query1.Next;
end;
ClientDataSet1.Close;
deletefile(form1.DirectoryListBox1.Directory+'\temp1.dbf');
ClientDataSet1.TableName:=form1.DirectoryListBox1.Directory+'\temp1.dbf';
chdir(form1.DirectoryListBox1.Directory);
CHJ;
CHJO;
CHJI;
{=================}


Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT 10,dept,sky14,sky21,sky25,sky29,sky34,sky38,x01,Chang,kakon,TCL,Haier from '+'"'+DirectoryListBox1.Directory+'\temp1.dbf"  order by x01 desc') ;
query1.Open;
query1.First;
ClientDataSet1.Close;
deletefile(form1.DirectoryListBox1.Directory+'\temp2.dbf');
ClientDataSet1.TableName:=form1.DirectoryListBox1.Directory+'\temp2.dbf';
chdir(form1.DirectoryListBox1.Directory);
CHJ;
CHJO;
CHJI;

Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT * from '+'"'+DirectoryListBox1.Directory+'\temp2.dbf" ') ;
query1.Open;
i:=0;
while not  query1.EOF do
begin
i:=i+1;
query1.Edit;
query1.FieldByName('X0').value:=i;
query1.Post;
query1.Next;
end;
ClientDataSet1.Close;
deletefile(form1.DirectoryListBox1.Directory+'\temp3.dbf');
ClientDataSet1.TableName:=form1.DirectoryListBox1.Directory+'\temp3.dbf';
chdir(form1.DirectoryListBox1.Directory);
CHJ;
CHJO;
CHJI;
{=================}
Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT * from '+'"'+DirectoryListBox1.Directory+'\temp3.dbf" ') ;
query1.Open;
sky14:=0;
sky21:=0;
sky25:=0;
sky29:=0;
sky34:=0;
sky38:=0;
CHANG:=0;
TCL:=0;
HAIER:=0;
KONKA:=0;
X01:=0;

while not  query1.EOF do
begin
sky14:=sky14+query1.FieldByName('SKY14').value;
sky21:=sky21+query1.FieldByName('sky21').value;
sky25:=sky25+query1.FieldByName('sky25').value;
sky29:=sky29+query1.FieldByName('sky29').value;
sky34:=sky34+query1.FieldByName('SKY34').value;
sky38:=sky38+query1.FieldByName('SKY38').value;
CHANG:=CHANG+query1.FieldByName('CHANG').value;
KONKA:=KONKA+query1.FieldByName('KAKON').value;
TCL:=TCL+query1.FieldByName('TCL').value;
HAIER:=HAIER+query1.FieldByName('HAIER').value;
X01:=X01+query1.FieldByName('X01').value;


query1.Next;
end;


 if not query1.IsEmpty then
begin
 try
        MsExcel:=CreateOleObject('Excel.Application.8');
        MsExcelWorkBook:=MsExcel.WorkBooks.Add;
        MsExcelWorkSheet:=MsExcel.WorkSheets.Add;
    except
        MessageDlg('启动Excel 97出现异常！！请检查你的机器内是否安装OFFICE97？',mtWarning,[mbOK],0);
        exit;
   end;

   MsExcel.Visible:=True;
  {+========================================+}
     MsExcelWorkSheet.Range['F1'].Value:='创维公司各分部专柜零售量(台数)统计表' ;
     MsExcelWorkSheet.Range['a3'].Value:='统计日期: '+datetostr(date) ;
      MsExcelWorkSheet.Range['l3'].Value:='创维销售总部市场部';
     MsExcelWorkSheet.Range['A4'].Value:='名次' ;
     MsExcelWorkSheet.Range['b4'].Value:='分部' ;
     MsExcelWorkSheet.Range['c4'].Value:='创维14寸' ;
     MsExcelWorkSheet.Range['d4'].Value:='创维21寸' ;
     MsExcelWorkSheet.Range['e4'].Value:='创维25寸' ;
     MsExcelWorkSheet.Range['f4'].Value:='创维29寸' ;
     MsExcelWorkSheet.Range['g4'].Value:='创维34寸' ;
     MsExcelWorkSheet.Range['h4'].Value:='创维38寸' ;
     MsExcelWorkSheet.Range['i4'].Value:='创维合计' ;
     MsExcelWorkSheet.Range['j4'].Value:='长虹合计' ;
     MsExcelWorkSheet.Range['k4'].Value:='康佳合计' ;
     MsExcelWorkSheet.Range['l4'].Value:='TCL合计' ;
     MsExcelWorkSheet.Range['m4'].Value:='海尔合计' ;
      QUERY1.First;
      J:=0;
        WHILE NOT QUERY1.EOF DO
           begin
           J:=j+1;

           for i:=0 to query1.FieldCount-1 do
           begin

           if i=0 then C:='A';
           if i=1 then C:='B';
           if i=2 then C:='C';
           if i=3 then C:='D';
           if i=4 then C:='E';
           if i=5 then C:='F';
           if i=6 then C:='G';
           if i=7 then C:='H';
           if i=8 then C:='I';
           if i=9 then C:='J';
           if i=10 then C:='K';
           if i=11 then C:='L';
           if i=12 then C:='M';
           if i=13 then C:='N';
           if i=14 then C:='O';
           if i=15 then C:='P';
           if i=16 then C:='K';
           if i=17 then C:='R';
           if i=18 then C:='S';
           if i=19 then C:='T';
           if i=20 then C:='U';
           if i=21 then C:='V';
           if i=22 then C:='W';
           if i=23 then C:='X';
           if i=24 then C:='Y';
           if i=25 then C:='Z';
           if i=26 then C:='AA';
           if i=27 then C:='AB';
           if i=28 then C:='AC';
           if i=29 then C:='AD';
           if i=30 then C:='AE';
           if i=31 then C:='AF';
           if i=32 then C:='AG';
           if i=33 then C:='AH';
           if i=34 then C:='AI';
           if i=35 then C:='AJ';
           if i=36 then C:='AK';
           if i=37 then C:='AL';
           if i=38 then C:='AM';
           if i=39 then C:='AN';
           if i=40 then C:='AO';
           if i=41 then C:='AP';
           if i=42 then C:='AK';
           if i=43 then C:='AR';
           if i=44 then C:='AS';
           if i=45 then C:='AT';
           if i=46 then C:='AU';
           if i=47 then C:='AV';
           if i=48 then C:='AW';
           if i=49 then C:='AX';
           if i=50 then C:='AY';
           if i=51 then C:='AZ';

MsExcelWorkSheet.Range[C+IntToStr(J+4)].Value:=QUERY1.Fields[i].AsString ;
           end;


       QUERY1.Next;
    end;
                     j:=j+4;
MsExcelWorkSheet.Range['A'+IntToStr(J+1)].Value:='总计:';
MsExcelWorkSheet.Range['b'+IntToStr(J+1)].Value:='';
MsExcelWorkSheet.Range['c'+IntToStr(J+1)].Value:=inttostr(sky14);
MsExcelWorkSheet.Range['d'+IntToStr(J+1)].Value:=inttostr(sky21);
MsExcelWorkSheet.Range['e'+IntToStr(J+1)].Value:=inttostr(sky25);
MsExcelWorkSheet.Range['f'+IntToStr(J+1)].Value:=inttostr(sky29);
MsExcelWorkSheet.Range['g'+IntToStr(J+1)].Value:=inttostr(sky34);
MsExcelWorkSheet.Range['h'+IntToStr(J+1)].Value:=inttostr(sky38);
MsExcelWorkSheet.Range['i'+IntToStr(J+1)].Value:=inttostr(X01);
MsExcelWorkSheet.Range['j'+IntToStr(J+1)].Value:=inttostr(CHANG);
MsExcelWorkSheet.Range['k'+IntToStr(J+1)].Value:=inttostr(KONKA);
MsExcelWorkSheet.Range['l'+IntToStr(J+1)].Value:=inttostr(TCL);
MsExcelWorkSheet.Range['m'+IntToStr(J+1)].Value:=inttostr(HAIER);



   {+================================================+}

   try
       deletefile(DirectoryListBox1.Directory+'\分部专柜统计('+datetostr(date)+').XLS');
       MsExcelWorkSheet.SaveAs(DirectoryListBox1.Directory+'\分部专柜统计('+datetostr(date)+').XLS');
   except
       MessageDlg('无法保存 '+DirectoryListBox1.Directory+'\分部专柜统计('+datetostr(date)+').XLS',mtInformation,[mbOK],0);
   end;
 end;



Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT dept ,sum(sky14) as sky14  ,sum(sky21) as sky21 ,sum(sky25) as sky25 ,sum(sky29) as sky29 ,sum(sky34) as sky34 ,sum(sky38) as sky38 ,101,sum(Chang) as Chang ,sum(Kakon) as Kakon ,sum(TCL) as TCL ,sum(Haier) as Haier  from '+'"'+DirectoryListBox1.Directory+'\db01\main.dbf"  where dateo>'+trim(ComboBox1.text)+' and dateo<'+trim(ComboBox2.text)+'  group by dept ' ) ;
query1.Open;
ClientDataSet1.Close;
deletefile(form1.DirectoryListBox1.Directory+'\temp.dbf');
ClientDataSet1.TableName:=form1.DirectoryListBox1.Directory+'\temp.dbf';
chdir(form1.DirectoryListBox1.Directory);
CHJ;
CHJO;
CHJI;
{=================}
Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT * from '+'"'+DirectoryListBox1.Directory+'\temp.dbf" ') ;
query1.Open;
query1.First;
while not  query1.EOF do
begin
query1.Edit;
query1.FieldByName('X01').value:=query1.FieldByName('sky14').value+query1.FieldByName('sky21').value+query1.FieldByName('sky25').value+query1.FieldByName('sky29').value+query1.FieldByName('sky34').value+query1.FieldByName('sky38').value;
query1.Post;
query1.Next;
end;
ClientDataSet1.Close;
deletefile(form1.DirectoryListBox1.Directory+'\temp1.dbf');
ClientDataSet1.TableName:=form1.DirectoryListBox1.Directory+'\temp1.dbf';
chdir(form1.DirectoryListBox1.Directory);
CHJ;
CHJO;
CHJI;
{=================}


Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT 10,dept,sky14,sky21,sky25,sky29,sky34,sky38,x01,Chang,kakon,TCL,Haier from '+'"'+DirectoryListBox1.Directory+'\temp1.dbf"  order by x01 desc') ;
query1.Open;
query1.First;
ClientDataSet1.Close;
deletefile(form1.DirectoryListBox1.Directory+'\temp2.dbf');
ClientDataSet1.TableName:=form1.DirectoryListBox1.Directory+'\temp2.dbf';
chdir(form1.DirectoryListBox1.Directory);
CHJ;
CHJO;
CHJI;

Query1.close;
Query1.SQL.Clear;
Query1.SQL.add('SELECT * from '+'"'+DirectoryListBox1.Directory+'\temp2.dbf" ') ;
query1.Open;
i:=0;
while not  query1.EOF do
begin
i:=i+1;
query1.Edit;
query1.FieldByName('X0').value:=i;
query1.Post;
query1.Next;
end;
ClientDataSet1.Close;
deletefile(form1.DirectoryListBox1.Directory+'\temp3.dbf');
ClientDataSet1.TableName:=form1.DirectoryListBox1.Directory+'\temp3.dbf';
chdir(form1.DirectoryListBox1.Directory);
12 3 下一页
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -