实现从excel表导入到SQL Sever中,并与SQL中的两表进行判断并最终插入,已有程序,麻烦看看(50分)

  • 主题发起人 linseaman
  • 开始时间
L

linseaman

Unregistered / Unconfirmed
GUEST, unregistred user!
实现从excel表导入到SQL Sever中,并与SQL中的两表进行判断并最终插入,已有程序,麻烦看看<br>我的思路是;将excel表“数据导出文件”(表1)通过程序导入与SQL中的“客户基本资料表”(表2)做判断,表1存在表2,就不做处理,若不存在,则进行下一步,将“数据导出文件”(表1)与SQL中的“税控导入资料”表(表3)做判断,存在不做处理,不存在则将表1插入到表3中,程序实现过称如下:<br>unit UText;<br><br>interface<br><br>uses<br>&nbsp; Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,<br>&nbsp; Dialogs, Buttons, StdCtrls, DB, ADODB, Grids, DBGrids;<br><br>type<br>&nbsp; TForm1 = class(TForm)<br>&nbsp; &nbsp; SpeedButton1: TSpeedButton;<br>&nbsp; &nbsp; Edit1: TEdit;<br>&nbsp; &nbsp; Label1: TLabel;<br>&nbsp; &nbsp; Button1: TButton;<br>&nbsp; &nbsp; OpenDialog1: TOpenDialog;<br>&nbsp; &nbsp; Button2: TButton;<br>&nbsp; &nbsp; ADOConnection1: TADOConnection;<br>&nbsp; &nbsp; DBGrid1: TDBGrid;<br>&nbsp; &nbsp; ADOTable1: TADOTable;<br>&nbsp; &nbsp; DataSource1: TDataSource;<br>&nbsp; &nbsp; Label2: TLabel;<br>&nbsp; &nbsp; ADOQuery1: TADOQuery;<br>&nbsp; &nbsp; Button3: TButton;<br>&nbsp; &nbsp; ADOTable2: TADOTable;<br>&nbsp; &nbsp; DataSource2: TDataSource;<br>&nbsp; &nbsp; DBGrid2: TDBGrid;<br>&nbsp; &nbsp; ADOQuery2: TADOQuery;<br>&nbsp; &nbsp; Label3: TLabel;<br>&nbsp; &nbsp; Button4: TButton;<br>&nbsp; &nbsp; procedure SpeedButton1Click(Sender: TObject);<br>&nbsp; &nbsp; procedure Button1Click(Sender: TObject);<br>&nbsp; &nbsp; procedure Button2Click(Sender: TObject);<br>&nbsp; &nbsp; procedure Button3Click(Sender: TObject);<br>&nbsp; &nbsp; procedure FormCreate(Sender: TObject);<br>&nbsp; &nbsp; procedure Button4Click(Sender: TObject);<br>&nbsp; private<br>&nbsp; &nbsp; { Private declarations }<br>&nbsp; public<br>&nbsp; &nbsp; { Public declarations }<br>&nbsp; end;<br><br>var<br>&nbsp; Form1: TForm1;<br><br>implementation<br><br>{$R *.dfm}<br>var<br>&nbsp; sSql,qSql,exlpath: string;<br><br>procedure TForm1.FormCreate(Sender: TObject);<br>begin<br>ADOTable1.Active :=True;<br>ADOTable2.Active :=True;<br>end;<br><br>procedure TForm1.SpeedButton1Click(Sender: TObject); &nbsp; //打开Excel表<br>begin<br>&nbsp; OpenDialog1.Title := '请导入Excel表';<br>&nbsp; OpenDialog1.Filter := 'Excel(*.xls)|*.xls';<br>&nbsp; if OpenDialog1.Execute then<br>&nbsp; &nbsp; &nbsp;edit1.Text := OpenDialog1.FileName;<br>end;<br><br>procedure TForm1.Button1Click(Sender: TObject); &nbsp;//判断路径和连接数据库<br>begin<br>&nbsp; if (trim(edit1.Text) = '') then<br>&nbsp; begin<br>&nbsp; &nbsp; MessageBox(GetActiveWindow(), '请选择正确路径!', '警告', MB_OK +<br>&nbsp; &nbsp; &nbsp; MB_ICONWARNING);<br>&nbsp; &nbsp; exit;<br>&nbsp; end;<br>&nbsp; exlpath:= trim(edit1.Text); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//Excel路径<br>&nbsp; ADOConnection1.Connected := False;<br><br><br>end;<br><br>procedure TForm1.Button2Click(Sender: TObject);<br>begin<br>&nbsp; close;<br>end;<br><br>procedure TForm1.Button3Click(Sender: TObject);<br>begin<br>ADOQuery1.ConnectionString :='Provider=Microsoft.Jet.OLEDB.4.0;'+<br>'Data Source='+exlpath+<br>';Extended Properties="Excel 8.0;Persist Security Info=False;HDR=YES;IMEX=1"';<br>ADOTable1.Active :=False;<br>try<br>&nbsp; ADOConnection1.ConnectionString :='Provider=SQLOLEDB.1;' +<br>&nbsp; 'Integrated Security=SSPI;Persist Security Info=False;' +<br>&nbsp; 'Initial Catalog=master;Data Source=NSHARP-SMAXF';<br>&nbsp; ADOConnection1.Connected := true;<br>try<br>with ADOQuery1 do begin<br>ADOQuery1.Close;<br>ADOQuery1.SQL.Clear;<br>sSql :='Insert into 客户基本资料表(单位名称,通信地址)'+<br>&nbsp; &nbsp; &nbsp; &nbsp;'select 客户名称,客户地址 from [exlpath$]'+<br>&nbsp; &nbsp; &nbsp; &nbsp;'Where [exlpath$].客户地址 not in'+<br>&nbsp; &nbsp; &nbsp; &nbsp;'(select 单位名称 from 客户基本资料表)';<br>ADOQuery1.Parameters.Clear;<br>ADOQuery1.ParamCheck := false;<br>ADOQuery1.SQL.Text := sSql;<br>ADOQuery1.Execsql;<br>MessageBox(GetActiveWindow(), '导入EXCEL成功!', '提示', MB_OK +<br>MB_ICONWARNING);<br>exit;<br>ADOTable1.Active :=True;<br>end;<br>except<br>MessageBox(GetActiveWindow(), '客户名称不存在,请执行下一步!', '提示', MB_OK +<br>MB_ICONWARNING);<br>end;<br>&nbsp;except<br>&nbsp; MessageBox(GetActiveWindow(), '连接SQL Sever失败!', '警告', MB_OK +<br>&nbsp; &nbsp; &nbsp; MB_ICONWARNING);<br>&nbsp; end;<br>end;<br><br>procedure TForm1.Button4Click(Sender: TObject);<br>begin<br>ADOQuery2.ConnectionString :='Provider=Microsoft.Jet.OLEDB.4.0;'+<br>'Data Source='+exlpath+<br>';Extended Properties="Excel 8.0;Persist Security Info=False;HDR=YES;IMEX=1"';<br>ADOTable1.Active :=False;<br>try<br>with ADOQuery2 do begin<br>ADOQuery2.Close;<br>ADOQuery2.SQL.Clear;<br>qSql :='Insert into 税控导入资料(客户名称,客户地址电话)'+<br>&nbsp; &nbsp; &nbsp; &nbsp;'select 客户名称,客户地址 from [exlpath$]'+<br>&nbsp; &nbsp; &nbsp; &nbsp;'Where [exlpath$].客户地址 not in'+<br>&nbsp; &nbsp; &nbsp; &nbsp;'(select 客户名称 from 税控导入资)';<br>ADOQuery2.Parameters.Clear;<br>ADOQuery2.ParamCheck := false;<br>ADOQuery2.SQL.Text := qSql;<br>ADOQuery2.Execsql;<br>MessageBox(GetActiveWindow(), '导入EXCEL成功!', '提示', MB_OK +<br>MB_ICONWARNING);<br>exit;<br>ADOTable2.Active :=True;<br>end;<br>except<br>MessageBox(GetActiveWindow(), '客户存在,导入失败!', '提示', MB_OK +<br>MB_ICONWARNING);<br>end;<br>end;<br><br>end.<br><br><br>附:表2,表3(都在SQL Sever中先创建)<br>SET ANSI_NULLS ON<br>GO<br>SET QUOTED_IDENTIFIER ON<br>GO<br>SET ANSI_PADDING ON<br>GO<br>CREATE TABLE [dbo].[客户基本资料表](<br> [系统编号] [int] IDENTITY(1,1) NOT NULL,<br> [单位级别代码] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [单位名称] [varchar](255) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [单位电话] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,<br> [所在省份代码] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [所在城市代码] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [邮政编码] [varchar](6) COLLATE Chinese_PRC_CI_AS NULL,<br> [通信地址] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,<br> [公司网址] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,<br> [公司类型代码] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [公司性质代码] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [所在行业代码] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [经营品牌] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,<br> [行业地位] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,<br> [其它描述] [text] COLLATE Chinese_PRC_CI_AS NULL,<br> [录入日期] [datetime] NULL,<br> [录入人员代码] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [公司传真] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,<br> [月开票量] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,<br> [可能购买的产品] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,<br> [最后跟踪时间] [datetime] NULL,<br> [签约标志] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,<br>&nbsp;CONSTRAINT [PK_客户基本资料表] PRIMARY KEY CLUSTERED <br>(<br> [系统编号] ASC<br>) ON [PRIMARY]<br>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]<br><br>GO<br>SET ANSI_PADDING OFF<br><br><br><br>CREATE TABLE [dbo].[税控导入资料](<br> [客户编码] [int] IDENTITY(1,1) NOT NULL,<br> [客户名称] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,<br> [客户地址电话] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,<br>&nbsp;CONSTRAINT [PK_税控导入资料] PRIMARY KEY CLUSTERED <br>(<br> [客户编码] ASC<br>) ON [PRIMARY]<br>) ON [PRIMARY]<br><br>excel表(表1)<br>客户代码 客户名称 客户税号 客户地址 客户开户银行账号 企业税号 自定义字段1 自定义字段2 自定义字段3 自定义字段4 自定义字段5<br>001 广州市先一商贸在限公司 440102724823977 广州市东山区淘金路98号202号 &nbsp;83489401 广州市商业银行海幢支行286-8001514-90 44010175559533X <br>003 广州市东庄世纪联华超市有限公司 440106747555856 广州市天河区东莞庄路38号九洲文化家园B1栋 &nbsp;61366166 工商银行广州市花城支行3602028509200154565 44010175559533X <br>005 PKA_非KA客户 44010175559533X <br>1 (货物名称详见货物清单) 44010175559533X <br>101011 广州市龙涛货仓有限公司 440106278743466 广州市广汕公路龙洞税务所办公楼87031056 工商银行麒麟岗办事处130-0248-0009423 44010175559533X <br>101018 广州市宏丽有限公司 440111725007402 广州市白云区机场路1111号86432848 中行广州市珠江支行8020-08573908091001 44010175559533X 广州市宏丽有限公司 <br>101054 广州市嘉福百货有限公司 440106618698850 广州市广汕路自编168号大楼首层之六87041678 广州商行福利支行0019-898-2-803035703 44010175559533X <br>101066 广东胜佳超市有限公司 440104739863429 广州市越秀区小北路52号四楼83516856 广州市商业银行恒福支行800051757402016 44010175559533X <br>004 OTH_零散客户 44010175559533X <br>006 RKA_地区性KA客户 44010175559533X <br>101003 南方大厦西城广客隆 440103191143265 "广州市环市西路89号(首层和地下层)<br>86502328-102" 中行站西办00036138091001 44010175559533X <br>101017 广州市好又多(广源)百货商业广场有限公司 440111618479463 广州市广源新村景泰直街33号86386419 招行广州天河支行0183439210001 44010175559533X <br>101023 广州市经济村货仓商场有限公司 440111708386551 广州市白云区广花三路29号86631592 市建行广花三路办事处987-2730161-85 44010175559533X <br>101049 广州市白云区乔一综合平价超市 440111716306734 广州市石井同德鹅掌垣村口综合楼86483840 中国银行芳村支行272-04395608091001 44010175559533X <br>101056 广州致美斋经营发展有限公司 440111190515557 广州市白云区三元里大道828号86629520 建行广花三办事处44001491103050454395 44010175559533X <br><br>麻烦帮我解决一下,我QQ237683327,可以随时找我
 
程序并不能解决使用中的所有未知问题。建议,编程之外,改由SQL SERVER查询命令 处理。<br>select * from A AS a left outer join B AS b on a.X=b.Y &nbsp;order by NO.<br>不知可否。见笑!
 

Similar threads

S
回复
0
查看
3K
SUNSTONE的Delphi笔记
S
S
回复
0
查看
2K
SUNSTONE的Delphi笔记
S
S
回复
0
查看
825
SUNSTONE的Delphi笔记
S
I
回复
0
查看
874
import
I
顶部