其实本来的工作任务是要导出excel, 很普通的工作,但是实现的时候发现没有完美的解决办法, 最后改为导出CSV文件.

导出Excel

方法一

导出一个Html的Table, 缺点是导出后在Excel中看不到网络线.

如 

方法二

先生成xml, 再通过xslt把数据转换成excel识别的格式. 缺点是维护,修改xslt比较困难.

 方法三

调用Excel SDK, 缺点是要在服务器上安装Excel, 不通用, 而且如果出错的话, Kill Excel进程是个麻烦事.

 方法四

调用开源组件，推荐这个，在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. 

摘自　;