双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

SQL SERVER 定时更新 EXCEL 文档

2017-09-14 09:58:16 1.6k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

我不叫康师傅墨衍会员 · AI 创作全网分发关注 + 加入社区

Wincc报表教程(SQL数据库的建立，wincc在数据库中保存和查询数据，调用Excel模板把数据保存到指定的位置和打印功能) 在工业生产中很多设备的数据是需要记录的，例如产能、所损耗的电能等都需要定时的记录，作为分析企业生产运营情况的依据。本文以WinCC V7.5 SP1为例，介绍WinCC如何实现设备运行数据记录并通过报表的形式展现统计数据，如图1所示: 1.2 主要功能可以实现如下功能：自动记录各设备的运行数据自动统计各设备的运行时间按设备、日期呈现报表按日期和设备编号以EXCEL表格保存数据可打印存有设备运行数据的表格本文档所使用的各软件版本如下：操作系统版本：Window10 专业版 WinCC 版本: 阅读详情

现在有一个项目需要我每小时向我自己本机上的一个共享文件夹中的一个excel进行更新。要求不能创建新的文档，只能更新Sheet3$中的数据。

我遇到的困难是：
1. 尝试制作SSIS直接向其中插入新的数据。结果是旧的数据不覆盖，无限叠加。

2. 使用Openrowset直接操作excel。但是excel文件本身不支持delete和truncate操作。如果是单调递增的数据库还好，做一下except检查和update更新就好。如果是大幅度波动的文件，会产生大量过期数据。

3. 尝试使用update为null值。结果发现excel不会自动把null值作为空行处理。但是如果你把所有行全部update成空，再打开excel文件，选中一下A1 保存关闭，excel里的table就干净了。非常诡异

请各位老哥指点，我该怎么完成我的需求，也就是定期完美更新excel里的一个工作表。
（步骤为，清空这个表内的数据，保留所有column，导入全新的数据，数据格式不发生改变）

如果真的要删除这个文件也行但是生成的excel文件名为B，表格名为Sheet3，这个不能发生改变。
我觉的这个需求大家是不是还挺经常遇到的？大家是怎么处理呢？
不考虑使用java或者.net语言进行操作，尽量仅使用sqlserver。如果需要使用powershell请上详细步骤，这个东西用的太少。
非常感谢

附：
这是我现在使用的语句，请参考路径于数据库名，表名。驱动使用ACE12.0 工作环境不支持JET4.0（发生不可预期的错误）



insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx',sheet3$)

select  * from expire_pool

except

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx','select * from [sheet3$]')

go

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx','select * from [Sheet3$]')

--我没有继续往下写，实际上，上方可以加入update语句，以studentid作为主键，进行其他栏目的更新检查，这个没有问题

...全文

Wincc报表教程(SQL数据库的建立，wincc在数据库中保存和查询数据，调用Excel模板把数据保存到指定的位置和打印功能) 在工业生产中很多设备的数据是需要记录的，例如产能、所损耗的电能等都需要定时的记录，作为分析企业生产运营情况的依据。图一本章主要是介绍WINCC7.5用VB来读写SQLserver数据库，网上有不少这类例子，实现功能的方法也很多，但是在运行过程中，或多或少的发现有些关键点没有提及到。... 阅读详情

我不叫康师傅

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

繁花尽流年 2017-09-19

打赏
举报

引用 14 楼 weixin_36463257 的回复:

[quote=引用 11 楼 zengertao 的回复:] [quote=引用 10 楼 weixin_36463257 的回复:] [quote=引用 9 楼 zengertao 的回复:] 老哥这个我是知道的，可是这个目标必须是已经存在的，假如我用file system task吧上一次的文件删掉，这里会报错，指定的路径不存在。这里不会自动创建excel文档的我已经试了很多次了

这个要用脚本任务自己在编辑器里写脚本创建，也可以写脚本删除excel现有数据。最简单的方法，直接备份一个空模板每次从其他目录复制过来。这个不需要写脚本，文件流里就能操作 [/quote]用这个方法完美解决了非常感谢[/quote] 解决了就结帖吧

我不叫康师傅 2017-09-19

打赏
举报

引用 11 楼 zengertao 的回复:

[quote=引用 10 楼 weixin_36463257 的回复:] [quote=引用 9 楼 zengertao 的回复:] 老哥这个我是知道的，可是这个目标必须是已经存在的，假如我用file system task吧上一次的文件删掉，这里会报错，指定的路径不存在。这里不会自动创建excel文档的我已经试了很多次了

