Technology Sharing

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

Source code is available

Use the .net toolkit NPOI to generate an excel file to the local disk.

In actual projects, you can specify the path to the server and store the generated files in the specified directory of the server.

Controller layer

        [HttpPost("ExportExcel")]
        public void ExportExcel()
        {
            _TestService.ExportToExcel();
        }

Service layer

        public void ExportToExcel() {
            String FileName = "D:\learning\yxl\chinese\2024年7月10日星期三.xlsx";
            var chineseWordList = this.WrapChineseWord();
            ExportExcelToDiskUtils<ChineseWord>.ExportToExcel(chineseWordList, FileName);
        }

NPOI Tools

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();
        }
    }
}

Business Object 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; }
 
 
    }
}

Data Encapsulation

Here we demonstrate directly mocking the data. In actual projects, you can replace it with your own data source.

        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;
        }