如何将Excel的数据导入到数据库中。
解决方案 »
- ShellExecute打开 ie,如何在原先IE中更新内容
- [ADO]网络中断过后,如何重新连接数据库?
- 请问如何才能把TreeView前面的+,-换成自己定义的图标?
- 用clientDataset通过soapconnection连接远程数据库,如何post到本地!
- WM_PASTE之前检查PASTE的字符串是否合法
- 能否把已知的变量就exe文件来保存?
- 一个query的问题
- 谁有dcomcnfg.exe for win98 的程序给我发一份(dcm98cfg.com),急用,谢了,邮箱:[email protected]
- 如何将图片保存到数据库中?
- 怎样才能用Image控件把Paradox表中的Blob字段的图象显示出来?
- Rave问题:当连接改变时打印的为什么还是原来的数据
- 关于工具栏的问题
如果是sql server直接用
insert into a
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...
或者用btp dts
先用ODBC建立一个数据源,数据源指向sql server的表,然后用TTABLE控件联上数据库,
再建立一个OleObject,然后用Append,Post把数据导入到EXCEL表。
实例:
var
MSExcel:Variant;//定义一个全程变量 MSExcel := CreateOleObject('Excel.Application');
MSExcel.WorkBooks.Open(Edit1.Text);//创建一个OleObject
Table.Active:=true;
Table.Append;
Table.FieldByName('字段名').Value:=MSExcel.Cells[rows_i,1].Value;
//上面一句可以根据具体情况多次引用。
Table.Post;//实现导入
label PH;
var
msexcel : olevariant;
wbook, wsheet : olevariant;
i,j : integer;
temp : string;
...
begin
MsExcel := CreateOleObject('Excel.Application');
WBook := MsExcel.Application;
WBook.Visible := False;
wbook.workbooks.Open('c:\xxxx.xls');//打开Excel文档
WSheet := WBook.ActiveSheet;
for i := 1 to WSheet.Rows.count - 1 do
begin
Table1.Append; //Paradox表,其它表当然也可以了
for j := 1 to WSheet.Columns.Count do
begin
temp := wsheet.cells[i, j].value;
if Trim(temp) = '' then //如果为空则跳出循环,当然,
// 也可以是其它条件
goto PH;
Table1.Fields[j - 1].AsString := temp;
end;
PH:
if Trim(wsheet.cells[i, 1].Value) = '' then break;//跳出循环
end;
...
WBook.SaveAs('c:\xxxx.xls');
MsExcel.quit;
end;
注意:Tabel1的字段数要大于要倒入的Excel文档的列数
var
MSExcel: Variant;
i: Integer;
st_BH:integer;
Max_Bh:string; // 商品编号
Temp_Bh:string; // 商品类别编号 SP_LB:string; // 商品类别
SP_MC:string; // 商品名
SP_GX:string; // 商品规格型号
SP_SM:string; // 商品说明
SP_JG:string; // 商品价格
SP_BZ:string; // 商品备注
begin
st_BH:=0; //进程条
Gauge1.Visible := True; OpenDialog1.Filter:='*.XLS|*.XLS';
OpenDialog1.DefaultExt:='XLS';
if OpenDialog1.Execute then
begin
MSExcel:=CreateOLEObject('Excel.Application');
MSExcel.WorkBooks.Open(OpenDialog1.FileName);
Edit1.Text:= OpenDialog1.FileName; MSExcel.Visible:=False;
//进程条
Gauge1.MaxValue:=MSExcel.ActiveSheet.UsedRange.Rows.Count; //从有数据的行逐行读入数据
try
for i:=1 to MSExcel.ActiveSheet.UsedRange.Rows.Count do
begin
SP_MC:=MSExcel.Cells[i,1].Value; // 商标名
SP_GX:=MSExcel.Cells[i,2].Value; // 商品规格型号
SP_SM:=MSExcel.Cells[i,3].Value; // 商品说明
SP_JG:=MSExcel.Cells[i,4].Value; // 商品价格
SP_BZ:=MSExcel.Cells[i,5].Value; // 商品备注 if (SP_MC='商标名') and (SP_GX='规格型号') and (SP_SM='商品说明') and (SP_JG='价格')and (SP_BZ='备注') then
begin
Gauge1.AddProgress(1);
Continue;
end; if SP_MC='' then
begin
Gauge1.AddProgress(1);
Continue;
end; if (SP_MC<>'') and (SP_GX='') then
begin
SP_LB:=SP_MC; // 商品类别
//商品编号
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='Select max(SP_SPBH) as SP_SPBH,max(SP_SPLBBH) as SP_SPLBBH from ASSERTS.PRODUCTINFO Where SP_SPSSLB='''+SP_LB+''' ';
ADO_Goods.Open;
if ADO_Goods.FieldByName('SP_SPBH').AsString<>'' then
begin
Max_Bh:= IntToStr(StrToInt(ADO_Goods.FieldByName('SP_SPBH').AsString)+1);
Temp_Bh:=ADO_Goods.FieldByName('SP_SPLBBH').AsString;
end else
begin
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='Select FL_FLBH from ASSERTS.PRODUCTCLASS Where FL_FLMC='''+SP_LB+''' ';
ADO_Goods.Open;
Max_Bh:= inttostr(strtoint(ADO_Goods.FieldByName('FL_FLBH').AsString+'001')+st_BH);
Temp_Bh:=ADO_Goods.FieldByName('FL_FLBH').AsString;
end;
Gauge1.AddProgress(1);
Continue;
end; if (SP_MC<>'') and (SP_GX<>'') then
begin
Max_Bh:=inttostr(strtoint(Max_Bh)+st_BH);
st_BH:=st_BH+1;
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='Select SP_SPBH,SP_SPLBBH,SP_SPSSLB,SP_SPBP,SP_SPXH,SP_SPMC,SP_SPSM,SP_JGLY,SP_SPJG,SP_CJSJ,SP_TPDZ ';
ADO_Goods.SQL.Text:=ADO_Goods.SQL.Text+' from ASSERTS.PRODUCTINFO Where SP_SPSSLB='''+SP_LB+''' and SP_SPBP='''+SP_MC+''' and SP_SPXH='''+SP_GX+''' and SP_JGLY=''参考价'' ';
ADO_Goods.Open;
if not ADO_Goods.IsEmpty then
begin
//编辑
ADO_Goods.Edit;
ADO_Goods.FieldByName('SP_SPSSLB').AsString:=SP_LB;
ADO_Goods.FieldByName('SP_SPBP').AsString:=SP_MC;
ADO_Goods.FieldByName('SP_SPXH').AsString:=SP_GX;
ADO_Goods.FieldByName('SP_SPMC').AsString:=SP_MC+SP_LB;
ADO_Goods.FieldByName('SP_SPSM').AsString:=SP_SM;
ADO_Goods.FieldByName('SP_JGLY').AsString:='参考价';
ADO_Goods.FieldByName('SP_SPJG').AsString:=SP_JG;
ADO_Goods.FieldByName('SP_CJSJ').AsString:=DateToStr(now);
ADO_Goods.FieldByName('SP_TPDZ').AsString:=SP_BZ;
ADO_Goods.Post;
end else
begin
//插入
ADO_Goods.Insert;
ADO_Goods.FieldByName('SP_SPBH').AsString:=Max_Bh;
ADO_Goods.FieldByName('SP_SPLBBH').AsString:=Temp_Bh;
ADO_Goods.FieldByName('SP_SPSSLB').AsString:=SP_LB;
ADO_Goods.FieldByName('SP_SPBP').AsString:=SP_MC;
ADO_Goods.FieldByName('SP_SPXH').AsString:=SP_GX;
ADO_Goods.FieldByName('SP_SPMC').AsString:=SP_MC+SP_LB;
ADO_Goods.FieldByName('SP_SPSM').AsString:=SP_SM;
ADO_Goods.FieldByName('SP_JGLY').AsString:='参考价';
ADO_Goods.FieldByName('SP_SPJG').AsString:=SP_JG;
ADO_Goods.FieldByName('SP_CJSJ').AsString:=DateToStr(now);
ADO_Goods.FieldByName('SP_TPDZ').AsString:=SP_BZ;
ADO_Goods.Post;
end;
Gauge1.AddProgress(1);
end;
end;
Gauge1.Hide;
MSExcel.ActiveWorkBook.Close;
MSExcel.Quit;
Except
Gauge1.Hide;
Exit;
end;
end;
end;
insert into a
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...
语法有错误
在Delphi5下的编程!