Delphi从Excel读取数据存入数据库Demo

最新推荐文章于 2025-05-29 10:05:24 发布

原创最新推荐文章于 2025-05-29 10:05:24 发布 · 2.8k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#delphi

本文介绍了一种使用Delphi将Excel数据导入到SQL Server数据库的方法。通过创建一个简单的Delphi应用程序，该程序利用OLE对象操作Excel，并将读取的数据插入到SQL Server表中。文章提供了一个完整的代码示例，包括如何打开Excel文件、读取单元格内容及执行SQL插入语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

代码如下：

{Excel to Delphi to SqlServer}
unit ExcelLoad;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls,ComObj, dxSkinsCore, dxSkinBlack, dxSkinBlue,
  dxSkinCaramel, dxSkinCoffee, dxSkinDarkSide, dxSkinGlassOceans,
  dxSkiniMaginary, dxSkinLilian, dxSkinLiquidSky, dxSkinLondonLiquidSky,
  dxSkinMcSkin, dxSkinMoneyTwins, dxSkinOffice2007Black,
  dxSkinOffice2007Blue, dxSkinOffice2007Green, dxSkinOffice2007Pink,
  dxSkinOffice2007Silver, dxSkinPumpkin, dxSkinSilver, dxSkinStardust,
  dxSkinSummer2008, dxSkinsDefaultPainters, dxSkinValentine,
  dxSkinXmas2008Blue, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage, cxEdit, DB, cxDBData, cxGridLevel, cxClasses, cxControls,
  cxGridCustomView, cxGridCustomTableView, cxGridTableView,
  cxGridDBTableView, cxGrid, cxTL, cxTextEdit, cxInplaceContainer, Grids,
  ADODB;

type
  TForm1 = class(TForm)
    OpenDialog: TOpenDialog;
    btn1: TButton;
    cxgrdbtblvwGrid1DBTableView1: TcxGridDBTableView;
    cxgrdlvlGrid1Level1: TcxGridLevel;
    cxgrd1: TcxGrid;
    cxgrdbclmnGrid1DBTableView1Column1: TcxGridDBColumn;
    cxgrdbclmnGrid1DBTableView1Column2: TcxGridDBColumn;
    lst1: TcxTreeList;
    cxtrlstclmnlst1cxTreeListColumn1: TcxTreeListColumn;
    cxtrlstclmnlst1cxTreeListColumn2: TcxTreeListColumn;
    strngrd1: TStringGrid;
    con1: TADOConnection;
    qry1: TADOQuery;
    procedure btn1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btn1Click(Sender: TObject);
var ExcelApp:Variant;
    n1:string;
    i,j:integer;
    FileName:string;
    OpenDialog:TOpenDialog;
    flag:boolean;
begin
  OpenDialog:=TOpenDialog.Create(nil);
  OpenDialog.Filter:='Excel文件|*.xls';
    if not OpenDialog.Execute then begin
      OpenDialog.Free;
      exit;
    end;
    FileName:=OpenDialog.FileName;
    ExcelApp:=CreateOleObject('Excel.Application');
    ExcelApp.visible:=False;
    ExcelApp.workbooks.open(FileName);

    //自适应宽度
    ExcelApp.worksheets[1].Cells.EntireColumn.AutoFit;

    //ShowMessage(ExcelApp.worksheets[1].cells[1,1].text);
    for  i:=1 to 4 do
    begin
      for j:=1 to 2 do
      begin
      if ExcelApp.worksheets[1].cells[i,1].text <>'' then
      strngrd1.Cells[j-1,i-1]:=ExcelApp.worksheets[1].cells[i,j].text;
      end;

     end;
    for i:=0 to 3 do
    begin
      with qry1 do
      begin
        Close;
        SQL.Clear;
        SQL.Text:='insert into excel(name,pwd) values(:name,:pwd)';
        Parameters.ParamByName('name').Value:=strngrd1.Cells[0,i+1];
        Parameters.ParamByName('pwd').Value:=strngrd1.Cells[1,i+1];
        ExecSQL;
      end;
    end;
end;
end.

数据库建表语句：

 create table excel(
 id int identity(1,1) primary key,
 name nchar(30),
 pwd nchar(3)
 );