关于读取Excel文件数据到数据库,以及从数据库导出数据到Excel的例子

 数据库:sqlserver
表名称:tperson  字段:code varchar(10)  name varchar(20)
创建一个结构体:
type
  PPerson = ^TPerson;
  TPerson = record
    FCode: String;
    FName: string;
  end;
相关函数及变量
FPerson: PPerson;  
FPersonList: TList; //用来存数据
function LoadExcel: Boolean;  //加载Excel
function SaveToDB: Boolean;   //写到数据库
function DBToExcel: Boolean;  //导出数据

两个按钮,一个adoconnection,一个adoquery

看到好多人问关于Excel方面的问题,所以写了这个比较简单的例子,有好多细节也没考虑,供参考
在真正写的时候,要注意指针的释放等.


全部源码:
unit Unit1;

interface

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

type
  PPerson = ^TPerson;
  TPerson = record
    FCode: String;
    FName: string;
  end;
type
  TForm1 = class(TForm)
    Button1: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
    FPerson: PPerson;
    FPersonList: TList;
    function LoadExcel: Boolean;
    function SaveToDB: Boolean;
    function DBToExcel: Boolean;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function TForm1.LoadExcel: Boolean;
var
  RangeMatrix: Variant;
  OpenDialog1: TOpenDialog;
  iRE: integer;
  IN_TYPE,vStockName: String;
  sStockName,sCompany: String;
  i: Integer;
  MsExcel, MsExcelWorkBook, MsExcelWorkSheet: Variant;
  Col1,Col2: String;
begin
  OpenDialog1:=TOpenDialog.Create(nil);
  try
    OpenDialog1.FileName:='*.csv;*.xls';
    if not OpenDialog1.Execute then
    begin
      Result := false;
      exit;
    end;
    MsExcel := CreateOleObject('Excel.Application');
    MsExcelWorkBook := msExcel.Workbooks.Open(OpenDialog1.FileName);
    MsExcelWorkSheet := msExcel.Worksheets.Item[1];
  except
    Result := false;
    Exit;
  end;
  //开始从EXCEL文件读取相关的信息
  try
    try
      Application.ProcessMessages;
      for i := 1 to MsExcelWorkSheet.Rows.Count do
      begin
        if trim(MsExcelWorkSheet.Range['A' + IntToStr(i)].Value)='' then break;
        New(FPerson);
        FPerson.FCode := trim(MsExcelWorkSheet.Range['A' + IntToStr(i)].Value);
        FPerson.FName := trim(MsExcelWorkSheet.Range['B' + IntToStr(i)].Value);
        FPersonList.Add(FPerson);
      end;
    except
      Result := false;
      Exit;
    end;
  finally
    MsExcel.WorkBooks.Close;
  end;
  Result := true;
  MsExcel.Quit;
  OpenDialog1.Free;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  //创建一个list列表,以用来存取表数据
  FPersonList := TList.create;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  FPersonList.Free;//释放资源
end;

function TForm1.SaveToDB: Boolean;
var
  i: integer;
begin
  AdoConnection1.BeginTrans;
  try
    for i:= 0 to FPersonList.Count-1 do
    begin
      New(FPerson);
      FPerson := FPersonList[i];
      with adoquery1 do
      begin
        close;
        sql.Text := ' insert into tperson(code,name) values(:code,:name)';
        parameters.ParamByName('code').Value := FPerson.FCode;
        Parameters.ParamByName('name').Value := FPerson.FName;
        ExecSql;
      end;
    end;
    AdoConnection1.CommitTrans;
    Result := true;
  except
    AdoConnection1.RollbackTrans;
    Result := false;
  end;
end;

function TForm1.DBToExcel: Boolean;
var
  SaveDialog: TSaveDialog;
  curname,Str: String;
  CsvFile: TextFile;
begin
  SaveDialog := TSaveDialog.Create(nil);
  SaveDialog.Filter := '.csv';
  try
    if SaveDialog.Execute then
    begin
      //开始保存文件
      curname:=SaveDialog.FileName+'.csv';  //生成存储文件名
      if FileExists(curname) then
      begin
        if (Application.MessageBox(PChar('文件【'+curname+'】已存在,要覆盖吗?'),'提

示',MB_ICONQUESTION+MB_YESNO)=IDYES) then
          DeleteFile(PChar(curname))
        else
        begin
          exit;
        end;
      end;
      //写标题
      AssignFile(CsvFile,curname);
      Rewrite(CsvFile);
      try
        str:='';
        str:='"'+'编号';
        str:=str+'","'+'姓名';
        str:=str+'"';
        Writeln(CsvFile,str);
        with adoquery1 do
        begin
          close;
          sql.Text := ' select code,name from tperson';
          Open;
          while not eof do
          begin
            str:='"';
            str:=str+StringReplace(adoquery1.FieldByName('code').AsString,'"','''''',[rfReplaceAll]);
            str:=str+'","'+StringReplace(adoquery1.FieldByName('name').AsString,'"','''''',[rfReplaceAll]);
            str:=str+'"';
            Writeln(CsvFile,str);
            Next;
          end;
        end;
      finally
        CloseFile(CsvFile);
      end;
    end;
  finally
    Savedialog.Free;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  LoadExcel;
  SaveToDB;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  DBToExcel;
end;

end.