技术共享

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

源码实测可用

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