Excel表导出

 前言

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

1、在MVC下的表格导出。

2、基于NPOI的表格导出。

3、读取Excel表格数据。

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

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

public class ListModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Password { get; set; }
    }

一个表格model，代码如下：

 public class ExcelModel
    {
        [Display(Name = "ID")]
        public int Id { get; set; }
         [Display(Name = "第一列")]
        public string Head { get; set; }
         [Display(Name = "第二列")]
        public string Center { get; set; }
    }

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

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Excel1.Helper
{
    public class EecelHelper<T>:ActionResult
    {
        private string _fileName;
        private List<T> _rows;
        private List<string> _headers;
        private TableStyle _tableStyle;
        private TableItemStyle _headerSytle;
        private TableItemStyle _itemStyle;
        public string FileName
        {
            get { return _fileName; }
        }
        public List<T>Rows
        {
            get { return _rows; }
        }
        public EecelHelper(List<T> rows,string fileName )
            :this(rows,fileName,null,null,null,null)
        {
            
        }
        public EecelHelper(List<T>rows,string fileName,List<string>headers  )
            :this(rows,fileName,headers,null,null,null)
        {
            
        }
        public EecelHelper(List<T> rows, string fileName, List<string> headers,TableStyle tableStyle,TableItemStyle headerSytle,TableItemStyle itemStyle)

        {
            _rows = rows;
            _fileName = string.IsNullOrEmpty(fileName) ? DateTime.Now.ToString("yyyyMMddHHmmss") : fileName;
            _headers = headers;
            _tableStyle = tableStyle;
            _headerSytle = headerSytle;
            _itemStyle = itemStyle;
            if(_tableStyle==null)
            {
                _tableStyle = new TableStyle();
                _tableStyle.BorderStyle = BorderStyle.Solid;
                _tableStyle.BorderColor = Color.Black;
                _tableStyle.BorderWidth = Unit.Parse("2px");
            }
            if(_headerSytle == null)
            {
                _headerSytle = new TableItemStyle();
                _headerSytle.BackColor = Color.LightGray;
            }
        }

        public override void ExecuteResult(ControllerContext context)
        {
            StringWriter sw = new StringWriter();
            HtmlTextWriter tw = new HtmlTextWriter(sw);

            if(_tableStyle !=null)
            {
                _tableStyle.AddAttributesToRender(tw);
            }
            tw.RenderBeginTag(HtmlTextWriterTag.Table);
            var properties = TypeDescriptor.GetProperties(typeof (T));

            if(_headers == null)
            {
                _headers = new List<string>();
                for(int i=0;i<properties.Count;i++)
                {
                    var attr = typeof (T).GetProperty(properties[i].Name).GetCustomAttributes(
                        typeof (DisplayAttribute), true);
                    _headers.Add(attr.Length>0?((DisplayAttribute)attr[0]).Name:properties[i].Name);
                }
            }
            tw.RenderBeginTag(HtmlTextWriterTag.Thead);
            foreach (string header in _headers) 
            {
                if(_headerSytle != null)
                    _headerSytle.AddAttributesToRender(tw);
                tw.RenderBeginTag(HtmlTextWriterTag.Th);
                tw.Write(header);
                tw.RenderEndTag();
            }

            tw.RenderBeginTag(HtmlTextWriterTag.Tbody);
            foreach (var row in _rows)
            {
                tw.RenderBeginTag(HtmlTextWriterTag.Tr);
                for(int i=0;i<properties.Count;i++)
                {
                    var strValue = properties[i].GetValue(row) == null
                                       ? string.Empty
                                       : properties[i].GetValue(row).ToString();
                    strValue = ReplaceSpecialCharacters(strValue);
                    if(_itemStyle != null)
                        _itemStyle.AddAttributesToRender(tw);
                    tw.RenderBeginTag(HtmlTextWriterTag.Td);
                    tw.Write(HttpUtility.HtmlEncode(strValue));
                    tw.RenderEndTag();
                }
                tw.RenderEndTag();
            }
            tw.RenderEndTag();
            tw.RenderEndTag();
            WriteFile(_fileName, "application/ms-excel", sw.ToString());
        }

        private static string ReplaceSpecialCharacters(string value)
        {
            value = value.Replace("' ", "'");
            value = value.Replace("“", "\"");
            value = value.Replace("”", "\"");
            value = value.Replace("—", "-");
            value = value.Replace("...", "");
            return value;
        }
        private static void WriteFile(string fileName,string contentType,string content)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            context.Response.AddHeader("content-disposition","attachment;filename="+fileName+".xls");
            context.Response.ContentEncoding = Encoding.Default;
            context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            context.Response.ContentType = contentType;
            context.Response.Write(content);
            context.Response.End();
        }
    }
}

 

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

