导出EXCEL是很多程序都要用到，导出EXCEL的速度问题让人比较头疼，在网上查找了好几天都没有找到满意的方法。我要导出的EXCEL文件
要求前面有表头，后面有总计。刚开始采用了填格子的方式导出，但是数据一多响应时间就让人皱眉，在网上看见有说可以用数组保存数据
再一次性填充EXCEL文件，但是我就是没有找到门道。因此，产生了下面的代码：
//在GridView的页脚添加总计
protected void gvExperimentGapStatistic_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow || e.Row.RowType == DataControlRowType.EmptyDataRow)
{
for (int i = 0; i < gvExperimentGapStatistic.Rows.Count; i++)
{
iNumber += long.Parse(gvExperimentGapStatistic.Rows[i].Cells[4].Text);
iMoney += double.Parse(gvExperimentGapStatistic.Rows[i].Cells[5].Text);
}
//生成总计
totalNumber = iNumber;
totalMoney = iMoney;
}
if (e.Row.RowType == DataControlRowType.Footer) //页脚
{
e.Row.Cells[3].Text = "总计:";
e.Row.Cells[4].Text = string.Format("缺口数量：{0}", totalNumber);
e.Row.Cells[5].Text = string.Format("缺口金额：{0}", totalMoney);
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
#region 绑定代码
string name = txtSort.Text;
if (name == "点击选择分类" || name == "")
{
name = "";
}
DataTable dt = new DataTable();
if (TreeViewSet.GetParent(ReportsTree1.SelectedNode) == "商品类别")
{
if (ddlND.Items.Count > 0)
{
dt = BllSchoolExperimentGapStatistic.GetDataSource("", nodeValue.Value.Trim(), name, ddlND.SelectedItem.Value);
}
else
{
dt = BllSchoolExperimentGapStatistic.GetDataSource("", nodeValue.Value.Trim(), name, "0");
}
fzr();
}
gvExperimentGapStatistic.ShowFooter = true;
gvExperimentGapStatistic.DataSource = dt;
gvExperimentGapStatistic.DataBind();
#endregion
#region 画表头
//创建一个GridViewRow，相当于表格的 TR 一行
GridViewRow rowHeader = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal);
string HeaderBackColor = "#EDEDED";
rowHeader.BackColor = System.Drawing.ColorTranslator.FromHtml(HeaderBackColor);
Literal newCells = new Literal();
newCells.Text = @"<th colspan='5'style='font-size:12pt'> 商品统计</th>
</tr>
<tr bgcolor='" + HeaderBackColor + "'>";
newCells.Text += "<th style='font-size:10pt'>统计单位:" + lblTJDW.Text + "</th>"
+ "<th style='font-size:10pt'>负责人:" + lblFZR.Text + "</th>"
+ "<th style='font-size:10pt'>统计/添表人:" + lblTBR.Text + "</th>"
+ "<th colspan='2' style='font-size:10pt'>统计/添表如期:" + lblTBSJ.Text + "</th>"
+ "</tr>";
//TableCellCollection cells = e.Row.Cells;
TableHeaderCell headerCell = new TableHeaderCell();
//下面的属性设置与 <td rowspan='2'>关键单元格</td> 要一致
headerCell.RowSpan = 2;
headerCell.Controls.Add(newCells);
rowHeader.Cells.Add(headerCell);
rowHeader.Cells.Add(headerCell);
rowHeader.Visible = true;
//添加到 GridView
gvExperimentGapStatistic.Controls[0].Controls.AddAt(0, rowHeader);
#endregion
#region 导出报表
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=YQQKTJ.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.gvExperimentGapStatistic.RenderControl(oHtmlTextWriter);
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
#endregion
}
在.CS文件里添加:
public override void VerifyRenderingInServerForm(Control control)
{ }
在页面的首行 添加:EnableEventValidation="false"
以上资料主要是在网上找的,本人只是整理了下.
http://blog.csdn.net/net_lover/archive/2006/09/29/1306211.aspx
评论