epplus读写Excel

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp4
{
    class Program
    {
        static async Task Main(string[] args)
        {
            //需要注明非商业用途
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            //YoutubeDemo.xlsx文件可以不存在，因为还没有创建，但是文件夹C:\Demos\一定要存在，不然会报错
            var file = new FileInfo(@"C:\Demos\Demo.xlsx");

            var people = GetSetupData();

            await SaveExcelFile(people, file);

            List<PersonModel> peopleFromExcel = await LoadExcelFile(file);
        }
        
        //从Excel读取数据
        private static async Task<List<PersonModel>> LoadExcelFile(FileInfo file)
        {
            var output = new List<PersonModel>();

            using (var package = new ExcelPackage(file))
            {
                await package.LoadAsync(file);
                var ws = package.Workbook.Worksheets[0];

                int row = 3;
                int col = 1;

                while (string.IsNullOrWhiteSpace(ws.Cells[row,col].Value?.ToString())==false)
                {
                    var person = new PersonModel
                    {
                        Id = int.Parse(ws.Cells[row, col].Value.ToString()),
                        FirstName = ws.Cells[row, col+1].Value.ToString(),
                        LastName = ws.Cells[row, col+2].Value.ToString()
                    };
                    output.Add(person);
                    row += 1;
                }
            }

            return output;
        }

        //写入数据Excel
        private static async Task SaveExcelFile(List<PersonModel> people, FileInfo file)
        {
            DeleteIfExists(file);

            using (var package = new ExcelPackage(file))
            {
                //新建名称为MainReport的sheet
                var ws = package.Workbook.Worksheets.Add("MainReport");
                //指定数据来源，并自动标出表头（属性名），指定A2单元格为左上角第一个单元格
                var range = ws.Cells["A2"].LoadFromCollection(people, true);
                //根据内容，自动调整列宽
                range.AutoFitColumns();
                //指定A1单元格的内容
                ws.Cells["A1"].Value = "Our Cool Report";
                //合并A1-C1单元格
                ws.Cells["A1:C1"].Merge = true;
                //水平居中
                ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                ws.Row(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                //字体大小
                ws.Row(1).Style.Font.Size = 24;
                //字体颜色
                ws.Row(1).Style.Font.Color.SetColor(Color.Blue);
                //字体加粗
                ws.Row(2).Style.Font.Bold=true;
                //单元格宽度
                ws.Column(3).Width = 20;

                await package.SaveAsync();
            }
        }

        //如果存在文件，则先删除
        private static void DeleteIfExists(FileInfo file)
        {
            if (file.Exists)
            {
                file.Delete();
            }
        }

        //初始化数据
        static List<PersonModel> GetSetupData()
        {
            var output = new List<PersonModel>
            {
                new PersonModel{ Id=1,FirstName="Tim",LastName="Corey"},
                new PersonModel{ Id=2,FirstName="Sue",LastName="Storm"},
                new PersonModel{ Id=3,FirstName="Jane",LastName="Smith"}
            };
            return output;
        }
    }
}