写流文件的方式把页面中的数据导到Excel的终极代码
程序员文章站
2022-03-25 16:18:37
...
在开发一个小项目的时候，当时说功能简单，但是要实现数据导出功能，不用太复杂，就有ASP.net那个response.write的功能就行。
想着是简单，可是实践中真是累死人了，跟大家一样老是有的文件出现乱码，找啊试啊最终这个中是比较可靠。现在粘出来大家分享。
如果有更高的需求还是用NPOI吧。
1.在页面上多放一个gridView控件
在你需要导出时把你的实际要导出的数据绑定到这个gridview上，然后把它隐藏掉，到点击导出按钮上在显示，导完就关闭掉，很简单就是visiable=false。
用户根本看不见这个多余的gridview。
2.在页面gridview前边加上
<meta http-equiv="content-type" content="application/ms-excel; charset=UTF-8"/>
我的：
<meta http-equiv="content-type" content="application/ms-excel; charset=UTF-8"/>
<table cellspacing="0" cellpadding="3">
<tr>
<td class="td_text_ipc" align="right" width="100%" height="30px" style="font-size: 11px;">
<table width="100%" cellspacing="0" cellpadding="0" rules="all" bordercolorlight="#C6D5F5"
style="border: 1px solid #C6D5F5; border-collapse: collapse;">
<tr>
<td align="left" style="font-weight: bold" colspan="4">
请选择查询条件：
</td>
</tr>
<tr>
<td width="10%">
<font color="RED">*</font>回款月份：
</td>
<td width="40%" align="left">
<asp:DropDownList ID="ddlYear" runat="Server" AutoPostBack="true">
</asp:DropDownList>
<asp:DropDownList ID="ddlMonth" runat="Server" AutoPostBack="true" OnSelectedIndexChanged="ddlMonth_SelectedIndexChanged">
<asp:ListItem>01</asp:ListItem>
<asp:ListItem>02</asp:ListItem>
<asp:ListItem>03</asp:ListItem>
<asp:ListItem>04</asp:ListItem>
<asp:ListItem>05</asp:ListItem>
<asp:ListItem>06</asp:ListItem>
<asp:ListItem>07</asp:ListItem>
<asp:ListItem>08</asp:ListItem>
<asp:ListItem>09</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
</asp:DropDownList>
</td>
<td width="10%">
</td>
<td width="40%" align="left">
</td>
</tr>
<tr>
<td>
公司:
</td>
<td align="left">
<asp:DropDownList ID="ddlCompany" runat="server" Width="80%"
OnSelectedIndexChanged="ddlCompany_SelectedIndexChanged">
</asp:DropDownList>
</td>
<td>
状态:
</td>
<td align="left">
<asp:DropDownList ID="ddlDepartment" runat="server" Width="80%">
<asp:ListItem Value="0">未填写</asp:ListItem>
<asp:ListItem Value="1">未提交</asp:ListItem>
<asp:ListItem Value="2">已提交</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
</td>
<td align="left">
</td>
<TD colspan="2"> </TD>
</tr>
<tr>
<td colspan="4" align="center">
<asp:Button ID="btnSave" runat="Server" CssClass="button" Text="查询"
onclick="btnSave_Click" />
<asp:Button ID="btnSubmit" runat="Server" CssClass="button" Text="重置"
onclick="btnSubmit_Click" />
<asp:Button ID="btnSubmit0" runat="Server" CssClass="button" Text="导出"
onclick="btnSubmit0_Click" />
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<asp:Panel runat="server" ID="PID" ScrollBars="Both" Width="1070" Height="460" >
<asp:GridView ID="gvIPC" runat="server" AutoGenerateColumns="False"
Width="100%" CellPadding="3" BorderWidth="1px" BorderColor="#C6D5F5"
BorderStyle="Solid" onrowdatabound="gvIPC_RowDataBound" >
<HeaderStyle BackColor="#EAF0FB" HorizontalAlign="Center" Font-Bold="true" Height="28px" />
<EmptyDataTemplate>
<table class="table_ipc" cellspacing="0" cellpadding="0">
<tr>
<td class="td_title_ipc" height="28px">
序号
</td>
<td class="td_title_ipc">
回款月份
</td>
<td class="td_title_ipc">
公司
</td>
<td class="td_title_ipc">
部门
</td>
<td class="td_title_ipc">
合同号
</td>
<td class="td_title_ipc">
合同名称
</td>
<td class="td_title_ipc">
甲方
</td>
<td class="td_title_ipc">
签订日期
</td>
<td class="td_title_ipc">
签订人
</td>
<td class="td_title_ipc">
回款责任人
</td>
<td class="td_title_ipc">
合同额
</td>
<td class="td_title_ipc">
币别
</td>
<td class="td_title_ipc">
已收款
</td>
<td class="td_title_ipc">
未收款
</td>
<td class="td_title_ipc">
回款可能性
</td>
<td class="td_title_ipc">
本月承诺回款
</td>
<td class="td_title_ipc">
未承诺原因
</td>
<td class="td_title_ipc">
备注
</td>
</tr>
<tr>
<td colspan="17" height="24px" align="center">
<font color="red"><b>您本月没有填写回款计划！</b></font>
</td>
</tr>
</table>
</EmptyDataTemplate>
<Columns>
<asp:TemplateField HeaderText="序号">
<ItemTemplate>
<%# Container.DataItemIndex + 1%>
</ItemTemplate>
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" Wrap="False" />
</asp:TemplateField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="回款月份" HtmlEncode="false"
DataField="PCMonth" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="公司" HtmlEncode="false"
DataField="PCCompany" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="合同号"
DataField="ContractNumber" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="合同名称" HtmlEncode="false"
DataField="ContractName" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="甲方"
DataField="FirstParty" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="签订日期" HtmlEncode="false"
DataField="SignDate" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="签订人"
DataField="SignPerson" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="ResponsiblePersonId" HeaderText="回款责任人工号"
HtmlEncode="False">
<HeaderStyle Wrap="False" />
<ItemStyle Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="回款责任人" HtmlEncode="false"
DataField="ResponsiblePersonName" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="right" HeaderText="合同额" ItemStyle-Font-Italic="true"
DataField="ContractMoney" DataFormatString="{0:N}" >
<HeaderStyle Wrap="False" />
<ItemStyle Font-Italic="True" HorizontalAlign="Right" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="币别"
DataField="Currency" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="right" HeaderText="已收款" ItemStyle-Font-Italic="true"
DataField="ReceivedMoney" DataFormatString="{0:N}" >
<ItemStyle Font-Italic="True" HorizontalAlign="Right" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="right" HeaderText="未收款" ItemStyle-Font-Italic="true"
DataField="NotReceivedMoney" DataFormatString="{0:N}" >
<HeaderStyle Wrap="False" />
<ItemStyle Font-Italic="True" HorizontalAlign="Right" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="回款可能性" HtmlEncode="false"
DataField="PCPossibility" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="本月承诺回款" HtmlEncode="false"
DataField="MonthPromisePC" DataFormatString="{0:N}" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="未承诺原因" HtmlEncode="false"
DataField="NoPromiseReason" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="备注" HtmlEncode="false"
DataField="Remark" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
</Columns>
</asp:GridView>
</asp:Panel>
<webdiyer:aspnetpager ID="AspNetPager1" runat="server" HorizontalAlign="Center"
OnPageChanged="AspNetPager1_PageChanged" ShowPageIndex="False"
UrlPageIndexName="img" Width="580px" PagingButtonType="Image"
ImagePath="../../images/" ButtonImageNameExtension="n" ButtonImageExtension=".gif"
DisabledButtonImageNameExtension="g"
PagingButtonSpacing="18px" ShowCustomInfoSection="Left"
CustomInfoHTML="第%CurrentPageIndex%页，共%PageCount%页，共%RecordCount%条记录,每页%PageSize%条" CurrentPageButtonPosition="End"
PageIndexBoxType="DropDownList" SubmitButtonText="Go" TextAfterPageIndexBox="页"
TextBeforePageIndexBox="转到" ShowPageIndexBox="Always"
NumericButtonCount="1000" PageSize="20">
</webdiyer:aspnetpager>
</td>
</tr>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
Width="100%" CellPadding="3" Visible="false">
<HeaderStyle BackColor="#EAF0FB" HorizontalAlign="Center" Font-Bold="true" Height="28px" />
<EmptyDataTemplate>
<table class="table_ipc" cellspacing="0" cellpadding="0">
<tr>
<td class="td_title_ipc" height="28px">
序号
</td>
<td class="td_title_ipc">
回款月份
</td>
<td class="td_title_ipc">
公司
</td>
<td class="td_title_ipc">
部门
</td>
<td class="td_title_ipc">
合同号
</td>
<td class="td_title_ipc">
合同名称
</td>
<td class="td_title_ipc">
甲方
</td>
<td class="td_title_ipc">
签订日期
</td>
<td class="td_title_ipc">
签订人
</td>
<td class="td_title_ipc">
回款责任人
</td>
<td class="td_title_ipc">
合同额
</td>
<td class="td_title_ipc">
币别
</td>
<td class="td_title_ipc">
已收款
</td>
<td class="td_title_ipc">
未收款
</td>
<td class="td_title_ipc">
回款可能性
</td>
<td class="td_title_ipc">
本月承诺回款
</td>
<td class="td_title_ipc">
未承诺原因
</td>
<td class="td_title_ipc">
备注
</td>
</tr>
<tr>
<td colspan="17" height="24px" align="center">
<font color="red"><b>您本月没有填写回款计划！</b></font>
</td>
</tr>
</table>
</EmptyDataTemplate>
<Columns>
<asp:TemplateField HeaderText="序号">
<ItemTemplate>
<%# Container.DataItemIndex + 1%>
</ItemTemplate>
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" Wrap="False" />
</asp:TemplateField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="回款月份" HtmlEncode="false"
DataField="PCMonth" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="公司" HtmlEncode="false"
DataField="PCCompany" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="合同号"
DataField="ContractNumber" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="合同名称" HtmlEncode="false"
DataField="ContractName" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="甲方"
DataField="FirstParty" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="签订日期" HtmlEncode="false"
DataField="SignDate" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="签订人"
DataField="SignPerson" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="ResponsiblePersonId" HeaderText="回款责任人工号"
HtmlEncode="False">
<HeaderStyle Wrap="False" />
<ItemStyle Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="回款责任人" HtmlEncode="false"
DataField="ResponsiblePersonName" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="right" HeaderText="合同额" ItemStyle-Font-Italic="true"
DataField="ContractMoney" DataFormatString="{0:N}" >
<HeaderStyle Wrap="False" />
<ItemStyle Font-Italic="True" HorizontalAlign="Right" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="币别"
DataField="Currency" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="right" HeaderText="已收款" ItemStyle-Font-Italic="true"
DataField="ReceivedMoney" DataFormatString="{0:N}" >
<ItemStyle Font-Italic="True" HorizontalAlign="Right" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="right" HeaderText="未收款" ItemStyle-Font-Italic="true"
DataField="NotReceivedMoney" DataFormatString="{0:N}" >
<HeaderStyle Wrap="False" />
<ItemStyle Font-Italic="True" HorizontalAlign="Right" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="回款可能性" HtmlEncode="false"
DataField="PCPossibility" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="本月承诺回款" HtmlEncode="false"
DataField="MonthPromisePC" DataFormatString="{0:N}" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="未承诺原因" HtmlEncode="false"
DataField="NoPromiseReason" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
<asp:BoundField ItemStyle-HorizontalAlign="left" HeaderText="备注" HtmlEncode="false"
DataField="Remark" >
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:BoundField>
</Columns>
</asp:GridView>
</table>
3 .编写后台代码
protected void btnSubmit0_Click(object sender, EventArgs e)
{
GridView1.Visible = true;
if (this.GridView1.Rows.Count <= 0)
return;
Response.Clear();
Response.Buffer = true;
Response.Charset = "gb2312";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("content-disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode("数据导出", System.Text.Encoding.UTF8) + ".xls\"");
Response.ContentType = "Application/ms-excel";
using (System.IO.StringWriter oStringWriter = new System.IO.StringWriter())
{
using (System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter))
{
Table table = new Table();
if (this.GridView1.HeaderRow != null)
{
table.Rows.Add(GridView1.HeaderRow);
}
foreach (GridViewRow item in GridView1.Rows)
{
table.Rows.Add(item);
}
table.RenderControl(oHtmlTextWriter);
}
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
}
GridView1.Visible = false;
//this.Label1.RenderControl(oHtmlTextWriter);
//this.GridView1.RenderControl(oHtmlTextWriter);
}
下一篇： 【C++并发实战】（一）并发基本概念