asp.net中datagrid使用总结二

将DATAGRID中的数据导出到EXCEL中
将DATAGRID中的数据导出到EXCEL中
1、查询出数据绑定到datagrid中
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionStringlocal"]);
SqlDataAdapter myCommand = new SqlDataAdapter("D_CustomerNew_searchsms", myConnection);
myCommand.SelectCommand.CommandTimeout=60;

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@oa_id", SqlDbType.NVarChar,10));
myCommand.SelectCommand.Parameters["@oa_id"].Value = foa_id;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@returninfo", SqlDbType.NVarChar,200));
myCommand.SelectCommand.Parameters["@returninfo"].Direction = ParameterDirection.Output;

DataSet ds = new DataSet();
myCommand.Fill(ds,"searchbill1");

MyDataGrid.DataSource=ds.Tables["searchbill1"].DefaultView;
MyDataGrid.DataBind();

ToExcel(MyDataGrid);
myConnection.Close();




2、导出EXCEL
public void ToExcel(System.Web.UI.Control ctl)
{
Response.Clear();
DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=zhixiaoCustomer"+Convert.ToString(currentTime.Year)+"_"+Convert.ToString(currentTime.Month)+"_"+Convert.ToString(currentTime.Day)+".xls"); //定义输出文件和文件名

//HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+mtitle+".xls"); //定义输出文件和文件名
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文，解决乱码的关键，如果不行就改为UTF8
//HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 可以是其他类型application/ms-word 　　 application/ms-txt 　　 application/ms-html

this.EnableViewState = false;

System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
this.ClearControls(MyDataGrid);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
private void ClearControls(Control control)
{
for (int i=control.Controls.Count -1; i>=0; i--)
{
ClearControls(control.Controls[i]);
}

if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);
}
catch

{

}

control.Parent.Controls.Remove(control);
}

else

if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
return;
}