我是菜鸟!用delphi想编一个将excel中的内容导入到TDBGrid组件中!急需要一个现成的例子,急急急急急急急!
解决方案 »
- Access violation at address 0054619C in module 'MyQSObject.exe'. Read of address 00000000
- 可执行文件的图标怎么添加?
- 帮忙解答一下 :)
- 想使用系统时间作一个文件的名字,设置了datetimepicker1的format为‘yyyyMMddHHmmss',保存时不对了。
- 李维高级数据库的d7books连接问题在线等待解决后马上结帖thank!!!
- 在线急等-50分. 由主窗体中Timer时间生成的多个MDI子窗体,在程序中怎么挨个访问他们!
- 晕…!把123456.63写入表中,存的却是123456.74,字段类型是decimal(15,2),哪错了???
- 800分求一个解决方案....(WAV播放)
- 如何做計時器??
- YUV420P用SDL显示问题.
- 怎么显示出WebBrowser中的XML代码?
- delphi7 indy idhttp控件文件续传求助
在form内uses此unit,然后你就可以使用了。
调用:ExportExcelToCDS(dbgrid1,'c:\myfile.xls');
unit Unit_Excel_TO_CDS;interfaceuses
Controls,SysUtils,Classes,DB,ADODB,ShellCtrls,Windows,Messages,Forms,rzpanel,Graphics,
Variants,ExtCtrls,StdCtrls,DBClient,Dialogs, cxControls, cxSSheet;procedure ExportExcelToCDS(mygrid:TDBGrid;filename:string);//将excel导入数据集implementation//只要dbgrid的标题名与excel的标题名一致就行,顺序可以不一样
procedure ExportExcelToCDS(mygrid:TDBGrid;filename:string);//将excel导入数据集
var
i,j,row,col,ValidFNCount:integer;
MyExcel:TcxSpreadSheet;
str1,Prompt:string;
fieldnames:string;
fieldList:array of string;
ColIndex:array of Integer;//Excel列序号
tmpcds:TDataSet;
tmpds:TDataSource;
CelValue:Variant;
fieldname:string; //搜索Excel的标题是否有对应到数据表中的字段
procedure SetFieldList;
var
t,t2,js:Integer;
str1,str2:string;
begin
//搜索Excel中的有效字段
for t:=0 to col-1 do
begin
str1:=StringReplace(MyExcel.Sheet.getcellobject(t,0).Text,' ','',[rfReplaceAll]);
for t2:=0 to mygrid.Columns.Count-1 do
begin
str2:=StringReplace(mygrid.Columns[t2].Title.Caption,' ','',[rfReplaceAll]);
if Pos(str1,str2)<>0 then
begin
ValidFNCount:=ValidFNCount+1;
Break;
end;
end;
end;
SetLength(fieldList,ValidFNCount);
SetLength(ColIndex,ValidFNCount);
js:=0;
for t:=0 to col-1 do
begin
str1:=StringReplace(MyExcel.Sheet.getcellobject(t,0).Text,' ','',[rfReplaceAll]);
for t2:=0 to mygrid.Columns.Count-1 do
begin
str2:=StringReplace(mygrid.Columns[t2].Title.Caption,' ','',[rfReplaceAll]);
if Pos(str1,str2)<>0 then
begin
fieldList[js]:=mygrid.Columns[t2].FieldName;//字段
ColIndex[js]:=t;//Excel列序号1...
js:=js+1;
Break;
end;
end;
end;
end; function CheckField:string;
var
t:Integer;
str1:string;
begin
for t:=0 to col-1 do
begin
str1:=stringreplace(MyExcel.Sheet.getcellobject(t,0).Text,' ','',[rfReplaceAll]);
if Pos(str1+',',fieldnames)=0 then
begin
Break;
end;
Result:=str1;
end;
end;
//Excel列名至少有一个与grid中的字段相对应,是否不执行数据追加操作
function CheckFieldArray:Boolean;
var
t,t2:integer;
begin
t2:=0;
for t:=0 to col-1 do
begin
if Trim(fieldList[t])<>'' then
begin
t2:=1;
Break;
end;
end;
if t2=0 then
Result:=true
else
Result:=False;
end;begin
tmpcds:=mygrid.DataSource.DataSet;
tmpds:=mygrid.DataSource;
MyExcel:=TcxSpreadSheet.Create(nil);
tmpcds.DisableControls;
try
for i:=0 to mygrid.Columns.Count-1 do
begin
if mygrid.Columns[i].Visible then
fieldname:=mygrid.Columns[i].FieldName;
fieldnames:=fieldnames+stringreplace(mygrid.Columns[i].Title.Caption,' ','',[rfReplaceAll])+',';
end;
str1:=CheckField;
if str1<>'' then
begin
MessageDlg('Excel中的'+str1+'不正确',mtError,[mbOK],0);
Exit;
end;
MyExcel.LoadFromFile(filename);
row:=MyExcel.Sheet.ContentRowCount;//行数
col:=MyExcel.Sheet.ContentColCount;//列数
if row<=1 then
begin
Prompt:='Excel中至少有一条数据'+#13+'第一行是标题,其它行为数据行'+#13+'条件不符,操作取消';
MessageDlg(Prompt,mtWarning,[mbOK],0);
Exit;
end;
if col<=1 then
begin
Prompt:='Excel中至少有一列数据'+#13+'条件不符,操作取消';
MessageDlg(Prompt,mtWarning,[mbOK],0);
Exit;
end;
SetFieldList;
if CheckFieldArray then
begin
Prompt:='Excel中第一行中的列名至少有一个与列表中栏位相同'+#13+'条件不符,操作取消';
MessageDlg(Prompt,mtWarning,[mbOK],0);
Exit;
end;
mygrid.DataSource:=nil;
Screen.Cursor:=crHourGlass;
if not tmpcds.Active then
tmpcds.Open;
for i:=1 to row-1 do
begin
Application.ProcessMessages;
tmpcds.Append;
for j:=0 to ValidFNCount-1 do
begin
fieldname:=mygrid.Columns[j].FieldName;
CelValue:=MyExcel.Sheet.getcellobject(ColIndex[j],i).Text;
if Length(CelValue)<>0 then
begin
try
tmpcds.FieldByName(fieldList[j]).AsVariant:=CelValue;
except
MessageDlg(VarToStr(CelValue),mtError,[mbOK],0);
end;
end;
end;
tmpcds.Post;
end;
mygrid.DataSource:=tmpds;
// Application.MessageBox('数据导入完毕,核对无误后再点击"上传至OA"按钮!!','提示',MB_ICONINFORMATION+mb_ok);
finally
FreeAndNil(MyExcel);
tmpcds.EnableControls;
if mygrid.DataSource=nil then
mygrid.DataSource:=tmpds;
Screen.Cursor:=crDefault;
end;
end;
end.