从SQL Server导出数据到Excel无法正常工作 [英] Exporting data to excel from sql server is not working

问题描述

我正在使用以下代码将数据导出到excel文件.当excel文件为空白时，这工作正常.但是，当我将字段更新为null时，插入查询将成功执行，但是excel显示为空白.

I am using the below code to export data to excel file. This is working fine when the excel file is blank. But when I update the filed to null then the insert query is executed successfully but the excel is showing blank.

update openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 
'select Column1,Column2,Column3 FROM [Sheet1$]')
set  Column1=null,Column2=null,Column3=null


insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 'SELECT * FROM [Sheet1$]') 
select Column1,Column2,Column3 from table_Name 

我想先将文件设置为空白，然后再写入.

I want set blank the file first before writing to that.

请帮助.

推荐答案

根据我在网上阅读的内容， openrowset 是只读的，因此您需要使用 OPENDATESOURCE .尝试这样的事情.

From what I've read online, openrowset is read only so you need to use OPENDATESOURCE instead. Try something like this.

UPDATE OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=E:\...\YourExcelFile.xlsx;;Extended Properties=Excel 12.0')...[Sheet1$]
    SET Column1=null,Column2=null,Column3=null


INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=E:\...\YourExcelFile.xlsx;;Extended Properties=Excel 12.0')...[Sheet1$]
   SELECT Column1,Column2,Column3 from table_Name 

这篇关于从SQL Server导出数据到Excel无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！