这个要用脚本任务自己在编辑器里写脚本创建，也可以写脚本删除excel现有数据。最简单的方法，直接备份一个空模板每次从其他目录复制过来。这个不需要写脚本，文件流里就能操作 [/quote]用这个方法完美解决了非常感谢

繁花尽流年 2017-09-14

打赏
举报

引用 3 楼 weixin_36463257 的回复:

[quote=引用 2 楼 zengertao 的回复:]
文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去

文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote]
生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。

我不叫康师傅 2017-09-14

打赏
举报

引用 2 楼 zengertao 的回复:

文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去

文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗

繁花尽流年 2017-09-14

打赏
举报

文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去

我不叫康师傅 2017-09-14

打赏
举报

我有考虑过反向解决这个问题，也就是用excel直接连接SQLserver 的table。问题是excel这个倒霉孩子，自己不支持定时刷新table。使用vba的话，你把excel关了vba就失效，你把excel开着别的程序也不能load。很烦。。。。如果有大神知道反向解决怎么定时刷新table，还请赐教

OwenZeng_DBA 2017-09-14

打赏
举报

引用楼主 weixin_36463257 的回复:

现在有一个项目需要我每小时向我自己本机上的一个共享文件夹中的一个excel进行更新。要求不能创建新的文档，只能更新Sheet3$中的数据。我遇到的困难是： 1. 尝试制作SSIS直接向其中插入新的数据。结果是旧的数据不覆盖，无限叠加。 2. 使用Openrowset直接操作excel。但是excel文件本身不支持delete和truncate操作。如果是单调递增的数据库还好，做一下except检查和update更新就好。如果是大幅度波动的文件，会产生大量过期数据。 3. 尝试使用update为null值。结果发现excel不会自动把null值作为空行处理。但是如果你把所有行全部update成空，再打开excel文件，选中一下A1 保存关闭，excel里的table就干净了。非常诡异请各位老哥指点，我该怎么完成我的需求，也就是定期完美更新excel里的一个工作表。（步骤为，清空这个表内的数据，保留所有column，导入全新的数据，数据格式不发生改变）如果真的要删除这个文件也行但是生成的excel文件名为B，表格名为Sheet3，这个不能发生改变。我觉的这个需求大家是不是还挺经常遇到的？大家是怎么处理呢？不考虑使用java或者.net语言进行操作，尽量仅使用sqlserver。如果需要使用powershell请上详细步骤，这个东西用的太少。非常感谢附：这是我现在使用的语句，请参考路径于数据库名，表名。驱动使用ACE12.0 工作环境不支持JET4.0（发生不可预期的错误）
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx',sheet3$)
select  * from expire_pool
except
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx','select * from [sheet3$]')
go
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\fu\Desktop\B.xlsx','select * from [Sheet3$]')
--我没有继续往下写，实际上，上方可以加入update语句，以studentid作为主键，进行其他栏目的更新检查，这个没有问题

会写程序吗，如果会的话，写程序完成更合适一些吧

听雨停了 2017-09-14

打赏
举报


/*--数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
*/

/*--调用示例

p_exporttb @sqlstr='select * from 地区资料'
,@path='c:\',@fname='aa.xls',@sheetname='地区资料'
--*/

create proc p_exporttb
@sqlstr varchar(8000),--查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),--文件存放目录
@fname nvarchar(250),--文件名
@sheetname varchar(250)=''--要创建的工作表名,默认为文件名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测
if isnull(@fname,'')=''set @fname='temp2012.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')

--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
+';DATABASE='+@sql+'"'

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)

select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
,@sql=@sql+',['+a.name+'] '
+case
when b.name like '%char'
then case when a.length>255 then 'memo'
else 'text('+cast(a.length as varchar)+')' end
when b.name like '%int' or b.name='bit' then 'int'
when b.name like '%datetime' then 'datetime'
when b.name like '%money' then 'money'
when b.name like '%text' then 'memo'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)

if @@rowcount=0 return

select @sql='create table ['+@sheetname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

set @sql='drop table ['+@tbname+']'
exec(@sql)
return

lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go


--为了执行这个存储过程，你得先打开以下两个开关：
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO


--调用实例
p_exporttb @sqlstr='select * from [Demo_A]'
,@path='c:\',@fname='Export2xls_ByProc.xls',@sheetname='员工名称'

