通用Excel导出

导出Excel时，经常会遇到需要重复性的书写每个列的名称，以此造成代码编写的重复书写，例如：

为了避免这种编写，我们可以采取特性加反射的形式进行代码解耦。代码例子如下：

代码封装：

using Model.Attribute;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;

namespace Services
{
    public class OfficeService
    {
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="list">数据列表</param>
        /// <returns></returns>
        public static MemoryStream ExportRecords<T>(List<T> list)
        {
            try
            {
                if (!list.Any())
                {
                    HttpContext.Current.Response.Write("没有数据需要导出");
                    return null;
                }
                var dt = ToExcelDataTable(list);//关键核心
                return DataTableToExcel(dt);//导出Excel的方法可以自己定义
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write("导出失败，失败原因：" + ex.Message);
            }
            return null;
        }

        /// <summary>
        /// 将泛型集合类转换成DataTable(只针对导出EXCEL)
        /// </summary>
        /// <typeparam name="T">集合项类型</typeparam>
        /// <param name="list">集合</param>
        /// <returns>数据集(表)</returns>
        public static DataTable ToExcelDataTable<T>(IList<T> list)
        {
            DataTable result = new DataTable();

            PropertyInfo[] propertys = typeof(T).GetProperties();
            foreach (PropertyInfo pi in propertys)
            {
                if (pi.IsDefined(typeof(ExportPropAttribute), true))
                {
                    result.Columns.Add(pi.Name.GetExtportName<T>(), pi.PropertyType == typeof(int) || pi.PropertyType == typeof(byte) ? typeof(String) : pi.PropertyType);
                }
            }
            if (list != null || list.Count > 0)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (pi.IsDefined(typeof(ExportPropAttribute), true))
                        {
                            object obj = pi.GetValue(list[i], null);
                            obj = pi.Name.GetEnumForDesc<T>(obj);
                            tempList.Add(obj);
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }

        /// <summary>  
        /// 导出Excel
        /// </summary>  
        /// <returns></returns>  
        public static MemoryStream DataTableToExcel(DataTable dt)
        {
            IWorkbook workbook = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            MemoryStream ms = new MemoryStream();
            try
            {
                if (dt != null && dt.Columns.Count > 0)
                {
                    workbook = new HSSFWorkbook();
                    sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表

                    int columnCount = dt.Columns.Count;//列数
                                                       //设置列头
                    row = sheet.CreateRow(0);//excel第一行设为列头
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                    }

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        //设置每行每列的单元格,
                        int rowCount = dt.Rows.Count;//行数
                        for (int i = 0; i < rowCount; i++)
                        {
                            row = sheet.CreateRow(i + 1);
                            for (int j = 0; j < columnCount; j++)
                            {
                                cell = row.CreateCell(j);//excel第二行开始写入数据
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                    }
                    workbook.Write(ms);//向打开的这个xls文件中写入数据
                    ms.Seek(0, SeekOrigin.Begin);
                }
                return ms;
            }
            catch (Exception ex)
            {
                if (ms != null)
                {
                    ms.Close();
                }
                return null;
            }
        }
    }
}

[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
    public class ExportPropAttribute : System.Attribute
    {
        /// <summary>
        /// //导出名称
        /// </summary>
        public string ExtportName = "";

        /// <summary>
        /// //枚举类型
        /// </summary>
        public Type EnumType = null;
    }

 public static class AttributeExt
    {
        /// <summary>
        /// 获取导出字段的名称
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="propName"></param>
        /// <returns></returns>
        public static string GetExtportName<T>(this string propName)
        {
            Type t = typeof(T);
            var item = t.GetProperty(propName);
            if (item != null && item.IsDefined(typeof(ExportPropAttribute), true))
            {
                var attribute = item.GetCustomAttributes(typeof(ExportPropAttribute), true)[0];
                return ((ExportPropAttribute)attribute).ExtportName;
            }
            return propName;
        }

        /// <summary>
        /// 获取扩展的枚举值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="propName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object GetEnumForDesc<T>(this string propName, object value)
        {
            Type t = typeof(T);
            var item = t.GetProperty(propName);
            if (item != null && item.IsDefined(typeof(ExportPropAttribute), true))
            {
                var attribute = item.GetCustomAttributes(typeof(ExportPropAttribute), true)[0];
                var objEnmu = ((ExportPropAttribute)attribute).EnumType;
                if (objEnmu != null && value != null)
                {
                    return EnumHelper.GetEnumDesc(objEnmu, Convert.ToByte(value));
                }
            }
            return value;
        }

    }

枚举相关代码：

 /// <summary>
    /// 学生类型
    /// </summary>
    public enum StudentTypeEnum
    {
        /// <summary>
        /// 小学生
        /// </summary>
        [Description("小学生")]
        Small = 1,
        /// <summary>
        /// 中学生
        /// </summary>
        [Description("中学生")]
        Middle = 2,
        /// <summary>
        /// 大学生
        /// </summary>
        [Description("大学生")]
        Big = 3,
    }  

public static class EnumHelper
    {
        #region 获取枚举的描述信息

        /// <summary>
        ///     获取枚举的描述信息
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public static String GetEnumDesc(this Enum e)
        {
            try
            {
                FieldInfo enumInfo = e.GetType().GetField(e.ToString());
                var enumAttributes
                    = (DescriptionAttribute[])enumInfo.GetCustomAttributes(typeof(DescriptionAttribute), false);
                return enumAttributes.Length > 0 ? enumAttributes[0].Description : e.ToString();
            }
            catch
            {
                return string.Empty;
            }

        }

        /// <summary>
        /// 获取枚举的描述信息，根据传入的枚举值
        /// </summary>
        /// <param name="enumType"></param>
        /// <param name="enumValue"></param>
        /// <returns></returns>
        public static String GetEnumDesc(Type enumType, byte enumValue)
        {
            string result = "";
            foreach (var e in Enum.GetValues(enumType).Cast<Enum>().Where(e => Convert.ToInt32(e) == enumValue))
            {
                result = GetEnumDesc(e);
                break;
            }

            return result;
        }

        #endregion
    }

调用端代码：

  public class OfficeController : Controller
    {
        // GET: ExcelDemo
        public ActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <returns></returns>
        public FileResult ExportRecordExcel()
        {
            var list = new List<StudentEntity>
            {
               new StudentEntity{ Age=10, StuId=1, StuName="张三", StuType = (int)StudentTypeEnum.Middle },
               new StudentEntity{ Age=10, StuId=2, StuName="李四", StuType = (int)StudentTypeEnum.Middle },
               new StudentEntity{ Age=12, StuId=3, StuName="王五", StuType = (int)StudentTypeEnum.Big },
               new StudentEntity{ Age=8, StuId=4, StuName="赵六" , StuType = (int)StudentTypeEnum.Small}
            };
            return File(OfficeService.ExportRecords(list), "application/vnd.ms-excel", $"导出记录{DateTime.Now.ToLongTimeString()}.xls");
        }
    }

Index.cshtml：

@{
    ViewBag.Title = "导出Excel测试";
}

<script type="text/javascript">

    //导出
    function btn_export() {
        window.open("ExportRecordExcel");
    }

</script>

<h2>导出Excel测试</h2>
<br />
<input id="btnExport" type="button" value="导出Excel" onclick="btn_export()" />

效果：