1,178
社区成员
发帖
与我相关
我的任务
分享
DECLARE @ExcelPath nvarchar(1024); --Excel文件路径
DECLARE @strErrorMessage VARCHAR(1000) ; --输出错误信息
DECLARE @hr INT;
DECLARE @objExcel INT;
DECLARE @objWorkBooks INT;
DECLARE @objWorkBook INT;
DECLARE @cmd NVARCHAR(4000);
SET @strErrorMessage = '';
set @ExcelPath='c:\ss.xls'
--创建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;