public class Mappers
    {
        public static ExcelModel ExcelModel(ListModel model)
        {
            return new ExcelModel
                       {
                           Id = model.Id,
                           Head = model.Name,
                           Center = model.Password,
                       };
        }
    }

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

public ActionResult Down()
        {
            List<ListModel> list = new List<ListModel>();
            ListModel model = new ListModel();
            model.Id = 1111;
            model.Name = "小明";
            model.Password = "123456";
            list.Add(model);
            var excelList = list.Select(Mappers.ExcelModel).ToList();
            return new EecelHelper<ExcelModel>(excelList.ToList(), "我的Excel");
        }

 

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

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

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Text;
using System.Windows.Forms;


namespace Ajax
{
    /// <summary>
    /// 0.引用bils文件夹的dll
    /// 1.新建一个model，里面的字段就是要导出的字段
    /// 2.输入List<T>数据，FilePath=@"E:\"
    /// 3.每行根据model的字段添加
    /// </summary>
    public class WorkBook
    {
        public void BuildWorkBook(List<Student> models,string FilePath)  //修改
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet("第一页");
            HSSFRow rowHead = sheet.CreateRow(0);
            rowHead.CreateCell(0).SetCellValue("姓名");                      //修改
            rowHead.CreateCell(1).SetCellValue("年龄");


            for (int rowIndex = 0; rowIndex < models.Count(); rowIndex++)
            {
                HSSFRow row = sheet.CreateRow(rowIndex + 1);
                row.CreateCell(0).SetCellValue(models[rowIndex].Name);      // 修改
                row.CreateCell(1).SetCellValue(models[rowIndex].Age);
            }


            using (FileStream fs = new FileStream(FilePath + DateTime.Now.ToString("yyyy-MM-dd") + ".xls", FileMode.Create))
            {
                workbook.Write(fs);
            }
        }
    }
}

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

 List<Student> model=new List<Student> (){
            new Student {Name="小张",Age=23},
            new Student {Name="小张1",Age=243},
            new Student {Name="小张2",Age=223},
            new Student {Name="小张3",Age=263},
            new Student {Name="小张4",Age=3} 
            };
            WorkBook work = new WorkBook(); 
            
            string path = @"E:\";
            try
            {
                work.BuildWorkBook(model, path);
                Response.Write("<script>alert('导出成功');</script>");
            }
            catch (Exception)
            { 
                Response.Write("<script>alert('导出失败');</script>");
            }

 

读取Excel表格数据

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

 //获得excel表的地址
        private string GetPath(string path)
        {
            var separetor = Path.DirectorySeparatorChar;
            return string.Format(path, separetor);
        }
        //excel表的版本
        private const string ExcelDefaultVersion = "12.0";
        //连接字符串
        private const string ConnectionStringTemplate =
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel {1};HDR=YES;IMEX=1'";//兼容office2003、office2007；

        
        private static string GetConnectionString(string excelPath,string excelVersion)
        {
            return string.Format(CultureInfo.InvariantCulture, ConnectionStringTemplate, excelPath, excelVersion);
        }

        //查询
        private static DataSet QueryBySheetName(OleDbConnection conn, string sheetName)
        {
            var query = string.Format("select * from [{0}]", sheetName);
            var adp = new OleDbDataAdapter(query, conn);
            var dt = new DataSet();
            adp.Fill(dt,"book");//填充到DataSet的 表格名为book的table里。表格名随便取
            return dt;
        }

        //创建连接字符串
        private static OleDbConnection CreateConnection(string excelPath, string excelVersion = ExcelDefaultVersion)
        {
            return new OleDbConnection(GetConnectionString(excelPath,excelVersion));
        }

        //查询工作表
        public static DataSet Query(string excelPath, string sheetName)
        {
            using (var conn = CreateConnection(excelPath))
            {
                var dt = QueryBySheetName(conn, sheetName);
                return dt;
            }
        }

2、调用，代码如下：

 public ActionResult Index()
        {
            var path = Server.MapPath(GetPath("{0}Content{0}Hero.xlsx"));//获得excel表格地址
            DataSet table = Query(path, "Sheet1$");// "Sheet1$"这是读取excel表的哪个工作表,记得加上$
            var tables = table.Tables["book"];

            for (int i = 0; i <= tables.Rows.Count; i++)
            {
                if (tables.Rows[i][0].ToString() != null)
                {
                    if (tables.Rows[i][0].ToString() == "10001")
                    {
                        Response.Write(tables.Rows[i][2]);
                        break; 
                    }
                } 
            } 
            return View();
        }

 

结束

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