Delphi+上传文件+ADO+导出Excel

View Code

  1 ///////////////////////////////////////////////
  2 //
  3 //Éè¼Æ±àÂë£ºÚùÎ·Î·
  4 //¿ª·¢Ê±¼ä£º2012-8
  5 //¹¦ÄÜÃèÊö£ºµ¼ÈëÉú²úÊý¾Ý£¨txtÎÄ¼þÓÉÉú²ú³µ¼äµ¼³ö¸øÎÒÃÇ£©£¬Í³¼Æ¡¢Éú²úExcelÎÄ¼þ
  6 //
  7 ///////////////////////////////////////////////
  8 unit Unit1;
  9 
 10 interface
 11 
 12 uses
 13   Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,StrUtils,
 14   Dialogs, Menus, Grids, DBGrids, StdCtrls,UnitCon,ADODB, DB, ExtCtrls,GIFImage,ComObj;
 15 
 16 type
 17   TForm1 = class(TForm)
 18     DBGrid1: TDBGrid;
 19     MainMenu: TMainMenu;
 20     rewrw1: TMenuItem;
 21     N1: TMenuItem;
 22     N2: TMenuItem;
 23     O1: TMenuItem;
 24     N3: TMenuItem;
 25     DlgOpenTxt: TOpenDialog;
 26     N4: TMenuItem;
 27     guia1: TMenuItem;
 28     LabShow: TLabel;
 29     DataSource1: TDataSource;
 30     N5: TMenuItem;
 31     LabInfo: TLabel;
 32     N6: TMenuItem;
 33     Panel1: TPanel;
 34     Button1: TButton;
 35     Edit1: TEdit;
 36     Edit2: TEdit;
 37     Edit3: TEdit;
 38     Edit4: TEdit;
 39     Label1: TLabel;
 40     Label2: TLabel;
 41     Label3: TLabel;
 42     Label4: TLabel;
 43     Label5: TLabel;
 44     BtnExport: TButton;
 45     PanelLine: TPanel;
 46     procedure N2Click(Sender: TObject);
 47     procedure N1Click(Sender: TObject);
 48     procedure guia1Click(Sender: TObject);
 49     procedure FormCreate(Sender: TObject);
 50     procedure N3Click(Sender: TObject);
 51     procedure N5Click(Sender: TObject);
 52     procedure TJ();
 53     procedure createData(const sql :string);
 54     procedure N6Click(Sender: TObject);
 55     procedure Button1Click(Sender: TObject);
 56     procedure BtnExportClick(Sender: TObject);  //²úÉúÐÂµÄÊý¾Ýµ½±íÀï¡¾´Ë¹¦ÄÜÊÇÈ¥³ýguidÖØ¸´µÄÖµ¡¿
 57   private
 58     { Private declarations }
 59   public
 60     { Public declarations }
 61   end;
 62 
 63 var
 64   Form1: TForm1;
 65   PubCon : TADOConnection;
 66   PubRs  : TADODataSet;
 67   constr : string;
 68   img : TImage;             //µ¼ÈëÎÄ±¾ÎÄ¼þÊÇµÄ¶¯»ÓÃµ½
 69   alreadyCount:Boolean;     //alreadyCount=true±íÊ¾ÓÃ»§µ¥»÷ÁË¡°¿ªÊ¼Í³¼Æ¡±²Ëµ¥
 70   GridRows:Integer;         //µã»÷¡°µ¼³öÊý¾Ý¡±°´Å¥µÄ¼ÇÂ¼¸öÊý
 71   ReportWitch:Integer;      // ReportWitch=1±íÊ¾µ¼³ö¡°¿ªÊ¼Í³¼Æ¡±²Ëµ¥µÄÊý¾Ý£¬ReportWitch=2±íÊ¾µ¼³ö¡°²éÑ¯Í³¼Æ¡±²Ëµ¥µÄÊý¾Ý
 72 implementation
 73 
 74 {$R *.dfm}
 75 
 76 procedure TForm1.N2Click(Sender: TObject);
 77 begin
 78   Application.Terminate();
 79 end;
 80 
 81 procedure TForm1.N1Click(Sender: TObject);
 82 var
 83   myop :TDB;
 84   b:Boolean;
 85 begin
 86   DlgOpenTxt.Filter :='ÎÄ±¾ÎÄ¼þ|*.txt';
 87   b:=DlgOpenTxt.Execute();
 88   if b then
 89   begin
 90     LabInfo.Visible:=True;
 91     img.Visible:=True;
 92     myop:=TDB.Create();
 93     myop.ReadToDB(DlgOpenTxt.FileName,PubCon);
 94     LabInfo.Visible:=false;
 95     img.Visible:=False;
 96     ShowMessage('µ¼Èë³É¹¦£¡');
 97   end;
 98 end;
 99 
