急!急!急!下午急用!datagrid导出excel~解决就给分!高手帮忙写写!

Question

string str = "Server=116.32.14.11;User ID=rep_date_User;Password=RepSystem0909;Database=rep_date" ;
SqlConnection con = new SqlConnection(str);
con.Open();
string sql2="select count(*),left(subno,4),sum(lddrbal) lddrbal,sum(ldcrbal) ldcrbal,sum(tddrbal) tddrbal,sum(tdcrbal) tdcrbal from a11001_000 group by left(subno,4) order by left(subno,4)";
sqlDataAdapter sda = new SqlDataAdapter(sql2,con);
DataSet ds =new DataSet();
sda.Fill(ds,"a11001_000");
    dataGrid1.SetDataBinding(ds,"a11001_000");
已经写入了datagrid~求~如何导出到excel~
麻烦给我写一下~新手~而且急用~能运行~马上给分~

Answer 1

 ///////////////////Excel Access/////////////////////// string strCmd = string.Empty;
 conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No';" + "Data Source= " + @"C:\Documents and Settings\Administrator\桌面\test.xls");
 cmd = new OleDbCommand();
 cmd.Connection = conn;
 conn.Open();
 strCmd = "create Table [Sheet1](";
 foreach (DataColumn dc in ds.Tables[0].Columns)
 {
    strCmd += "[" + dc.ColumnName + "] nvarchar(20),";
 }
 strCmd = strCmd.Trim().Substring(0, strCmd.Length - 1);
 strCmd += ")";
 cmd.CommandText = strCmd; cmd.ExecuteNonQuery(); foreach (DataRow dr in ds.Tables[0].Rows)
 {
    if (dr.RowState != System.Data.DataRowState.Deleted)
    {
        strCmd = "insert into [Sheet1] values(";
        foreach (DataColumn dc in ds.Tables[0].Columns)
        {
           strCmd += "'" + dr[dc.ColumnName].ToString() + "',";
        }        strCmd = strCmd.Substring(0, strCmd.Length - 1);
        strCmd += ")";        cmd.CommandText = strCmd;        cmd.ExecuteNonQuery();
       }
 }    
conn.Close();   

Answer 2

Response.Buffer = false;
        Response.Charset = "GB2312";
        Response.AppendHeader("Content-Disposition", "attachment;filename=123.xls", System.Text.Encoding.UTF8));
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();        oStringWriter.Write(strtxt);        Response.Write(oStringWriter.ToString());
        oStringWriter.Close();
        Response.End();

Answer 3

private   void   OutExcel(DataGrid   dg   ,string   name,string   type)   
{
dg.Visible=true;   
Response.Clear();     
Response.Buffer=   true;     
Response.Charset="GB2312";   
Response.AppendHeader("Content-Disposition",name);   
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");   
Response.ContentType   =   type;   
dg.EnableViewState   =   false;   
System.IO.StringWriter   oStringWriter   =   new   System.IO.StringWriter();     
System.Web.UI.HtmlTextWriter   oHtmlTextWriter   =   new   System.Web.UI.HtmlTextWriter(oStringWriter);   
dg.RenderControl(oHtmlTextWriter);     
Response.Write(oStringWriter.ToString());   
Response.End();   
}
this.OutExcel(this.DataGrid1,"attachment;filename="名字".xls","application/ms-excel");//导出数据到excel中

Answer 4

偶写的东东奉献了命名空间这里改成偶的ID吧。ppwyw.DoExcel。哈哈，要用的话记得保留偶的版权啊。 楼主不给分我就不是人！！！！！！（这个昨天也在另一个人的贴子里发过，反现很多朋友需要这个东东，我自己做成一个小查块，随便到哪都可以用，只要引用dll就可以了。）方法CreateExcel(string   excelTempletePath,   int   startRow,   string   saveExcelPath,string   workSheet,   DataTable   sourceTable,   DataTable   configTable)参数说明 
excelTempletePath模板excel的路径，startRow为excel中插入数据的起始行，saveExcelPath文件保存路径，workSheet为excel中的工作表名，sourceTable从数据库中得到的数据源（要插入到excel的数据），configTable配置表。这里对configTable说明一下。configTable下设几个字段，dataColumn字段为与sourceTable中的列名（须保持一致），excelColumn字段为对应excel中的列的位置（1，2，8等表示是在excel中的第几列），另外你还可以设一个tableName字对应数据源sourceTable的表名。示列如下 id           tableName           dataColumn           excelColumn 
  1         sourceTable               name                  1 
  2         sourceTable             address                 9 
