如何将SQL Server 2005中数据表导出为Excel格式？

如何将SQL Server 2005中数据表导出为Excel格式？
在回贴前请先自己试着检验，以免产生更多的问题。

12 个解决方案

#1

--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
select * from 表
--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:
--导出表的情况
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out "c:\test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'
--导出查询的情况
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'
说明.
c:\test.xls   为导入/导出的Excel文件名.
sheet1$       为Excel文件的工作表名,一般要加上$才能正常使用. 

#2

我是这样写的(已存在Excel文件）：
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=F:\2.xls',Sheet1$)　select * from us
结果提示“消息 102，级别 15，状态 1，第 1 行
'　' 附近有语法错误”。

这是哪儿出了问题呢？

#3

引用 2 楼 kunshanzhiyu 的回复:

我是这样写的(已存在Excel文件）：
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=F:\2.xls',Sheet1$)　select * from us
结果提示“消息 102，级别 15，状态 1，第 1 行
'　' 附近有语法错误”。

这是哪儿出了问题呢？

insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=F:\2.xls',Sheet1$)　select * from us 

你的SELECT 前面是全角空格,

insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=F:\2.xls',Sheet1$) select * from us

#4

按照你说的改了。
但又提示：“SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"”。
我使用sp_configure启动了它，接着我又把sp_configure这一句注释了，然后使用修改后的语句，它又提示：：“SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"”。
该怎么办呢？

#5

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'

EXEC master..xp_cmdshell 'bcp "SELECT id,name FROM tb" 
queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A) values (1)
启用外围配置器
EXEC sp_configure 'show advanced options',1
GO
reconfigure
GO
EXEC sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure
GO

#6

引用 5 楼 wuyq11 的回复:

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'

EXEC master..xp_cmdshell 'bcp "SELECT id,name FROM tb"
queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A) values (1)
启用外围配置器
EXEC sp_configure 'show advanced options',1
GO
reconfigure
GO
EXEC sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure
GO

就是这个了,呵呵

#7

您好！上面的问题解决了，但还会出现这个错:
消息 7399，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "MICROSOFT.JET.OLEDB.4.0" 报错。提供程序未给出有关错误的任何信息。
消息 7303，级别 16，状态 1，第 1 行
无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "MICROSOFT.JET.OLEDB.4.0" 的数据源对象。

#8

确定Excel文件和

#9

1）确定Excel文件和数据库在一个电脑上
2）确定Excel文件已经关闭
3）确定Excel文件路径是否正确

#10

至于你们这样吗？ SQLServer 与EXCEL本来就是同一家公司出的！ 为什么要这样做呢？  别忘了还有ODBC
数据源这一说！ excle 本身就可以建立数据源！ 这样SQLserver 里面的数据不自然的呈现出来了吗？

#11

我是这样的 把数据粘贴到文本文件上，然后再粘贴到excel上的，这样对应一般情况应该没什么问题的！

#12

 //Excel导出
    protected void Button2_Click(object sender, EventArgs e)
    {
        //数据源
        DataTable dtSearchData = new DataTable();
        DataColumn dc1 = new DataColumn("ID", Type.GetType("System.Int16"));
        DataColumn dc2 = new DataColumn("username", Type.GetType("System.String"));
        dtSearchData.Columns.Add(dc1);
        dtSearchData.Columns.Add(dc2);
        for (int i = 0; i < 10; i++)
        {
            DataRow dr = dtSearchData.NewRow();
            dr["ID"] = 10;
            dr["username"] = "娃娃";
            dtSearchData.Rows.Add(dr);
        }
        StringWriter sw = new StringWriter();
        sw.Write("<table cellpadding='1' cellspacing='1' border='1'>");
        sw.Write("<tr><td style='background-color:skyblue'>编号<td style='background-color:skyblue'>姓名</td></tr>");
        foreach (DataRow item in dtSearchData.Rows)
        {
            sw.Write("<tr><td>"
                   + Convert.ToInt32(item["ID"])+"</td><td>"
                   +item["username"].ToString()+"</td></tr>");
        }
        sw.Write("</table>");
      

        //打印Excel
        if (sw != null)
        {
            Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode("学生列表", System.Text.UTF8Encoding.UTF8) + ".xls");
            Response.ContentType = "application/ms-excel";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.Write((System.IO.StringWriter)sw);
            Response.End();
        }

    }