通过NPOI如何导出数据到Excel

导出依赖于NPOI，

NPOI是指构建在POI 3.x版本之上的一个程序，NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。

NPOI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。

使用方法前，请先通过Nuget安装NPOI项目；

当前方法只支持Excel导出，并且现在的方法不支持格式。比如列多宽目前无法使用。

 NPOI的优势

（一）传统操作Excel遇到的问题：

1、如果是.NET，需要在服务器端装Office，且及时更新它，以防漏洞，还需要设定权限允许.NET访问COM+，如果在导出过程中出问题可能导致服务器宕机。

2、Excel会把只包含数字的列进行类型转换，本来是文本型的，Excel会将其转成数值型的，比如编号000123会变成123。

3、导出时，如果字段内容以“-”或“=”开头，Excel会把它当成公式进行，会报错。

4、Excel会根据Excel文件前8行分析数据类型，如果正好你前8行某一列只是数字，那它会认为该列为数值型，自动将该列转变成类似1.42702E+17格式，日期列变成包含日期和数字的。

（二）使用NPOI的优势

1、您可以完全免费使用该框架

2、包含了大部分EXCEL的特性(单元格样式、数据格式、公式等等)

3、专业的技术支持服务(24*7全天候) (非免费)

4、支持处理的文件格式包括xls, xlsx, docx.

5、采用面向接口的设计架构( 可以查看 NPOI.SS 的命名空间)

6、同时支持文件的导入和导出

7、基于.net 2.0 也支持xlsx 和 docx格式(当然也支持.net 4.0)

8、来自全世界大量成功且真实的测试Cases

9、大量的实例代码

11、你不需要在服务器上安装微软的Office，可以避免版权问题。

12、使用起来比Office PIA的API更加方便，更人性化。

13、你不用去花大力气维护NPOI，NPOI Team会不断更新、改善NPOI，绝对省成本。

14、不仅仅对与Excel可以进行操作，对于doc、ppt文件也可以做对应的操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Data;
using NPOI.SS.UserModel;
using Yikangtech.Framework.Core.Util;