上面name为sourceTable表中的列名，1为excel中的列的位置 using   System; 
using   System.Collections.Generic; 
using   System.Text; 
using   System.Data; namespace   ppwyw.DoExcel 
{ 
        ///作者：ppwyw 
        ///摘要：生成Excel 
        ///日期：2007－11－22 
        public   static   class   EduceExcel 
        { 
                #region   CreateExcel用于导出数据生成Excel 
                public   static   string   CreateExcel(string   excelTempletePath,   int   startRow,   string   saveExcelPath,string   workSheet,   DataTable   sourceTable,   DataTable   configTable) 
                { 
                        //excel生成文件路径 
                        string   fileName   =   DateTime.Now.ToString("yyyyMMddhhmmss")   +   ".xls"; 
                        string   SaveExcelFileName   =   saveExcelPath   +   fileName; 
                        string   columnName;//设置列名 
                        //创建excel应用程序对象 
                        Excel.Application   excelApp   =   new   Excel.ApplicationClass(); 
                        try 
                        { 
                                //创建工作薄对象 
                                Excel.Workbook   excelWb   =   excelApp.Workbooks.Open(excelTempletePath,   0,   false,   5,   "",   "",   false,   Excel.XlPlatform.xlWindows,   "",   true,   false,   0,   true,   false,   false); 
                                //创建工作表对象 
                                Excel.Worksheet   excelSt   =   (Excel.Worksheet)excelWb.Sheets.get_Item(workSheet);                                 //循环结果集数据插入到工作表中 
                                for   (int   i   =   0;   i   <   sourceTable.Rows.Count;   i++) 
                                { 
                                        for   (int   j   =   0;   j   <   configTable.Rows.Count;   j++) 
                                        { 
                                                columnName   =   configTable.Rows[j]["dataColumn"].ToString(); 
                                                excelSt.Cells[startRow,   int.Parse(configTable.Rows[j]["excelColumn"].ToString())]   =   sourceTable.Rows[i][columnName]; 
                                        } 
                                        startRow++;//写下一行 
                                }                                 System.Reflection.Missing   oMissing   =   System.Reflection.Missing.Value;//定义一个使用缺省参数的对象 
                                //保存文件 
                                excelWb.SaveAs(SaveExcelFileName,   oMissing,   oMissing,   oMissing,   oMissing,   oMissing,   Excel.XlSaveAsAccessMode.xlShared,   oMissing,   oMissing,   oMissing,   oMissing,   oMissing); 
                        } 
                        catch   (Exception   ex) 
                        { 
                                fileName   =   ""; 
                                System.Console.Write(ex); 
                        } 
                        finally 
                        { 
                                sourceTable.Dispose(); 
                                configTable.Dispose(); 
                                //停止excel应用程序 
                                excelApp.Quit(); 
                                //释放资源 
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); 
                                System.GC.Collect();//强制垃圾回收 
                        } 
                        return   fileName; 
                } 
                #endregion 
        } 
} 下面放一个对上面的应用吧 
        protected   void   bt_educeExcel_Click(object   sender,   EventArgs   e) 
        { 
                SqlConnection   con   =   new   SqlConnection(ConfigurationManager.AppSettings["Str_connectSql"]); 
                con.Open(); 
                SqlCommand   sc   =   new   SqlCommand("select   row_number()   over(order   by   poreceivedt   desc)   as   [index],*   from   Report.BOReport   where   issuedt   between   '"   +   tb_start.Text   +   "'   and   '"   +   tb_end.Text   +   "'",   con); 
                SqlCommand   sc2   =   new   SqlCommand("select   *   from   exportconfig   where   reportName='Report.BOReport'",   con); 
                SqlDataAdapter   sda   =   new   SqlDataAdapter(sc); 
                SqlDataAdapter   sda2   =   new   SqlDataAdapter(sc2); 
                DataTable   dt   =   new   DataTable(); 
                DataTable   dt2   =   new   DataTable(); 
                sda.Fill(dt); 
                sda2.Fill(dt2); 
                con.Close(); 
                if   (dt.Rows.Count   >   0) 
                { 
                        string   fileName   =   ppwyw.DoExcel.EduceExcel.CreateExcel(@"D:\Asp.net\My_Demo\Web\sourceDataTable.xls",   3,   @"D:\Asp.net\My_Demo\Web\Download\",   @"Sheet1",   dt,   dt2); 
                        if   (fileName!="") 
                        { 
                                lb_message.Text   =   "Success!"; 
                                hl_downExcel.Visible   =   true; 
                                hl_downExcel.NavigateUrl   =   "http://localhost:2192/Web/Download/"+fileName; 
                        } 
                        else 
                        { 
                                lb_message.Text   =   "Defeat!"; 
                                hl_downExcel.NavigateUrl   =   ""; 
                                hl_downExcel.Visible   =   false; 
                        } 
                }   else 
                { 
                        lb_message.Text   =   "Sorry!Not   found   Data!"; 
                        GridView1.DataSource   =   null; 
                        GridView1.DataBind(); 
                        hl_downExcel.NavigateUrl   =   ""; 
                        hl_downExcel.Visible   =   false; 
                } 
        }