使用OPENROWSET将SQL Server导出到Excel

Question

我成功地使用以下语句将数据导出到Excel：

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\template.xls;', 
'SELECT * FROM [SheetName$]') 
select * from myTable

有没有标准的方法可以使用此模板指定一个新的Excel工作表名称，以便模板永远不会被写入，还是我必须想出一些解决方法？

根据人们的经验，最好的方法是什么？

- JohnIdol

---

哦，幸运的人。我已经花了2个小时尝试运行这个程序，并且遇到了各种错误。(使用2008SP2) - iDevlop

Answer 1

您需要使用动态SQL。 OPENROWSET等只允许参数为字面量。

DECLARE @myfile varchar(800)

SET @myfile = 'C:\template.xls'

EXEC ('
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
''Excel 8.0;Database=' + @myfile + ';'', 
''SELECT * FROM [SheetName$]'') 
select * from myTable
')

记住：路径是相对于 SQL Server 运行的位置。

- gbn

---

这很好 - 但我仍然必须首先将模板放在那里，因此我不能在SQL中想出名称，我必须先以编程方式创建电子表格。 - JohnIdol

啊，我明白了。不，你不能在T-SQL中以那种方式操作Excel。也许可以使用CLR存储过程？ - gbn

以前没有使用过CLR SPs - 但我猜应该很容易 - 有没有好的示例可以让我开始？ - JohnIdol

抱歉...我没有使用过它们。"SQLCLR"标签有31个相关问题。 - gbn

我来澄清一下，我们公司的数据库工程师说不行。 - gbn

我正在按照相同的步骤操作，但是出现了错误：“未注册OLE DB提供程序'Microsoft.Jet.OLEDB.12.0'。”我已经尝试更改OLEDB版本。 - Sunil Acharya

Answer 2

你不能先复制一份模板，然后将复制文件的文件名传递给OPENROWSET吗？

- Seth Ladd

Answer 3

您可以在一个位置使用模板，在另一个位置使用数据文件。 当您运行脚本时，它将删除旧文件并生成新的数据文件。

 EXEC xp_cmdshell 'del D:\template.xls'
 EXEC xp_cmdshell 'copy C:\template.xls D:\template.xls'

 INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
 'Excel 8.0;Database=D:\template.xls;', 
 'SELECT * FROM [SheetName$]') 
  SELECT * FROM myTable

- Tharindu