1、安装NPOI
NuGet\Install-Package NPOI -Version 2.7.0
2、准备Excel操作相关类
新建类库项目,添加项目被引用,新建文件夹Exceloperation,添加3个类文件
TitleAttribute.cs
[AttributeUsage(AttributeTargets.Property)]
public class TitleAttribute : Attribute
{
public string? Titile;
public HSSFCellStyle? CellType;
}
ExcelDataResource.cs
public class ExcelDataResource
{
/// <summary>
/// 页签名称
/// </summary>
public string? SheetName { get; set; }
/// <summary>
/// 标题所在行
/// </summary>
public int TitleIndex { get; set; }
/// <summary>
/// 每一行的数据
/// </summary>
public List<object>? SheetDataResource { get; set; }
}
public class UserInfo
{
[Title(Titile = "用户Id")]
public int UserId { get; set; }
[Title(Titile = "用户名称")]
public string? UserName { get; set; }
[Title(Titile = "用户年龄")]
public int Age { get; set; }
[Title(Titile = "用户类型")]
public int UserType { get; set; }
[Title(Titile = "描述")]
public string? Description { get; set; }
}
public class ClassInfo
{
[Title(Titile = "学生Id")]
public int UserId { get; set; }
[Title(Titile = "学生名称")]
public string? UserName { get; set; }
[Title(Titile = "学生年龄")]
public int Age { get; set; }
[Title(Titile = "学生类型")]
public int UserType { get; set; }
[Title(Titile = "学生描述1")]
public string? Description1 { get; set; }
[Title(Titile = "学生描述2")]
public string? Description2 { get; set; }
[Title(Titile = "学生描述3")]
public string? Description3 { get; set; }
[Title(Titile = "学生描述4")]
public string? Description4 { get; set; }
}
ExcelOperationHelper.cs
public class ExcelOperationHelper
{
/// <summary>
/// 创建一个ExcelWorkbook
/// </summary>
/// <returns></returns>
public static IWorkbook CreateExcelWorkbook()
{
HSSFWorkbook _Workbook = new HSSFWorkbook();
ISheet sheet = _Workbook.CreateSheet("Sheet1");
IRow titleRow = sheet.CreateRow(0);
ICell cell = titleRow.CreateCell(1);
cell.SetCellValue("你好");
return _Workbook;
}
/// <summary>
/// 导出
/// </summary>
public static IWorkbook DataToHSSFWorkbook(List<ExcelDataResource>? dataResources)
{
HSSFWorkbook _Workbook = new HSSFWorkbook();
if (dataResources == null && dataResources?.Count == 0)
{
return _Workbook;
}
foreach (var sheetResource in dataResources)
{
if (sheetResource.SheetDataResource != null && sheetResource.SheetDataResource.Count == 0)
{
break;
}
ISheet sheet = _Workbook.CreateSheet(sheetResource.SheetName);
object obj = sheetResource.SheetDataResource[0];
Type type = obj.GetType();
List<PropertyInfo> propList = type.GetProperties().Where(c => c.IsDefined(typeof(TitleAttribute), true)).ToList();
IRow titleRow = sheet.CreateRow(0);
ICellStyle style = _Workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
style.Alignment = HorizontalAlignment.CenterSelection;
style.VerticalAlignment = VerticalAlignment.Center;
titleRow.Height = 100 * 4;
for (int i = 0; i < propList.Count(); i++)
{
TitleAttribute propertyAttribute = propList[i].GetCustomAttribute<TitleAttribute>();
ICell cell = titleRow.CreateCell(i);
cell.SetCellValue(propertyAttribute.Titile);
cell.CellStyle = style;
}
for (int i = 0; i < sheetResource.SheetDataResource.Count(); i++)
{
IRow row = sheet.CreateRow(i + 1);
object objInstance = sheetResource.SheetDataResource[i];
for (int j = 0; j < propList.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(propList[j].GetValue(objInstance).ToString());
}
}
}
return _Workbook;
}
/// <summary>
/// 生成Excel的内存流-MemoryStream
/// </summary>
/// <param name="dataResources"></param>
/// <returns></returns>
public static MemoryStream ToExcelMemoryStream(List<ExcelDataResource> dataResources)
{
IWorkbook _Workbook = DataToHSSFWorkbook(dataResources);
using (MemoryStream stream = new MemoryStream())
{
_Workbook.Write(stream, true);
return stream;
}
}
/// <summary>
///通过数据生成Excel 然后转换成byte[]
/// </summary>
/// <param name="dataResources"></param>
/// <returns></returns>
public static byte[] ToExcelByteArray(List<ExcelDataResource> dataResources)
{
IWorkbook _Workbook = DataToHSSFWorkbook(dataResources);
using (MemoryStream stream = new MemoryStream())
{
_Workbook.Write(stream, true);
byte[] bt = stream.ToArray();
stream.Write(bt, 0, (int)stream.Length);
return bt;
}
}
/// <summary>
///Excel转换成DataTable
/// </summary>
/// <param name="hSSFWorkbook"></param>
/// <returns></returns>
public static List<DataTable> ToExcelDateTable(IWorkbook hSSFWorkbook)
{
List<DataTable> datatableList = new List<DataTable>();
for (int sheetIndex = 0; sheetIndex < hSSFWorkbook.NumberOfSheets; sheetIndex++)
{
ISheet sheet = hSSFWorkbook.GetSheetAt(sheetIndex);
//获取表头 FirstRowNum 第一行索引 0
IRow header = sheet.GetRow(sheet.FirstRowNum);//获取第一行
if (header == null)
{
break;
}
int startRow = 0;//数据的第一行索引
DataTable dtNpoi = new DataTable();
startRow = sheet.FirstRowNum + 1;
for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
{
ICell cell = header.GetCell(i);
if (cell != null)
{
string cellValue = $"Column{i + 1}_{cell.ToString()}";
if (cellValue != null)
{
DataColumn col = new DataColumn(cellValue);
dtNpoi.Columns.Add(col);
}
else
{
DataColumn col = new DataColumn();
dtNpoi.Columns.Add(col);
}
}
}
//数据 LastRowNum 最后一行的索引 如第九行---索引 8
for (int i = startRow; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);//获取第i行
if (row == null)
{
continue;
}
DataRow dr = dtNpoi.NewRow();
//遍历每行的单元格
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
if (row.GetCell(j) != null)
dr[j] = row.GetCell(j).ToString();
}
dtNpoi.Rows.Add(dr);
}
datatableList.Add(dtNpoi);
}
return datatableList;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="stream"></param>
/// <returns></returns>
public static List<DataTable> ExcelStreamToDateTable(Stream stream)
{
IWorkbook hSSFWorkbook = WorkbookFactory.Create(stream);
return ToExcelDateTable(hSSFWorkbook);
}
}
3、Utility文件夹下新建ResultExt文件夹,新建类ExcelResult.cs
/// <summary>
/// 这个返回值类型,只能适用在mvc返回值,他就是专门用来返回Excel文件的
/// </summary>
public class ExcelResult : IActionResult
{
private string _ExcelName;
private List<ExcelDataResource> _ExcelDataResources;
/// <summary>
/// 如果没有时间就默认以当前时间为文件名称
/// </summary>
/// <param name="excelDataResources"></param>
public ExcelResult(List<ExcelDataResource> excelDataResources) : this(DateTime.Now.ToString("yyyyMMddHHmmssffffff"), excelDataResources)
{
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="excelName">文件名称</param>
/// <param name="excelDataResources">数据源</param>
public ExcelResult(string excelName, List<ExcelDataResource> excelDataResources)
{
_ExcelName = excelName;
_ExcelDataResources = excelDataResources;
}
public async Task ExecuteResultAsync(ActionContext context)
{
//给定输出的结果,浏览器以什么方式来解析
context.HttpContext.Response.ContentType = "application/vnd.ms-excel";
//设置响应的头信息
context.HttpContext.Response.Headers.Add("Content-Disposition", $"attachment;filename={_ExcelName}.xls");
//得到需要响应给浏览器的数据
byte[] bt = ExcelOperationHelper.ToExcelByteArray(_ExcelDataResources);
//各种不同类型的数据其实最终都是要写到这儿来;
await context.HttpContext.Response.BodyWriter.WriteAsync(bt);
}
}
4、Controller中获取数据返回数据
//扩展返回
public IActionResult ReturnExcelFile()
{
// 获取导出Excel需要的数据源
List<ExcelDataResource> list = GetExcelSheetData(1);
return new ExcelResult(list); //调用IActionResult的扩展返回Excel
}
//新建数据的方式准备两个sheet的数据
private List<ExcelDataResource> GetExcelSheetData(int id)
{
List<object> objlist = new List<object>();
for (int i = 0; i < 100; i++)
{
objlist.Add(new UserInfo()
{
UserId = i + 1,
UserName = $"名称-{i}",
Age = i + i + 1,
UserType = i + 1,
Description = $"Description_{i}"
});
}
List<object> Classobjlist = new List<object>();
for (int i = 0; i < 200; i++)
{
Classobjlist.Add(new ClassInfo()
{
UserId = i + 1,
UserName = $"名称-{i}",
Age = i + i + 1,
UserType = i + 1,
Description1 = $"Description_{i}",
Description2 = $"Description_{i}",
Description3 = $"Description_{i}",
Description4 = $"Description_{i}"
});
}
List<ExcelDataResource> datalist = new List<ExcelDataResource>()
{
new ExcelDataResource(){
SheetName="Sheet1",
TitleIndex=1,
SheetDataResource=objlist
},
new ExcelDataResource(){
SheetName="Sheet2",
TitleIndex=1,
SheetDataResource=Classobjlist
}
};
return datalist;
}