C#进阶-读写Excel常用框架及其使用方式

目录

一、MiniExcel开源框架（推荐）

1、写/导出

方式一 

方式二 

多表创建

更改配置

特性使用

CSV尾行新增行

CSV、XLSX互转

2、读/导入

简单示例

二、NPOI开源框架

一、MiniExcel开源框架（推荐）

 添加NuGet包MiniExcel

详细了解：https://gitee.com/dotnetchina/MiniExcel

1、写/导出

方式一 

        private void Button_Click_TestMini(object sender, RoutedEventArgs e)
        {
            var path = Path.Combine(Directory.GetCurrentDirectory(), "testExcel.xlsx");
            //匿名类型
            //MiniExcel.SaveAs(path, new[]
            //{
            //    new { ID=1,Name="Test"},
            //    new { ID=2,Name="Mini" },
            //    new { ID=3,Name="Excel"}
            //}, overwriteFile: true);
            MiniExcel.SaveAs(path, new[]
            {
                new Test(){ ID=1,Name="Test"},
                new Test(){ ID=2,Name="Mini" },
                new Test(){ ID=3,Name="Excel"}
            }, overwriteFile: true);
        }

        public class Test
        {
            [ExcelColumn(Name = "ID", Width = 20)]
            public int ID { get; set; }
            [ExcelColumn(Name = "Name", Width = 20)]
            public string Name { get; set; }
        }

方式二 

        private void Button_Click_TestMini(object sender, RoutedEventArgs e)
        {
            var path = Path.Combine(Directory.GetCurrentDirectory(), "testExcel.xlsx");
           //List<Dictionary<string,object>> test= new List<Dictionary<string, object>>()
           //{ 
           // new Dictionary<string, object>(){ { "ID", "1" },{"Name","Test" } },
           // new Dictionary<string, object>(){ { "ID", "2" },{"Name","Mini" } },
           // new Dictionary<string, object>(){ { "ID", "3" },{"Name","Excel" } },
           //};
           
            List<Test> test = new List<Test>()
            {
                new Test(){ ID=1,Name="Test"},
                new Test(){ ID=2,Name="Mini" },
                new Test(){ ID=3,Name="Excel"}
            };
            MiniExcel.SaveAs(path, test);
        }

        public class Test
        {
            [ExcelColumn(Name = "ID", Width = 20)]
            public int ID { get; set; }
            [ExcelColumn(Name = "Name", Width = 20)]
            public string Name { get; set; }
        }

多表创建

        private void Button_Click_TestMini(object sender, RoutedEventArgs e)
        {
            var path = Path.Combine(Directory.GetCurrentDirectory(), "testExcel.xlsx");
            var Books = new[]
            {
                new {ID=1,Name="红楼梦" },
                new {ID=2,Name="三国演义" },
                new {ID=3,Name="西游记" },
                new {ID=4,Name="水浒传" },
            };
            var users = new List<Test>()
            {
                new Test(){ ID=1,Name="Mini" },
                new Test(){ ID=2,Name="Test"}
            };
            var sheets = new Dictionary<string, object>()
            {
                { "Sheet1",Books },
                { "Sheet2",users}
            };
            MiniExcel.SaveAs(path, sheets,excelType:ExcelType.XLSX);
        }

        public class Test
        {
            [ExcelColumn(Name = "ID", Width = 20)]
            public int ID { get; set; }
            [ExcelColumn(Name = "Name", Width = 20)]
            public string Name { get; set; }
        }

更改配置

MiniExcel.SaveAs(path, sheets,configuration:new OpenXmlConfiguration() 
{
    TableStyles=TableStyles.None,//表格样式选择
    AutoFilter=false,//自动筛选
    EnableWriteNullValueCell=false,//是否可写入空值，默认true
});

特性使用

• Name，指定列名称
• Width，指定列宽
• Index，指定第几列
• Ignore，是否忽略该列
• Format，自定义格式

public class Test
{
    [ExcelColumn(Name = "Id", Width = 20,Index =1,Ignore =true)]
    public int ID { get; set; }

    [ExcelColumn(Name = "UserName", Width = 20)]
    public string Name { get; set; }

    [ExcelColumn(Name = "Date", Width = 20,Format ="yyyy/MM/dd HH:mm:ss")]
    public DateTime DateTime { get; set; }= DateTime.Now;
}

CSV尾行新增行

private void Button_Click(object sender, RoutedEventArgs e)
{
    var path = Path.Combine(Directory.GetCurrentDirectory(), "testExcel.csv");
    var Books = new[]
    {
        new {ID=5,Name="WPF深入浅出" },
        new {ID=6,Name="C#高级编程" },
        new {ID=7,Name="重构" },
    };
    MiniExcel.Insert(path, Books);
}

CSV、XLSX互转

private void Button_Click_Convert(object sender, RoutedEventArgs e)
{
    var xlsxPath = Path.Combine(Directory.GetCurrentDirectory(), "testExcel.xlsx");
    var csvPath = Path.Combine(Directory.GetCurrentDirectory(), "testExcel.csv");
    MiniExcel.ConvertXlsxToCsv(xlsxPath, csvPath);
    //MiniExcel.ConvertCsvToXlsx(csvPath, xlsxPath);
}

2、读/导入

简单示例

public class User
{
    [ExcelColumn(Name = "Date", Width = 20, Format = "yyyy/MM/dd HH:mm:ss")]
    public DateTime DateTime { get; set; } = DateTime.Now;

    [ExcelColumn(Name = "Id", Width = 20)]
    public int ID { get; set; }

    [ExcelColumn(Name = "UserName", Width = 20)]
    public string Name { get; set; }
}

private void Button_Click_ReadTest(object sender, RoutedEventArgs e)
{
    var path = Path.Combine(Directory.GetCurrentDirectory(), "testExcel.xlsx");
    var users = MiniExcel.Query<User>(path).ToList();
    var user = users.Where(u => u.ID.Equals(4)).FirstOrDefault();
    if (user != null)
        MessageBox.Show(user.Name);
}

二、NPOI开源框架

添加NuGet包NPOI

简单示例如下：

        private void Button_Click_TestNPOI(object sender, RoutedEventArgs e)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");
            sheet1.CreateRow(0).CreateCell(0).SetCellValue(1);
            sheet1.GetRow(0).CreateCell(1).SetCellValue("NPOI");
            sheet1.CreateRow(1).CreateCell(0).SetCellValue(2);
            sheet1.GetRow(1).CreateCell(1).SetCellValue("Test");
            sheet1.CreateRow(2).CreateCell(0).SetCellValue(3);
            sheet1.GetRow(2).CreateCell(1).SetCellValue("Sheet");

            var path = Path.Combine(Directory.GetCurrentDirectory(), "newExcel.xlsx");
            using (FileStream fs = new FileStream(path, FileMode.Create))
            {
                workbook.Write(fs);
            }
            workbook.Close();
        }