excelapp,book,sheet:variant;
begin
excelapp:=createoleobject('excel.application');
excelapp.workbooks[1].worksheets.count //这行是不是说EXCEL表格中记录的总数
//以下代码哪里出现错误呀???大家帮个忙呀,谢谢!
//批量增加学生!
procedure TAdmin_Operation_Form_Add.Button3Click(Sender: TObject);
var
i : integer;
excelapp : variant;
s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11 : string ;
begin
excelapp := createoleobject('excel.application');
// excelapp.visible := false;
// excelapp.DisplayAlerts := False;
try
if opendialog1.Execute then
excelapp.workbooks.open(opendialog1.fileName)
else
begin
showmessage('没有选择文件,此次操作无效!') ;
abort ;
end ;
for i:=1 to excelapp.workbooks[1].worksheets.count do
begin
if excelapp.Workbooks[1].Sheets[1].Cells[i , 1] <> '' then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from student where s_no = :para_s_no') ;
query2.ParamByName('para_s_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 1]) ;
query2.Open ;
if query2.RecordCount < 1 then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from building where building_no = :para_building_no') ;
query2.ParamByName('para_building_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 11]) ;
query2.Open ;
if query2.RecordCount >= 1 then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from building , house where building.building_no = house.building_no ') ;
query2.SQL.Add(' And building.building_no = :para_building_no AND house.house_no = :para_house_no') ;
query2.ParamByName('para_building_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 11] ) ;
query2.ParamByName('para_house_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 12]) ;
query2.Open ;
if query2.RecordCount >= 1 then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from building , house where building.building_no = house.building_no ') ;
query2.SQL.Add(' And building.building_no = :para_building_no AND house.house_no = :para_house_no') ;
query2.ParamByName('para_building_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 11] ) ;
query2.ParamByName('para_house_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 12]) ;
query2.Open ;
if query2.RecordCount >= 1 then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from building , house , bed where building.building_no = house.building_no ') ;
query2.SQL.Add(' AND house.house_no = bed.house_no AND Building.building_no = :para_building_no') ;
query2.SQL.Add(' AND house.house_no = :para_house_no AND bed_no = :para_bed_no') ;
query2.ParamByName('para_building_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 11]) ;
query2.ParamByName('para_house_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 12]) ;
query2.ParamByName('para_bed_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 13]) ;
query2.Open ;
if query2.RecordCount >= 1 then
begin
if query2.FieldByName('s_no').AsString = '' then
begin
query3.Close ;
query3.SQL.Clear ;
query3.SQL.Add('select * from bed where bed_no = :para_bed_no') ;
query3.SQL.Add(' AND house_no = :para_house_no') ;
query3.ParamByName('para_bed_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 13]) ;
query3.ParamByName('para_house_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 12]) ;
query3.Open ;
query3.Edit ;
query3.FieldByName('s_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 1]) ;
query3.Post ;
query3.Close ;
query3.SQL.Clear ;
query3.SQL.Add('select * from student') ;
query3.Open ;
s1 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 1]) ;
s2 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 2]) ;
s3 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 3]) ;
s4 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 14]) ;
s5 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 4]) ;
s6 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 5]) ;
s7 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 6]) ;
s8 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 7]) ;
s9 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 8]) ;
s10 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 9]) ;
s11 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 10]) ;
query3.Edit ;
query3.AppendRecord( [s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11 ,nil]) ;
query2.Close ;
query2.Open ;
end ;
end ;
end ;
end ;
end ;
end ;
end ;
excelapp.quit;
end ;
except
excelapp.quit;
end;
end;
begin
excelapp:=createoleobject('excel.application');
excelapp.workbooks[1].worksheets.count //这行是不是说EXCEL表格中记录的总数
//以下代码哪里出现错误呀???大家帮个忙呀,谢谢!
//批量增加学生!
procedure TAdmin_Operation_Form_Add.Button3Click(Sender: TObject);
var
i : integer;
excelapp : variant;
s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11 : string ;
begin
excelapp := createoleobject('excel.application');
// excelapp.visible := false;
// excelapp.DisplayAlerts := False;
try
if opendialog1.Execute then
excelapp.workbooks.open(opendialog1.fileName)
else
begin
showmessage('没有选择文件,此次操作无效!') ;
abort ;
end ;
for i:=1 to excelapp.workbooks[1].worksheets.count do
begin
if excelapp.Workbooks[1].Sheets[1].Cells[i , 1] <> '' then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from student where s_no = :para_s_no') ;
query2.ParamByName('para_s_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 1]) ;
query2.Open ;
if query2.RecordCount < 1 then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from building where building_no = :para_building_no') ;
query2.ParamByName('para_building_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 11]) ;
query2.Open ;
if query2.RecordCount >= 1 then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from building , house where building.building_no = house.building_no ') ;
query2.SQL.Add(' And building.building_no = :para_building_no AND house.house_no = :para_house_no') ;
query2.ParamByName('para_building_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 11] ) ;
query2.ParamByName('para_house_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 12]) ;
query2.Open ;
if query2.RecordCount >= 1 then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from building , house where building.building_no = house.building_no ') ;
query2.SQL.Add(' And building.building_no = :para_building_no AND house.house_no = :para_house_no') ;
query2.ParamByName('para_building_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 11] ) ;
query2.ParamByName('para_house_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 12]) ;
query2.Open ;
if query2.RecordCount >= 1 then
begin
query2.Close ;
query2.SQL.Clear ;
query2.SQL.Add('select * from building , house , bed where building.building_no = house.building_no ') ;
query2.SQL.Add(' AND house.house_no = bed.house_no AND Building.building_no = :para_building_no') ;
query2.SQL.Add(' AND house.house_no = :para_house_no AND bed_no = :para_bed_no') ;
query2.ParamByName('para_building_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 11]) ;
query2.ParamByName('para_house_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 12]) ;
query2.ParamByName('para_bed_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 13]) ;
query2.Open ;
if query2.RecordCount >= 1 then
begin
if query2.FieldByName('s_no').AsString = '' then
begin
query3.Close ;
query3.SQL.Clear ;
query3.SQL.Add('select * from bed where bed_no = :para_bed_no') ;
query3.SQL.Add(' AND house_no = :para_house_no') ;
query3.ParamByName('para_bed_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 13]) ;
query3.ParamByName('para_house_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 12]) ;
query3.Open ;
query3.Edit ;
query3.FieldByName('s_no').AsString := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 1]) ;
query3.Post ;
query3.Close ;
query3.SQL.Clear ;
query3.SQL.Add('select * from student') ;
query3.Open ;
s1 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 1]) ;
s2 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 2]) ;
s3 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 3]) ;
s4 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 14]) ;
s5 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 4]) ;
s6 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 5]) ;
s7 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 6]) ;
s8 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 7]) ;
s9 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 8]) ;
s10 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 9]) ;
s11 := trim(excelapp.Workbooks[1].Sheets[1].Cells[i , 10]) ;
query3.Edit ;
query3.AppendRecord( [s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11 ,nil]) ;
query2.Close ;
query2.Open ;
end ;
end ;
end ;
end ;
end ;
end ;
end ;
excelapp.quit;
end ;
except
excelapp.quit;
end;
end;
Maxc :=ExlApp.WorkSheets[1].UsedRange.Columns.Count;
Maxr :=ExlApp.WorkSheets[1].UsedRange.Rows.Count;
Maxr :=ExlApp.WorkSheets[1].UsedRange.Rows.Count;
错误提示为: Invalid variant operation
begin
query2.Close ; //这行错误!这怎么可能,奇怪!这里怎么不可以关掉Table呢?是不是操作Excel表格要有什么前提呀?