Mikel
来源： [Delphi-码农]Delphi EXCEL导入 – CSDN博客
整理过的
- procedure TParameterALterFines.btnImportExcelClick(Sender: TObject);
- const
- BeginRow = 2; BeginCol = 1;
- var
- Excel: OleVariant;
- iRow,iCol : integer;
- xlsFilename: string;
- ExcelRowCount : integer;
- begin
- OpenDialog1.Title := ‘请选择正确的excel文件’;
- OpenDialog1.Filter := ‘Excel(*.xls)|*.xls’;
- if OpenDialog1.Execute then
- edit1.Text := OpenDialog1.FileName;
- if (trim(edit1.Text) = ”) then
- begin
- GetActiveWindow();
- showmessage( ‘请选择正确的excel路径’);
- exit;
- end;
- xlsFilename := trim(edit1.Text);
- try
- Excel := CreateOLEObject(‘Excel.Application’);
- except
- showmessage(‘excel没有安装’);
- Exit;
- end;
- Excel.Visible := false;
- Excel.WorkBooks.Open(xlsFilename);
- ExcelRowCount := Excel.WorkSheets[1].UsedRange.Rows.Count;
- //showmessage(inttoStr(ExcelRowCount));
- try
- iRow := BeginRow;
- iCol := BeginCol;
- while trim(Excel.WorkSheets[1].Cells[iRow,iCol].value) <> ” do
- //WHILE iRow<ExcelRowCount Do
- begin
- with ADOQuery1 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);
- close;
- SQL.clear;
- SQL.Text:=‘insert into Temp (ID,Content) ‘;
- SQL.Text:=sql.Text+‘select ‘+trim(Excel.WorkSheets[1].Cells[iRow,iCol].value);
- sql.Text:=sql.Text+‘ , ‘+ trim(Excel.WorkSheets[1].Cells[iRow,iCol+1].value);
- execsql;
- iRow := iRow + 1;
- end;
- end;
- Excel.Quit;
- ADOQuery1.UpdateStatus ;
- except
- showmessage(‘导入数据出错’);
- Excel.Quit;
- //Exit;
- end;
- //MessageBox(GetActiveWindow(), ‘数据导入成功’, ‘提示信息’, MB_OK + MB_ICONWARNING);
- end;
- 元接口部分引用 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;
Delphi Excel导入 的通用程序 .
- Delphi Excel导入 的通用程序 .
- 分类： delphi 2012–09–24 18:19 127人阅读 评论(0) 收藏 举报
- exceldelphiintegerprocedure TForm1.btnClick(Sender: TObject);
- begin
- OpenDialog1.Title := ‘请选择正确的excel文件’;
- OpenDialog1.Filter := ‘Excel(*.xls)|*.xls’;
- if OpenDialog1.Execute then
- edit1.Text := OpenDialog1.FileName;
- end;
- procedure TForm1.btninClick(Sender: TObject);
- const
- BeginRow = 2; BeginCol = 1;
- var
- Excel: OleVariant;
- iRow,iCol : integer;
- xlsFilename: string;
- begin
- if (trim(edit1.Text) = ”) then
- begin
- MessageBox(GetActiveWindow(), 请选择正确的excel路径’, MB_OK +
- MB_ICONWARNING);
- exit;
- end;
- xlsFilename := trim(edit1.Text);
- 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;
- while trim(Excel.WorkSheets[1].Cells[iRow,iCol].value) <> ” do begin
- with ADOQuery1 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);
- iRow := iRow + 1;
- end;
- end;
- Excel.Quit;
- ADOQuery1.UpdateStatus ;
- except
- Application.MessageBox(‘导入数据出错’, ‘提示信息’, MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
- Excel.Quit;
- end;
- MessageBox(GetActiveWindow(), ‘数据导入成功’, ‘提示信息’, MB_OK +
- MB_ICONWARNING);
- end;
