寻求帮助EXCEL和STRINGGRID 本帖最后由 ilovesafety 于 2011-06-15 12:05:00 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 使用ado组件连接excel,可以将excel里面的数据加载到adodataset中,之后你遍历dataset将每条数据处理后插入到stringgrid中 给你一个简单的示例:本文来自CSDN博客,出自:http://blog.csdn.net/yinsekoushao/archive/2008/11/19/3333539.aspx1.unit U_excel; 2. 3.interface 4. 5.uses 6. Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, 7. Dialogs, StdCtrls, ExcelXP, OleServer, Grids, DBGrids; 8. 9.type 10. TF_excel = class(TForm) 11. Button1: TButton; 12. OpenDialog1: TOpenDialog; 13. ExcelApplication1: TExcelApplication; 14. ExcelWorksheet1: TExcelWorksheet; 15. ExcelWorkbook1: TExcelWorkbook; 16. StringGrid1: TStringGrid; 17. Edit1: TEdit; 18. Edit2: TEdit; 19. Edit3: TEdit; 20. Edit4: TEdit; 21. Edit5: TEdit; 22. Label1: TLabel; 23. Label2: TLabel; 24. Label3: TLabel; 25. Button2: TButton; 26. Button4: TButton; 27. Label4: TLabel; 28. Label5: TLabel; 29. Label6: TLabel; 30. Label8: TLabel; 31. Edit7: TEdit; 32. procedure Button1Click(Sender: TObject); 33. procedure Button2Click(Sender: TObject); 34. procedure FormCreate(Sender: TObject); 35. procedure StringGrid1Click(Sender: TObject); 36. procedure Button4Click(Sender: TObject); 37. private 38. { Private declarations } 39. public 40. { Public declarations } 41. end; 42. 43.var 44. F_excel: TF_excel; 45. HH:integer; 46. rowCount_1:integer;//总行数 47. 48.implementation 49. 50.uses DM; 51. 52.{$R *.dfm} 53. 54.procedure TF_excel.Button1Click(Sender: TObject); 55.var i,j:integer; 56.begin 57.opendialog1.InitialDir:=ExtractFileDir(paramstr(0));//文件的打存放初始路径 58.opendialog1.Execute; 59.Try 60.ExcelApplication1.Connect;//EXCEL应用程序 61.Except 62.MessageDlg('Excel may not be installed',mtError, [mbOk], 0); 63.Abort; 64.End; 65.ExcelApplication1.Visible[0]:=false; 66.ExcelApplication1.Caption:='Excel Application'; 67.try 68.excelapplication1.Workbooks.Open(opendialog1.FileName, 69.null,null,null,null,null,null,null,null,null,null,null,null,null,null,0);//打开指定的EXCEL 文件 70.except 71.begin 72.ExcelApplication1.Disconnect;//出现异常情况时关闭 73.ExcelApplication1.Quit;showmessage('请选择EXCEL电子表格!'); 74.exit; 75.end; 76.end; 77.ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);//ExcelWorkbook1与Eexcelapplication1建立连接 78.ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1与Excelworkbook1建立连接 79.//开始从EXCEL中取数,放到stringgrid1中,取完数后关闭EXCEL 80. 81. 82.for i:=1 to 1000 do//最大取值1000 83.for j:=0 to 6 do 84.begin 85.if trim(excelworksheet1.cells.item[i+1,1])<>'' then 86.begin 87. 88.stringgrid1.rowCount:=i+1; // 89.stringgrid1.Cells[j,i]:=ExcelWorksheet1.Cells.Item[i+1,j+1]; 90.rowCount_1:=i; 91. 92.end 93.else 94.begin 95.label3.caption:=inttostr(i-1); 96.ExcelApplication1.Disconnect; 97.ExcelApplication1.Quit; 98.{//将第一条数据赋给编辑框 99.edit1.text:=stringgrid1.Cells[1,1]; 100.edit2.text:=stringgrid1.Cells[2,1]; 101.edit3.text:=stringgrid1.Cells[3,1]; 102.edit4.text:=stringgrid1.Cells[4,1]; 103.edit5.text:=stringgrid1.Cells[5,1]; } 104.exit; 105.end; 106.end; 107.end; 108. 109.{begin 110. 111. with U_DM.adoquery_excel do 112. begin 113. U_DM.adoquery_excel.Close; 114. U_DM.adoquery_excel.ConnectionString:= 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='''+ edit1.text +''';Extended Properties="Excel 8.0;HDR=NO;IMEX=1" '; 115. U_DM.adoquery_excel.sql.add('select * from [sheet1$]'); 116. end; 117. 118. end;} 119. 120. procedure TF_excel.Button2Click(Sender: TObject); 121.begin 122. // HH 当前行号 123. 124. if HH>0 then 125. begin 126. stringgrid1.Cells[0,HH]:= Edit1.Text; 127. stringgrid1.Cells[1,HH]:= Edit2.Text; 128. stringgrid1.Cells[2,HH]:= Edit3.Text; 129. stringgrid1.Cells[3,HH]:= Edit4.Text; 130. stringgrid1.Cells[4,HH]:= Edit5.Text; 131. stringgrid1.Cells[5,HH]:= Edit7.Text; 132. 133. exit; 134. end 135. else 136. begin 137. showmessage('请选择油井!'); 138. end; 139. 140. 141. 142.end; 143. 144.procedure TF_excel.FormCreate(Sender: TObject); 145.begin 146. stringgrid1.Cells[0,0]:='油井编码'; 147. stringgrid1.Cells[1,0]:='油井名称'; 148. stringgrid1.Cells[2,0]:='单位编码'; 149. stringgrid1.Cells[3,0]:='单位名称'; 150. stringgrid1.Cells[4,0]:='计量间编码'; 151. stringgrid1.Cells[5,0]:='油井类型'; 152. 153. 154.end; 155. 156.procedure TF_excel.StringGrid1Click(Sender: TObject); 157. 158.begin 159. 160.edit1.Text :=StringGrid1.Cells[0,StringGrid1.selection.bottom]; 161.edit2.Text := StringGrid1.Cells[1,StringGrid1.selection.bottom]; 162.edit3.Text := StringGrid1.Cells[2,StringGrid1.selection.bottom]; 163.edit4.Text := StringGrid1.Cells[3,StringGrid1.selection.bottom]; 164.edit5.Text := StringGrid1.Cells[4,StringGrid1.selection.bottom]; 165.edit7.Text := StringGrid1.Cells[5,StringGrid1.selection.bottom]; 166.HH:=Stringgrid1.Row; 167. 168. 169.end; 170. 171.procedure TF_excel.Button4Click(Sender: TObject); 172. 173.var 174. str:array[0..5] of string; 175. i,j:Integer; 176. str_2:string; 177. 178.begin 179.for i:=1 to rowCount_1 do 180.begin 181. 182. for j:=0 to 5 do 183. 184.begin 185. str[j]:=stringgrid1.Cells[j,i]; 186. 187.end; 188.begin 189. 190.with U_DM.adoquery_yjxx do 191. 192. begin 193. str_2:= 'insert into T_YJZD(F_YJBM,F_YJMC,F_SBBM,F_DWBM,F_DWMC,F_JLJBM,F_TYPE) values('''+str[0]+''','''+str[1]+''','''','''+str[2]+''','''+str[3]+''','''+str[4]+''','''+str[5]+''')'; 194. 195. close; 196. sql.clear; 197. sql.add(str_2); 198. execsql; 199. end; 200. end; 201.end; 202.begin 203.with U_DM.adoquery_yjxx do 204. begin 205. close; 206. sql.clear; 207. sql.add('select * from T_YJZD'); 208. open; 209.end; 210.end; 211. showmessage('导入已完成!'); 212. end; 213. 214.end.论坛坏了,没有编辑代码 首先你要会用Excel Application读取EXCEL、获得行数等基本操作然后将需要的行列对应放进stringgrid里 uses ComObj;procedure TForm1.Button2Click(Sender: TObject);var eclApp:Variant;//声明为OLEAutomation对象 xlsFileName:string; row,i:integer;begin if not opendialog1.Execute then exit else xlsFilename := Opendialog1.FileName; try eclapp :=CreateOLEObject('Excel.Application'); except application.MessageBox('Excel没有安装!','提示信息',MB_OK); Exit; end; try screen.Cursor := crSqlWait; eclapp.visible := false; eclapp.workbooks.open(xlsFilename); row:= eclApp.ActiveSheet.UsedRange.Rows.Count; stringgrid1.rowCount:=row+1; for i := 1 to row do begin stringgrid1.Cells[1,i]:=eclApp.Cells[i,1].value; stringgrid1.Cells[2,i]:=eclApp.Cells[i,2].value; end; EclApp.DisplayAlerts := False; // 不显示保存EXCEL的提示 showmessage('导入完毕'); finally eclApp.Quit; //释放VARIANT变量 eclApp:=Unassigned; screen.Cursor := crDefault; end;end; 参考小布点的代码,但是要把Excel的特殊格式去掉,否则读出来会有乱码。 求个可以用的数字小键盘的控件!delphi Delphi7 高手进!!!求算法!!! 谁有delphi的中文帮助文档? 中文状态下的分号怎么处理 彪哥,一路走好。。。。。。 排序并给每一条记录添加上序号 怎样计算组件个数? 怎样将录制的声音保存为流? 高分请教:RichEdit的问题 Updata ...set.... from 难道在Access中不能用吗? 用TreeView对数据表操作,点击相应节点,实现添加,删除等功能 想用D来和安捷伦的53132A通讯,发现在VB中的一个控件,不能安装到D中使用。有解决过此类问题的给个建议。
本文来自CSDN博客,出自:http://blog.csdn.net/yinsekoushao/archive/2008/11/19/3333539.aspx
1.unit U_excel;
2.
3.interface
4.
5.uses
6. Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
7. Dialogs, StdCtrls, ExcelXP, OleServer, Grids, DBGrids;
8.
9.type
10. TF_excel = class(TForm)
11. Button1: TButton;
12. OpenDialog1: TOpenDialog;
13. ExcelApplication1: TExcelApplication;
14. ExcelWorksheet1: TExcelWorksheet;
15. ExcelWorkbook1: TExcelWorkbook;
16. StringGrid1: TStringGrid;
17. Edit1: TEdit;
18. Edit2: TEdit;
19. Edit3: TEdit;
20. Edit4: TEdit;
21. Edit5: TEdit;
22. Label1: TLabel;
23. Label2: TLabel;
24. Label3: TLabel;
25. Button2: TButton;
26. Button4: TButton;
27. Label4: TLabel;
28. Label5: TLabel;
29. Label6: TLabel;
30. Label8: TLabel;
31. Edit7: TEdit;
32. procedure Button1Click(Sender: TObject);
33. procedure Button2Click(Sender: TObject);
34. procedure FormCreate(Sender: TObject);
35. procedure StringGrid1Click(Sender: TObject);
36. procedure Button4Click(Sender: TObject);
37. private
38. { Private declarations }
39. public
40. { Public declarations }
41. end;
42.
43.var
44. F_excel: TF_excel;
45. HH:integer;
46. rowCount_1:integer;//总行数
47.
48.implementation
49.
50.uses DM;
51.
52.{$R *.dfm}
53.
54.procedure TF_excel.Button1Click(Sender: TObject);
55.var i,j:integer;
56.begin
57.opendialog1.InitialDir:=ExtractFileDir(paramstr(0));//文件的打存放初始路径
58.opendialog1.Execute;
59.Try
60.ExcelApplication1.Connect;//EXCEL应用程序
61.Except
62.MessageDlg('Excel may not be installed',mtError, [mbOk], 0);
63.Abort;
64.End;
65.ExcelApplication1.Visible[0]:=false;
66.ExcelApplication1.Caption:='Excel Application';
67.try
68.excelapplication1.Workbooks.Open(opendialog1.FileName,
69.null,null,null,null,null,null,null,null,null,null,null,null,null,null,0);//打开指定的EXCEL 文件
70.except
71.begin
72.ExcelApplication1.Disconnect;//出现异常情况时关闭
73.ExcelApplication1.Quit;showmessage('请选择EXCEL电子表格!');
74.exit;
75.end;
76.end;
77.ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);//ExcelWorkbook1与Eexcelapplication1建立连接
78.ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1与Excelworkbook1建立连接
79.//开始从EXCEL中取数,放到stringgrid1中,取完数后关闭EXCEL
80.
81.
82.for i:=1 to 1000 do//最大取值1000
83.for j:=0 to 6 do
84.begin
85.if trim(excelworksheet1.cells.item[i+1,1])<>'' then
86.begin
87.
88.stringgrid1.rowCount:=i+1; //
89.stringgrid1.Cells[j,i]:=ExcelWorksheet1.Cells.Item[i+1,j+1];
90.rowCount_1:=i;
91.
92.end
93.else
94.begin
95.label3.caption:=inttostr(i-1);
96.ExcelApplication1.Disconnect;
97.ExcelApplication1.Quit;
98.{//将第一条数据赋给编辑框
99.edit1.text:=stringgrid1.Cells[1,1];
100.edit2.text:=stringgrid1.Cells[2,1];
101.edit3.text:=stringgrid1.Cells[3,1];
102.edit4.text:=stringgrid1.Cells[4,1];
103.edit5.text:=stringgrid1.Cells[5,1]; }
104.exit;
105.end;
106.end;
107.end;
108.
109.{begin
110.
111. with U_DM.adoquery_excel do
112. begin
113. U_DM.adoquery_excel.Close;
114. U_DM.adoquery_excel.ConnectionString:= 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='''+ edit1.text +''';Extended Properties="Excel 8.0;HDR=NO;IMEX=1" ';
115. U_DM.adoquery_excel.sql.add('select * from [sheet1$]');
116. end;
117.
118. end;}
119.
120. procedure TF_excel.Button2Click(Sender: TObject);
121.begin
122. // HH 当前行号
123.
124. if HH>0 then
125. begin
126. stringgrid1.Cells[0,HH]:= Edit1.Text;
127. stringgrid1.Cells[1,HH]:= Edit2.Text;
128. stringgrid1.Cells[2,HH]:= Edit3.Text;
129. stringgrid1.Cells[3,HH]:= Edit4.Text;
130. stringgrid1.Cells[4,HH]:= Edit5.Text;
131. stringgrid1.Cells[5,HH]:= Edit7.Text;
132.
133. exit;
134. end
135. else
136. begin
137. showmessage('请选择油井!');
138. end;
139.
140.
141.
142.end;
143.
144.procedure TF_excel.FormCreate(Sender: TObject);
145.begin
146. stringgrid1.Cells[0,0]:='油井编码';
147. stringgrid1.Cells[1,0]:='油井名称';
148. stringgrid1.Cells[2,0]:='单位编码';
149. stringgrid1.Cells[3,0]:='单位名称';
150. stringgrid1.Cells[4,0]:='计量间编码';
151. stringgrid1.Cells[5,0]:='油井类型';
152.
153.
154.end;
155.
156.procedure TF_excel.StringGrid1Click(Sender: TObject);
157.
158.begin
159.
160.edit1.Text :=StringGrid1.Cells[0,StringGrid1.selection.bottom];
161.edit2.Text := StringGrid1.Cells[1,StringGrid1.selection.bottom];
162.edit3.Text := StringGrid1.Cells[2,StringGrid1.selection.bottom];
163.edit4.Text := StringGrid1.Cells[3,StringGrid1.selection.bottom];
164.edit5.Text := StringGrid1.Cells[4,StringGrid1.selection.bottom];
165.edit7.Text := StringGrid1.Cells[5,StringGrid1.selection.bottom];
166.HH:=Stringgrid1.Row;
167.
168.
169.end;
170.
171.procedure TF_excel.Button4Click(Sender: TObject);
172.
173.var
174. str:array[0..5] of string;
175. i,j:Integer;
176. str_2:string;
177.
178.begin
179.for i:=1 to rowCount_1 do
180.begin
181.
182. for j:=0 to 5 do
183.
184.begin
185. str[j]:=stringgrid1.Cells[j,i];
186.
187.end;
188.begin
189.
190.with U_DM.adoquery_yjxx do
191.
192. begin
193. str_2:= 'insert into T_YJZD(F_YJBM,F_YJMC,F_SBBM,F_DWBM,F_DWMC,F_JLJBM,F_TYPE) values('''+str[0]+''','''+str[1]+''','''','''+str[2]+''','''+str[3]+''','''+str[4]+''','''+str[5]+''')';
194.
195. close;
196. sql.clear;
197. sql.add(str_2);
198. execsql;
199. end;
200. end;
201.end;
202.begin
203.with U_DM.adoquery_yjxx do
204. begin
205. close;
206. sql.clear;
207. sql.add('select * from T_YJZD');
208. open;
209.end;
210.end;
211. showmessage('导入已完成!');
212. end;
213.
214.end.
论坛坏了,没有编辑代码
然后将需要的行列对应放进stringgrid里
ComObj;
procedure TForm1.Button2Click(Sender: TObject);
var
eclApp:Variant;//声明为OLEAutomation对象
xlsFileName:string;
row,i:integer;
begin
if not opendialog1.Execute then
exit
else
xlsFilename := Opendialog1.FileName;
try
eclapp :=CreateOLEObject('Excel.Application');
except
application.MessageBox('Excel没有安装!','提示信息',MB_OK);
Exit;
end;
try
screen.Cursor := crSqlWait;
eclapp.visible := false;
eclapp.workbooks.open(xlsFilename);
row:= eclApp.ActiveSheet.UsedRange.Rows.Count;
stringgrid1.rowCount:=row+1;
for i := 1 to row do
begin
stringgrid1.Cells[1,i]:=eclApp.Cells[i,1].value;
stringgrid1.Cells[2,i]:=eclApp.Cells[i,2].value;
end;
EclApp.DisplayAlerts := False; // 不显示保存EXCEL的提示
showmessage('导入完毕');
finally
eclApp.Quit;
//释放VARIANT变量
eclApp:=Unassigned;
screen.Cursor := crDefault;
end;
end;