繁花尽流年 2017-09-14

打赏
举报

引用 10 楼 weixin_36463257 的回复:

[quote=引用 9 楼 zengertao 的回复:] 老哥这个我是知道的，可是这个目标必须是已经存在的，假如我用file system task吧上一次的文件删掉，这里会报错，指定的路径不存在。这里不会自动创建excel文档的我已经试了很多次了

这个要用脚本任务自己在编辑器里写脚本创建，也可以写脚本删除excel现有数据。最简单的方法，直接备份一个空模板每次从其他目录复制过来。这个不需要写脚本，文件流里就能操作

我不叫康师傅 2017-09-14

打赏
举报

[quote=引用 9 楼 zengertao 的回复:] 老哥这个我是知道的，可是这个目标必须是已经存在的，假如我用file system task吧上一次的文件删掉，这里会报错，指定的路径不存在。这里不会自动创建excel文档的我已经试了很多次了

繁花尽流年 2017-09-14

打赏
举报

引用 8 楼 weixin_36463257 的回复:

[quote=引用 7 楼 zengertao 的回复:]
[quote=引用 6 楼 zengertao 的回复:]
[quote=引用 5 楼 weixin_36463257 的回复:]
[quote=引用 4 楼 zengertao 的回复:]
[quote=引用 3 楼 weixin_36463257 的回复:]
[quote=引用 2 楼 zengertao 的回复:]
文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去

[/quote]
这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？
（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。
当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）
再次感谢您的帮助。[/quote]
创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛[/quote]
数据流里做的[/quote]

可是这个地方不能生成excel，只能选择现有的excel呀[/quote]

我不叫康师傅 2017-09-14

打赏
举报

引用 7 楼 zengertao 的回复:

[quote=引用 6 楼 zengertao 的回复:]
[quote=引用 5 楼 weixin_36463257 的回复:]
[quote=引用 4 楼 zengertao 的回复:]
[quote=引用 3 楼 weixin_36463257 的回复:]
[quote=引用 2 楼 zengertao 的回复:]
文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去

可是这个地方不能生成excel，只能选择现有的excel呀

繁花尽流年 2017-09-14

打赏
举报

引用 6 楼 zengertao 的回复:

[quote=引用 5 楼 weixin_36463257 的回复:] [quote=引用 4 楼 zengertao 的回复:] [quote=引用 3 楼 weixin_36463257 的回复:] [quote=引用 2 楼 zengertao 的回复:] 文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去

文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote] 生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。

[/quote] 这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）再次感谢您的帮助。[/quote] 创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛[/quote] 数据流里做的

繁花尽流年 2017-09-14

打赏
举报

引用 5 楼 weixin_36463257 的回复:

[quote=引用 4 楼 zengertao 的回复:] [quote=引用 3 楼 weixin_36463257 的回复:] [quote=引用 2 楼 zengertao 的回复:] 文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去

文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote] 生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。

[/quote] 这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）再次感谢您的帮助。[/quote] 创建文件直接把你sql里查到的结果集输出到excel目标里不就有了嘛

我不叫康师傅 2017-09-14

打赏
举报

引用 4 楼 zengertao 的回复:

[quote=引用 3 楼 weixin_36463257 的回复:] [quote=引用 2 楼 zengertao 的回复:] 文件不大的话，可以考虑移除源文件到备份文件夹，把全量更新后的数据重新生成一个同名excel放回去

文件不大老哥，可是具体该怎么做呢，方便的话能提供下代码吗[/quote] 生成数据就没啥好说的了，通过SSIS里的文件流就可以操作，移除，创建，复制excel你可以打开看一眼。

[/quote] 这个方法删除文件成功了，但是我没找到创建文件，请问一下想创建一个新的空excel，控制流要配置成什么形状呢？（需要一个空excel是因为，导出功能需要指定一个现有的excel，不能凭空创建。当然，采用BCP创建确实是可行的，但会有两个问题，第一没有列名，第二，想保持数据格式只能采用text，如果使用xls有一些特殊数据会被转换从日期格式，这个是excel一直以来的问题，比如某个家庭编号是MAY516，excel自动识别成5月什么什么，非常讨厌）再次感谢您的帮助。

✦双11狂欢大促✦

SQL SERVER 定时更新 EXCEL 文档

相关推荐

双11狂欢大促