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

[shuiying825074]

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~
麻烦给我写一下~新手~而且急用~能运行~马上给分~

[jiatong1981]

 ///////////////////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();   

[dinons]

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();

[asge8900]

楼上的可以可以添加类库

[tongling15]

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中

[ppwyw]

偶写的东东奉献了命名空间这里改成偶的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;
}
}

[robert126]

mark

[zjsyw]

mark