向EXCEl中写入数据时 提示 不能打开文件

Question

/*
Author      : 梁嘉辉
Date        : 2009-06-15
Function    : 分Sheet导出同一Excel工作薄
Comment     : 使用或转载请保留此信息
*/-------------------------------------------------------------------
--创建存储过程
-------------------------------------------------------------------USE tempdb
GO--创建Excel文件
CREATE PROC dbo.usp_CreateExcelFile
    @ExcelPath nvarchar(1024),                 --Excel文件路径
    @strErrorMessage VARCHAR(1000) OUTPUT      --输出错误信息
AS
    SET NOCOUNT ON;
    DECLARE @hr INT;
    DECLARE @objExcel INT;
    DECLARE @objWorkBooks INT;
    DECLARE @objWorkBook INT;
    DECLARE @cmd NVARCHAR(4000);
    SET @strErrorMessage = '';
    
    --创建Excel.Application对象
EXEC @hr = sp_OACreate 'Excel.Application',@objExcel OUTPUT;
IF @hr = 0
BEGIN
       --创建WorkBooks对象
   EXEC @hr = sp_OAGetProperty @objExcel,'Workbooks',@objWorkbooks OUTPUT;
   IF @hr = 0 
 BEGIN
             --使用Workbooks对象的Add添加一个Workbook
 EXEC @hr = sp_OAGetProperty @objWorkbooks,'Add',@objWorkBook OUTPUT;
 IF @hr = 0
   BEGIN
                   --使用SaveAs方法保存
   SET @cmd = 'SaveAs("' + @ExcelPath + '")'
   EXEC @hr = sp_OAMethod @objWorkBook,@cmd;
               
                   --关闭掉Workbook
   IF @hr = 0
   EXEC @hr = sp_OAMethod @objWorkBook,'Close';
   ELSE
   SET @strErrorMessage = '保存Excel文件失败!';
   END
 ELSE 
 SET @strErrorMessage = '添加工作薄失败!';
 END
   ELSE
 SET @strErrorMessage = '创建工作薄失败!';
END
ELSE
  SET @strErrorMessage = '创建Excel对象失败!'
    
    IF @hr = 0
    BEGIN
        EXEC @hr = sp_OAMethod @objExcel,'Quit';
    END
    
    --消除Excel对象
    
    IF @hr = 0
        EXEC @hr = sp_OADestroy @objWorkbooks;
        
IF @hr = 0 
    EXEC @hr = sp_OADestroy @objExcel;
GO--在Excel里添加Sheet
CREATE PROC dbo.usp_AddExcelSheet
    @ExcelPath nvarchar(1024),             --Excel文件路径
    @SheetName sysname,                    --Sheet名字
    @IndexCount INT,                       --一共要生成多少个Sheet
    @columns VARCHAR(1000),                --Sheet的列名,用逗号分隔
    @strErrorMessage VARCHAR(1000) OUTPUT
AS
    SET NOCOUNT ON
    DECLARE @hr INT;
    DECLARE @objExcel INT;
    DECLARE @objWorkBooks INT;
    DECLARE @objWorkBook INT;
    DECLARE @objSheets INT;
    DECLARE @objSheet INT;
    DECLARE @cmd NVARCHAR(4000);
    DECLARE @i INT;
    DECLARE @id INT;
    DECLARE @col VARCHAR(256);
    SET @strErrorMessage = '';    --创建Excel.Application对象
