在页面上把数据导出到Excel中,如果导出的数据为都为数字的字符串时,导出Excel中后认为是数值进行显示(前几位为零时不显示,如果字符串过长时,Excel中显示的数据为科学计数法,把单元格的属性设为文本也不起作用),这样的数据能后以字符串的形式显示。代码如下:
static public void ToExcel(DataTable dtData)
{
string filename="SaiLun-Report-"+System.DateTime.Now.ToString("yyyyMMddhhmmss") +".csv";
StringWriter sw=new StringWriter();
//列数
int ColumnCount=dtData.Columns.Count;
//标题
string strTitle="";
for(int i=0;i<dtData.Columns.Count;i++)
{
strTitle=strTitle+dtData.Columns[i].ColumnName+",";
}
sw.WriteLine(strTitle);
string strRead="";
for(int i=0;i<ColumnCount;i++)
{
if(i==ColumnCount-1)
{
strRead=strRead+"dr["+dtData.Columns[i].ColumnName+"]";
}
else
{
strRead=strRead+"dr["+dtData.Columns[i].ColumnName+"]+','+";
}
}
//写数据
foreach(DataRow dr in dtData.Rows)
{
for(int i=0;i<ColumnCount;i++)
{
if(dtData.Columns[i].ColumnName=="身份证号")
{
sw.Write("'"+dr[i].ToString());
}
else
{
sw.Write(dr[i].ToString());
}
sw.Write(",");
}
sw.WriteLine("\t");
}
sw.Close();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +filename);
System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流,必须被下载
System.Web.HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
System.Web.HttpContext.Current.Response.Write(sw); // 把文件流发送到客户端
System.Web.HttpContext.Current.Response.End();
}
谢谢大家!
static public void ToExcel(DataTable dtData)
{
string filename="SaiLun-Report-"+System.DateTime.Now.ToString("yyyyMMddhhmmss") +".csv";
StringWriter sw=new StringWriter();
//列数
int ColumnCount=dtData.Columns.Count;
//标题
string strTitle="";
for(int i=0;i<dtData.Columns.Count;i++)
{
strTitle=strTitle+dtData.Columns[i].ColumnName+",";
}
sw.WriteLine(strTitle);
string strRead="";
for(int i=0;i<ColumnCount;i++)
{
if(i==ColumnCount-1)
{
strRead=strRead+"dr["+dtData.Columns[i].ColumnName+"]";
}
else
{
strRead=strRead+"dr["+dtData.Columns[i].ColumnName+"]+','+";
}
}
//写数据
foreach(DataRow dr in dtData.Rows)
{
for(int i=0;i<ColumnCount;i++)
{
if(dtData.Columns[i].ColumnName=="身份证号")
{
sw.Write("'"+dr[i].ToString());
}
else
{
sw.Write(dr[i].ToString());
}
sw.Write(",");
}
sw.WriteLine("\t");
}
sw.Close();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +filename);
System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流,必须被下载
System.Web.HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
System.Web.HttpContext.Current.Response.Write(sw); // 把文件流发送到客户端
System.Web.HttpContext.Current.Response.End();
}
谢谢大家!
http://dotnet.aspx.cc/Exam/OutPutExcel.aspx
{
System.Web.HttpResponse resp = pPage.Response; if(dt.Rows.Count==0)
{
resp.Write("<script>alert('对不起,没有查询到任何记录,无法导出')</script>");
resp.End();
} resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel";
resp.AppendHeader("Content-Disposition", "attachment;filename=HrExport.xls");
int colCount = dt.Columns.Count;
StringBuilder sb = new StringBuilder();
sb.Append("<html><head>\n");
sb.Append("<meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n");
sb.Append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n");
sb.Append("</head>\n");
sb.Append("<table border=1>");
//大标题
if(str1 != "")
{
string sTitle = "<font size=4><b>"+str1+"</b></font>";
//副标题
if(str2 != "") sTitle += "("+str2+")"; sb.Append("<tr><td colspan="+colCount+">"+sTitle+"</td></tr>");
}
//查询时间
sb.Append("<tr><td colspan="+colCount+" valign=middle height=24>");
sb.Append("查询时间:"+System.DateTime.Now.ToString()+"</td></tr>");
//表标题
sb.Append("<tr>\n");
for(int i=0; i<colCount; i++)
{
string sCaption = dt.Columns[i].Caption.ToString();
if(sCaption.ToLower() != "id")
sb.Append("<td bgcolor=#969696><b>"+dt.Columns[i].Caption.ToString()+"</b></td>\n");
}
//表内容
foreach(DataRow dr in dt.Rows)
{
sb.Append("<tr>");
for(int j=0; j<colCount; j++)
{
if(dt.Columns[j].Caption.ToString().ToLower() != "id")
sb.Append("<td style='vnd.ms-excel.numberformat:@'>"+dr[j].ToString()+"</td>");
}
sb.Append("</tr>\n");
}
sb.Append("</table>\n"); resp.Write(sb.ToString());
resp.End();
}
{
if (e.Item.ItemType == ListItemType.Item ||
e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Cells[列].Attributes.Add("style","vnd.ms-excel.numberformat:@");
}}
{
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");
e.Item.Cells[3].Attributes.Add("style","vnd.ms-excel.numberformat:¥#,###.00");
}
}