/*
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;