C# NPOI reflection import data into excel file

using System.ComponentModel.DataAnnotations;
using System.Diagnostics;
using System.Runtime.CompilerServices;
using System.Security.Cryptography;
using System.Text;
using Newtonsoft.Json;
using System.Reflection;
using NPOI.SS.Formula.Functions;
using NPOI.XSSF;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;


namespace ConsoleApp2
{
    internal class Program
    {
        static void Main(string[] args)
        {
            ImportStuListToExcel(100);
            PrintLog();
        }

        static void PrintLog(string msg = null, [System.Runtime.CompilerServices.CallerLineNumber] int lineNum = -1,
          [CallerMemberName] string func = null, [CallerFilePath] string fileName = null)
        {
            Console.WriteLine($"{DateTime.Now.ToString("yyyyMMddHHmmssffff")},{msg},{lineNum},{func},{fileName}");
        }

        static void ImportStuListToExcel(int len)
        {
            var list = InitStudentList(len);
            ImportListTToExcel<Student>(list, $"aaa{DateTime.Now.ToString("yyyyMMddHHmmssffff")}.xlsx");
        }

        static void ImportListTToExcel<T>(List<T> dataList, string excelFilePath) where T : class
        {
            if(File.Exists(excelFilePath))
            {
                File.Delete(excelFilePath);
            }

            //create work book
            IWorkbook workbook = new XSSFWorkbook();  

            //create sheet
            ISheet sheet = workbook.CreateSheet("Sheet1");

            //Header row
            int rowIdx = 0;
            IRow headerRow = sheet.CreateRow(rowIdx++);
            var props = typeof(T).GetProperties();
            int propCount=props.Length;

            int columnIdx = 0;
            foreach (var prop in props)
            {
                var cell = headerRow.CreateCell(columnIdx++);
                cell.SetCellValue(prop.Name);
            }  
            
            foreach (var stu in dataList) 
            {
                IRow dataRow=sheet.CreateRow(rowIdx++);
                columnIdx = 0;
                foreach (var prop in props)
                {
                    var cell = dataRow.CreateCell(columnIdx++);                    
                    cell.SetCellValue(prop.GetValue(stu).ToString());                        
                }
            }

            using (FileStream file = new FileStream(excelFilePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(file); 
            }
            workbook.Close();             
        }

        static List<Student> InitStudentList(int len)
        {
            List<Student> list = new List<Student>();
            for (int i = 0; i < len; i++)
            {
                list.Add(new Student()
                {
                    Id = i + 1,
                    Name = Guid.NewGuid().ToString(),
                    Description = Guid.NewGuid().ToString(),
                    Title = Guid.NewGuid().ToString(),
                    Topic = Guid.NewGuid().ToString(),
                    Author = Guid.NewGuid().ToString(),
                    ISBN = Guid.NewGuid().ToString(),
                    Summary = Guid.NewGuid().ToString(),
                });
            }
            return list;
        }
    } public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string Title { get; set; }
        public string Topic { get; set; }
        public string ISBN { get; set; }
        public string Summary { get; set; }
        public string Author { get; set; }
    }
}
 

 

posted @ 2024-02-03 17:06  FredGrit  阅读(1)  评论(0编辑  收藏  举报