namespace Yikangtech.PMP.SMM.Infrastructrue.Util
{
/// <summary>
/// 导出文件工具
/// </summary>
public sealed class ExportFileUtil
{
#region 属性、字段

private string _fileName;
private string _fileStoredPath;
private int _excelSheetCount = 0;

/// <summary>
/// 要导出的文件名称
/// </summary>
public string FileName
{
get { return this._fileName ?? (this._fileName = Guid.NewGuid().ToString()); }
}
/// <summary>
/// 要导出文件在服务器端的存储路径
/// </summary>
public string FileStoredPath
{
get { return this._fileName ?? (this._fileStoredPath = "/TemplateDoc/"); }
}
/// <summary>
/// 标准excel文件应用类型
/// </summary>
public string StandardExcelFileContentType
{
get { return "application/vnd.ms-excel"; }
}
/// <summary>
/// 是否显示所有的字段
/// </summary>
public bool IsDisplayAllFields { get; set; }

/// <summary>
/// 绑定表头值时触发的回调方法
/// </summary>
public Func<ICell, object, string> OnBindingXlsHeadValue;
/// <summary>
/// 绑定表正文值时触发的回调方法
/// </summary>
public Func<ICell, object, string> OnBindingXlsBodyValue;

#endregion

#region 构造函数

public ExportFileUtil() { }
public ExportFileUtil(string fileName) : this(fileName, string.Empty) { }
public ExportFileUtil(bool isDisplayAllFields)
{
this.IsDisplayAllFields = isDisplayAllFields;
}
public ExportFileUtil(string fileName, string fileStoredPath) : this(fileName, fileStoredPath, true) { }
public ExportFileUtil(string fileName, string fileStoredPath, bool isDisplayAllFields)
{
_fileName = fileName;
_fileStoredPath = fileStoredPath;
IsDisplayAllFields = isDisplayAllFields;
}

#endregion

#region 导出excel

/// <summary>
/// 以路径的形式导出excel
/// </summary>
/// <param name="objects"></param>
/// <returns></returns>
public string ExportExcelToPath(IEnumerable<object> objects)
{
string sheetName = string.Empty;
return this.ExportExcelToPath(sheetName, objects);
}
/// <summary>
/// 以路径的形式导出excel
/// </summary>
/// <param name="objects"></param>
/// <param name="columnNameDic"></param>
/// <returns></returns>
public string ExportExcelToPath(IEnumerable<object> objects, Dictionary<string, string> columnNameDic)
{
string sheetName = string.Empty;
return this.ExportExcelToPath(sheetName, objects, columnNameDic);
}
/// <summary>
/// 以路径的形式导出excel
/// </summary>
/// <param name="sheetName"></param>
/// <param name="objects"></param>
/// <returns></returns>
public string ExportExcelToPath(string sheetName, IEnumerable<object> objects)
{
return this.ExportExcelToPath(sheetName, objects);
}
/// <summary>
/// 以路径的形式导出excel
/// </summary>
/// <param name="sheetName"></param>
/// <param name="objects"></param>
/// <param name="columnNameDic"></param>
/// <returns></returns>
public string ExportExcelToPath(string sheetName, IEnumerable<object> objects, Dictionary<string, string> columnNameDic)
{
IEnumerable<object> values = objects ?? new List<object>();
values = values.Where(v => v != null);

Type objectType;
object value = values.FirstOrDefault();
if (value == null)
{
objectType = typeof(object);
}
else
{
objectType = value.GetType();
}

return this.ExportExcelToPath(sheetName, values, objectType, columnNameDic);
}
/// <summary>
/// 以路径的形式导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName"></param>
/// <param name="entities"></param>
/// <returns></returns>
public string ExportExcelToPath<T>(string sheetName, IEnumerable<T> entities)
{
return this.ExportExcelToPath<T>(sheetName, entities, null);
}
/// <summary>
/// 以路径的形式导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName"></param>
/// <param name="entities"></param>
/// <param name="columnNameDic"></param>
/// <returns></returns>
public string ExportExcelToPath<T>(string sheetName, IEnumerable<T> entities, Dictionary<string, string> columnNameDic)
{
IEnumerable<T> values = entities ?? new List<T>();
values = values.Where(v => v != null);

return this.ExportExcelToPath(sheetName, values.Select(v => v as object), typeof(T), columnNameDic);
}
/// <summary>
/// 以路径的形式导出excel
/// </summary>
/// <param name="sheetName"></param>
/// <param name="objects"></param>
/// <param name="columnNameDic"></param>
/// <param name="objectType"></param>
/// <returns></returns>
public string ExportExcelToPath(string sheetName, IEnumerable<object> objects, Type objectType, Dictionary<string, string> columnNameDic)
{
sheetName = sheetName ?? objectType.Name;

DataSet dataSet = new DataSet();
DataTable dataTable = ConverterUtil.ToDataTable(objects, objectType);
dataTable.TableName = sheetName;
dataSet.Tables.Add(dataTable);

return this.ExportExcelToPathInternal(dataSet, columnNameDic);
}

/// <summary>
/// 以路径的形式导出excel
/// 注：取dataSet中DataTable的名称为工作簿名称，若不存在表名，则由系统自定义工作簿名称
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public string ExportExcelToPath(DataTable dataTable)
{
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable.Copy());

return this.ExportExcelToPath(dataSet);
}
/// <summary>
/// 以路径的形式导出excel
/// 注：取dataSet中DataTable的名称为工作簿名称，若不存在表名，则由系统自定义工作簿名称
/// </summary>
/// <param name="dataTable"></param>
/// <param name="columnNameDic"></param>
/// <returns></returns>
public string ExportExcelToPath(DataTable dataTable, Dictionary<string, string> columnNameDic)
{
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable.Copy());

return this.ExportExcelToPath(dataSet, columnNameDic);
}
/// <summary>
/// 以路径的形式导出excel
/// 注：取dataSet中DataTable的名称为工作簿名称，若不存在表名，则由系统自定义工作簿名称
/// </summary>
/// <param name="dataSet"></param>
/// <returns></returns>
public string ExportExcelToPath(DataSet dataSet)
{
return ExportExcelToPath(dataSet, null);
}
/// <summary>
/// 以路径的形式导出excel
/// 注：取dataSet中DataTable的名称为工作簿名称，若不存在表名，则由系统自定义工作簿名称
/// </summary>
/// <param name="dataSet"></param>
/// <param name="columnNames"></param>
/// <returns></returns>
public string ExportExcelToPath(DataSet dataSet, Dictionary<string, string> columnNameDic)
{
return this.ExportExcelToPathInternal(dataSet, columnNameDic);
}

