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.