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