/// <summary>
/// 获取默认的
/// </summary>
/// <returns></returns>
private string GetDefaultSheetName()
{
return string.Format("sheet{0}", _excelSheetCount++);
}
/// <summary>
/// 获取工作簿名称，若dataTable为空或表名不存在，则获取默认工作簿名
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private string GetSheetName(DataTable dataTable)
{
if (dataTable == null || string.IsNullOrEmpty(dataTable.TableName) || string.IsNullOrEmpty(dataTable.TableName.Trim()))
{
return this.GetDefaultSheetName();
}
else
{
return dataTable.TableName;
}
}
/// <summary>
/// 获取表正文标题列列名称
/// </summary>
/// <param name="dataTable"></param>
/// <param name="columnNameDic"></param>
/// <returns></returns>
private List<string> GetRowHeadNames(DataTable dataTable, Dictionary<string, string> columnNameDic)
{
List<string> result = new List<string>();
DataColumnCollection dataColumnCollection = dataTable.Columns;

if (dataColumnCollection == null && dataColumnCollection.Count < 1)
{
result.AddRange(columnNameDic.Values.Select(v => v));
return result;
}

if (this.IsDisplayAllFields)
{
foreach (var column in dataColumnCollection)
{
DataColumn dataColumn = column as DataColumn;
if (columnNameDic != null && columnNameDic.Keys.Contains(dataColumn.ColumnName))
{
result.Add(columnNameDic[dataColumn.ColumnName]);
}
else
{
result.Add(dataColumn.ColumnName);
}
}
}
else
{
result.AddRange(columnNameDic.Values);
}

return result;
}
/// <summary>
/// 创建工作簿
/// </summary>
/// <param name="workbook"></param>
/// <param name="dataTable"></param>
/// <param name="columnNameDic"></param>
private void CreateSheet(IWorkbook workbook, DataTable dataTable, Dictionary<string, string> columnNameDic)
{
ISheet sheet = workbook.CreateSheet(this.GetSheetName(dataTable));
List<string> rowHeadNames = this.GetRowHeadNames(dataTable, columnNameDic);

IRow xlsRow;
int rowCounter = 0, cellCounter = 0;

//填充标题
xlsRow = sheet.CreateRow(rowCounter++);
foreach (var name in rowHeadNames)
{
string columnName;
xlsRow.CreateCell(cellCounter);
if (OnBindingXlsHeadValue != null)
{
columnName = OnBindingXlsHeadValue(xlsRow.GetCell(cellCounter), name);
}
else
{
columnName = name;
}

xlsRow.GetCell(cellCounter).SetCellValue(columnName);
cellCounter++;
}

//填充正文
foreach (DataRow row in dataTable.Rows)
{
xlsRow = sheet.CreateRow(rowCounter++);

cellCounter = 0;
if (this.IsDisplayAllFields)
{
foreach (DataColumn column in dataTable.Columns)
{
object value = row[column] ?? string.Empty;

xlsRow.CreateCell(cellCounter);
if (OnBindingXlsBodyValue != null)
{
value = OnBindingXlsBodyValue(xlsRow.GetCell(cellCounter), value);
}

xlsRow.GetCell(cellCounter).SetCellValue(value.ToString());
cellCounter++;
}
}
else
{
foreach (string key in columnNameDic.Keys)
{
object value = row[key] ?? string.Empty;

xlsRow.CreateCell(cellCounter);
if (OnBindingXlsBodyValue != null)
{
value = OnBindingXlsBodyValue(xlsRow.GetCell(cellCounter), value);
}

xlsRow.GetCell(cellCounter).SetCellValue(value.ToString());
cellCounter++;
}
}

}
}
/// <summary>
/// 以路径的形式导出excel
/// 注：取dataSet中DataTable的名称为工作簿名称，若不存在表名，则由系统自定义工作簿名称
/// </summary>
/// <param name="dataSet"></param>
/// <param name="columnNameDic"></param>
/// <returns></returns>
private string ExportExcelToPathInternal(DataSet dataSet, Dictionary<string, string> columnNameDic)
{
IWorkbook workbook = new HSSFWorkbook();
DataTableCollection dataTableCollection = (dataSet ?? new DataSet()).Tables;

//如果表集为空时，提供一个默认表

if (dataTableCollection.Count < 1)
{
dataTableCollection.Add(new DataTable(GetDefaultSheetName()));
}

//创建工作簿

foreach (var dataTable in dataTableCollection)
{
this.CreateSheet(workbook, dataTable as DataTable, columnNameDic);
}

//返回数据

string
rootFullPath = HttpContext.Current.Server.MapPath(FileStoredPath),
storedFullPath;
if (!Directory.Exists(rootFullPath))
{
Directory.CreateDirectory(rootFullPath);
}

storedFullPath = Path.Combine(HttpContext.Current.Server.MapPath(FileStoredPath), FileName + ".xls");
using (FileStream fileStream = new FileStream(storedFullPath, FileMode.Create))
{
workbook.Write(fileStream);
}

return storedFullPath;
}

#endregion
}
}