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