C#中如何使用NPOI将List数据导出到Excel文档

今天小编给大家分享一下C#中如何使用NPOI将List数据导出到excel文档的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

NPOI是一个开源的C#读写Excel、Word等微软OLE2组件文档的项目。使用 NPOI 可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。

这里简单封装了一个使用NPOI导出Excel的DLL，方便项目使用。步骤如下：

1、NuGet包管理器添加对NPOI和log4net的安装引用

2、添加Excel属性信息类ExcelProperty.cs

/// <summary>/// 用于定义导出的excel属性/// </summary>public class ExcelProperty    {        public ExcelProperty() { }         /// <summary>        /// 文件基本属性        /// </summary>        /// <param name="company">公司名称</param>        /// <param name="author">作者信息</param>        /// <param name="ApplicationName">创建程序信息</param>        /// <param name="Comments">填加xls文件备注</param>        /// <param name="title">填加xls文件标题信息</param>        /// <param name="Subject">填加文件主题信息</param>        public ExcelProperty(string company, string author, string applicationName, string comments, string title, string subject)        {            this.Company = company;            this.Author = author;            this.ApplicationName = applicationName;            this.Comments = comments;            this.Title = title;            this.Subject = subject;        }        /// <summary>        /// 公司名称        /// </summary>        private string company = "";        /// <summary>        /// 公司名称        /// </summary>        public string Company        {            get { return company; }            set { company = value; }        }        /// <summary>        /// 作者信息        /// </summary>        private string author = "";        /// <summary>        /// 作者信息        /// </summary>        public string Author        {            get { return author; }            set { author = value; }        }        /// <summary>        /// 创建程序信息        /// </summary>        private string applicationName = "";        /// <summary>        /// 创建程序信息        /// </summary>        public string ApplicationName        {            get { return applicationName; }            set { applicationName = value; }        }        /// <summary>        ///填加xls文件备注        /// </summary>        private string comments = "";        /// <summary>        ///填加xls文件备注        /// </summary>        public string Comments        {            get { return comments; }            set { comments = value; }        }        /// <summary>        /// 填加xls文件标题信息        /// </summary>        private string title = "";        /// <summary>        /// 填加xls文件标题信息        /// </summary>        public string Title        {            get { return title; }            set { title = value; }        }        /// <summary>        /// 填加文件主题信息        /// </summary>        private string subject = "";        /// <summary>        /// 填加文件主题信息        /// </summary>        public string Subject        {            get { return subject; }            set { subject = value; }        }    }

3、添加Excel导出类ExcelExportHelper.cs

