const
BeginRow = 2; BeginCol = 1;
var
Excel: OleVariant;
iRow,iCol : integer;
xlsFilename: string;
filename:string;
i,ExcelRowCount,OkNum,FailNum:integer;
begin
inherited;
begin
OpenDialog1.Title := '请选择相应的Excel文件';
OpenDialog1.Filter := 'Excel(*.xls)|*.xls';
if OpenDialog1.Execute then
begin
filename := OpenDialog1.FileName;
edit2.Text := filename;
xlsFilename := trim(filename);
try
Excel := CreateOLEObject('Excel.Application');
except
Application.MessageBox('Excel没有安装!', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
Exit;
end;
Excel.Visible := false;
Excel.WorkBooks.Open(xlsFilename);
try
iRow := BeginRow;
iCol := BeginCol;
ExcelRowCount := Excel.WorkSheets[1].UsedRange.Rows.Count;
// while trim(Excel.WorkSheets[1].Cells[iRow,iCol].value) <> '' do
for i:=2 to ExcelRowCount do
begin
with Svr_Remote.DW_Query do
begin
Append;
Fields[0].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol].value);
Fields[1].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+1].value);
Fields[2].Asstring := trim(Excel.WorkSheets[1].Cells[iRow,iCol+2].value);
Fields[3].Asstring := trim(Excel.WorkSheets[1].Cells[iRow,iCol+3].value);
Fields[4].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+4].value);
Fields[5].Asstring := trim(Excel.WorkSheets[1].Cells[iRow,iCol+5].value);
Fields[6].Asstring := trim(Excel.WorkSheets[1].Cells[iRow,iCol+6].value);
Fields[7].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+7].value);
Fields[8].Asstring := trim(Excel.WorkSheets[1].Cells[iRow,iCol+8].value);
Fields[9].Asstring := trim(Excel.WorkSheets[1].Cells[iRow,iCol+9].value);
Fields[10].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+10].value);
Fields[11].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+11].value);
Fields[12].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+12].value);
Fields[13].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+13].value);
Fields[14].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+14].value);
Fields[15].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+15].value);
iRow := iRow + 1;
Label1.Caption:='导入记录:'+inttostr(iRow-1)+'条';
label4.Caption :='正在导入的数据...'+Fields[0].AsString+Fields[1].AsString+Fields[2].AsString+
Fields[3].AsString+Fields[4].AsString+Fields[5].AsString+Fields[6].AsString+
Fields[7].AsString+Fields[8].AsString+Fields[9].AsString+Fields[10].AsString+
Fields[11].AsString+Fields[12].AsString+Fields[13].AsString+Fields[14].AsString+Fields[15].AsString;
end;
end;
Excel.Quit;
Svr_Remote.DW_Query.UpdateStatus ;
MessageBox(GetActiveWindow(), '数据导入成功!', '...系统提示', MB_OK + MB_ICONWARNING);
except
Application.MessageBox('导入数据出错!请检查文件的格式是否正确!', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
Excel.Quit;
end;
end
else
begin
MessageBox(GetActiveWindow(), '请重新选择导入文件路径!', ...系统提示', MB_OK + MB_ICONWARNING);
end;
end;
end;
注:一执行后,导入的数据就是不全,本来excel表中有524条记录,执行后确只能导入523条记录,缺少一条记录,请大师给指点一下,谢谢
/////////////////////显示数据
Svr_Remote.DW_Query.close;
Svr_Remote.DW_Query.sql.clear;
Svr_Remote.DW_Query.sql.add('select * from STU7');
Svr_Remote.DW_Query.Open;
Svr_Remote.DW_Query.first;
///////////////////////