请问怎样把DataGrid保存成Excel文件？

/// <summary>
/// 导出DataGrid到Execl表单, DataGrid中不能包含模板列
/// </summary>
public static void ExportExcel(System.Web.UI.Page page, System.Web.UI.WebControls.DataGrid dgExcel)
{
page.Response.Clear();
page.Response.Buffer= true;
page.Response.AppendHeader("Content-Disposition","Attachment;fileName=Export.xls");
page.Response.ContentType = "application/vnd.ms-excel";
page.Response.Charset = "";
page.EnableViewState = false;
StringWriter oStringWriter = new StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
dgExcel.RenderControl(oHtmlTextWriter);
page.Response.Write(oStringWriter.ToString());
page.Response.End();
}

using System.Configuration; //须在Config中指定连接的数据库
using common_function; //声明 namespace
using System.Data .SqlClient; private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
BindGrid();
}
} private void BindGrid() { string sql_str="select * from repair_now where repair_sort_id=''";
MyDataGrid.DataSource =Public_Class.Get_Select_Table(sql_str); //调用公共类返回datatable
MyDataGrid.DataBind(); //导入Excel表时，先求数量
string sql_count="select count(*) from repair_now ";
string temp=Public_Class.Judge_Repeat(sql_count);
ViewState["count"]=temp; } private string Table_Name()
{
string tn="To_Excel";
return tn;
}
private void ToExcel(System.Web.UI.Control ctl,string Table_Name)
{
HttpContext.Current.Response.AppendHeader("Content- Disposition","attachment;filename="+""+Table_Name+".xls");
HttpContext.Current.Response.Charset ="gb-2312";
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default ;
HttpContext.Current.Response.ContentType ="application/ms-excel/ms- word";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctl.Page.EnableViewState =true;
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw); ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
} private void Button_Excel_Click(object sender, System.Web.UI.ImageClickEventArgs e)//倒入Excel按钮事件
{
MyDataGrid.PageSize=Convert.ToInt32(ViewState["count"].ToString());
BindGrid();
MyDataGrid.PagerStyle.Visible =false;
ToExcel(MyDataGrid,Table_Name());
} 以下是被调用公共类 Public_Class.cs using System.Data .SqlClient ;
using System.Configuration; public class Public_Class
{ private static string ConnectString = ConfigurationSettings.AppSettings["ConnectString"]; public static DataTable Get_Select_Table(string sql_str) //执行查询，返回DataTable
{
SqlConnection myConnection;
myConnection = new SqlConnection( ConnectString);
SqlDataAdapter myCommand = new SqlDataAdapter(sql_str, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "t1");
return ds.Tables ["t1"]; } public static string Judge_Repeat(string sql_str)
{ SqlConnection myConnection = new SqlConnection(ConnectString);
SqlCommand myCommand = new SqlCommand (sql_str,myConnection);
myCommand.Connection.Open();
SqlDataReader Dr;
Dr=myCommand.ExecuteReader();
try
{
Dr.Read();
return Dr[0].ToString (); }
catch(Exception err)
{
return "";
}
finally
{
Dr.Close();
myCommand.Connection.Close();
} } }

<%@ Page Language="VB" AutoEventWireup="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Drawing.Color" %><html>
   <script runat="server">
     dim MyConnection as OleDbConnection
     dim cmdSelect as OleDbCommand
     dim dtrReader as OleDbDataReader
      Sub Page_Load(sender As Object, e As EventArgs)
      MyConnection = New OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + Server.MapPath(".")+"/datagrid.mdb")
         If Not IsPostBack Then
            BindGrid()
         End If
      End Sub       Sub BindGrid()
      Dim MyCommand As OleDbDataAdapter = new OleDbDataAdapter("select * from [news]", MyConnection)
          Dim DS As DataSet = new DataSet()
          MyCommand.Fill(DS,"min")
          MyDataGrid.DataSource =DS.Tables("min").DefaultView
          MyDataGrid.DataBind()
      End Sub      Sub Button1_Click(sender As Object, e As System.EventArgs)
      MyDataGrid.SelectedItemStyle.BackColor=Color.white
      MyDataGrid.AlternatingItemStyle.BackColor=Color.white
      MyDataGrid.ItemStyle.BackColor=Color.white
  MyDataGrid.HeaderStyle.BackColor=Color.white
  MyDataGrid.HeaderStyle.ForeColor=Color.red          Response.ContentType = "application/vnd.ms-excel"
          Response.Charset = ""
          Me.EnableViewState = False
          Dim tw As New System.IO.StringWriter()
          Dim hw As New System.Web.UI.HtmlTextWriter(tw)
          MyDataGrid.RenderControl(hw)
          Response.Write(tw.ToString())
          Response.End()      End Sub
   </script>
<body>
   <form runat="server">
    DataGrid Custom Paging Example<BR>
<asp:Button id="button1" onclick="Button1_Click" text="Write To Excle File" runat="server"/>
<BR>
      <asp:datagrid id="MyDataGrid"
  runat="server"
  CellPadding="4"
  BackColor="White"
      BorderColor="#330033"
  BorderWidth="1px"
  BorderStyle="None"
      Font-Size="9pt"
  Font-Names="§ºÊ^">
      <SelectedItemStyle  BackColor="#FFCC66"></SelectedItemStyle>
      <AlternatingItemStyle BackColor="#FFCC99"></AlternatingItemStyle>
      <ItemStyle BackColor="White"></ItemStyle>
      <HeaderStyle Font-Bold="True" HorizontalAlign="Center"  ForeColor="#330033" BorderColor="Black" BackColor="#FF9900"></HeaderStyle>
      </asp:datagrid>
   </form>
</body>
</html>

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vbtchTopQuestionsAboutASPNETDataGridServerControl.asp

你在.cs文件中加入下面这个函数：public void ToExcel(System.Web.UI.Control ctl)
{
// HttpContext.Current.Response.Charset ="GB2312";
HttpContext.Current.Response.Charset ="";
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=money.xls");

HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctl.Page.EnableViewState =false;
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}然后再调用：ToExcel(datagrid1)

调试易

请问怎样把DataGrid保存成Excel文件？

解决方案 »