使用sql查询从sql表转换为excel [英] Convert from sql table to excel using sql query

问题描述

嗨


i希望从sql表转换为excelusing sql查询...我使用下面的查询..运行没有任何错误。但它没有生成excel文件。

Hi
i want to convert from sql table to excelusing sql query...i used the below query..its run without any errors.But its not generating the excel file.

DECLARE @cmd VARCHAR(255)
SET @cmd = 'bcp select * from TESTCONTROL where COUNTERID =1 out D:\Downloads\testing.xls -U sa -P sql123 -c'
Exec xp_cmdshell @cmd

EXEC sp_configure 'xp_cmdshell', 1

推荐答案

你可以使用SSIS（SQL Server Integration Services）程序包执行此操作，该程序包可自动执行整个导出到Excel任务。



然后使用SQL Server代理作业部署该程序包。这是我发现的更整洁更干净的解决方案。



问候，

Bhagyesh

Hi,

You can do this operation with SSIS (SQL Server Integration Services) package, which automate the whole Export to Excel task.

Then deploy that package using SQL Server Agent Jobs. This is a more neat and clean solution as I found.

Regards,
Bhagyesh

参考此代码，

private void GetData（）

{

DataTable dtTable = new DataTable（）;

SqlConnection conn = new SqlConnection（connString）;

conn.Open（）;

DataTable dt = new DataTable（）;

SqlCommand cmd = new SqlCommand（你的选择查询，conn）;

SqlDataAdapter daTable = new SqlDataAdapter（cmd）;

daTable.Fill（dt）;

string attachment =attachment; filename = flename.xls;

// Response.ClearContent（）;

Response.AddHeader（content-disposition，附件）;

Response.ContentType =application / vnd.ms-excel;

string tab =;

foreach（dt.Columns中的DataColumn dc）

{

Response.Write（tab + dc.ColumnName） ;

tab =\t;

}

Response.Write（\ n）;



int i;

foreach（DataRow dr in dt.Rows）

{

tab = ;

for（i = 0;我< dt.Columns.Count; i ++）

{

Response.Write（tab + dr [i] .ToString（））;

tab =\ t ;

}

Response.Write（\ n）;

}

Response.End（ ）;

}

Ref this code,
private void GetData()
{
DataTable dtTable = new DataTable();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("Your select query", conn);
SqlDataAdapter daTable = new SqlDataAdapter(cmd);
daTable.Fill(dt);
string attachment = "attachment; filename=flename.xls";
// Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");

int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}

这篇关于使用sql查询从sql表转换为excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！