CSDN博客

img smilnet

DataGrid导出为Excel

发表于2004/6/28 16:57:00  501人阅读

How it works

This main functionality to Export a datagrid from an ASP.Net Web Form to an Excel format is actually very simple. There are several solutions for this implementation and in this example we will convert the datagrid to excel format by manipulating the MIME type (media type or Content Type) of the Response. The RenderControl method available in the .Net Framework provides the server control content to an HtmlTextWriter which is subsequently written out to the Response Stream.

private void Button1_Click(object sender, System.EventArgs e)
{
//export to excel

Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

this.ClearControls(dg);
dg.RenderControl(oHtmlTextWriter);

Response.Write(oStringWriter.ToString());

Response.End();
}

Code Listing : Output the contents of the datagrid to Excel spreadsheet

And just one more detail

There's just one thing to take care of. A run-time error occurs if the DataGrid contains any controls other than the LiteralControl. This means that enabling Sorting, Paging or adding Template Columnns or Button columns to the datagrid can cause an error. There are several approaches to workaround this limitation. We will remove all the non-Literal controls in the DataGrid and replace the controls with a text representation , where possible. To do so, we will make use of Reflection. instead of querying each type of control and working out a replacement.
For all controls that have a SelectedItem property, we replace the control with the literal value of the SelectedItem property of the control. This covers most lists. For all controls that have a Text property, we replace the control with the literal value of the Text property of the control. This covers TextBox, Buttons, Button Columns, TemplateColumns. We make an exception only for TableCell controls. This takes care of most of the cases and you can add more checks and balances as required. The only drawback for this generalised formula is the order of the controls within a single cell could get changed.


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;
}

Code Listing : Output the contents of the datagrid to Excel spreadsheet

In our sample web form, we connect to the Sample Pubs SQL Server database and display the data from the Employees table. The sample datagrid uses paging and a dummy Edit Column.

Complete Code Listing

<%@ Page language="C#" Debug="true" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script Language="C#" runat="server">
private void Button1_Click(object sender, System.EventArgs e)
{
//export to excel

Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

this.ClearControls(dg);
dg.RenderControl(oHtmlTextWriter);

Response.Write(oStringWriter.ToString());

Response.End();
}

private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{

SqlConnection conn = new SqlConnection ("data source=(local);initial catalog=Northwind;Pwd=p@ssw0rd;User ID=sa");
SqlCommand cmd = new SqlCommand ("Select LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country from Employees", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
dg.DataSource = ds.Tables[0];
dg.DataBind();
}

}

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;
}
</script>
<html>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form id="frm" runat="server">
<asp:Button id="Button1" runat="server" Text="Export to Excel"
OnClick="Button1_Click"></asp:Button><BR>
<asp:Datagrid id="dg" runat="server" AutoGenerateColumns="True"
AllowSorting="true" AllowPaging="true"
CellPadding="3" PageSize=3>
<columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:LinkButton runat="server" CommandName="Edit"
CausesValidation="false" ID="btnView"
Text="Edit"/>
</ItemTemplate>
</asp:TemplateColumn>

</columns>
</asp:datagrid>

<BR> </form>
</body>
</html>

Note that you will need to have Excel 97 or later installed on the client. You can also add extra code for formatting the excel output.

0 0

相关博文

我的热门文章

img
取 消
img