NPOI 导出Excel 数据方式

使用NPOI的库进行Excel导出操作

公共帮助类：

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

namespace Common.Helper
{
    /// <summary>
    /// 创建人员：杨汨
    /// 创建时间：2017-05-10
    /// 创建说明：Excel 帮助类
    /// </summary>
    public static class ExcelHelper
    {
        //列宽的预留宽度
        private const int WID = 2;

        #region DataTable 操作
        /// <summary>
        /// 将DataTable 转换为 HSSFWorkbook Excel
        /// </summary>
        /// <param name="dt">数据源 DataTable</param>
        /// <returns>HSSFWorkbook</returns>
        public static HSSFWorkbook WriteTableToBook(DataTable dt, string sheetName = "Sheet1")
        {
            HSSFWorkbook book = new HSSFWorkbook();
            if (dt == null || dt.Rows.Count <= 0)
            {
                return book;
            }
            try
            {
                ISheet sheet = book.CreateSheet(sheetName);

                DataColumnCollection cols = dt.Columns;
                int rIndex = 0;
                //设置表头
                ICellStyle style = GetHeaderStyle(book);
                IRow rowHeader = sheet.CreateRow(rIndex);
                for (int j = 0; j < cols.Count; j++)
                {
                    ICell cell = rowHeader.CreateCell(j);
                    cell.CellStyle = style;
                    cell.SetCellValue(cols[j].ColumnName);
                }
                //设置内容
                rIndex++;
                string val = null;
                for (int i = rIndex; i < (dt.Rows.Count + rIndex); i++)
                {
                    IRow row = sheet.CreateRow(i);
                    for (int j = 0; j < cols.Count; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellType(GetCellType(cols[j].DataType));
                        val = dt.Rows[i - rIndex][cols[j].ColumnName].ToString();
                        cell.SetCellValue(val);
                        sheet.SetColumnWidth(j, (Encoding.UTF8.GetBytes(val).Length + WID) * 256);
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return book;
        }

        /// <summary>
        /// 将DataTable 转换为 MemoryStream 的 Excel
        /// </summary>
        /// <param name="dt">数据源 DataTable</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream WriteTableToStream(DataTable dt, string sheetName = "Sheet1")
        {
            MemoryStream ms = new MemoryStream();
            HSSFWorkbook book = WriteTableToBook(dt,sheetName);
            book.Write(ms);
            return ms;
        } 

        /// <summary>
        /// 自定义设置表格表头名称
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dicNames"></param>
        public static void SetColName(DataTable dt , Dictionary<string,string> dicNames)
        {
            foreach (var key in dicNames.Keys)
            {
                dt.Columns[key].ColumnName = dicNames[key];
            }
        }



        #endregion

        #region List 集合操作
        /// <summary>
        /// 将集合写入 HSSFWorkbook 的Excel
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="list">数据集合</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns>HSSFWorkbook</returns>
        public static HSSFWorkbook WriteListToBook<T>(List<T> list, string sheetName = "Sheet1")
        {
            HSSFWorkbook book = new HSSFWorkbook();
            if (list == null || list.Count <= 0)
            {
                return book;
            }
            Type t = typeof(T);
            PropertyInfo[] props = t.GetProperties();

            ISheet sheet = book.CreateSheet(sheetName);

            int rIndex = 0;

            //创建头
            ICellStyle style = GetHeaderStyle(book);
            IRow rowHeader = sheet.CreateRow(rIndex);
            for (int i = 0; i < props.Length; i++)
            {
                ICell cell = rowHeader.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(props[i].Name);
            }

            rIndex++;
            string val = null;
            foreach (T item in list)
            {
                IRow row = sheet.CreateRow(rIndex++);
                for (int i = 0; i < props.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellType(GetCellType(props[i].PropertyType));
                    val  =props[i].GetValue(item,null).ToString();
                    cell.SetCellValue(val);
                    sheet.SetColumnWidth(i, (Encoding.UTF8.GetBytes(val).Length + WID) * 256);
                }
            }
            return book;
        }

        /// <summary>
        /// 将集合写入MemoryStream 的Excel
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="list">数据集合</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream WriteListToStream<T>(List<T> list, string sheetName = "Sheet1")
        {
            HSSFWorkbook book = WriteListToBook(list, sheetName);
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            return ms;
        } 
        #endregion

        #region 内部方法

        /// <summary>
        /// 获取单元格存储类型
        /// </summary>
        /// <param name="t">C# 类型</param>
        /// <returns>CellType</returns>
        private static CellType GetCellType(Type t)
        {
            switch (t.ToString().ToLower())
            {
                case "string":
                    return CellType.String;
                case "int16":
                case "int32":
                case "int64":
                    return CellType.Numeric;
                case "boolean":
                    return CellType.Boolean;
                default:
                    return CellType.String;
            }
        }

        /// <summary>
        /// 获取表头样式
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        private static ICellStyle GetHeaderStyle(HSSFWorkbook book)
        {
            //设置表头
            IFont font = book.CreateFont();
            //font.Boldweight= (short)FontBoldWeight.Bold;
            font.IsBold = true;
            font.FontHeightInPoints = 12;
            ICellStyle style = book.CreateCellStyle();
            style.SetFont(font);
            return style;
        } 
        #endregion

    }
}

在MVC的控制器中进行导出

public ActionResult ExcelOut()
        {
            //从业务层获取需要导出的DataTable类型的数据
            DataTable dt = BIZ_EDU_FLOW_BLL.Instance.GetChargedList();
            if (dt != null)
            {
                //dt 的自定义二次操作
                ReconstructionTable(dt);
            }
            else
            {
                dt = new DataTable();
            }
            MemoryStream ms = Common.Helper.ExcelHelper.WriteTableToStream(dt);
            string fileName = "已收费学员信息-" + DateTime.Now.ToString("yyyy-MM-dd_HHmmss") + ".xls";
            //ms.Seek(0, SeekOrigin.Begin);   
            //File(ms, "application/ms-excel", fileName); //这种方式下载的时候必须使用 ms.Seek();
            return File(ms.ToArray(), "application/ms-excel", Url.Encode(fileName));
        }

        /// <summary>
        /// 自定义重构DataTable
        /// </summary>
        /// <param name="dt"></param>
        private static void ReconstructionTable(DataTable dt)
        {
            dt.Columns.Add("JYJBStr", typeof(string));
            dt.Columns.Add("JYLXStr", typeof(string));
            dt.Columns.Add("JYZTStr", typeof(string));
            dt.Columns.Add("ZFFSStr", typeof(string));
            foreach (DataRow dr in dt.Rows)
            {
                dr["JYJBStr"] = dr["JYJB"].ToString() == "1" ? "普通" : "重点";
                dr["JYLXStr"] = dr["JYLX"].ToString() == "1" ? "满分" : "审验";
                dr["JYZTStr"] = BLL.ParaDict.JYZT[dr["JYZT"].ToString()];
                dr["ZFFSStr"] = dr["ZFFS"].ToString() == "1" ? "现金" : "Pos刷卡";
            }
            dt.Columns.Remove("JYJB");
            dt.Columns.Remove("JYLX");
            dt.Columns.Remove("JYZT");
            dt.Columns.Remove("ZFFS");

            //dt 的自定义二次操作
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("ID", "序号");
            dic.Add("XM", "姓名");
            dic.Add("SFZH", "身份证号");
            dic.Add("JYJBStr", "教育级别");
            dic.Add("JYLXStr", "教育类型");
            dic.Add("LXDH", "联系电话");
            dic.Add("JYZTStr", "教育状态");
            dic.Add("JE", "金额");
            dic.Add("SFR", "收费人");
            dic.Add("SFSJ", "收费时间");
            dic.Add("ZFFSStr", "支付方式");
            Common.Helper.ExcelHelper.SetColName(dt, dic);
        }