[Delphi-码农]Delphi EXCEL导入

整理过的

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;

http://zhidao.baidu.com/link?url=hNhGnrIAKOWJEIICoZwdKtcof7zyj_LMp-PlV2xmjgRIdAKSWDRuPT3xxzqGdhSlZc6NXsKSckbPSOdk7cBJ2a

元接口部分引用 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导入 的通用程序 .

http://www.cnblogs.com/zhangzhifeng/archive/2013/05/24/3096742.html

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;

分类:  Delphi 操作Excel几种控件的用法,  Delphi Excel

绿色通道：  好文要顶  关注我  收藏该文 与我联系 

╰★张志峰★╮
关注 - 7
粉丝 - 76

+加关注