代码如下:
{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)
);