导出SQLServer数据到Excel文件中

转自http://www.cnblogs.com/kevin/archive/2004/09/21/45351.html

 从DataGrids中导出数据到Excel

DataSet结果导出到Excel

Export DataSets to Excel...

导出到Excel的四种方法

SQL SERVER 与ACCESS、EXCEL的数据转换（Link0 ， Link1 ， Link2 ， Link3 。。。呵呵，实在不知出自何处了）——不过确是好贴，

其间提到转换SQLServer到Excel时：

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa-Ppassword'

使用SQLServer自带的bcp命令——应该算是第五种方法了。

bcp 表名 out 文件名 –c –q –S”服务器名” –U”用户名” –P”密码”

bcp ”select 语句” queryout 文件名 –c –q –S”服务器名” –U”用户名” –P”密码”

 

我只是来把“查询条件”加上，最后组成一个；

EXEC master..xp_cmdshell 'bcp "select * from hrca.dbo.v_k_EmployeeOutput  where 公司=''产业集团'' and 部门=''总裁办'' and 是否在职=''Yes''" queryout C:\HRCA\PrintFile\3f162a34-09c6-415b-8e2a-9acf08697f39.xls -c -q -S"CQUPT-KEVIN" -U"sa" -P"sa"'

需要注意的一点不过是''产业集团''等条件要加两个单引号。

“如果字符串包含单引号，则需要在单引号前再增加一个单引号”——语出SQLServer帮助文档“QUOTED_IDENTIFIER”索引。

我的代码：

private void btnEmpOutput_Click(object sender, System.EventArgs e)

         {

              string connectiongStr = System.Configuration.ConfigurationSettings.AppSettings["MyConnection"];

              string serverName = System.Configuration.ConfigurationSettings.AppSettings["ServerName"];

              string passWord = System.Configuration.ConfigurationSettings.AppSettings["Password"];

              string userId = System.Configuration.ConfigurationSettings.AppSettings["UserID"];

              string dbName = System.Configuration.ConfigurationSettings.AppSettings["DatabaseName"];

 

              string physicPath = System.Configuration.ConfigurationSettings.AppSettings["PrintFileTempPath"];

              if(!System.IO.Directory.Exists(physicPath))

              {

                   System.IO.Directory.CreateDirectory(physicPath);

              }

              string fileName = Guid.NewGuid() + ".xls";

 

              string genExcelComm = "EXEC master..xp_cmdshell \'bcp \"select * from "+string.Concat(dbName,".dbo.v_k_EmployeeOutput ");

              switch(this.drop_Org.SelectedValue)

              {

                   case "1":

                       // 查询部门在职员工列表；

                       genExcelComm += " where 公司=''"+this.txtOLabel1.Text+"'' and 部门=''"+this.txtOLabel2.Text+"'' and 是否在职=''Yes''";

                       break;

                   case "2":

                       // 查询公司在职员工列表；

                       genExcelComm += " where 公司=''"+this.txtOLabel1.Text+"'' and 是否在职=''Yes''";

 

                       break;

                   case "3":

                       // 查询部门所有员工列表；

                       genExcelComm += " where 公司=''"+this.txtOLabel1.Text+"'' and 部门=''"+this.txtOLabel2.Text+"''";

                       break;

                   case "4":

                       // 查询公司所有员工列表；

                       genExcelComm += " where 公司=''"+this.txtOLabel1.Text+"''";

                       break;

              }

              genExcelComm += "\" queryout "+ physicPath + fileName;

              genExcelComm += " -c -q -S\""+serverName+"\" -U\""+userId+"\" -P\""+passWord+"\"'";

             

              SqlConnection conn = new SqlConnection(connectiongStr);

              SqlCommand command = new SqlCommand(genExcelComm);

              command.Connection = conn;

              try

              {

                   if(conn.State != ConnectionState.Open)

                       conn.Open();

                   command.ExecuteNonQuery();

              }

              catch(Exception ex)

              {

                   hrca.BusinessLayer.ErrorHandler.Handle(ex.ToString());

                   this.Message.Text = "导出员工失败。";

                   return;

              }

              finally

              {

                   if(conn.State != ConnectionState.Closed)

                       conn.Close();

                   command.Dispose();

              }

 

              HttpResponse response = HttpContext.Current.Response;

              response.Clear();

              response.WriteFile( physicPath + fileName);

              string httpHeader="attachment;filename="+fileName;

              response.AppendHeader("Content-Disposition", httpHeader);

              response.Flush();

 

              try

              {

                   System.IO.File.Delete( physicPath + fileName);//删除临时文件

              }

              catch(Exception)

              {

                   // do nothing

              }

              response.End();

         }

转载于:https://www.cnblogs.com/yaojunyi/archive/2012/06/27/2565398.html