Excel表导出_.NET_编程开发_程序员俱乐部

 前言

分别介绍两种导出Exce表格的方法。

1、在MVC下的表格导出。

2、基于NPOI的表格导出。

第一种方法：在MVC下的表格导出。

首先，创建一个数据model，代码如下：

class="code_img_closed" src="/Upload/Images/2013121717/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('c8b1074a-8a97-41e0-97c0-5ce02dde99f0',event)" src="/Upload/Images/2013121717/2B1B950FA3DF188F.gif" alt="" />

1 public class ListModel
2     {
3         public int Id { get; set; }
4         public string Name { get; set; }
5         public string Password { get; set; }
6     }

View Code

一个表格model，代码如下：

1  public class ExcelModel
2     {
3         [Display(Name = "ID")]
4         public int Id { get; set; }
5          [Display(Name = "第一列")]
6         public string Head { get; set; }
7          [Display(Name = "第二列")]
8         public string Center { get; set; }
9     }

View Code

其次，创建一个操作表格的类，代码如下：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.ComponentModel.DataAnnotations;
  5 using System.Drawing;
  6 using System.IO;
  7 using System.Linq;
  8 using System.Text;
  9 using System.Web;
 10 using System.Web.Mvc;
 11 using System.Web.UI;
 12 using System.Web.UI.WebControls;
 13 
 14 namespace Excel1.Helper
 15 {
 16     public class EecelHelper<T>:ActionResult
 17     {
 18         private string _fileName;
 19         private List<T> _rows;
 20         private List<string> _headers;
 21         private TableStyle _tableStyle;
 22         private TableItemStyle _headerSytle;
 23         private TableItemStyle _itemStyle;
 24         public string FileName
 25         {
 26             get { return _fileName; }
 27         }
 28         public List<T>Rows
 29         {
 30             get { return _rows; }
 31         }
 32         public EecelHelper(List<T> rows,string fileName )
 33             :this(rows,fileName,null,null,null,null)
 34         {
 35             
 36         }
 37         public EecelHelper(List<T>rows,string fileName,List<string>headers  )
 38             :this(rows,fileName,headers,null,null,null)
 39         {
 40             
 41         }
 42         public EecelHelper(List<T> rows, string fileName, List<string> headers,TableStyle tableStyle,TableItemStyle headerSytle,TableItemStyle itemStyle)
 43 
 44         {
 45             _rows = rows;
 46             _fileName = string.IsNullOrEmpty(fileName) ? DateTime.Now.ToString("yyyyMMddHHmmss") : fileName;
 47             _headers = headers;
 48             _tableStyle = tableStyle;
 49             _headerSytle = headerSytle;
 50             _itemStyle = itemStyle;
 51             if(_tableStyle==null)
 52             {
 53                 _tableStyle = new TableStyle();
 54                 _tableStyle.BorderStyle = BorderStyle.Solid;
 55                 _tableStyle.BorderColor = Color.Black;
 56                 _tableStyle.BorderWidth = Unit.Parse("2px");
 57             }
 58             if(_headerSytle == null)
 59             {
 60                 _headerSytle = new TableItemStyle();
 61                 _headerSytle.BackColor = Color.LightGray;
 62             }
 63         }
 64 
 65         public override void ExecuteResult(ControllerContext context)
 66         {
 67             StringWriter sw = new StringWriter();
 68             HtmlTextWriter tw = new HtmlTextWriter(sw);
 69 
 70             if(_tableStyle !=null)
 71             {
 72                 _tableStyle.AddAttributesToRender(tw);
 73             }
 74             tw.RenderBeginTag(HtmlTextWriterTag.Table);
 75             var properties = TypeDescriptor.GetProperties(typeof (T));
 76 
 77             if(_headers == null)
 78             {
 79                 _headers = new List<string>();
 80                 for(int i=0;i<properties.Count;i++)
 81                 {
 82                     var attr = typeof (T).GetProperty(properties[i].Name).GetCustomAttributes(
 83                         typeof (DisplayAttribute), true);
 84                     _headers.Add(attr.Length>0?((DisplayAttribute)attr[0]).Name:properties[i].Name);
 85                 }
 86             }
 87             tw.RenderBeginTag(HtmlTextWriterTag.Thead);
 88             foreach (string header in _headers) 
 89             {
 90                 if(_headerSytle != null)
 91                     _headerSytle.AddAttributesToRender(tw);
 92                 tw.RenderBeginTag(HtmlTextWriterTag.Th);
 93                 tw.Write(header);
 94                 tw.RenderEndTag();
 95             }
 96 
 97             tw.RenderBeginTag(HtmlTextWriterTag.Tbody);
 98             foreach (var row in _rows)
 99             {
100                 tw.RenderBeginTag(HtmlTextWriterTag.Tr);
101                 for(int i=0;i<properties.Count;i++)
102                 {
103                     var strValue = properties[i].GetValue(row) == null
104                                        ? string.Empty
105                                        : properties[i].GetValue(row).ToString();
106                     strValue = ReplaceSpecialCharacters(strValue);
107                     if(_itemStyle != null)
108                         _itemStyle.AddAttributesToRender(tw);
109                     tw.RenderBeginTag(HtmlTextWriterTag.Td);
110                     tw.Write(HttpUtility.HtmlEncode(strValue));
111                     tw.RenderEndTag();
112                 }
113                 tw.RenderEndTag();
114             }
115             tw.RenderEndTag();
116             tw.RenderEndTag();
117             WriteFile(_fileName, "application/ms-excel", sw.ToString());
118         }
119 
120         private static string ReplaceSpecialCharacters(string value)
121         {
122             value = value.Replace("' ", "'");
123             value = value.Replace("“", "\"");
124             value = value.Replace("”", "\"");
125             value = value.Replace("—", "-");
126             value = value.Replace("...", "");
127             return value;
128         }
129         private static void WriteFile(string fileName,string contentType,string content)
130         {
131             HttpContext context = HttpContext.Current;
132             context.Response.Clear();
133             context.Response.AddHeader("content-disposition","attachment;filename="+fileName+".xls");
134             context.Response.ContentEncoding = Encoding.Default;
135             context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
136             context.Response.ContentType = contentType;
137             context.Response.Write(content);
138             context.Response.End();
139         }
140     }
141 }
142 
143  

View Code

最后，新建一个方法，这个方法是用来，把数据model转换成表格model的，代码如下：

 1 public class Mappers
 2     {
 3         public static ExcelModel ExcelModel(ListModel model)
 4         {
 5             return new ExcelModel
 6                        {
 7                            Id = model.Id,
 8                            Head = model.Name,
 9                            Center = model.Password,
10                        };
11         }
12     }

View Code

在控制器下，就可以调用上面创建的操作表格类进行处理了，代码如下：

 1 public ActionResult Down()
 2         {
 3             List<ListModel> list = new List<ListModel>();
 4             ListModel model = new ListModel();
 5             model.Id = 1111;
 6             model.Name = "小明";
 7             model.Password = "123456";
 8             list.Add(model);
 9             var excelList = list.Select(Mappers.ExcelModel).ToList();
10             return new EecelHelper<ExcelModel>(excelList.ToList(), "我的Excel");
11         }

View Code

 

第二种方法：基于NPOI的表格导出。

 首先下载NPOI.dll http://files.cnblogs.com/zl879211310/bils.rar