100 procedure TForm1.guia1Click(Sender: TObject);
101 var msg:pchar;
102 begin
103   msg:= #10+'ÒÁÀû¼¯ÍÅÄÌ·ÛÊÂÒµ²¿                   ' +#10+#10+ '¿ª·¢£ºÚùÎ·Î· …e…d'+#10;
104   MessageBox(Handle,msg,'¹ØÓÚ',0);
105 end;
106 
107 procedure TForm1.FormCreate(Sender: TObject);
108 var
109   myop:TDB;
110   gif : TGIFImage;
111 begin
112   Application.Title:='Éú²úÊý¾ÝÍ³¼Æ';
113   self.DBGrid1.Hide;
114   PubCon:=TADOConnection.Create(nil);
115   PubRs:=TADODataSet.Create(nil);
116   constr:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+ExtractFilePath(Application.ExeName)+'\productDB.mdb;Persist Security Info=False';
117   myop:=TDB.Create();
118   myop.CreateCon(constr,PubCon);
119   PubRs:=TADODataSet.Create(nil);
120 
121   //****************************************
122   Img := TImage.Create(Self);
123   Img.Parent := Self;
124   Img.SetBounds(80, 5, 140, 200);
125 
126   gif := TGIFImage.Create;
127   gif.LoadFromFile(ExtractFilePath(Application.ExeName)+'\001.gif');
128   Img.Picture.Assign(Gif);
129   TGIFImage(Img.Picture.Graphic).Animate := True;
130   Img.Visible:=false;
131   LabInfo.Visible:=False;
132 
133   Panel1.Visible:=False;
134 
135   alreadyCount:=False;
136 end;
137 
138 procedure TForm1.N3Click(Sender: TObject);
139 var
140   myop:TDB;
141   sql:string;
142   sqlcn:string;
143 begin
144   Panel1.Visible:=False;
145   sql :='select cn as ÊýÁ¿,T.guid as ·À´Ü»õÂë,tray as ÍÐÅÌÂë from ';
146   sql := sql + '(SELECT count(*) as cn,guid FROM ProductData group by guid)T inner join ';
147   sql := sql + '(select distinct guid,tray from ProductData)T2 ON T.guid=T2.guid';
148   Self.DBGrid1.Show;
149   createData(sql);                    //Í¨¹ýÉÏÃæµÄsql±äÁ¿Éú²ú´´½¨myTable±í
150   myop:=TDB.Create();
151 
152   sqlcn:='SELECT Mid(code,1,2) AS ²úÆ·±àÂë, Mid(code,3,2) AS Éú²úÏß, Mid(code,5,6) AS ÈÕÆÚ, Mid(code,11,1) AS °à×é,cn as ²úÆ·Êý ';
153   sqlcn:=sqlcn + 'FROM (SELECT mid(·À´Ü»õÂë,1,11) as code, count(*) AS cn FROM myTable GROUP BY mid(·À´Ü»õÂë,1,11))';
154 
155   myop.RunSelSQL(sqlcn,PubCon,PubRs);
156   PubRs.Active:=True;
157   DataSource1.DataSet:=PubRs;
158   DBGrid1.DataSource :=DataSource1;
159   DBGrid1.Columns[0].Width:=100;
160   DBGrid1.Columns[1].Width:=100;
161   DBGrid1.Columns[2].Width:=100;
162   DBGrid1.Columns[3].Width:=100;
163   DBGrid1.Columns[4].Width:=100;
164 
165   alreadyCount:=True;
166   GridRows:=PubRs.RecordCount;
167   ReportWitch:=1;
168 end;
169 
170 procedure TForm1.N5Click(Sender: TObject);
171 var
172   n:integer;
173   myop:TDB;
174 begin
175   n:=MessageBox(Self.Handle,'ÊÇ·ñÇå³ýµ¼ÈëµÄÊý¾Ý£¿','ÌáÊ¾£º',4);
176   //ShowMessage(IntToStr(n));
177   if n=6 then
178   begin
179     myop:=TDB.Create();
180     myop.RunSQL('delete from ProductData',PubCon);
181     ShowMessage('Êý¾ÝÒÑ¾È«²¿Çå³ý£¡');
182   end;
183 end;
184 
185 procedure TForm1.TJ();
186 var
187   sql:string;
188   myop:TDB;
189   Rs  : TADODataSet;
190 begin
191   sql :='select count(*) from (select cn as ÊýÁ¿,T.guid as ·À´Ü»õÂë,tray as ÍÐÅÌÂë,printCode as ´òÓ¡ÅúºÅ,realCode as Êµ¼ÊÅúºÅ,outDate as Êý¾Ýµ¼³öÈÕÆÚ from ';
192   sql := sql + '(SELECT count(*) as cn,guid FROM ProductData group by guid)T inner join ';
193   sql := sql + '(select distinct guid,tray,PrintCode,RealCode,outDate from ProductData)T2 ON T.guid=T2.guid)tnt';
194 
195   myop:=TDB.Create();
196   Rs:=TADODataSet.Create(nil);
197   myop.RunSelSQL(sql,PubCon,Rs);
198   Rs.Free;
199 end;
200 
201 procedure TForm1.createData(const sql :string);
202 var
203   //Rs  : TADODataSet;
204   myop: TDB;
205   newsql:string;
206 begin
207   //Rs := TADODataSet.Create(nil);
208   myop:=TDB.Create;
209   newsql:='select * into myTable from(' + sql + ')';
210   myop.RunSQL('drop table myTable',PubCon);
211   myop.RunSQL(newsql,PubCon);
212 end;
213 procedure TForm1.N6Click(Sender: TObject);
214 begin
215   //·¶µÂÈø·¢·Å
216   if alreadyCount then
217   begin
218   Panel1.Visible:=true;
219   DBGrid1.Visible:=True;
220   ReportWitch:=2;
221   end
222   else
223     ShowMessage('ÇëÏÈµã»÷¡°¿ªÊ¼Í³¼Æ¡±²Ëµ¥£¡');
224 
225 end;
226 
227 procedure TForm1.Button1Click(Sender: TObject);
228 var
229   txt1,txt2,txt3,txt4:string;
230   sql:string;
231   where,where2 :string;
232   n:Integer;
233   myop:TDB;
234 begin
235   n:=5;
236   txt1:=Trim(Edit1.Text);
237   txt2:=Trim(Edit2.Text);
238   txt3:=Trim(Edit3.Text);
239   txt4:=Trim(Edit4.Text);
240   if (txt1='') and (txt2='') and (txt3='') and (txt4='') then
241   begin
242     ShowMessage('ÇëÊäÈëÒª²éÑ¯µÄÌõ¼þ£¡');
243     exit;
244   end;
245 
246   sql:='select * from myTable ';
247   where:='where';
248   if (txt1<>'') then
249   begin
250     where:=where + ' mid(·À´Ü»õÂë,1,2)=''' + txt1 + ''' and ';
251     n:=n+4;
252   end;
253   if (txt2<>'') then
254   begin
255     where:=where + ' mid(·À´Ü»õÂë,3,2)=''' + txt2 + ''' and ';
256     n:=n+4;
257   end;
258   if (txt3<>'') then
259   begin
260     where:=where + ' mid(·À´Ü»õÂë,5,6)=''' + txt3 + ''' and ';
261     n:=n+4;
262   end;
263   if (txt4<>'') then
264   begin
265     where:=where + ' mid(·À´Ü»õÂë,11,1)=''' + txt4 + ''' and ';
266     n:=n+4;
267   end;
268   where2 := LeftStr(where,Length(where)-n);
269 
270   sql:=sql + where2;
271   //ShowMessage(sql);
272   myop:=TDB.Create;
273   myop.RunSelSQL(sql,PubCon,PubRs);
274   PubRs.Active:=True;
275   DataSource1.DataSet:=PubRs;
276   DBGrid1.DataSource :=DataSource1;
277   myop.Free;
278   GridRows:=PubRs.RecordCount;
279 end;
280 
281 procedure TForm1.BtnExportClick(Sender: TObject);
282 var
283   excel,WorkBook:OleVariant;
284   rows,cols,i,j:Integer;
285 begin
286   excel:=CreateOLEObject('Excel.Application');
287   WorkBook:=excel.workbooks.add;
288   //excel.sheets[0].range['A1:C1'].merge(true);
289   //excel.Sheets[1].Cells[1,1].Value :='²¿ÃÅ±àÂë±í' ;
290 
291   cols:=DBGrid1.Columns.Count;
292   rows :=GridRows;
293   if ReportWitch=1 then
294   begin
295   excel.Sheets[1].Cells[1,1].Value :='²úÆ·±àÂë';
296   excel.Sheets[1].Cells[1,2].Value :='Éú²úÏß';
297   excel.Sheets[1].Cells[1,3].Value :='ÈÕÆÚ';
298   excel.Sheets[1].Cells[1,4].Value :='°à×é';
299   excel.Sheets[1].Cells[1,5].Value :='²úÆ·Êý';
300   end
301   else if ReportWitch=2 then
302   begin
303   excel.Sheets[1].Cells[1,1].Value :='ÊýÁ¿';
304   excel.Sheets[1].Cells[1,2].Value :='·À´Ü»õÂë';
305   excel.Sheets[1].Cells[1,3].Value :='ÍÐÅÌÂë';
306   end;
307 
308 
309   try
310   for i:=1 to rows do
311   begin
312     for j:=0 to cols-1 do
313     begin
314       DBGrid1.DataSource.DataSet.RecNo := i;
315       excel.Sheets[1].Cells[i+1,j+1].Value := '''' + vartostr(DBGrid1.DataSource.DataSet.Fields[j].Value);
316     end;
317   end;
318   except
319   end;
320 
321   DlgOpenTxt.Filter:='ExcelÎÄ¼þ2007|*.xlsx|ExcelÎÄ¼þ2003|*.xls';
322   if DlgOpenTxt.Execute then
323   begin
324     excel.ActiveWorkbook.SaveAs(DlgOpenTxt.FileName);
325   end;
326 
327   excel.quit;
328 end;
329 
330 end.

