C#使用NPOI实现Excel的导入导出实例

之前写过一篇<初试C#中使用NPOI实现Excel的导入导出>，前两天在写<C#使用NPOI导出Dictionary到Excel表>的时候看了一下，之前那个已经是很老的版本了，今天更新一下最新的代码。

老规矩，首先需要在项目中安装NPOI支持库，在NuGet中搜索NPOI直接安装即可。

新建NpoiHelper调用类，复制以下代码，把命名空间改为你自己项目对应的即可。

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace PublicAddress.Core.Tools
{
    public class NpoiHelper
    {
        /// <summary>
        /// Excel转DataTabel(调用层)
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static System.Data.DataTable Import(string filePath, string sheetName = "")
        {
            var excelType = Path.GetExtension(filePath).ToLower();
            System.Data.DataTable dt;
            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                dt = NpoiHelper.ImportExcel(stream, excelType, sheetName);
            }
            return dt;
        }

        /// <summary>
        /// Excel转DataTabel(底层)
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="type"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable ImportExcel(Stream stream, string type, string sheetName)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            try
            {
                if (type != ".xls")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else
                {
                    workbook = new HSSFWorkbook(stream);
                }
                ISheet sheet = null;
                //获取工作表 默认取第一张
                if (string.IsNullOrWhiteSpace(sheetName))
                    sheet = workbook.GetSheetAt(0);
                else
                    sheet = workbook.GetSheet(sheetName);
                if (sheet == null)
                    return null;
                IEnumerator rows = sheet.GetRowEnumerator();
                #region 获取表头
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    if (cell != null)
                    {
                        dt.Columns.Add(cell.ToString());
                    }
                    else
                    {
                        dt.Columns.Add("");
                    }
                }
                #endregion
                #region 获取内容
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            //判断单元格是否为日期格式
                            if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
                            {
                                if (row.GetCell(j).DateCellValue.Year >= 1970)
                                {
                                    dataRow[j] = row.GetCell(j).DateCellValue.ToString();
                                }
                                else
                                {
                                    dataRow[j] = row.GetCell(j).ToString();
                                }
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                    }
                    dt.Rows.Add(dataRow);
                }
                #endregion
            }
            catch (Exception ex)
            {
                dt = null;
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                    stream.Dispose();
                }
            }
            return dt;
        }

        /// <summary>
        /// List<T>转Excel文件流
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SheetName"></param>
        /// <param name="list"></param>
        /// <param name="FiedNames"></param>
        /// <returns></returns>
        public static HSSFWorkbook BuildSwitchData<T>(string SheetName, List<T> list, Dictionary<string, string> FiedNames)
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(SheetName); //创建工作表
            sheet.CreateFreezePane(0, 1); //冻结列头行
            HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
            row_Title.HeightInPoints = 25F; //设置列头行高
            HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
            cs_Title_Font.IsBold = true; //字体加粗
            cs_Title_Font.FontHeightInPoints = 12; //字体大小
            cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
            #region /*生成列头*/
            int ii = 0;
            foreach (string key in FiedNames.Keys)
            {
                HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(ii); //创建单元格
                cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
                cell_Title.SetCellValue(key);
                sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
                ii++;
            }
            #endregion
            //获取 实体类 类型对象
            Type t = typeof(T); // model.GetType();
            //获取 实体类 所有的 公有属性
            List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
            //创建 实体属性 字典集合
            Dictionary<string, PropertyInfo> dictPros = new Dictionary<string, PropertyInfo>();
            //将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名  值：属性对象
            proInfos.ForEach(p =>
            {
                if (FiedNames.Values.Contains(p.Name))
                {
                    dictPros.Add(p.Name, p);
                }
            });

            HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            for (int i = 0; i < list.Count; i++)
            {
                HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行
                row_Content.HeightInPoints = 20;
                int jj = 0;
                foreach (string proName in FiedNames.Values)
                {
                    if (dictPros.ContainsKey(proName))
                    {
                        HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(jj); //创建单元格
                        cell_Conent.CellStyle = cs_Content;

                        //如果存在，则取出要属性对象
                        PropertyInfo proInfo = dictPros[proName];
                        //获取对应属性的值
                        object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
                        string cell_value = value == null ? "" : value.ToString();
                        cell_Conent.SetCellValue(cell_value);
                        jj++;
                    }
                }
            }
            return wb;
        }


        /// <summary>
        /// Dictionary<string, string>转Excel文件流
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="SheetName"></param>
        /// <param name="list"></param>
        /// <param name="FiedNames"></param>
        /// <returns></returns>
        public static HSSFWorkbook BuildSwitchDataDic<T>(string SheetName, List<Dictionary<string, string>> list, Dictionary<string, string> FiedNames)
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(SheetName); //创建工作表
            sheet.CreateFreezePane(0, 1); //冻结列头行
            HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
            row_Title.HeightInPoints = 25F; //设置列头行高
            HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
            cs_Title_Font.IsBold = true; //字体加粗
            cs_Title_Font.FontHeightInPoints = 12; //字体大小
            cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式

            int ii = 0;
            foreach (string key in FiedNames.Keys)
            {
                HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(ii); //创建单元格
                cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
                cell_Title.SetCellValue(key);
                sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
                ii++;
            }
            HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中

            for (int i = 0; i < list.Count; i++)
            {
                HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行
                row_Content.HeightInPoints = 20;
                int jj = 0;
                foreach (var proName in list[i])
                {

                    HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(jj); //创建单元格
                    cell_Conent.CellStyle = cs_Content;

                    //获取对应属性的值
                    object value = proName.Value; //object newValue = model.uName;
                    string cell_value = value == null ? "" : value.ToString();
                    cell_Conent.SetCellValue(cell_value);
                    jj++;

                }

            }

            return wb;
        }
    } 
}

调用代码，注意看加粗部分，其他的根据自己的需求进行简单的更改即可。

            //设置导出文件列标题
            Dictionary<string, string> FiedNames = new Dictionary<string, string>();

            FiedNames.Add("车牌号", "CarNum");
            FiedNames.Add("车架号", "Vin");
            FiedNames.Add("商品名称", "ShopName");
            FiedNames.Add("车库代码", "Garage");
            FiedNames.Add("车库名称", "GarageName");
            FiedNames.Add("保险状态代码", "InsurancePolicyStatus");
            FiedNames.Add("保险状态名称", "InsurancePolicyStatusName");

            //调用通用方法把list转excel
            HSSFWorkbook wb = NpoiHelper.BuildSwitchData("数据导出", insurancePolicyCarOutModels, FiedNames);
            //设置文件导出目录
            string sExportFileName = @"D:/ExcelFile/";
            string fileName = "数据导出_" + DateTime.Now.ToString("yyyyMMddHHmmss", DateTimeFormatInfo.CurrentInfo) + ".xls";
            //将数据转化为二进制流
            MemoryStream stream = new MemoryStream();
            wb.Write(stream);
            var buf = stream.ToArray();
            //将二进制流转化为文件并保存在本地计算机
            using (FileStream fileStream = new FileStream(sExportFileName + fileName, FileMode.Create, FileAccess.Write))
            {
                fileStream.Write(buf, 0, buf.Length);
                fileStream.Flush();
            }
            stream.Close();