C#使用NPOI將List數(shù)據(jù)導(dǎo)出到Excel文檔

第C#使用NPOI將List數(shù)據(jù)導(dǎo)出到Excel文檔NPOI是一個開源的C#讀寫Excel、WORD等微軟OLE2組件文檔的項(xiàng)目。使用NPOI可以在沒有安裝Office或者相應(yīng)環(huán)境的機(jī)器上對WORD/EXCEL文檔進(jìn)行讀寫。

這里簡單封裝了一個使用NPOI導(dǎo)出Excel的DLL，方便項(xiàng)目使用。步驟如下：

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

2、添加Excel屬性信息類ExcelProperty.cs

///summary

///用于定義導(dǎo)出的excel屬性

////summary

publicclassExcelProperty

{

publicExcelProperty(){}

///summary

///文件基本屬性

////summary

///paramname="company"公司名稱/param

///paramname="author"作者信息/param

///paramname="ApplicationName"創(chuàng)建程序信息/param

///paramname="Comments"填加xls文件備注/param

///paramname="title"填加xls文件標(biāo)題信息/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

///創(chuàng)建程序信息

////summary

privatestringapplicationName="";

///summary

///創(chuàng)建程序信息

////summary

publicstringApplicationName

{

get{returnapplicationName;}

set{applicationName=value;}

}

///summary

///填加xls文件備注

////summary

privatestringcomments="";

///summary

///填加xls文件備注

////summary

publicstringComments

{

get{returncomments;}

set{comments=value;}

}

///summary

///填加xls文件標(biāo)題信息

////summary

privatestringtitle="";

///summary

///填加xls文件標(biāo)題信息

////summary

publicstringTitle

{

get{returntitle;}

set{title=value;}

}

///summary

///填加文件主題信息

////summary

privatestringsubject="";

///summary

///填加文件主題信息

////summary

publicstringSubject

{

get{returnsubject;}

set{subject=value;}

}

}

3、添加Excel導(dǎo)出類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;

//構(gòu)造函數(shù)

publicExcelExportHelper(){}

publicExcelExportHelper(ILogloghelper)

{

this.LogHelper=loghelper;

}

///summary

///日志

////summary

privateILogLogHelper;

///summary

///要導(dǎo)出的Excel對象

////summary

privateHSSFWorkbookworkbook=null;

///summary

///要導(dǎo)出的Excel對象屬性

////summary

privateHSSFWorkbookWorkbook

{

get

{

if(workbook==null)

{

workbook=newHSSFWorkbook();

}

returnworkbook;

}

set{workbook=value;}

}

///summary

///設(shè)置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文件創(chuàng)建程序信息

si.Comments=ep.Comments;//填加xls文件備注

si.Title=ep.Title;//填加xls文件標(biāo)題信息

si.Subject=ep.Subject;//填加文件主題信息

si.CreateDateTime=DateTime.Now;

Workbook.SummaryInformation=si;

}

///summary

///泛型列表List導(dǎo)出到Excel文件

////summary

///paramname="list"源List表/param

///paramname="strHeaderText"標(biāo)題信息/param

///paramname="strFileName"保存路徑/param

///paramname="titles"列名/param

publicvoidExportToFileT(ListTlist,stringstrHeaderText,stringstrFileName,string[]titles=null)

{

try

{

//轉(zhuǎn)換數(shù)據(jù)源

DataTabledtSource=ListToDataTable(list,titles);

//開始導(dǎo)出

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導(dǎo)出到Excel文件

////summary

///paramname="dtSource"源DataTable/param

///paramname="strHeaderText"標(biāo)題信息/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導(dǎo)出到Excel的MemoryStream

////summary

///paramname="dtSource"源DataTable/param

///paramname="strHeaderText"標(biāo)題信息/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;idtSource.Rows.Count;i++)

{

for(intj=0;jdtSource.Columns.Count;j++)

{

intintTemp=Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;

if(intTemparrColWidth[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四個參數(shù)為：起始行，結(jié)束行，起始列，結(jié)束列

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;

//設(shè)置列寬

sheet.SetColumnWidth(column.Ordinal,(arrColWidth[column.Ordinal]+1)*256);

}

}

#endregion

rowIndex=2;

}

#endregion

#region填充內(nèi)容

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"://浮點(diǎn)型

case"System.Double":

doubledoubV=0;

double.TryParse(drValue,outdoubV);

newCell.SetCellValue(doubV);

break;

case"System.DBNull"://空值處理

newCell.SetCellValue("");

break;

default:

newCell.SetCellValue("");

break;

}

}

#endregion

rowIndex++;

}

using(MemoryStreamms=newMemoryStream())

{

Workbook.Write(ms);

ms.Flush();

ms.Position=0;

returnms;

}

}

///summary

///泛型列表List轉(zhuǎn)換為DataTable

////summary

///typeparamname="T"泛型實(shí)體/typeparam

///paramname="list"要轉(zhuǎn)換的列表/param

///paramname="titles"標(biāo)題/param

///returns/returns

publicDataTableListToDataTableT(ListTlist,string[]titles)

{

DataTabledt=newDataTable();

TypelistType=typeof(T);

PropertyInfo[]properties=listType.GetProperties();

//標(biāo)題行

if(titles!=nullproperties.Length==titles.Length)

{

for(inti=0;iproperties.Length;i++)

{

PropertyInfoproperty=properties[i];

dt.Columns.Add(newDataColumn(titles[i],property.PropertyType));

}

}

else

{

for(inti=0;iproperties.Length;i++)

{

PropertyInfoproperty=properties[i];

dt.Columns.Add(newDataColumn(property.Name,property.PropertyType));

}