View Code

 1 ///////////////////////////////////////////////
 2 //
 3 //Éè¼Æ±àÂë£ºÚùÎ·Î·
 4 //¿ª·¢Ê±¼ä£º2012-8
 5 //¹¦ÄÜÃèÊö£ºµ¼ÈëÉú²úÊý¾Ý£¨txtÎÄ¼þÓÉÉú²ú³µ¼äµ¼³ö¸øÎÒÃÇ£©£¬Í³¼Æ¡¢Éú²úExcelÎÄ¼þ
 6 //
 7 ///////////////////////////////////////////////
 8 
 9 unit UnitCon;
10 
11 interface
12 uses
13   SysUtils,ADODB,Grids,DateUtils,DBGrids,StrUtils,Classes,Dialogs,Windows, Messages,Forms;
14 type
15   TDB=class
16   private
17   public
18     procedure CreateCon(const cons:string;var con:TADOConnection);
19     procedure RunSelSQL(const sql:string;var con:TADOConnection;var Rec:TADODataSet);
20     procedure RunSQL(const sql:string;var con:TADOConnection);
21     procedure ReadToDB(const path:string;var con:TADOConnection);
22     procedure Bind(var myGrid:TDBGrid;var rec:TADODataSet) ;
23   end;
24 implementation
25 procedure TDB.ReadToDB(const path:string;var con:TADOConnection);
26 var
27   F:TextFile;
28   str:string;
29   Ts:TStringList;
30   sql:string;
31 begin
32   AssignFile(F,path);
33   Reset(F);
34   while not Eof(F) do
35   begin
36     try
37     Readln(F,str);
38     Ts:=TStringList.Create();
39     Ts.Delimiter:=',';
40     str:=StringReplace(str,' ','', [rfReplaceAll]);
41     Ts.DelimitedText := str;
42     
43     sql:='insert into ProductData ';
44     sql:=sql + 'values(''%s'',''%s'',''%s'',''%s'',''%s'',''%s'')';
45 
46     sql:=Format(sql,[Ts[0],Ts[1],Ts[2],Ts[3],Ts[4],Ts[5]]);
47     RunSQL(sql,con);
48     Ts.Free;
49     Application.ProcessMessages;
50     except
51     
52     end;
53   end;
54   CloseFile(F);
55 end;
56 
57 procedure TDB.CreateCon(const cons:string;var con:TADOConnection);
58 begin
59   con:=TADOConnection.Create(nil);
60   con.ConnectionString:=cons;
61   con.LoginPrompt:=false;
62   con.Open();
63 end;
64 
65 procedure TDB.RunSelSQL(const sql:string;var con:TADOConnection;var Rec:TADODataSet);
66 begin
67   Rec.Recordset:=con.Execute(sql,cmdText);
68 end;
69 
70 procedure TDB.RunSQL(const sql:string;var con:TADOConnection);
71 begin
72   con.Execute(sql,cmdtext);
73 end;
74 
75 procedure TDB.Bind(var myGrid:TDBGrid;var rec:TADODataSet);
76 begin
77   
78   myGrid.DataSource :=rec.DataSource;
79 
80 end;
81 end.

posted @ 2012-08-29 13:57 翱翔软件阅读(965) 评论(0) 收藏举报

刷新页面返回顶部

翱翔软件

Delphi+上传文件+ADO+导出Excel

公告