前言
最近做项目使用上.Net Core，而且又要写一个excel的导出导入功能，以前我曾经写两篇关于http://asp.net上使用NPOI做导出导入功能文章--传送阵。
因为现在使用的框架是.Net core,因此以前引用的dll都要升级，以及写法上也有点点不同，所以就重新再写一文记录。
首先都是引入dll,到Nuget那里搜索
DotNetCore.NPOI
,然后下载安装即可。
导出篇
先引入这四个命名空间
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
接下就是正式的代码导入代码
public byte[] ExportToExcel<T>(List<T> entities, ExportModel model)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFPalette palette = workbook.GetCustomPalette();
HSSFColor hssFColor;
byte red, green, bule;
if (model.DataFields == null || model.DataFields.Length == 0)
{
model.DataFields = model.ColumnNames;
}
#region 标题
// 标题字体
IFont titleFont = workbook.CreateFont();
var titleColor = model.TitleRow.CellStyle.Font.Color;
red = titleColor[0];
green = titleColor[1];
bule = titleColor[2];
palette.SetColorAtIndex(8, red, green, bule);
hssFColor = palette.FindColor(red, green, bule);
titleFont.Color = hssFColor.Indexed;
titleFont.FontHeightInPoints = model.TitleRow.CellStyle.Font.FontHeightInPoints;
// 标题前景色
var titleForegroundColor = model.TitleRow.CellStyle.FillForegroundColor;
red = titleForegroundColor[0];
green = titleForegroundColor[1];
bule = titleForegroundColor[2];
palette.SetColorAtIndex(9, red, green, bule);
hssFColor = palette.FindColor(red, green, bule);
// 标题
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.FillForegroundColor = hssFColor.Indexed;
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
row.HeightInPoints = model.DataRow.HeightInPoints;
ICell cell = null;
for (int i = 0; i < model.ColumnNames.Length; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = titleStyle;
cell.SetCellValue(model.ColumnNames[i]);
}
#endregion
if (entities.Count > 0)
{
// 数据行
object cellValue = string.Empty;
ICellStyle cellStyle = workbook.CreateCellStyle();
IFont cellFont = workbook.CreateFont();
cellFont.FontHeightInPoints = model.DataRow.CellStyle.Font.FontHeightInPoints;
cellStyle.SetFont(cellFont);
cellStyle.VerticalAlignment = VerticalAlignment.Center;
for (int i = 0; i < entities.Count; i++)
{
row = sheet.CreateRow(i + 1);
row.HeightInPoints = model.DataRow.HeightInPoints;
object entity = entities[i];
for (int j = 0; j < model.DataFields.Length; j++)
{
cellValue = entity.GetType().GetProperty(model.DataFields[j]).GetValue(entity);
cell = row.CreateCell(j);
cell.CellStyle = cellStyle;
cell.SetCellValue(Convert.ToString(cellValue));
}
}
// 调整列宽
for (int i = 0; i <= entities.Count; i++)
{
sheet.AutoSizeColumn(i);
}
for (int columnNum = 0; columnNum <= model.ColumnNames.Length; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
columnWidth = Math.Min(columnWidth, 255);
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
return ms.GetBuffer();
}
}
这里需要注意的是，我使用的是对象是
HSSFWorkbook
，因此生成的文档是03版excel即xls类型的文档。如果想生成07版的excel即xlsx类型的文档，则需要使用
XSSFWorkbook
对象。其他的写法都基本一致。
还有一点就是
ExportModel
这个对象属于我自己封装的，是一些关于生成excel文档的一些基本配置，后面我给出这个对象代码属性。
调用public IActionResult ExportToExcel()
{
IExcelService _IExcelService = new ExcelService();
//var result = _IPostingStatusService.GetExcelData(model).ToList();
List<string> result = new List<string>();
ExportModel excelModel = new ExportModel();
excelModel.DataFields = new string[] { "Department", "JobTitle", "Folio" };
excelModel.ColumnNames = new string[] { "Department", "Job Title", "Request No." };
var buffer = _IExcelService.ExportToExcel(result, excelModel);
return File(buffer, MediaTypeNames.Application.Octet, "111.xls");
}
public class ExportModel
{
public string[] DataFields { get; set; }
public string[] ColumnNames { get; set; }
public RowModel TitleRow { get; set; } = new RowModel()
{
HeightInPoints = 22,
CellStyle = new CellStyleModel
{
FillForegroundColor = new byte[] { 0, 74, 134 },
Font = new FontModel
{
FontHeightInPoints = 12,
Color = new byte[] { 255, 255, 255 }
}
}
};
public RowModel DataRow { get; set; } = new RowModel()
{
HeightInPoints = 22,
CellStyle = new CellStyleModel
{
FillForegroundColor = new byte[] { 255, 255, 255 },
Font = new FontModel
{
FontHeightInPoints = 10,
Color = new byte[] { 0, 0, 0 }
}
}
};
}
public class RowModel
{
public CellStyleModel CellStyle { get; set; }
public float HeightInPoints { get; set; }
}
public class CellStyleModel
{
public byte[] FillForegroundColor { get; set; }
public FontModel Font { get; set; }
}
public class FontModel
{
public short FontHeightInPoints { get; set; }
/// <summary>
/// R,G,B
/// </summary>
public byte[] Color { get; set; }
}
导入篇
直接上代码
public List<T> ExcelToList<T>(Stream stream, string fileName) where T : class, new()
{
IWorkbook workbook = null;
string _ext = fileName.Substring(fileName.LastIndexOf("."), fileName.Length - fileName.LastIndexOf("."));
if (_ext == ".xlsx")
{
workbook = new XSSFWorkbook(stream);
}
else
{
workbook = new HSSFWorkbook(stream);
}
ISheet sheet = workbook.GetSheetAt(0);
IRow ITitleRow = sheet.GetRow(0);
int totalColumn = ITitleRow.LastCellNum;
int totalRow = sheet.LastRowNum;
Dictionary<string, int> dic = new Dictionary<string, int>();
var properties = typeof(T).GetProperties();
for (int i = 0, len = properties.Length; i < len; i++)
{
object[] _attributes = properties[i].GetCustomAttributes(typeof(DescriptionAttribute), false);
if (_attributes.Length == 0)
{
continue;
}
string _description = ((DescriptionAttribute)_attributes[0]).Description;
if (!string.IsNullOrWhiteSpace(_description))
{
dic.Add(_description, i);
}
}
string _value = string.Empty;
string _type = string.Empty;
int index = 0;
List<T> list = new List<T>();
for (int i = 1; i <= totalRow; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
var obj = new T();
for (int j = 0; j < totalColumn; j++)
{
if (dic.TryGetValue(ITitleRow.GetCell(j).ToString(), out index) && row.GetCell(j) != null)
{
_type = (properties[index].PropertyType).FullName;
_value = row.GetCell(j).ToString();
if (_type == "System.String")
{
properties[index].SetValue(obj, _value, null);
}
else if (_type == "System.DateTime")
{
DateTime pdt = Convert.ToDateTime(_value, CultureInfo.InvariantCulture);
properties[index].SetValue(obj, pdt, null);
}
else if (_type == "System.Boolean")
{
bool pb = Convert.ToBoolean(_value);
properties[index].SetValue(obj, pb, null);
}
else if (_type == "System.Int16")
{
short pi16 = Convert.ToInt16(_value);
properties[index].SetValue(obj, pi16, null);
}
else if (_type == "System.Int32")
{
int pi32 = Convert.ToInt32(_value);
properties[index].SetValue(obj, pi32, null);
}
else if (_type == "System.Int64")
{
long pi64 = Convert.ToInt64(_value);
properties[index].SetValue(obj, pi64, null);
}
else if (_type == "System.Byte")
{
byte pb = Convert.ToByte(_value);
properties[index].SetValue(obj, pb, null);
}
else
{
properties[index].SetValue(obj, null, null);
}
}
}
list.Add(obj);
}
return list;
}
public IActionResult LoadExcel(IFormFile file)
{
IExcelService _IExcelService = new ExcelService();
try
{
var rows = _IExcelService.ExcelToList<ImportExcelModel>(file.OpenReadStream(), file.FileName);
return Content("导入成功！");
}
catch (Exception e) { return Content("导入失败"); }
}
：
ImportExcelModel
其实也就是自己建的一个保存数据对象，这个对象可以跟你的表对应，这样导入的数据就能与表关联起来再保存到数据库。
最后
上面的代码就是.NetCore使用NPOI做的导出导入功能的通用写法，是可以直接复制到自己代码，稍微更改一下就能直接使用，希望对大家有所帮助。
![bootstraptable导出excel独立使用_使用 EasyPOI 优雅导出Excel模板数据（含图片）[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)