将sqlserver2005中的表导出到excel中

[use_zj]

将sqlserver2005中的表导出到excel中，代码实现

[peilianhai]

读取数据到dataset
然后 再 建立excel对象
一行一行的写进去

[use_zj]

导出成功了，反过来导入怎么弄，给具体的代码

[use_zj]

来个大大帮帮忙

[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
也可读取数据到dataset,再打开EXCEL模板实现赋值
或绑定到gridview等控件导出
导入
private DataTable XlsToDataTable(String strpath)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strpath + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
return dt;
}

[use_zj]

如梦 导入没改成功 给个sqlserver版本的

[use_zj]

谁给个SqlServer的链接字符串的 ？？

[tjficcbw]

        Dim cstring As String = "User ID=sa;pwd=Sa;Initial Catalog=库名;Data Source=(local)"
        Dim con As New SqlClient.SqlConnection(cstring)

        Dim sql As String = "select * from manid"
        Dim myAdapter As New SqlClient.SqlDataAdapter(sql, con)
        Dim myDateset As New DataSet
        myAdapter.Fill(myDateset, "dbset")
        DataGridView1.DataSource = myDateset.Tables("dbset")

[lukeye]

Dim cstring As String = "User ID=sa;pwd=Sa;Initial Catalog=库名;Data Source=(local)"
        Dim con As New SqlClient.SqlConnection(cstring)

        Dim sql As String = "select * from manid"
        Dim myAdapter As New SqlClient.SqlDataAdapter(sql, con)
        Dim myDateset As New DataSet
        myAdapter.Fill(myDateset, "dbset")
        DataGridView1.DataSource = myDateset.Tables("dbset")

[use_zj]

是这段代码的SqlServer链接，
导入
private DataTable XlsToDataTable(String strpath)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strpath + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
return dt;
}