EXEC @hr = sp_OACreate 'Excel.Application',@objExcel OUTPUT;
IF @hr = 0
BEGIN
       --创建Workbooks对象
   EXEC @hr = sp_OAGetProperty @objExcel,'Workbooks',@objWorkbooks OUTPUT;
   IF @hr = 0 
 BEGIN
             --打开Excel文件
     SET @cmd = 'Open("' + @ExcelPath + '")'; 
 EXEC @hr = sp_OAMethod @objWorkbooks,@cmd,@objWorkBook OUTPUT;
 IF @hr = 0
   BEGIN
                   --得到Sheets对象
       EXEC @hr = sp_OAGetProperty @objWorkbook,'Sheets',@objSheets OUTPUT;
   SET @i = 1;
   
   --分解字段名
   DECLARE @tb TABLE(id int identity,col varchar(256));
   INSERT @tb(col)
       SELECT B.x.value('.','nvarchar(256)') AS col
       FROM (
           SELECT 
               CONVERT(XML,'<v>'+REPLACE(@columns,',','</v><v>')+'</v>') AS col
       ) AS A
            CROSS APPLY A.col.nodes('//v') AS B(x);
   
                   --循环多少个Sheet
   WHILE @i <= @IndexCount
     BEGIN
                         --添加Sheet
         EXEC @hr = sp_OAGetProperty @objSheets,'Add',@objSheet OUTPUT;
         SET @cmd = @SheetName + CASE WHEN @IndexCount = 1 
                                         THEN ''
                                      ELSE RTRIM(@i) END;
                         --设置Sheet的Name属性,形式为@SheetName + 序号
         EXEC @hr = sp_OASetProperty @objSheet,'Name',@cmd;
         
                         
         SET @id = (SELECT TOP(1) id FROM @tb ORDER BY id);
                         --循环所有的列,将Sheet的第一行设置为列号
         WHILE @id IS NOT NULL
           BEGIN
               SET @col = (SELECT col FROM @tb WHERE id=@id);
               SET @cmd = 'Range("' + CHAR(65-1+@id) + '1").value';
               EXEC @hr = sp_OASetProperty @objSheet,@cmd,@col;
               SET @id = (SELECT TOP(1) id FROM @tb WHERE id > @id ORDER BY id);
           END
         
         SET @i = @i +1;
         EXEC @hr = sp_OADestroy @objSheet; 
     END
   EXEC @hr = sp_OADestroy @objSheets;
   IF @hr = 0 
       EXEC @hr = sp_OAMethod @objWorkBook,'Save';
   
   IF @hr = 0
   EXEC @hr = sp_OAMethod @objWorkBook,'Close';
   ELSE
   SET @strErrorMessage = '保存Excel文件失败!';
   END
 ELSE 
 SET @strErrorMessage = '打开工作薄失败!';
 END
   ELSE
 SET @strErrorMessage = '创建工作薄对象失败!';
END
ELSE
SET @strErrorMessage = '创建Excel对象失败!'    IF @hr = 0
    BEGIN
        EXEC @hr = sp_OAMethod @objExcel,'Quit';
    END    --消除Excel对象
    IF @hr = 0
        EXEC @hr = sp_OADestroy @objWorkbooks;
    
IF @hr = 0 
    EXEC @hr = sp_OADestroy @objExcel;
GO

Answer 1

你那个EXCEL文件存在吗，还是加了密码的，或者已经打开了？

Answer 2

--导出Excel主存储过程
CREATE PROC dbo.usp_ExportTableToExcel
    @ExcelPath nvarchar(1024),         --Excel文件路径
    @DatabaseName sysname,             --导出Excel数据所在的数据库
    @TableName sysname,                --导出Excel数据的表
    @Columns nvarchar(4000) = N'*',    --导出哪些列,默认为所有列
    @whereAnd nvarchar(4000) = '',     --过滤条件,形式为: and col = 'xx' and col2 > 1
    @PageRecord INT = 65535,           --每个Sheet的记录数
    @IsCreate BIT = 1                  --1表示创建新的Excel文件,0表示在源Excel文件中追加Sheet