using log4net;using NPOI.HPSF;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Reflection;using System.Text; namespace NPOIExcelExportHelper{    public class ExcelExportHelper    {        //委托        public delegate void ExportResult(bool res);        public event ExportResult ExportResultEvent;         //构造函数        public ExcelExportHelper() { }        public ExcelExportHelper(ILog loghelper)        {            this.LogHelper = loghelper;        }         /// <summary>        /// 日志        /// </summary>        private ILog LogHelper;        /// <summary>        /// 要导出的Excel对象        /// </summary>        private HSSFWorkbook workbook = null;        /// <summary>        /// 要导出的Excel对象属性        /// </summary>        private HSSFWorkbook Workbook        {            get            {                if (workbook == null)                {                    workbook = new HSSFWorkbook();                }                return workbook;            }            set { workbook = value; }        }         /// <summary>        /// 设置Excel文件基本属性        /// </summary>        /// <param name="ep">属性</param>        public void SetExcelProperty(ExcelProperty ep)        {            DocumentSummaryInfORMation dsi = PropertySetFactory.CreateDocumentSummaryInformation();            dsi.Company = ep.Company;//填加xls文件公司信息            Workbook.DocumentSummaryInformation = dsi;             SummaryInformation si = PropertySetFactory.CreateSummaryInformation();            si.Author = ep.Author; //填加xls文件作者信息            si.ApplicationName = ep.ApplicationName; //填加xls文件创建程序信息            si.Comments = ep.Comments; //填加xls文件备注            si.Title = ep.Title; //填加xls文件标题信息            si.Subject = ep.Subject;//填加文件主题信息            si.CreateDateTime = DateTime.Now;            Workbook.SummaryInformation = si;        }         /// <summary>        /// 泛型列表List导出到Excel文件        /// </summary>        /// <param name="list">源List表</param>        /// <param name="strHeaderText">标题信息</param>        /// <param name="strFileName">保存路径</param>        /// <param name="titles">列名</param>        public void ExportToFile<T>(List<T> list, string strHeaderText, string strFileName, string[] titles = null)        {            try            {                //转换数据源                DataTable dtSource = ListToDataTable(list, titles);                //开始导出                Export(dtSource, strHeaderText, strFileName);                System.GC.Collect();                ExportResultEvent?.Invoke(true);            }            catch (Exception ex)            {                if (LogHelper != null)                    LogHelper.Error(string.Format("ExportToFile error:{0}", ex));                ExportResultEvent?.Invoke(false);            }        }         /// <summary>        /// DataTable导出到Excel文件        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">标题信息</param>        /// <param name="strFileName">保存路径</param>        public void Export(DataTable dtSource, string strHeaderText, string strFileName)        {            using (MemoryStream ms = Export(dtSource, strHeaderText))            {                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))                {                    byte[] data = ms.ToArray();                    fs.Write(data, 0, data.Length);                    fs.Flush();                }            }        }         /// <summary>        /// DataTable导出到Excel的MemoryStream        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">标题信息</param>        private MemoryStream Export(DataTable dtSource, string strHeaderText)        {            ISheet sheet = Workbook.CreateSheet();            ICellStyle dateStyle = Workbook.CreateCellStyle();            IDataFormat format = Workbook.CreateDataFormat();            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");             //取得列宽            int[] arrColWidth = new int[dtSource.Columns.Count];            foreach (DataColumn item in dtSource.Columns)            {                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;            }            for (int i = 0; i < dtSource.Rows.Count; i++)            {                for (int j = 0; j < dtSource.Columns.Count; j++)                {                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;                    if (intTemp > arrColWidth[j])                    {                        arrColWidth[j] = intTemp;                    }                }            }            int rowIndex = 0;            foreach (DataRow row in dtSource.Rows)            {                #region 新建表，填充表头，填充列头，样式                if (rowIndex == 65535 || rowIndex == 0)                {                    if (rowIndex != 0)                    {                        sheet = Workbook.CreateSheet();                    }                     #region 表头及样式                    {                        IRow headerRow = sheet.CreateRow(0);                        headerRow.HeightInPoints = 25;                        headerRow.CreateCell(0).SetCellValue(strHeaderText);                         ICellStyle headStyle = Workbook.CreateCellStyle();                        headStyle.Alignment = HorizontalAlignment.Center;                        IFont font = Workbook.CreateFont();                        font.FontHeightInPoints = 20;                        font.Boldweight = 700;                        headStyle.SetFont(font);                        headerRow.GetCell(0).CellStyle = headStyle;                        //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));                    }                    #endregion                     #region 列头及样式                    {                        IRow headerRow = sheet.CreateRow(1);                        ICellStyle headStyle = Workbook.CreateCellStyle();                        headStyle.Alignment = HorizontalAlignment.Center;                        IFont font = Workbook.CreateFont();                        font.FontHeightInPoints = 10;                        font.Boldweight = 700;                        headStyle.SetFont(font);                        foreach (DataColumn column in dtSource.Columns)                        {                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                            //设置列宽                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);                        }                    }                    #endregion                     rowIndex = 2;                }                #endregion                 #region 填充内容                IRow dataRow = sheet.CreateRow(rowIndex);                foreach (DataColumn column in dtSource.Columns)                {                    ICell newCell = dataRow.CreateCell(column.Ordinal);                    string drValue = row[column].ToString();                    switch (column.DataType.ToString())                    {                        case "System.String"://字符串类型                            newCell.SetCellValue(drValue);                            break;                        case "System.DateTime"://日期类型                            DateTime dateV;                            DateTime.TryParse(drValue, out dateV);                            newCell.SetCellValue(dateV);                            newCell.CellStyle = dateStyle;//格式化显示                            break;                        case "System.Boolean"://布尔型                            bool boolV = false;                            bool.TryParse(drValue, out boolV);                            newCell.SetCellValue(boolV);                            break;                        case "System.Int16"://整型                        case "System.Int32":                        case "System.Int64":                        case "System.Byte":                            int intV = 0;                            int.TryParse(drValue, out intV);                            newCell.SetCellValue(intV);                            break;                        case "System.Decimal"://浮点型                        case "System.Double":                            double doubV = 0;                            double.TryParse(drValue, out doubV);                            newCell.SetCellValue(doubV);                            break;                        case "System.DBNull"://空值处理                            newCell.SetCellValue("");                            break;                        default:                            newCell.SetCellValue("");                            break;                    }                }                #endregion                 rowIndex++;            }            using (MemoryStream ms = new MemoryStream())            {                Workbook.Write(ms);                ms.Flush();                ms.Position = 0;                return ms;            }        }         /// <summary>        /// 泛型列表List转换为DataTable        /// </summary>        /// <typeparam name="T">泛型实体</typeparam>        /// <param name="list">要转换的列表</param>        /// <param name="titles">标题</param>        /// <returns></returns>        public DataTable ListToDataTable<T>(List<T> list, string[] titles)        {            DataTable dt = new DataTable();            Type listType = typeof(T);            PropertyInfo[] properties = listType.GetProperties();            //标题行            if (titles != null && properties.Length == titles.Length)            {                for (int i = 0; i < properties.Length; i++)                {                    PropertyInfo property = properties[i];                    dt.Columns.Add(new DataColumn(titles[i], property.PropertyType));                }            }            else            {                for (int i = 0; i < properties.Length; i++)                {                    PropertyInfo property = properties[i];                    dt.Columns.Add(new DataColumn(property.Name, property.PropertyType));                }            }            //内容行            foreach (T item in list)            {                DataRow dr = dt.NewRow();                for (int i = 0; i < dt.Columns.Count; i++)                {                    dr[i] = properties[i].GetValue(item, null);                }                dt.Rows.Add(dr);            }            return dt;        }    }}

调用方法：

新建一项目，添加对上述DLL的引用

创建TestItem测试类

public class TestItem    {        public string Name { get; set; }        public int Id { get; set; }        public string Date { get; set; }        public TestItem(string name, int id, string date)        {            Name = name;            Id = id;            Date = date;        }    }

调用

private void GetList()        {            List<TestItem> list = new List<TestItem>();            for (int i = 0; i < 100000; i++)            {                list.Add(new TestItem("姓名" + i, i, "2020-04-21"));            }            ExcelExportHelper exportHelper = new ExcelExportHelper();            exportHelper.ExportResultEvent += ExportHelper_ExportResultEvent;            exportHelper.SetExcelProperty(new ExcelProperty("TEST", "DNA", "ExcelExport", "", "统计查询", "统计信息"));            exportHelper.ExportToFile(list, "查询结果统计", @"C:\Test.xls", new string[]{ "姓名", "编号", "日期"});        }         private void ExportHelper_ExportResultEvent(bool res)        {            Console.Write(res);        }

4、结果

以上就是“C#中如何使用NPOI将List数据导出到Excel文档”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注编程网精选频道。