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;
        }
    }
}

調用方法：

1、新建一項目，添加對上述DLL的引用

2、創建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;
        }
    }

3、調用

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文檔”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。