How to use NPOI in C # to export List data to Excel documents

Today Xiaobian to share with you how to use NPOI to export List data to Excel documents in C# related knowledge points, detailed content, clear logic, I believe that most people still know too much about this knowledge, so share this article for your reference, I hope you can learn something after reading this article, let's take a look at it.

NPOI is an open source project for C # to read and write Microsoft OLE2 component documents such as Excel, WORD and so on. Using NPOI, you can read and write WORD/EXCEL documents on machines that do not have Office installed or the corresponding environment.

Here simply encapsulates a DLL that uses NPOI to export Excel, which is convenient for the project to use. The steps are as follows:

1. The NuGet package manager adds installation references to NPOI and log4net

2. Add Excel attribute information class ExcelProperty.cs

/ used to define exported excel attributes / public class ExcelProperty {public ExcelProperty () {} / basic file attributes / Company name / author information / create program information / add xls text Note / / add xls file title information / add file subject information 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;} / Company name / private string company = "" / Company name / public string Company {get {return company;} set {company = value;}} / author Information / private string author = "" / author information / public string Author {get {return author;} set {author = value;}} / create program information / private string applicationName = "" / create program information / public string ApplicationName {get {return applicationName;} set {applicationName = value;}} / add xls file remarks / private string comments = "" / / add xls file remarks / public string Comments {get {return comments;} set {comments = value;}} / add xls file title information / private string title = "" / add xls file title information / public string Title {get {return title;} set {title = value;}} / add file topic information / private string subject = "" / add file topic information / public string Subject {get {return subject;} set {subject = value;}

3. Add Excel export class 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 {/ / delegate public delegate void ExportResult (bool res); public event ExportResult ExportResultEvent / / Constructor public ExcelExportHelper () {} public ExcelExportHelper (ILog loghelper) {this.LogHelper = loghelper;} / log / private ILog LogHelper; / the Excel object / private HSSFWorkbook workbook = null to be exported / Excel object properties to be exported / private HSSFWorkbook Workbook {get {if (workbook = = null) {workbook = new HSSFWorkbook ();} return workbook } set {workbook = value;}} / set the basic properties of the Excel file / attribute public void SetExcelProperty (ExcelProperty ep) {DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation (); dsi.Company = ep.Company / / add xls file company information Workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation (); si.Author = ep.Author; / / add xls file author information si.ApplicationName = ep.ApplicationName; / / add xls file creation program information si.Comments = ep.Comments; / / add xls file remarks si.Title = ep.Title / / add xls file title information si.Subject = ep.Subject;// add file topic information si.CreateDateTime = DateTime.Now; Workbook.SummaryInformation = si } / generic list List is exported to Excel file / Source List table / header information / Save path / column name public void ExportToFile (List list, string strHeaderText, string strFileName) String [] titles = null) {try {/ / convert data source DataTable dtSource = ListToDataTable (list, titles) / / start exporting 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) }} / DataTable exports to Excel file / Source DataTable / header information / Save path 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 () } / DataTable exported to Excel MemoryStream / Source DataTable / header information 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"); / / get the column width 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 >