unit1.~pas

完成从EXCEL文件向SQLSERVER的数据导入和导出

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs,comobj, StdCtrls, DB, ADODB ;

type
  TForm1 = class(TForm)
    Button1: TButton;
    OpenDialog: TOpenDialog;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    Label1: TLabel;
    Button2: TButton;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var excelx,excely : string;
    ExcelApp,WorkBook: Variant;
    ExcelRowCount,i:integer;

    tempstr:string;
begin

try

ExcelApp := CreateOleObject('Excel.Application');
if opendialog.Execute then
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
Label1.Caption :='正在转换第'+inttostr(i)+'个数据';
form1.refresh;

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(看情况而定)行如果为空就退出,这样的设定,最好是你的档案力这两行//对应数据库中不能为空的数据 
if ((length(excelx)=0) or (length(excely)=0)) then
    begin
    Label1.Caption :='总共转换入数据库'+inttostr(i-1)+'个数据';
    exit;
    end

else 

with ADOQuery1 do 

begin 
close; 
sql.clear;
tempstr:='insert into test(name,address) values('''+excelx+''','''+excely+''')';
sql.add(tempstr);
//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;
/////////////////////////////////////////////////////
procedure TForm1.Button2Click(Sender: TObject);
var excelx,excely : string;
    ExcelApp,WorkBook: Variant;
    ExcelRowCount,i:integer;

    tempstr:string;
begin 

try

ExcelApp := CreateOleObject('Excel.Application');
//if opendialog.Execute then
WorkBook := ExcelApp.WorkBooks.Open('C:\TEST.XLS');
//使用opendialog对话框指定
//excel档路径 



ExcelApp.Visible := false; 

with ADOQuery1 do

begin
close;
sql.clear;
tempstr:='SELECT * FROM test';
sql.add(tempstr);
//parambyname('name').asstring := excelx;//excel档的第一列插入到test表的 name栏位;
//parambyname('address').asstring := excely;//excel档的第二列插入到test表的 address 栏位;
OPEN;
excelrowcount:= ADOQuery1.recordCount;

end;

for i := 1 to excelrowcount  do

begin
Label1.Caption :='正在转换第'+inttostr(i)+'个数据';

form1.refresh;
excelx:=ADOQuery1.Fields.Fields[1].AsString;
excely:=ADOQuery1.Fields.Fields[2].AsString ;
excelapp.Cells[i,1].Value :=excelx      ;
excelapp.Cells[i,2].Value :=excely      ;
ADOQuery1.Next
end;
finally 

WorkBook.SAVE;
WorkBook.Close;

ExcelApp.Quit;

ExcelApp := Unassigned;

WorkBook := Unassigned; 

end; 


end;


End.