MVC 导出CSV

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

导出Excel

方法一

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

如 http://stephenwalther.com/archive/2008/06/16/asp-net-mvc-tip-2-create-a-custom-action-result-that-returns-microsoft-excel-documents.aspx

方法二

先生成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. 

摘自　http://unicode.org/faq/utf_bom.html#bom1;