用delphi将excel数据导入到access中
'''' xlspath '''' 改为: xlspath 不需要用引号引用excel文件路径。
delphi怎样实现excel导入到数据库?
口部分引用 comobj 单元(uses )
procedure TForm1.Button1Click(Sender: TObject);
var excelx,excely : string;
begin
try
ExcelApp := CreateOleObject('Excel.Application');
WorkBook := ExcelApp.WorkBooks.Open(opendialog.FileName);//使用opendialog对话框指定
//excel档路径
ExcelApp.Visible := false;
ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;
for i := 1 to excelrowcount 1 do
begin
excelx := excelapp.Cells[i,1].Value;
excely := excelapp.Cells[i,2].Value;
if ((excelapp.Cells[i,1].Value = '') and (ExcelApp.Cells[i,2].Value = '')) then
//指定excel档的 i 行 ,第 1,2(看情况而定)行如空就退出,这样的设定,最你的档案力这两行//对应数据库中不能为空的数据
exit
else
with query1 do
begin
close;
sql.clear;
sql.add(insert into test(name,address) values(:name,:address));
parambyname('name').asstring := excelx;//excel档的第一列插入到test表的 name栏位;
parambyname('address').asstring := excely;//excel档的第二列插入到test表的 address 栏位;
execsql;
end;
end;
finally
WorkBook.Close;
ExcelApp.Quit;
ExcelApp := Unassigned;
WorkBook := Unassigned;
end;
end;
上面的是导到sql 中的。oracle应该也差不多的吧。。
Delphi中导入EXCEL到数据库SQL中
用这个方法吧,好且不容易出错。
var
nu,barcode1,da,na,ena,com,spec:string;
ExcelApp,WorkBook: Olevariant;
ExcelRowCount,i :Integer;
begin
dbgrid2.Enabled:=false;
try
if SaveDialog1.Execute then
begin
ExcelApp := CreateOleObject('Excel.Application');
WorkBook := ExcelApp.WorkBooks.Open(savedialog1.FileName);
ExcelApp.Visible := false;
ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;
for i := 2 to excelrowcount 1 do
begin
nu := excelapp.Cells[i,1].Value;
if trim(excelapp.cells[i,2].value)='' then
na:=' '
else begin
na:=excelapp.Cells[i,2].Value;
end;
if trim(excelapp.cells[i,3].value)='' then
ena:=' '
else
ena:= excelapp.Cells[i,3].Value;
if trim(excelapp.cells[i,4].value)='' then
com:=' '
else
com:= trim(excelapp.Cells[i,4].Value);
if trim(excelapp.cells[i,5].value)='' then
spec:=' '
else
spec:= excelapp.Cells[i,5].Value;
if ((excelapp.Cells[i,1].Value = '') and (ExcelApp.Cells[i,3].Value = '')) then //指定excel档的第 i 行 ,第 1,2(看情况而定)行为空就退出,这样的设定,最好是你案力这两行//对应数据库中不能为空的数据
exit
else
with data.product do
begin
close;
sql.clear;
sql.Add('select * from twoven where number=''' nu '''');
open;
if recordcount>=1 then begin
if MessageBox(handle,'此记录已存在,是否覆盖原记录?','提示',MB_IconQuestion MB_YesNo)=IDYES then
BEGIN
Delete;
sql.Clear;
sql.add('insert into twoven(number,name,ename,wf_comp,wf_spec)');
sql.add(' values(:number,:name,:ename,:wf_comp,:wf_spec)');
Parameters.ParamByName('number').Value :=trim(nu);//excel档的第一列插入到aa表的 a 栏位;
Parameters.ParamByName('name').Value := trim(na);//excel档的第二列插入到aa表的 b 栏位;
Parameters.ParamByName('ename').Value := trim(ena);//excel档的第一列插入到aa表的 a 栏位;
Parameters.ParamByName('wf_comp').Value := trim(com);//excel档的第一列插入到aa表的 a 栏位;
Parameters.ParamByName('wf_spec').Value := trim(spec);//excel档的第二列插入到aa表的 b 栏位;
execsql;
END;
end;=
end;
showmessage('数据导入成功!');
WorkBook.Close;
ExcelApp.Quit;
ExcelApp := Unassigned;
WorkBook := Unassigned;
ExcelWorkBook1.Close(false);
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
Screen.Cursor:=crDefault;
end;
delphi dbgrid中的数据如何导入到excel表中?
这个问题很难,我粘贴一段我从excel中读的代码:
try
{
Variant v1;
ExcelApplication->Connect();
ExcelApplication->set_WindowState(0, xlMinimized);
//ExcelApplication->set_Visible(0, true);
try
{
Form5 = new TForm5(Application);
Form5->ShowModal();
delete Form5;
ExcelApplication->set_Visible(0, showexcel);
ExcelApplication->Workbooks->Open(StringToOleStr(OpenDialog1->FileName.c_str()));
ExcelApplication->set_Caption(StringToOleStr(OpenDialog1->FileName));
ExcelWorkbook->ConnectTo(ExcelApplication->Workbooks->get_Item(Variant(1)));
ExcelWorksheet->ConnectTo(ExcelWorkbook->Worksheets->get_Item(Variant(isheet)));
for(int i = 1; i < sRow; i)
for(int j = 1; j < sCol; j)
{
v1 = ExcelWorksheet->Cells->get_Item(Variant(i), Variant(j));
if(!v1.Empty())
StringGrid1->Cells[j][i] = VarToStr(v1);
}
openfile = true;
//Form2 = new TForm2(Application);
Form2->Show();
caled = false;
}
catch(Exception