作者:乔山办公网日期:
返回目录:excel表格制作
你就是要写个存储过程 把执行结果保存在xls里吧 你要传入什么东西啊
可以给个需求 还是随便
楼主这个是我自己写的SQL导入execl功能
1创建数据库e79fa5e98193e78988e69d83339
USE [Report]GO
/****** Object: Table [dbo].[BookInfo] Script Date: 11/29/2011 20:52:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BookInfo]') AND type in (N'U'))
DROP TABLE [dbo].[BookInfo]
GO
USE [Report]GO
/****** Object: Table [dbo].[BookInfo] Script Date: 11/29/2011 20:52:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ONGO
SET ANSI_PADDING ONGO
CREATE TABLE [dbo].[BookInfo](
[省份] [varchar](50) NULL,
[市] [varchar](50) NULL,
[县] [varchar](50) NULL,
[社区名字] [varchar](50) NULL,
[管理员名字] [varchar](50) NULL,
[手机号码] [varchar](50) NULL,
[会员数] int NULL,
[录入名字] [varchar](50) NULL,
[录入时间] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFFGO
2个步骤
--开启导入exec程序
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--改你所要execl的地址 Sheet2$ 看你的数据在哪里
insert into BookInfo
SELECT * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0', 'EXCEL 5.0;HDR=YES;IMEX=2;DATABASE=E:\统计数据.xls'
,'SELECT * FROM [Sheet2$]')
--关闭导入exec程序
--使用完成后,关闭Ad Hoc Distributed Queries:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
select * from BookInfo --查询所有
--获取你所要的数据
select 省份,count(省份),sum(会员数) as 会员数 from BookInfo group by 省份
Sub Test()
'工具->引用->Microsoft ActiveX DataObjects 2.0
'定义连接对7a686964616fe59b9ee7ad94365象
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'连接字符串
strcn = "Driver=sql server;Server=服务器;database=数据库;uid=sa;pwd=密码"
cnn.Open strcn
Dim D1 As Date '开始日期
Dim D2 As Date '结束日期
D1 = Range("B2").Text
D2 = Range("B3").Text
If IsDate(D1) And IsDate(D2) Then
rs.Open "sp_djcount '" & D1 & "','" & D2 & "'", strcn, 3, 1 '存储过程
rs.Open "Select * From 表 ", strcn, 3, 1 'sql语句
Range("A5").CopyFromRecordset rs
MsgBox "成功!!!", vbInformation + vbOKOnly, "温馨提示"
Else
MsgBox "请输入开始日期和截止日期", vbQuestion + vbOKOnly, "温馨提示"
End If
'关闭连接
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
先把你的数据导入到表temptable中
update oldtable
set Brand=(select brand from temptable o where
oldtable.Partno=o.Partno,oldtable.Quantity=o.Quantity,oldtable.UserId=o.Quantity ),
DateCode=(select DateCode from temptable o where
oldtable.Partno=o.Partno,oldtable.Quantity=o.Quantity,oldtable.UserId=o.Quantity ),
Price=(select Price from temptable o where
oldtable.Partno=o.Partno,oldtable.Quantity=o.Quantity,oldtable.UserId=o.Quantity ),
Package=(select Packagefrom temptable o where
oldtable.Partno=o.Partno,oldtable.Quantity=o.Quantity,oldtable.UserId=o.Quantity ),
Note=(select Note temptable o where
oldtable.Partno=o.Partno,oldtable.Quantity=o.Quantity,oldtable.UserId=o.Quantity )
where exists(
select *
from temptable o
where oldtable.Partno=o.Partno,oldtable.Quantity=o.Quantity,oldtable.UserId=o.Quantity
)
delete temptable t--注意是templtable 不要把以前的数据删e799bee5baa6e997aee7ad94e4b893e5b19e330除了,这里是删除--以前的数据
where exists(
select *
from oldtemp
where partno=t.Partno and
Quantity=t.Quantity and
UserId=t.UserId
)
--不存在的数据插入表中
insert into oldtable( UserId,PartNo,Brand,DateCode,Quantity,Price,Package,Note )
select
UserId,PartNo,Brand,DateCode,Quantity,Price,Package,Note
from temptable