Excel表导出

前言

分别介绍两种导出Exce表格的方法和读取Excel表格数据方法。

1、在MVC下的表格导出。

2、基于NPOI的表格导出。

3、读取Excel表格数据。

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

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

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

其次，把NPOI.dll 放到项目中，创建一个表格操作类，代码如下：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using NPOI.HSSF.UserModel;
 6 using System.IO;
 7 using System.Text;
 8 using System.Windows.Forms;
 9 
10 
11 namespace Ajax
12 {
13     /// <summary>
14     /// 0.引用bils文件夹的dll
15     /// 1.新建一个model，里面的字段就是要导出的字段
16     /// 2.输入List<T>数据，FilePath=@"E:"
17     /// 3.每行根据model的字段添加
18     /// </summary>
19     public class WorkBook
20     {
21         public void BuildWorkBook(List<Student> models,string FilePath)  //修改
22         {
23             HSSFWorkbook workbook = new HSSFWorkbook();
24             HSSFSheet sheet = workbook.CreateSheet("第一页");
25             HSSFRow rowHead = sheet.CreateRow(0);
26             rowHead.CreateCell(0).SetCellValue("姓名");                      //修改
27             rowHead.CreateCell(1).SetCellValue("年龄");
28 
29 
30             for (int rowIndex = 0; rowIndex < models.Count(); rowIndex++)
31             {
32                 HSSFRow row = sheet.CreateRow(rowIndex + 1);
33                 row.CreateCell(0).SetCellValue(models[rowIndex].Name);      // 修改
34                 row.CreateCell(1).SetCellValue(models[rowIndex].Age);
35             }
36 
37 
38             using (FileStream fs = new FileStream(FilePath + DateTime.Now.ToString("yyyy-MM-dd") + ".xls", FileMode.Create))
39             {
40                 workbook.Write(fs);
41             }
42         }
43     }
44 }

View Code

最后，调用就可以了，代码如下：

 1  List<Student> model=new List<Student> (){
 2             new Student {Name="小张",Age=23},
 3             new Student {Name="小张1",Age=243},
 4             new Student {Name="小张2",Age=223},
 5             new Student {Name="小张3",Age=263},
 6             new Student {Name="小张4",Age=3} 
 7             };
 8             WorkBook work = new WorkBook(); 
 9             
10             string path = @"E:";
11             try
12             {
13                 work.BuildWorkBook(model, path);
14                 Response.Write("<script>alert('导出成功');</script>");
15             }
16             catch (Exception)
17             { 
18                 Response.Write("<script>alert('导出失败');</script>");
19             }

View Code

读取Excel表格数据

1、读取excel表格需要的方法，代码如下：

 1  //获得excel表的地址
 2         private string GetPath(string path)
 3         {
 4             var separetor = Path.DirectorySeparatorChar;
 5             return string.Format(path, separetor);
 6         }
 7         //excel表的版本
 8         private const string ExcelDefaultVersion = "12.0";
 9         //连接字符串
10         private const string ConnectionStringTemplate =
11             "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel {1};HDR=YES;IMEX=1'";//兼容office2003、office2007；
12 
13         
14         private static string GetConnectionString(string excelPath,string excelVersion)
15         {
16             return string.Format(CultureInfo.InvariantCulture, ConnectionStringTemplate, excelPath, excelVersion);
17         }
18 
19         //查询
20         private static DataSet QueryBySheetName(OleDbConnection conn, string sheetName)
21         {
22             var query = string.Format("select * from [{0}]", sheetName);
23             var adp = new OleDbDataAdapter(query, conn);
24             var dt = new DataSet();
25             adp.Fill(dt,"book");//填充到DataSet的 表格名为book的table里。表格名随便取
26             return dt;
27         }
28 
29         //创建连接字符串
30         private static OleDbConnection CreateConnection(string excelPath, string excelVersion = ExcelDefaultVersion)
31         {
32             return new OleDbConnection(GetConnectionString(excelPath,excelVersion));
33         }
34 
35         //查询工作表
36         public static DataSet Query(string excelPath, string sheetName)
37         {
38             using (var conn = CreateConnection(excelPath))
39             {
40                 var dt = QueryBySheetName(conn, sheetName);
41                 return dt;
42             }
43         }

View Code

2、调用，代码如下：

 1  public ActionResult Index()
 2         {
 3             var path = Server.MapPath(GetPath("{0}Content{0}Hero.xlsx"));//获得excel表格地址
 4             DataSet table = Query(path, "Sheet1$");// "Sheet1$"这是读取excel表的哪个工作表,记得加上$
 5             var tables = table.Tables["book"];
 6 
 7             for (int i = 0; i <= tables.Rows.Count; i++)
 8             {
 9                 if (tables.Rows[i][0].ToString() != null)
10                 {
11                     if (tables.Rows[i][0].ToString() == "10001")
12                     {
13                         Response.Write(tables.Rows[i][2]);
14                         break; 
15                     }
16                 } 
17             } 
18             return View();
19         }

View Code

结束

两个小例子，只是简单的实现，为的是用过记录下来。