DataGrid导出为Excel
来源:互联网 发布:microsoft windows 编辑:程序博客网 时间:2024/05/06 07:32
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.
- DataGrid导出为Excel
- 将Datagrid导出为Excel
- datagrid导出为excel的简单代码
- VC++ 导出DataGrid数据为Excel文件
- DataGrid导出为excel和word
- 实现easyui的datagrid导出为excel
- 实现easyui的datagrid导出为excel
- 实现easyui的datagrid导出为excel
- easyui的datagrid导出为excel
- easyUI datagrid数据导出为Excel
- 实现easyui的datagrid导出为excel
- asp.net中将DataGrid的内容导出为excel文件
- 将DataGrid中的数据导出为Excel的方法
- Flex/Air将DataGrid数据导出为CSV/Excel格式
- Asp.net中把DataTable或DataGrid导出为Excel
- Asp.net中把DataTable或DataGrid导出为Excel
- VB.net中DataGrid导出为Excel文件函数
- flex 从DataGrid列表中导出数据为excel表格
- 前几天看到一个简单的基于socket的程序,特放上来!
- 50部经典影片,你看过哪些?
- 程序员的突破口
- 男人必修10部电影
- 我做科研的几点体会
- DataGrid导出为Excel
- 论瀑布模型、Rup是否适用于一般的国内软件公司。(二)
- 我的EJB学习历程(一)
- 猴年马月
- 入住Blog第一篇
- 其实我是猪
- 偶这学年的课程分析
- 终于有了gmail,测试了一下
- 厕所~加锁