html表格导出Excel的实例 - itjeff
html表格导出Excel的实例
1. 拼成出完整的HMTL的Table代码片段后，转成二进制byte[]类型并存入数据库中，供下载时调出来使用。
System.Text.StringBuilder sb = new StringBuilder();
sb.AppendLine(@"<html xmlns:o=\'urn:schemas-microsoft-com:office:office\' xmlns:x=\'urn:schemas-microsoft-com:office:excel\' xmlns=\'http://www.w3.org/TR/REC-html40\'>");
sb.AppendLine(@"<head>");
sb.AppendLine(@"<meta http-equiv=\'Content-Type\' content=\'text/html; charset=utf-8\'>");
sb.AppendLine(@"<meta name=\'ProgId\' content=\'Excel.Sheet\'>");
sb.AppendLine(@"<style>");
sb.AppendLine(@".Header{background-color:#B8CCE4;font-family: Arial;}");
sb.AppendLine(@".Content{background-color:#B8CCE4;text-align: left;font-family: Times New Roman;}");
sb.AppendLine(@".N0{background-color:#B8CCE4;text-align: right ;font-family: Times New Roman;mso-number-format:\'\#\,\#\#0\';}");
sb.AppendLine(@".N2{background-color:#B8CCE4;text-align: right ;font-family: Times New Roman;mso-number-format:\'\#\,\#\#0\.00\';}");
sb.AppendLine(@".N6{background-color:#B8CCE4;text-align: right ;font-family: Times New Roman;mso-number-format:\'\#\,\#\#0\.000000\';}");
//下面一行的代码，是为了解决超过11位数字的纯文本导出Excel后，Excel默认会转为科学计数法表示，加入下面的class并标注在需要显示的字段的td后，导出Excel可以强制把数字用文本表示
sb.AppendLine(@".CvtTxt{background-color:#B8CCE4;text-align: left ;font-family: Times New Roman;mso-number-format:\'\@\';}");
sb.AppendLine(@"</style>");
sb.AppendLine(@"</head>");
sb.AppendLine(@"<body>");
sb.AppendLine(@"<div divSplit\' align=\'center\' x:publishsource=\'Excel\'>");
sb.AppendLine(@" <table 2\' border-collapse:collapse;\'>");
sb.AppendLine(@" <tr>");
sb.AppendLine(@" </tr>");
sb.AppendLine(@" <tr>");
sb.AppendLine(@" <th colspan=\'14\' bgcolor=\'#538ED5\' text-align: center; font-family: Times New Roman\'>" + "MyCoreInfo Provide" + "</th>");
sb.AppendLine(@" </tr>");
if (exchangeRate != null)
{
sb.AppendLine(@" <tr>");
sb.AppendLine(@" <th colspan=\'11\' bgcolor=\'#B8CCE4\' border-right:0\'></th>");
sb.AppendLine(@" <th bgcolor=\'#B8CCE4\' border-right:0;border-left:0;text-align:left\'>Exchange Rate: </th>");
sb.AppendLine(@" <th colspan=\'2\' bgcolor=\'#B8CCE4\' border-left:0;text-align:right;mso-number-format:\#\,\#\#0\.000000\'>" + exchangeRate.GetValueOrDefault().ToString("N6") + "</th>");
sb.AppendLine(@" </tr>");
}
if (stockPrice != null)
{
sb.AppendLine(@" <tr>");
sb.AppendLine(@" <th colspan=\'11\' bgcolor=\'#B8CCE4\' border-right:0\'></th>");
sb.AppendLine(@" <th bgcolor=\'#B8CCE4\' border-right:0;border-left:0;text-align:left\'>Stock Closing Price: </th>");
sb.AppendLine(@" <th colspan=\'2\' bgcolor=\'#B8CCE4\' border-left:0;text-align:right;mso-number-format:\#\,\#\#0\.00\'>" + stockPrice.GetValueOrDefault().ToString("N2") + "</th>");
sb.AppendLine(@" </tr>");
}
sb.AppendLine(@" <tr>");
sb.AppendLine(@" <td class=\'Content\'>Forbid Exercise</td>");
sb.AppendLine(@" <td class=\'Content\'>OrderID</td>");
sb.AppendLine(@" <td class=\'Content\'>GrantID</td>");
sb.AppendLine(@" <td class=\'Content\'>GrantDate</td>");
sb.AppendLine(@" <td class=\'Content\'>Exercise Date(YYYY-MM-DD)</td>");
sb.AppendLine(@" <td class=\'Content\'>Officer</td>");
sb.AppendLine(@" <td class=\'Content\'>Employee ID</td>");
sb.AppendLine(@" <td class=\'Content\'>Brokerage Account #</td>");
sb.AppendLine(@" <td class=\'Content\'>English Name</td>");
sb.AppendLine(@" <td class=\'Content\'>Shares Vested</td>");
sb.AppendLine(@" <td class=\'Content\'>Shares Withheld for Taxes</td>");
sb.AppendLine(@" <td class=\'Content\'>Withholding Tax" + taxCurrency + "</td>");
sb.AppendLine(@" <td class=\'Content\'>Net Shares</td>");
sb.AppendLine(@" <td class=\'Content\'>Option Cost" + currency + "</td>");
sb.AppendLine(@" </tr>");
if (!String.IsNullOrEmpty(orderDetail.GrantID))
{
sb.AppendLine(@" <td class=\'CvtTxt\'>" + orderDetail.GrantID + "</td>");
}
else
{
sb.AppendLine(@" <td bgcolor=\'#B8CCE4\' text-align: left;font-family: Times New Roman\'></td>");
}
。。。
sb.AppendLine(@" </tr>");
sb.AppendLine(@" </table>");
sb.AppendLine(@" </div>");
sb.AppendLine(@" </body>");
sb.AppendLine(@"</html>");
bytes = ASCIIEncoding.UTF8.GetBytes(sb.ToString());
2. 下载时从DB中取出上面的bytes[], 然后扔给浏览器下载，代码如下：
string type = context.Request.QueryString["type"];
try
{
IFileDownloadHandler handler = GetHandler("xls");
if (handler == null)
{
throw new Exception("未知的文件类型");
}
string fileName;
byte[] fileContent;
//取出byte[]并给浏览器下载xls文件类型的文件
handler.GetFile(context, out fileName, out fileContent);
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
fileName = fileName.Replace("+", "%20");
context.Response.Clear();
context.Response.Charset = "utf-8";
context.Response.Buffer = true;
context.Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}", fileName));
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.ContentType = String.Format("application/octet-stream");
const int buffersize = 1024 * 16;
int count = fileContent.Length / buffersize;
int i;
for (i = 0; i < count; i++)
{
context.Response.OutputStream.Write(fileContent, i * buffersize, buffersize);
}
int remainder = fileContent.Length % buffersize;
if (remainder != 0)
{
context.Response.OutputStream.Write(fileContent, i * buffersize, remainder);
}
try
{
context.Response.End();
}
catch (ThreadAbortException ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
}
catch (Exception exp)
{
if (exp is ThreadAbortException)
{
System.Diagnostics.Debug.WriteLine(exp.Message);
}
else
{
context.Response.Write(exp.Message);
}
context.Response.End();
}
/// <summary>
/// 获取文件名及文件内容
/// </summary>
/// <param name="context">上下文</param>
/// <param name="fileName">文件名</param>
/// <param name="fileContent">文件内容</param>
public void GetFile(HttpContext context, out string fileName, out byte[] fileContent)
{
Guid fileID = new Guid(context.Request.QueryString["FileID"]);
IUploadFileService service = null;// ServiceFactory.FindService<IUploadFileService>();
try
{
// service = ServiceFactory.FindService<IUploadFileService>();
// var file = service.GetByUploadFileID(fileID);
UploadFileBizProcess bizObj = UploadFileBizProcess.GetInstance();
var file = bizObj.GetByUploadFileID(fileID);
if (file == null)
{
throw new Exception("文件不存在");
}
fileName = file.FileName;
fileContent = file.FileContent;
}
finally
{
Helper.Dispose(service);
}
}
在css中加入：mso-number-format定义数据格式，格式可以在excel中查看自定义格式，具体可以参考一下：
mso-number-format:"0" NO Decimals
mso-number-format:"0\.000" 3 Decimals
mso-number-format:"\#\,\#\#0\.000" Comma with 3 dec
mso-number-format:"mm\/dd\/yy" Date7
mso-number-format:"mmmm\ d\,\ yyyy" Date9
mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM" D -T AMPM
mso-number-format:"Short Date" 01/03/1998
mso-number-format:"Medium Date" 01-mar-98
mso-number-format:"d\-mmm\-yyyy" 01-mar-1998
mso-number-format:"Short Time" 5:16
mso-number-format:"Medium Time" 5:16 am
mso-number-format:"Long Time" 5:16:21:00
mso-number-format:"Percent" Percent - two decimals
mso-number-format:"0%" Percent - no decimals
mso-number-format:"0\.E+00" Scientific Notation
mso-number-format:"\@" Text
mso-number-format:"\#\ ???\/???" Fractions - up to 3 digits (312/943)
