今天小编给大家分享一下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>publicclassExcelProperty{publicExcelProperty(){}///<summary>///文件基本属性///</summary>///<paramname="company">公司名称</param>///<paramname="author">作者信息</param>///<paramname="ApplicationName">创建程序信息</param>///<paramname="Comments">填加xls文件备注</param>///<paramname="title">填加xls文件标题信息</param>///<paramname="Subject">填加文件主题信息</param>publicExcelProperty(stringcompany,stringauthor,stringapplicationName,stringcomments,stringtitle,stringsubject){this.Company=company;this.Author=author;this.ApplicationName=applicationName;this.Comments=comments;this.Title=title;this.Subject=subject;}///<summary>///公司名称///</summary>privatestringcompany="";///<summary>///公司名称///</summary>publicstringCompany{get{returncompany;}set{company=value;}}///<summary>///作者信息///</summary>privatestringauthor="";///<summary>///作者信息///</summary>publicstringAuthor{get{returnauthor;}set{author=value;}}///<summary>///创建程序信息///</summary>privatestringapplicationName="";///<summary>///创建程序信息///</summary>publicstringApplicationName{get{returnapplicationName;}set{applicationName=value;}}///<summary>///填加xls文件备注///</summary>privatestringcomments="";///<summary>///填加xls文件备注///</summary>publicstringComments{get{returncomments;}set{comments=value;}}///<summary>///填加xls文件标题信息///</summary>privatestringtitle="";///<summary>///填加xls文件标题信息///</summary>publicstringTitle{get{returntitle;}set{title=value;}}///<summary>///填加文件主题信息///</summary>privatestringsubject="";///<summary>///填加文件主题信息///</summary>publicstringSubject{get{returnsubject;}set{subject=value;}}}

3、添加Excel导出类ExcelExportHelper.cs

usinglog4net;usingNPOI.HPSF;usingNPOI.HSSF.UserModel;usingNPOI.SS.UserModel;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.IO;usingSystem.Reflection;usingSystem.Text;namespaceNPOIExcelExportHelper{publicclassExcelExportHelper{//委托publicdelegatevoidExportResult(boolres);publiceventExportResultExportResultEvent;//构造函数publicExcelExportHelper(){}publicExcelExportHelper(ILogloghelper){this.LogHelper=loghelper;}///<summary>///日志///</summary>privateILogLogHelper;///<summary>///要导出的Excel对象///</summary>privateHSSFWorkbookworkbook=null;///<summary>///要导出的Excel对象属性///</summary>privateHSSFWorkbookWorkbook{get{if(workbook==null){workbook=newHSSFWorkbook();}returnworkbook;}set{workbook=value;}}///<summary>///设置Excel文件基本属性///</summary>///<paramname="ep">属性</param>publicvoidSetExcelProperty(ExcelPropertyep){DocumentSummaryInformationdsi=PropertySetFactory.CreateDocumentSummaryInformation();dsi.Company=ep.Company;//填加xls文件公司信息Workbook.DocumentSummaryInformation=dsi;SummaryInformationsi=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>///<paramname="list">源List表</param>///<paramname="strHeaderText">标题信息</param>///<paramname="strFileName">保存路径</param>///<paramname="titles">列名</param>publicvoidExportToFile<T>(List<T>list,stringstrHeaderText,stringstrFileName,string[]titles=null){try{//转换数据源DataTabledtSource=ListToDataTable(list,titles);//开始导出Export(dtSource,strHeaderText,strFileName);System.GC.Collect();ExportResultEvent?.Invoke(true);}catch(Exceptionex){if(LogHelper!=null)LogHelper.Error(string.Format("ExportToFileerror:{0}",ex));ExportResultEvent?.Invoke(false);}}///<summary>///DataTable导出到Excel文件///</summary>///<paramname="dtSource">源DataTable</param>///<paramname="strHeaderText">标题信息</param>///<paramname="strFileName">保存路径</param>publicvoidExport(DataTabledtSource,stringstrHeaderText,stringstrFileName){using(MemoryStreamms=Export(dtSource,strHeaderText)){using(FileStreamfs=newFileStream(strFileName,FileMode.Create,FileAccess.Write)){byte[]data=ms.ToArray();fs.Write(data,0,data.Length);fs.Flush();}}}///<summary>///DataTable导出到Excel的MemoryStream///</summary>///<paramname="dtSource">源DataTable</param>///<paramname="strHeaderText">标题信息</param>privateMemoryStreamExport(DataTabledtSource,stringstrHeaderText){ISheetsheet=Workbook.CreateSheet();ICellStyledateStyle=Workbook.CreateCellStyle();IDataFormatformat=Workbook.CreateDataFormat();dateStyle.DataFormat=format.GetFormat("yyyy-mm-dd");//取得列宽int[]arrColWidth=newint[dtSource.Columns.Count];foreach(DataColumnitemindtSource.Columns){arrColWidth[item.Ordinal]=Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;}for(inti=0;i<dtSource.Rows.Count;i++){for(intj=0;j<dtSource.Columns.Count;j++){intintTemp=Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;if(intTemp>arrColWidth[j]){arrColWidth[j]=intTemp;}}}introwIndex=0;foreach(DataRowrowindtSource.Rows){#region新建表，填充表头，填充列头，样式if(rowIndex==65535||rowIndex==0){if(rowIndex!=0){sheet=Workbook.CreateSheet();}#region表头及样式{IRowheaderRow=sheet.CreateRow(0);headerRow.HeightInPoints=25;headerRow.CreateCell(0).SetCellValue(strHeaderText);ICellStyleheadStyle=Workbook.CreateCellStyle();headStyle.Alignment=HorizontalAlignment.Center;IFontfont=Workbook.CreateFont();font.FontHeightInPoints=20;font.Boldweight=700;headStyle.SetFont(font);headerRow.GetCell(0).CellStyle=headStyle;//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列sheet.AddMergedRegion(newNPOI.SS.Util.CellRangeAddress(0,0,0,dtSource.Columns.Count-1));}#endregion#region列头及样式{IRowheaderRow=sheet.CreateRow(1);ICellStyleheadStyle=Workbook.CreateCellStyle();headStyle.Alignment=HorizontalAlignment.Center;IFontfont=Workbook.CreateFont();font.FontHeightInPoints=10;font.Boldweight=700;headStyle.SetFont(font);foreach(DataColumncolumnindtSource.Columns){headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);headerRow.GetCell(column.Ordinal).CellStyle=headStyle;//设置列宽sheet.SetColumnWidth(column.Ordinal,(arrColWidth[column.Ordinal]+1)*256);}}#endregionrowIndex=2;}#endregion#region填充内容IRowdataRow=sheet.CreateRow(rowIndex);foreach(DataColumncolumnindtSource.Columns){ICellnewCell=dataRow.CreateCell(column.Ordinal);stringdrValue=row[column].ToString();switch(column.DataType.ToString()){case"System.String"://字符串类型newCell.SetCellValue(drValue);break;case"System.DateTime"://日期类型DateTimedateV;DateTime.TryParse(drValue,outdateV);newCell.SetCellValue(dateV);newCell.CellStyle=dateStyle;//格式化显示break;case"System.Boolean"://布尔型boolboolV=false;bool.TryParse(drValue,outboolV);newCell.SetCellValue(boolV);break;case"System.Int16"://整型case"System.Int32":case"System.Int64":case"System.Byte":intintV=0;int.TryParse(drValue,outintV);newCell.SetCellValue(intV);break;case"System.Decimal"://浮点型case"System.Double":doubledoubV=0;double.TryParse(drValue,outdoubV);newCell.SetCellValue(doubV);break;case"System.DBNull"://空值处理newCell.SetCellValue("");break;default:newCell.SetCellValue("");break;}}#endregionrowIndex++;}using(MemoryStreamms=newMemoryStream()){Workbook.Write(ms);ms.Flush();ms.Position=0;returnms;}}///<summary>///泛型列表List转换为DataTable///</summary>///<typeparamname="T">泛型实体</typeparam>///<paramname="list">要转换的列表</param>///<paramname="titles">标题</param>///<returns></returns>publicDataTableListToDataTable<T>(List<T>list,string[]titles){DataTabledt=newDataTable();TypelistType=typeof(T);PropertyInfo[]properties=listType.GetProperties();//标题行if(titles!=null&&properties.Length==titles.Length){for(inti=0;i<properties.Length;i++){PropertyInfoproperty=properties[i];dt.Columns.Add(newDataColumn(titles[i],property.PropertyType));}}else{for(inti=0;i<properties.Length;i++){PropertyInfoproperty=properties[i];dt.Columns.Add(newDataColumn(property.Name,property.PropertyType));}}//内容行foreach(Titeminlist){DataRowdr=dt.NewRow();for(inti=0;i<dt.Columns.Count;i++){dr[i]=properties[i].GetValue(item,null);}dt.Rows.Add(dr);}returndt;}}}

调用方法：

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

2、创建TestItem测试类

publicclassTestItem{publicstringName{get;set;}publicintId{get;set;}publicstringDate{get;set;}publicTestItem(stringname,intid,stringdate){Name=name;Id=id;Date=date;}}

3、调用

privatevoidGetList(){List<TestItem>list=newList<TestItem>();for(inti=0;i<100000;i++){list.Add(newTestItem("姓名"+i,i,"2020-04-21"));}ExcelExportHelperexportHelper=newExcelExportHelper();exportHelper.ExportResultEvent+=ExportHelper_ExportResultEvent;exportHelper.SetExcelProperty(newExcelProperty("TEST","DNA","ExcelExport","","统计查询","统计信息"));exportHelper.ExportToFile(list,"查询结果统计",@"C:\Test.xls",newstring[]{"姓名","编号","日期"});}privatevoidExportHelper_ExportResultEvent(boolres){Console.Write(res);}

4、结果

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