NPOI MVC浏览器下载形式和指定路径导出Excel

转载NPOI 两种方式导出Excel，浏览器和指定路径导出
web MVC程序实现
思路和流程如下
新建web MVC程序 ，我就命名WebApplication1好了
程序NuGet引入NPOI类库，两种引入方法：第一种，程序包管理控制台输入Install-Package NPOI；第二种在管理解决方案NuGet程序包中次搜索NPOI然后安装。
Models文件下新建一个模型StudentEntity
Home/Index页面加两个a标签，用作导出按钮
Home控制器写导出方法，完成，
第1和第2点就不说了直接第3点，开始实现----------啥都别说了，代码走起
第三点注意：这是模型反射原理，不熟悉也没事，复制进去就好

    public class StudentEntity
    {
        [Description("姓名")]
        public string name { get; set; }

        [Description("年龄")]
        public int age { get; set; }

        [Description("地址")]
        public string address { get; set; }

        [Description("手机号码")]
        public string telphone { get; set; }
    }

第四点：为了代码清晰，删掉了自带的代码，然后加一个a标签

@{
    ViewBag.Title = "Home Page";
}
<div>
    <a href="/Home/WebExport">浏览器导出</a>
</div>
<div>
    <a href="/Home/Export">指定路径导出</a>
</div>

第五点：啥都不说，复制代码进去吧，都有注释了。指定路径的导出不用新建一个Excel文件，代码自动识别有没有文件的，有就覆盖没有就自动新建，演示的放在F盘的根目录，当然，路径都可以自己改，文件名也是。

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication1.Helper;
using WebApplication1.Models;

