源码实测可用
使用.net工具包NPOI,生成excel文件到本地磁盘。
实际项目中可以指定路径到服务器,把生成的文件存放到服务器指定目录。
controller层
[HttpPost("ExportExcel")]
public void ExportExcel()
{
_TestService.ExportToExcel();
}
service层
public void ExportToExcel() {
String FileName = "D:\\learning\\yxl\\chinese\\2024年7月10日星期三.xlsx";
var chineseWordList = this.WrapChineseWord();
ExportExcelToDiskUtils<ChineseWord>.ExportToExcel(chineseWordList, FileName);
}
NPOI工具类
using Newtonsoft.Json;
using NPOI.HPSF;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Reflection;
namespace Learning.Dotnet
{
public class ExportExcelToDiskUtils<T>
{
public static void ExportToExcel(List<T> list, String FileName)
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("Sheet1");
IRow row1 = sheet.CreateRow(0);
Type t = typeof(T);
int cell = 0;
var properties = t.GetProperties().Where(p => p.GetCustomAttribute<JsonPropertyAttribute>() != null).Select(p => p).ToList();
foreach (var pro in properties)
{
row1.CreateCell(cell).SetCellValue(pro.GetCustomAttribute<JsonPropertyAttribute>().PropertyName);
cell++;
}
int row = 1;
foreach (var item in list)
{
cell = 0;
IRow newRow = sheet.CreateRow(row);
foreach (var pro in properties)
{
var value = list[row - 1];
newRow.CreateCell(cell).SetCellValue(Convert.ToString(pro.GetValue(value, null)));
cell++;
}
row++;
}
// 导出到文件
using (FileStream file = new FileStream(FileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(file);
}
// 释放资源
workbook.Close();
}
}
}
业务对象Model
using Newtonsoft.Json;
namespace Learning.Models.Chinese
{
public class ChineseWord
{
[JsonProperty(PropertyName = "field1")]
public string field1 { get; set; }
[JsonProperty(PropertyName = "field2")]
public string field2 { get; set; }
[JsonProperty(PropertyName = "field3")]
public string field3 { get; set; }
[JsonProperty(PropertyName = "field4")]
public string field4 { get; set; }
[JsonProperty(PropertyName = "field5")]
public string field5 { get; set; }
[JsonProperty(PropertyName = "field6")]
public string field6 { get; set; }
[JsonProperty(PropertyName = "field7")]
public string field7 { get; set; }
[JsonProperty(PropertyName = "field8")]
public string field8 { get; set; }
[JsonProperty(PropertyName = "field9")]
public string field9 { get; set; }
[JsonProperty(PropertyName = "field10")]
public string field10 { get; set; }
}
}
数据封装
这里演示,直接mock数据,实际项目中,替换成自己的数据源即可。
private List<ChineseWord> WrapChineseWord()
{
var chineseWord = new ChineseWord();
chineseWord.field1 = "field1";
chineseWord.field2 = "field2";
chineseWord.field3 = "field3";
chineseWord.field4 = "field4";
chineseWord.field5 = "field5";
chineseWord.field6 = "field6";
chineseWord.field7 = "field7";
chineseWord.field8 = "field8";
chineseWord.field9 = "field9";
chineseWord.field10 = "field10";
var chineseWord2 = new ChineseWord();
chineseWord2.field1 = "field1";
chineseWord2.field2 = "field2";
chineseWord2.field3 = "field3";
chineseWord2.field4 = "field4";
chineseWord2.field5 = "field5";
chineseWord2.field6 = "field6";
chineseWord2.field7 = "field7";
chineseWord2.field8 = "field8";
chineseWord2.field9 = "field9";
chineseWord2.field10 = "field10";
var chineseWordList = new List<ChineseWord>();
chineseWordList.Add(chineseWord);
chineseWordList.Add(chineseWord2);
_Logger.LogInformation("chineseWordList:{}", JsonConvert.SerializeObject(chineseWordList));
return chineseWordList;
}