AS
    SET NOCOUNT ON;
    DECLARE @IsExcelExist INT;
    DECLARE @strErrorMessage VARCHAR(1000);
    DECLARE @cmd NVARCHAR(4000);
    
    
    --如果没有对@ExcelPath参数设置值,则退出
    IF @ExcelPath IS NULL OR @ExcelPath = ''
      BEGIN
          RAISERROR('必须设置Excel文件路径!',16,1);
          RETURN -1;
      END
    
    --判断数据库是否存在
    IF DB_ID(@DatabaseName) IS NULL
      BEGIN
          SET @cmd = '数据库' + @DatabaseName + '不存在!';
          RAISERROR(@cmd,16,1);
          RETURN -1;
      END
    
    --判断数据表是否存在
    SET @cmd =  @DatabaseName + '.dbo.' + @TableName; 
    IF OBJECT_ID(@cmd,'U') IS NULL
      BEGIN
          SET @cmd = '表' + @TableName + '不存在';
          RAISERROR(@cmd,16,1);
          RETURN -1;
      END
    
    --启用ole自动化和xp_cmdshell
    EXEC sp_configure 'show advanced options',1;
    RECONFIGURE WITH OVERRIDE;
    EXEC sp_configure 'Ole Automation Procedures',1;
    RECONFIGURE WITH OVERRIDE;
    EXEC sp_configure 'xp_cmdshell',1;
    RECONFIGURE WITH OVERRIDE;
    EXEC sp_configure 'Ad Hoc Distributed Queries',1;
    RECONFIGURE WITH OVERRIDE;
    
    --判断Excel文件是否存在
    EXEC xp_fileexist @ExcelPath,@IsExcelExist OUTPUT;
    
    --如果设置了创建新的Excel文件
    IF @IsCreate = 1 
      BEGIN
           --文件已存在,则先删除
           IF @IsExcelExist = 1
             BEGIN
                 SET @cmd = N'del ' + @ExcelPath ;
                 EXEC master.dbo.xp_cmdshell @cmd,NO_OUTPUT;
             END
               --创建Excel文件
           SET @strErrorMessage = '';
           EXEC dbo.usp_CreateExcelFile @ExcelPath,@strErrorMessage OUTPUT;
      END
    ELSE
      --不是创建新的Excel文件,则要判断文件是否存在
      BEGIN
          IF @IsExcelExist = 0
            BEGIN
                RAISERROR('文件不存在!',16,1);
                RETURN -1;
            END
      END    IF @strErrorMessage <> ''  --创建Excel.Workbook里出错
      BEGIN
         RAISERROR(@strErrorMessage,16,1);
         RETURN -1;
      END
      
    --分页,计算出一共有几页
    --页数=总记录数/每页记录数 + 如果总记录数%每页记录数<>0,则1
    DECLARE @RecordCount INT;
    SET @cmd = N'SELECT @RecordCount=COUNT(*) FROM ' + @DatabaseName + '.dbo.' + @TableName;
    SET @cmd = @cmd + ' WHERE 1 = 1 ' + @whereAnd;
    EXEC sp_executesql @cmd,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT;
    
    DECLARE @page INT;
    DECLARE @i INT;
    SET @page = @RecordCount / @PageRecord;
    SET @page = @page +  CASE WHEN @RecordCount % @PageRecord = 0 THEN 0 ELSE 1 END;
    
    --如果@Columns参数为*,表示查所以的列,则先通过系统视图得到所有列名
    IF @Columns = '*'
      BEGIN
          SET @Columns = '';
          SET @cmd = N'SELECT @columns = @columns + '','' + c.name
                       FROM ' + @DatabaseName + '.sys.tables AS t
                            JOIN ' + @DatabaseName + '.sys.columns AS c
                       ON t.object_id=c.object_id
                       WHERE t.name=''' + @TableName + '''';
          EXEC sp_executesql @cmd,N'@columns VARCHAR(1000) OUTPUT',@columns OUTPUT
          SET @Columns = STUFF(@Columns,1,1,'');
      END 
    
    SET @strErrorMessage = '';
    --添加Sheet
    EXEC dbo.usp_AddExcelSheet @ExcelPath,@TableName,@page,@Columns,@strErrorMessage OUTPUT;    IF @strErrorMessage <> ''  --添加Excel.Sheet时出错
      BEGIN
         RAISERROR(@strErrorMessage,16,1);
         RETURN -1;
      END    SET @i = 1;
    --循环每一页,将记录插入到该页的Sheet里    WHILE @i <= @page
      BEGIN
          SET @cmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OleDb.4.0'',''Excel 8.0;Database=' + @ExcelPath + ''',[' + @TableName 
                    + CASE WHEN @page = 1 THEN '' ELSE RTRIM(@i) END + '$])';
          SET @cmd = @cmd + ' SELECT ' + @Columns + ' 
                              FROM (
                                  SELECT '+@Columns + ',rowid=ROW_NUMBER() OVER(ORDER BY (SELECT 1))
                                  FROM ' + @DatabaseName + '.dbo.' + @TableName 
                               + ' WHERE 1 = 1 ' + @whereAnd + '
                              ) AS T
                              WHERE rowid BETWEEN ' + RTRIM((@i-1) * @pageRecord + 1) 
                                     + ' AND ' + RTRIM(@i * @pageRecord);
          EXEC(@cmd);
          
          SET @i = @i + 1;
      END
    RETURN 0
GO

Answer 3

--测试导出Excel--生成10万条测试数据CREATE TABLE tb(id INT IDENTITY,date DATETIME,dzbz NUMERIC(2,1),fssd NUMERIC(2,1),dzxh VARCHAR(6),ylxh INT,com VARCHAR(4))INSERT tb(date,dzbz,fssd,dzxh,ylxh,com)
    SELECT TOP(100000) --这里设置多少行
        DATEADD(hour,ROW_NUMBER() OVER(ORDER BY o.object_id)/24,'2009-05-01') AS Date,
        (ABS(CHECKSUM(NEWID())) % 5 + 4) / 10. AS dzbz,
        (ABS(CHECKSUM(NEWID())) % 3 + 12) / 10. AS fssd,
        'DZ_00' + RTRIM(CASE WHEN ROW_NUMBER() OVER(ORDER BY o.object_id) % 4 = 0 
                          THEN 4 ELSE ROW_NUMBER() OVER(ORDER BY o.object_id) % 4 END) AS dzxh,
        CASE WHEN ROW_NUMBER() OVER(ORDER BY o.object_id) % 2 = 0 THEN 1 ELSE 0 END AS ylxh,
        CASE WHEN ((ROW_NUMBER() OVER(ORDER BY o.object_id)-1) / 4 % 4) %2 = 0 
                THEN 'com1' ELSE 'com2' END AS com
    FROM sys.columns AS c
        CROSS JOIN sys.objects AS o
        CROSS JOIN sys.types AS t
--执行主存储过程,导出Excel
EXEC dbo.usp_ExportTableToExcel 
         N'G:\Book1.xls',         --导出的Excel文件路径
         'tempdb',                --表所在的数据库
         'tb',                    --要导出哪个表
         '*',                     --导出哪些列
         '',                      --过滤条件
         65535,                    --每个Sheet多少条记录
         1;                       --创建文件GO
--删除测试
DROP PROC dbo.usp_ExportTableToExcel,dbo.usp_CreateExcelFile,
         dbo.usp_AddExcelSheet;
DROP TABLE tb;

Answer 4

exec    @err=sp_oamethod @obj,'open',null,@constr
if    @err<>0 goto lberr select    @sql='',@fdlist=''
select    @sql='create table [異常報告] ([CD] int,[異常件数] int,[影響金額] int)',
    @fdlist='CD,異常件数,影響金額'exec    @err=sp_oamethod @obj,'execute',@out out,@sql
if    @err<>0 goto lberr 
没有Close掉呀...

Answer 5

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

Answer 6

问题可能出在创建Excel附近。比如：创建了，没有关闭

Answer 7

？
必须要Close吗？不写Close 我的代码也成功过，只在一台机器上

Answer 8

小梁，不行啊，加上exec    @err=sp_oamethod @obj,'close';下面的代码就没有办法执行了啊下面还有好多个
exec @err=sp_oamethod @obj,'execute',@out out,@sql目的是向EXCEL的多个SHEET中输入数据
而且 SHEET数量是动态的
declare @SN Nvarchar(20),@sqlstr Nvarchar(4000),@sql Nvarchar(4000)
declare @sheetname Nvarchar(30),@path Nvarchar(200),@fname Nvarchar(200),@constr Nvarchar(2000)
declare @fdlist Nvarchar(4000),@tbname sysname,@err int,@obj int,@out int,@src Nvarchar(255),@desc Nvarchar(255)set @path='D:\SalesOoO\'
set @fname=right('0000'+@SupplierCD,4)+'.xls'
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES;DATABASE='+@path+@fname+'"'if object_id('tempdb..#tb') is not null drop table #tb
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
begin
set @sql=convert(varchar(8),getdate(),112)+left(newid(),8) 
set @sql='ren '+@path+@fname+' '+@sql+@fname
exec ('exec master..xp_cmdshell '''+@sql+''',no_output')
endexec @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 select @sql='',@fdlist=''
select @sql='create table [異常報告] ([CD] int,[異常件数] int,[影響金額] int)',
@fdlist='CD,異常件数,影響金額'exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr 
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@path+@fname+''',''select * from [異常報告$]'')'exec ('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from [##t] ')if object_id('tempdb..##t') is not null drop table ##t
declare cur_SALES cursor 
        for 
   select SN from mst_sheetname order by IDopen cur_SALES
fetch next from cur_SALES  into @SN
while @@fetch_status=0
begin
set @sheetname=@SNset @sqlstr=
'select  発生計上月,発生伝票NO, 発生納品日, 発生店CD, 発生CD, 
発生納品数合計,発生原価合計, 発生売価合計, 発生財務計上日, 
回収伝票NO, 回収納品日, 回収店CD, 回収CD, 回収納品数合計, 
回収原価合計,回収売価合計,  回収締日, 差伝票NO, 差納品日, 差店CD, 
差ベンダーCD, 差納品数合計,差原価合計, 差売価合計,差計上日, 理由, 成否
from dbo.TB_発生回収誤差 with (nolock)
where 作表区分 = ''' +@SN+''''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 in('char','nchar','varchar','nvarchar') then 'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
from tempdb..syscolumns a 
left join 
tempdb..systypes b 
on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)select @sql='create table ['+@sheetname+']('+substring(@sql,2,4000)+')',@fdlist=substring(@fdlist,2,4000)exec @err=sp_oamethod @obj,'execute',@out out,@sqlset @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@path+@fname+''',''select * from ['+@sheetname+'$]'')'exec ('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+'] order by 発生財務計上日,発生納品日,回収納品日')set @sql='drop table ['+@tbname+']'exec (@sql)fetch next from cur_SALES  into @SNend 
close cur_SALES
deallocate cur_SALES-- print @path
-- print @fname-- lbexit:
-- select cast(@err as varbinary(4)) as ErrCode,@src as ErrSource,@desc as ErrDescription
-- select @sql,@constr,@fdlist
returnlberr:
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as ErrCode,@src as ErrSource,@desc as ErrDescription
select @sql,@constr,@fdlist
raiserror ('OAERROR',16,1)
return

Answer 9

哈哈我解决了多亏了你的colse每次
exec    @err=sp_oamethod @obj,'execute',@out out,@sql
后接一个
exec    @err=sp_oamethod @obj,'close'; 
就行了
多谢揭帖

Answer 10

打开了就要CLOSE..没什么好解释的.

Answer 11

阿。
原来是这样以前总是觉得有个地方不太对劲儿呢
因为有一台机器是可以执行成功的那台机器我仔细看了一下是因为 文件夹的安全属性里设置的单用户
这个单用户是   EVERYONE  哈哈
其他机器上除了EVERYONE还有SYSTEM , users 等等