namespace WebApplication1.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }
        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
        //浏览器导出
        public ActionResult WebExport()
        {
            StudentEntity se1 = new StudentEntity() { name = "张三", age = 20, address = "上海", telphone = "16278171615" };
            StudentEntity se2 = new StudentEntity() { name = "李四", age = 18, address = "北京", telphone = "19278187590" };
            StudentEntity se3 = new StudentEntity() { name = "王五", age = 19, address = "广州", telphone = "18278187590" };

            List<StudentEntity> selist = new List<StudentEntity>();
            selist.Add(se1);
            selist.Add(se2);
            selist.Add(se3);
            return ExportToExcelWeb(selist, "学生信息", "学生信息表");
        }
        //浏览器导出
        public FileResult ExportToExcelWeb<T>(List<T> data, string head, string sheetName)
        {
            IWorkbook wb = new HSSFWorkbook();
            //设置工作簿的名称
            sheetName = string.IsNullOrEmpty(sheetName) ? "sheet1" : sheetName;
            //创建一个工作簿
            ISheet sh = wb.CreateSheet(sheetName);

            //全局索引
            int gloal_index = 0;
            System.Reflection.PropertyInfo[] oProps = null;
            foreach (T en in data)
            {
                if (oProps == null)
                {
                    oProps = ((Type)en.GetType()).GetProperties();
                }
                if (gloal_index == 0)
                {
                    #region 表头(第1行)
                    //合并单元格
                    sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, oProps.Length - 1));
                    //创建第1行
                    IRow row0 = sh.CreateRow(0);
                    //设置第1行高度
                    row0.Height = 20 * 20;
                    //创建第1行第1列
                    ICell icell1top0 = row0.CreateCell(0);
                    //设置第1行第1列格式
                    icell1top0.CellStyle = Getcellstyle(wb, "head");
                    //设置第1行第1列内容
                    icell1top0.SetCellValue(head);
                    #endregion

                    #region 抬头(第2行)
                    //创建第2行
                    IRow row1 = sh.CreateRow(1);
                    //设置高度
                    row1.Height = 20 * 20;
                    //columnt_index是列的索引
                    int columnt_index = 0;
                    foreach (System.Reflection.PropertyInfo item in oProps)
                    {
                        //获取T的字段名称
                        string name = item.Name;
                        //获取T的字段名称的描述
                        string des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;

                        //创建第2行的第columnt_index列
                        ICell icell1top = row1.CreateCell(columnt_index);
                        //设置第2行的第columnt_index列的格式
                        icell1top.CellStyle = Getcellstyle(wb, "");
                        //设置第2行的第columnt_index列的内容
                        if (!string.IsNullOrEmpty(des))
                        {
                            icell1top.SetCellValue(des);
                        }
                        else
                        {
                            icell1top.SetCellValue(name);
                        }
                        //设置第2行的第columnt_index列的宽度
                        sh.SetColumnWidth(columnt_index, (int)((15 + 0.72) * 256));
                        columnt_index++;
                    }
                    #endregion

                    gloal_index = 2;
                }

                #region 这里是List<T>具体内容
                //创建第gloal_index行
                IRow row_zs = sh.CreateRow(gloal_index);
                int column_index = 0;
                foreach (System.Reflection.PropertyInfo pi in oProps)
                {
                    //创建第gloal_index行的第columnt_index列
                    ICell icell1top = row_zs.CreateCell(column_index);
                    //设置第gloal_index行的第columnt_index列格式
                    icell1top.CellStyle = Getcellstyle(wb, "");
                    //获取en字段值
                    string v_value = pi.GetValue(en, null) == null ? "" : pi.GetValue(en, null).ToString();
                    //设置第gloal_index行的第columnt_index列的内容
                    icell1top.SetCellValue(v_value);

                    column_index++;
                }
                #endregion

                gloal_index++;
            }

            //输出内容 指定路径
            //using (FileStream stm = File.OpenWrite(@"E:\studentInfo.xls"))
            //{
            //    wb.Write(stm);
            //}
            //输出内容 浏览器输出
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Write(ms);
                byte[] buffers = ms.ToArray();
                return File(buffers, "application/vnd.ms-excel", string.Format("{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));

            }
        }
        //指定路径导出
        public void Export()
        {
            StudentEntity se1 = new StudentEntity() { name = "张三", age = 20, address = "上海", telphone = "16278171615" };
            StudentEntity se2 = new StudentEntity() { name = "李四", age = 18, address = "北京", telphone = "19278187590" };
            StudentEntity se3 = new StudentEntity() { name = "王五", age = 19, address = "广州", telphone = "18278187590" };

            List<StudentEntity> selist = new List<StudentEntity>();
            selist.Add(se1);
            selist.Add(se2);
            selist.Add(se3);
            ExportToExcel(selist, "学生信息", "学生信息表");
        }
        //指定路径导出
        public void ExportToExcel<T>(List<T> data, string head, string sheetName)
        {
            IWorkbook wb = new HSSFWorkbook();
            //设置工作簿的名称
            sheetName = string.IsNullOrEmpty(sheetName) ? "sheet1" : sheetName;
            //创建一个工作簿
            ISheet sh = wb.CreateSheet(sheetName);

            //全局索引
            int gloal_index = 0;
            System.Reflection.PropertyInfo[] oProps = null;
            foreach (T en in data)
            {
                if (oProps == null)
                {
                    oProps = ((Type)en.GetType()).GetProperties();
                }
                if (gloal_index == 0)
                {
                    #region 表头(第1行)
                    //合并单元格
                    sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, oProps.Length - 1));
                    //创建第1行
                    IRow row0 = sh.CreateRow(0);
                    //设置第1行高度
                    row0.Height = 20 * 20;
                    //创建第1行第1列
                    ICell icell1top0 = row0.CreateCell(0);
                    //设置第1行第1列格式
                    icell1top0.CellStyle = Getcellstyle(wb, "head");
                    //设置第1行第1列内容
                    icell1top0.SetCellValue(head);
                    #endregion

                    #region 抬头(第2行)
                    //创建第2行
                    IRow row1 = sh.CreateRow(1);
                    //设置高度
                    row1.Height = 20 * 20;
                    //columnt_index是列的索引
                    int columnt_index = 0;
                    foreach (System.Reflection.PropertyInfo item in oProps)
                    {
                        //获取T的字段名称
                        string name = item.Name;
                        //获取T的字段名称的描述
                        string des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;

                        //创建第2行的第columnt_index列
                        ICell icell1top = row1.CreateCell(columnt_index);
                        //设置第2行的第columnt_index列的格式
                        icell1top.CellStyle = Getcellstyle(wb, "");
                        //设置第2行的第columnt_index列的内容
                        if (!string.IsNullOrEmpty(des))
                        {
                            icell1top.SetCellValue(des);
                        }
                        else
                        {
                            icell1top.SetCellValue(name);
                        }
                        //设置第2行的第columnt_index列的宽度
                        sh.SetColumnWidth(columnt_index, (int)((15 + 0.72) * 256));
                        columnt_index++;
                    }
                    #endregion

                    gloal_index = 2;
                }

                #region 这里是List<T>具体内容
                //创建第gloal_index行
                IRow row_zs = sh.CreateRow(gloal_index);
                int column_index = 0;
                foreach (System.Reflection.PropertyInfo pi in oProps)
                {
                    //创建第gloal_index行的第columnt_index列
                    ICell icell1top = row_zs.CreateCell(column_index);
                    //设置第gloal_index行的第columnt_index列格式
                    icell1top.CellStyle = Getcellstyle(wb, "");
                    //获取en字段值
                    string v_value = pi.GetValue(en, null) == null ? "" : pi.GetValue(en, null).ToString();
                    //设置第gloal_index行的第columnt_index列的内容
                    icell1top.SetCellValue(v_value);

                    column_index++;
                }
                #endregion

                gloal_index++;
            }

            // 输出内容
            using (FileStream stm = System.IO.File.OpenWrite(@"F:\studentInfo.xls"))
            {
                wb.Write(stm);
            }
            //MemoryStream ms = new MemoryStream();
            //string filename = DateTime.Now.ToString("yyyyMMddHHmmss");
            //wb.Write(ms);
            //ms.Seek(0, SeekOrigin.Begin);
        }
        /// <summary>
        /// 格式设置
        /// </summary>
        static ICellStyle Getcellstyle(IWorkbook wb, string type)
        {
            ICellStyle cellStyle = wb.CreateCellStyle();
            //定义字体  
            IFont font = wb.CreateFont();
            font.FontName = "微软雅黑";
            //水平对齐  
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
            //垂直对齐  
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            //自动换行  
            cellStyle.WrapText = true;
            //缩进
            cellStyle.Indention = 0;

            switch (type)
            {
                case "head":
                    cellStyle.SetFont(font);
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    break;
                default:
                    cellStyle.SetFont(font);
                    break;
            }
            return cellStyle;
        }
    }
}

获取Excel指定单元格示例(先安装Microsoft.Office.Interop.Excel包)

///<summary>
        /// 获取指定文件的指定单元格内容
        ///</summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="row">行号</param>
        /// <param name="column">列号</param>
        /// <returns>返回单元指定单元格内容</returns>
        public string getExcelOneCell(string fileName, int row, int column)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(fileName, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing);
 
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)wbook.Worksheets[1];
 
            string temp = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[row, column]).Text.ToString();
 
            wbook.Close(false, fileName, false);
            app.Quit();
            NAR(app);
            NAR(wbook);
            NAR(workSheet); 
            return temp;
 
        }
 
        //此函数用来释放对象的相关资源
        private void NAR(Object o)
        {
            try
            {
                //使用此方法，来释放引用某些资源的基础 COM 对象。 这里的o就是要释放的对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally
            {
                o = null; GC.Collect();
            }
        }