其实本来的工作任务是要导出excel, 很普通的工作,但是实现的时候发现没有完美的解决办法, 最后改为导出CSV文件.
导出Excel
方法一
导出一个Html的Table, 缺点是导出后在Excel中看不到网络线.
方法二
先生成xml, 再通过xslt把数据转换成excel识别的格式. 缺点是维护,修改xslt比较困难.
http://blogs.msdn.com/b/brian_jones/archive/2005/06/27/433152.aspx
方法三
调用Excel SDK, 缺点是要在服务器上安装Excel, 不通用, 而且如果出错的话, Kill Excel进程是个麻烦事.
方法四
调用开源组件epplus，推荐这个，在nopCommerce中就是用的它。
导出CSV
最后还是决定导出为csv格式, 但碰到的问题是导出中文乱码(用editplus打开正常), 即使是用Utf-8编码导出.
经过查找, 原来是因为excel默认是以ansi模式去开启csv文件的，并不直接支持unicode格式.
解决办法, 添加BOM(UTF-8的BOM为EFBBBF)。
代码片断
1,javascript调用
jQuery(document).ready(function() {
$("#btnExport").click(myExport);
});
function myExport() {
var url = "/Credential/ExportToExcel?CredentialName={0}&Owner={1}";
url = format(url,
$("#qCredentialName").val(),
$("#qOwner").val()
);
window.location.href = url;
}
2, Controller
public ActionResult ExportToExcel(string CredentialName, string Owner)
{
var items = repository.Search(CredentialName, Owner);
var data = (
from item in items
select new
{
CredentialName = item.CredentialName,
Owner = item.Owner,
CredentialNo = item.CredentialNo,
}).ToList();
StringBuilder sw = new StringBuilder("证件名称, 持有人, 证件编号");
sw.AppendLine();
foreach (var item in data)
{
sw.Append(item.CredentialName).Append(",");
sw.Append(item.Owner).Append(",");
sw.Append(item.CredentialNo).Append(",");
sw.AppendLine();
}
return this.Excel(sw.ToString(), "证件资料.xls");
}
3, 帮助类
ExcelControllerExtensions.cs
using System;
using System.Web.Mvc;
using System.Data.Linq;
using System.Collections;
using System.Web.UI.WebControls;
using System.Linq;
using System.Collections.Generic;
namespace MIS.Helper
{
public static class ExcelControllerExtensions
{
public static ActionResult Excel
(
this Controller controller,
string content,
string fileName
)
{
return new ExcelResult(content, fileName);
}
}
}
ExcelResult.cs
using System;
using System.Web.Mvc;
using System.Data.Linq;
using System.Collections.Generic;
using System.IO;
using System.Web.UI.WebControls;
using System.Linq;
using System.Web;
using System.Text;
using System.Web.UI;
namespace MIS.Helper
{
public class ExcelResult : ActionResult
{
private string _fileName;
private string _content;
public string FileName
{
get { return _fileName; }
}
public string Content
{
get { return _content; }
}
public ExcelResult(string content, string fileName)
{
_content = content;
_fileName = fileName;
}
public override void ExecuteResult(ControllerContext context)
{
WriteFile(_fileName, "application/ms-excel", _content);
}
private static void WriteFile(string fileName, string contentType, string content)
{
HttpContext context = HttpContext.Current;
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8); //对中文文件名进行HTML转码
byte[] buffer = Encoding.UTF8.GetBytes(content);
context.Response.ContentEncoding = Encoding.UTF8;
byte[] outBuffer = new byte[buffer.Length + 3];
outBuffer[0] = (byte)0xEF;//有BOM,解决乱码
outBuffer[1] = (byte)0xBB;
outBuffer[2] = (byte)0xBF;
Array.Copy(buffer, 0, outBuffer, 3, buffer.Length);
char[] cpara= Encoding.UTF8.GetChars(outBuffer); // byte[] to char[]
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
context.Response.Charset = "";
context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
context.Response.ContentType = contentType;
context.Response.Write(cpara, 0, cpara.Length);
context.Response.End();
}
}
}
OK,一切大功造成了. 但发现找开CSV时, Excel总是跳出一个安全警告. 实在看烦了它, 通过修改注册表把它搞定了.
方法为: 打开注册表, 找到HKEY_CURRENT_USER->Software->Microsoft>Office>12.0>Excel>Security
添加一DWORD值, 名称为ExtensionHardening, 值为0.
关于BOM
Q: What is a BOM?
A: A byte order mark (BOM) consists of the character code U+FEFF at the beginning of a data stream, where it can be used as a signature defining the byte order and encoding form, primarily of unmarked plaintext files. Under some higher level protocols, use of a BOM may be mandatory (or prohibited) in the Unicode data stream defined in that protocol.