最近接到了一个实习任务,写一个可以从web页面上导入表格信息并下载成excel文件的demo
ps:博主刚参加实习,有过一段java编写的经验….
首先页面是通过html代码实现的,而数据信息是通过从数据库查找显示在页面上的。
先开始建一个WebForm
博主这里对.net确实不太熟悉,直接建了个WebApi,但并没有用控制器,直接在*.aspx.cs里写逻辑代码,这里就将错就错吧。
在aspx前台创建一个表格,里面先填上数据,页面做的比较简陋,情况如下图:
给Downloading添加OnClick事件:
在后开给Downloading添加事件绑定:`
protected void hidExport_Click(object sender, System.EventArgs e)
{
string content = getExcelContent();
string filename = "Test.xls";
CommonTool.ExportToExcel(filename, content);
}`
content的获取写成一个getExcelContent()函数,主要功能是获取当前页面的html代码转换成string类型返回,具体的代码如下:
public static string getExcelContent()
{
StringBuilder sb = new StringBuilder();
sb.Append(getMessages.GetHtmlString(getMessages.GetCurrentUrl()));
return sb.ToString();
}
考虑到在真实环境中页面的信息会发生改变,这里采用StringBuilder对象来接受获取到的(操作速度快,字符长度可变),具体的String,StringBuilder,StringBuffer的区别请参考这个博客
public static class getMessages
{
public static string GetCurrentUrl()
{
string uri = HttpContext.Current.Request.Url.ToString();
return uri;
}
public static string GetHtmlString(string url)
{
WebRequest wr = WebRequest.CreateHttp(url);
WebResponse res = wr.GetResponse();
Stream st = res.GetResponseStream();
StreamReader sr = new StreamReader(st, Encoding.UTF8);
string GetHtml = sr.ReadToEnd();
st.Close();
sr.Close();
return GetHtml;
}
content就是将要输出给用户的文本内容,不过在传送之前要将html改变成excel的格式,转换的代码如下:
res.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
res.ContentType = "application/vnd.ms-excel;charset=UTF-8";
对于用户下载的操作,这里博主参考了网上的做法,具体代码是通过缓存输出到客户端的:
public class CommonTool
{
public static void ExportToExcel(string filename, string content)
{
var res = HttpContext.Current.Response;
res.Clear();
res.Buffer = true;
res.Charset = "UTF-8";
res.AddHeader("Content-Disposition", "attachment; filename=" + filename);
res.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
res.ContentType = "application/vnd.ms-excel;charset=UTF-8";
res.Write(content.Replace("<td","<td STYLE='MSO-NUMBER-FORMAT:\\@'"));
res.Flush();
res.End();
}
}
这里差一插话,当数字过长时excel会自动转换成科学计数法,这样如果我需要输入一个身份证号,就出现了bug:
所以博主将content里的<td
都用了<td STYLE='MSO-NUMBER-FORMAT:\\@'
代替,其中 STYLE=’MSO-NUMBER-FORMAT:\@’是excel中的文本格式,这样就避免了以上问题。
运行后下载的excel如下图:
整体的代码在下面贴出:
using System;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication1.Models
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void hidExport_Click(object sender, System.EventArgs e)
{
string content = getExcelContent();
string filename = "Test.xls";
CommonTool.ExportToExcel(filename, content);
}
//设置http头部信息,并输出到客户端
public class CommonTool
{
public static void ExportToExcel(string filename, string content)
{
var res = HttpContext.Current.Response;
res.Clear();
res.Buffer = true;
res.Charset = "UTF-8";
res.AddHeader("Content-Disposition", "attachment; filename=" + filename);
res.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
res.ContentType = "application/vnd.ms-excel;charset=UTF-8";
res.Write(content.Replace("<td","<td STYLE='MSO-NUMBER-FORMAT:\\@'"));
res.Flush();
res.End();
}
}
public static string getExcelContent()
{
StringBuilder sb = new StringBuilder();
sb.Append(getMessages.GetHtmlString(getMessages.GetCurrentUrl()));
return sb.ToString();
}
}
}
public static class getMessages
{
public static string GetCurrentUrl()
{
string uri = HttpContext.Current.Request.Url.ToString();
return uri;
}
public static string GetHtmlString(string url)
{
WebRequest wr = WebRequest.CreateHttp(url);
WebResponse res = wr.GetResponse();
Stream st = res.GetResponseStream();
StreamReader sr = new StreamReader(st, Encoding.UTF8);
string GetHtml = sr.ReadToEnd();
st.Close();
sr.Close();
return GetHtml;
}
}
ps:后发现一般web页面上都有分页功能,所以这个demo只是练手,实际并不实用。