使用NPOI导出Excel操作的改进

NPOI导出Excel是比较方便的，可以设置标题，列宽等，原先我导出Excel的方法如下：

/// <summary>
        /// 导出到Excel文件
        /// </summary>
        /// <param name="dt">表</param>
        /// <param name="sheetname">文件名</param>
        /// <param name="dictList">标题头与列宽</param>
        /// <param name="context">上下文</param>
        public void WriteExcel(DataTable dt, string sheetname, Dictionary<string, int> dictList, HttpContextBase context)
        { 
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
             
            if (dt != null && dt.Rows.Count > 0)
            {
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);

                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);

                //标题头与列宽
                var dtList = dictList.ToList();
                for (int i = 0; i < dtList.Count; i++)
                {
                    sheet.SetColumnWidth(i, dtList[i].Value);
                    row.CreateCell(i).SetCellValue(dtList[i].Key);
                }

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                } 
            }
            using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
            {
                book.Write(ms);
                context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", sheetname));
                context.Response.BinaryWrite(ms.ToArray());
                book = null;
                ms.Close();
                ms.Dispose();
            }
        }

需要传入DataTable,然后将表的标题和列宽以字典形式传入。这样的做法比较麻烦，标题和数据是分别传入，而且会将dt上所有的列都导出。所有需要将dt上的列和字典中的数据一一对应，不然导出的Excel会错位。当然也可以自己定义个类改传入标题，字段名，列宽，这样会比较方便的，dt导出数据时可以根据传入字典中的字段来判断需要导入哪些字段的数据。

但是现在使用DataTable越来越少了，直接改为传入List<T>集合的形式，我们可以定义标题特性的，在dto中进行设置然后导入时通过反射的方式来回去标题内容等信息，这样会更方便。

标题特性定义

/*
 * 导出Excel的相关特性设置
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyUtility.attribute
{
    [AttributeUsage(AttributeTargets.Property,Inherited=true)]
    public class TitleAttribute:Attribute
    {
        public string TitleName { get; set; }
        public int Width { get; set; }

        /// <summary>
        /// 导出Excel的相关特性
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="width">列宽度字符长度设置</param>
        public TitleAttribute(string title,int width)
        {
            TitleName = title;
            Width = width;
        }
    }
}

相关Dto设置 我们只需设置我们需要导出Excel字段的特性就行了，这样方便我们查看的时候和导出时的格式数据的差异性,如下我们只会导出工号，姓名，职位三个字段到Excel中

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyUtility.attribute;

namespace MyMis.DTO
{
    public class empDto
    {
        [Title("工号",10)]
        public string account { get; set; }
        [Title("姓名", 10)]
        public string name { get; set; }
        [Title("职位", 10)]
        public string duty { get; set; }
        public string depid { get; set; }
        public string state { get; set; }
        public string py { get; set; }
        public string tel { get; set; }
        public string email { get; set; }
        public status status { get; set; }
        public string password { get; set; }
        public string pstatus { get; set; }
        public Nullable<System.DateTime> pdate { get; set; }
        public Nullable<System.DateTime> preset { get; set; }
        public string preseter { get; set; }
        public string smsstatus { get; set; }
        public string emailstatus { get; set; }
    }
}

导出Excel类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Web;
using MyUtility.attribute;

namespace MyUtility
{
    public static class MyExcel
    {

        /// <summary>
        /// 导出到Excel文件 根据Dto设置的相关Title属性进行导出
        /// </summary>
        /// <param name="dt">表</param>
        /// <param name="sheetname">文件名</param>
        /// <param name="context">上下文</param>
        public static void WriteExcel<T>(List<T> ExpList, string sheetname, HttpContextBase context)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

            if (ExpList != null && ExpList.Count() > 0)
            {
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);

                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);

                //取得导出字段标题宽度相关信息
                List<ExpDto> list = GetExpFields<T>();

                //设置标题与列宽
                for (int i = 0; i < list.Count; i++)
                {
                    sheet.SetColumnWidth(i, list[i].width * 256);//参数的单位是1/256个字符宽度
                    row.CreateCell(i).SetCellValue(list[i].title);
                }

                //设置导出数据
                for (int i = 0; i < ExpList.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < list.Count; j++)
                    {
                        T obj = ExpList[i];
                        row2.CreateCell(j).SetCellValue(GetValue<T>(ExpList[i], list[j].field));
                    }
                }
            }
            using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
            {
                book.Write(ms);
                context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", sheetname));
                context.Response.BinaryWrite(ms.ToArray());
                book = null;
                ms.Close();
                ms.Dispose();
            }
        }

        /// <summary>
        /// 取得导出Excel相关字段 根据Title特性来判断
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<ExpDto> GetExpFields<T>()
        {
            List<ExpDto> list = new List<ExpDto>();
            var fields = typeof(T).GetProperties();//获取所有属性
            foreach (var item in fields)
            {
                var attribute = item.GetCustomAttributes(typeof(TitleAttribute), true);//取得属性上是否存在指定的特性
                if (attribute.Any())
                {
                    ExpDto exp = new ExpDto();
                    exp.field = item.Name;
                    exp.title = ((TitleAttribute)attribute[0]).TitleName;
                    exp.width = ((TitleAttribute)attribute[0]).Width;
                    list.Add(exp);
                }
            }
            return list;
        }

        /// <summary>
        /// 取得指定字段的值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj">obj</param>
        /// <param name="key">属性名</param>
        /// <returns></returns>
        public static string GetValue<T>(T obj, string key)
        {
            var prop = obj.GetType().GetProperty(key);//取得指定属性
            var type = prop.PropertyType.Name;//取得指定属性的字段类型
            var value = prop.GetValue(obj);//从对象上取得值
            return value==null ? null : value.ToString();
        }

        /// <summary>
        /// 导出Excel标题设置数据对象
        /// </summary>
        public class ExpDto
        {
            public string field { get; set; }
            public string title { get; set; }
            public int width { get; set; }
        }
    }
}

直接调用此方法就行了