GridView导出数据到Excel（形如身份证等数据的处理）

最新推荐文章于 2020-04-01 18:46:46 发布

y29508968

最新推荐文章于 2020-04-01 18:46:46 发布

阅读量506

点赞数

//以下是一个DEMO

protected void Page_Load(object sender,EventArgs e)

{
DataTable dt = CreateTable();
//填充数据
InitialDataTable(dt);
//绑定
BindData(dt);
}

private void BindData(DataTable dt)
{
grdInfo.DataSource = dt;
grdInfo.DataBind();
}

private void InitialDataTable(DataTable dt)
{
for (int i = 0; i < 100; i++)
{
DataRow dr = dt.NewRow();
dr["姓名"] = "姓名" + i.ToString("000");
dr["年龄"] = i.ToString();
dr["性别"] = "男";
dr["身份证"] = "123456789012345"+i.ToString("000");
dt.Rows.Add(dr);
}
}

private DataTable CreateTable()
{
DataTable dtTemp = new DataTable();
DataColumn clmXm = new DataColumn("姓名", typeof(System.String));
dtTemp.Columns.Add(clmXm);
DataColumn clmNl = new DataColumn("年龄", typeof(System.String));
dtTemp.Columns.Add(clmNl);
DataColumn clmXb = new DataColumn("性别", typeof(System.String));
dtTemp.Columns.Add(clmXb);
DataColumn clmSfz = new DataColumn("身份证", typeof(System.String));
dtTemp.Columns.Add(clmSfz);
return dtTemp;
}

protected void btnExport_Click(object sender, EventArgs e)
{
//可以用以下两种方法来导出数据
//方法1.
/*用下面方法导出形如身份证的数据到Excel中时，需要在导出前给身份证所在Cell数据添加样式
foreach (GridViewRow dg in grdInfo.Rows)
{
dg.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
string a=dg.Cells[3].Text;
}
或者
protected void grdInfo_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//3表示身份证所在列
e.Row.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat: @; ");
}
}
*/
//有数据显示的地方有黑色边框，其余为空白
Export(grdInfo, "application/vnd.ms-excel", "data.xls");

//方法2
/*
如果用下面的方法来导出形如身份证的数据到Excel中
通过以下语句，不能达到目的
foreach (GridViewRow dg in grdInfo.Rows)
{
dg.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
string a = dg.Cells[3].Text;
}
或者
protected void grdInfo_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//3表示身份证所在列
e.Row.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat: @; ");
}
}
需要在<td>标签中加入样式<td style=vnd.ms-excel.numberformat:@>
*/
//和默认Excel一样，有默认格线
Export1(grdInfo, "", "data.xls");
}

public void Export(System.Web.UI.Control source, string fileType, string fileName)
{
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.Buffer = true;
response.Charset = "UTF-8"; //default-value
response.ContentEncoding = System.Text.Encoding.UTF7;
response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8).ToString());
response.ContentType = fileType;
source.Page.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
source.RenderControl(hw);
response.Write(sw.ToString());
response.End();
}
public static void Export1(GridView source, string fileType, string fileName)
{
if (source.Rows.Count > 0)
{
HttpContext ctx = HttpContext.Current;
StringWriter sw = new StringWriter();
sw.WriteLine("<htmlxmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine("<head>");
sw.WriteLine("");
sw.WriteLine("</head>");
sw.WriteLine("<body>");
sw.WriteLine("<table>");
for (int i = 0; i < source.Rows.Count; i++)
{
if (i == 0)
{
sw.WriteLine("<tr>");
for (int j = 0; j < source.Columns.Count; j++)
{
sw.WriteLine("<td><strong>" + source.Columns[j].HeaderText + "</strong></td>");
}
sw.WriteLine("</tr>");
}
sw.WriteLine("<tr>");
for (int j = 0; j < source.Columns.Count; j++)
{
//sw.WriteLine("<td>" + source.Rows[i].Cells[j].Text + "</td>");
//j==3表示身份证所在列
if (j == 3)
{
//加上这句，像身份证需要加入style=vnd.ms-excel.numberformat:@这句，
//否则会以科学计数法显示数据
sw.WriteLine("<td style=vnd.ms-excel.numberformat:@>" + source.Rows[i].Cells[j].Text + "</td>");
}
else
{ sw.WriteLine("<td>" + source.Rows[i].Cells[j].Text + "</td>"); }
}
sw.WriteLine("</tr>");
}
sw.WriteLine("</table>");
sw.WriteLine("</body>");
sw.WriteLine("</html>");
sw.Close();
ctx.Response.Clear();
ctx.Response.Buffer = true;
ctx.Response.Charset = "UTF-8";
source.Page.EnableViewState = false;
ctx.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + "");
ctx.Response.ContentType = "application/ms-excel";
ctx.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
ctx.Response.Write(sw);
ctx.Response.End();
}
}

public override void VerifyRenderingInServerForm(Control control)
{
}

//方法1时，也可以在这里设置样式
protected void grdInfo_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//3表示身份证所在列
e.Row.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat: @; ");
}
}