在组织部这个系统中有个需求是导入数据,一般来说导入数据就是给定表头就行,不过我这里是根据不同指标下载不同的Excel。如下图所示:
刚开始下载Excel模板是没有链接的,需要选择年份和指标进行下载模板。做法如下:
前台JS中:
<div id="inputExcel">
<fieldset style="width: 500px; height: 70px;">
<span class="tip">提示:导入数据前请选择指标下载相应的EXCEL模板</span>
<a id="downloadExcel" class="btn-lit"><span>点击下载EXCEL模板</span></a>
<legend>Excel导入</legend>
<input id="File1" type="file" />
<input type="button" value="导入" style="width: 100px; height: 25px; margin-top: 10px;" />
</fieldset>
</div>
在点击指标后加载<a>的href属性
$('#downloadExcel').attr('href', 'CountyQuality.ashx?test=downExcel&yearText=' + getYear + '&targetid=' + getTarget + '&TargetText=' + getTargetText);
通过在href,值中传入链接,链接包含着需要的参数,用一般处理程序来接收。
/// <summary>
/// 下载excel
/// </summary>
/// <param name="context"></param>
public void DownExcel(HttpContext context)
{
string strCheckedYearValue = context.Request.Params["yearText"]; //获取年份
string id = context.Request.Params["targetid"];
string targetText = "";
if (null != context.Request.Params["TargetText"])
{
targetText = "县市区" + context.Request.Params["TargetText"]; //获取指标的名称;
}
StringBuilder strWhere = new StringBuilder();
strWhere.Append("TargetId ='" + id + "'and Timestamp='" + strCheckedYearValue + "'");
DataSet ds = cityVotesbll.GetNewColumn(strWhere.ToString());
DataTable dt = ds.Tables[0];
ToExcel(dt,targetText); //传入dt,调用方法生成Excel
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="ExportFileName"></param>
public void ToExcel(DataTable dt, string strName)
{
DataGrid dgExcel = new DataGrid();
dgExcel.DataSource = dt;
dgExcel.DataBind();
HttpContext.Current.Response.Charset = "GB2312";
string fileName = "";
if (strName == "")
{
fileName = HttpUtility.UrlEncode(Guid.NewGuid().ToString(), System.Text.Encoding.UTF8);
}
else
{
fileName = HttpUtility.UrlEncode(strName, System.Text.Encoding.UTF8);
}
//string fileName = HttpUtility.UrlEncode(Guid.NewGuid().ToString(), System.Text.Encoding.UTF8);
string str = "attachment;filename=" + fileName + ".xls";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.AppendHeader("content-disposition", str);
StringWriter sw = new StringWriter();
HtmlTextWriter htmTextWriter = new HtmlTextWriter(sw);
dgExcel.RenderControl(htmTextWriter);
HttpContext.Current.Response.Write("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
string style = "<style>td{mso-number-format:\"\\@\";}</style>";//防止导出excel时将以0开头的全数字数据的0去掉
HttpContext.Current.Response.Write(style);
HttpContext.Current.Response.Write("</head><body>");
HttpContext.Current.Response.Write(sw);
HttpContext.Current.Response.Write("</body></html>");
HttpContext.Current.Response.End();
}
这样获取的表头后就可以导出Excel,前台显示为: