源碼實測可用

使用.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;}