开源 - Ideal库 - Excel帮助类，ExcelHelper实现（五）

魏晓东  金牌会员 | 2024-12-6 02:13:12 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 946|帖子 946|积分 2838

书接上回，我们继续来聊聊ExcelHelper的具体实现。

01、读取Excel到DataSet单元测试

在上一章我们主要讲解了读取Excel到DataSet的三个重载方法具体实现，还没来得及做单元测试，因此我们首先对这三个方法做个单元测试。具体代码如下：
  1. [Fact]
  2. public void Read_FileName_DataSet()
  3. {
  4.     //读取所有工作簿
  5.     var dataSet = ExcelHelper.Read("Read.xlsx");
  6.     Assert.Equal(3, dataSet.Tables.Count);
  7.     var table1 = dataSet.Tables[0];
  8.     Assert.Equal("Sheet1", table1.TableName);
  9.     Assert.Equal("A", table1.Rows[0][0]);
  10.     Assert.Equal("B", table1.Rows[0][1]);
  11.     Assert.Equal("1", table1.Rows[0][2]);
  12.     Assert.Equal("C", table1.Rows[1][0]);
  13.     Assert.Equal("D", table1.Rows[1][1]);
  14.     Assert.Equal("2", table1.Rows[1][2]);
  16.     //读取所有工作簿，并且首行数据作为表头
  17.     dataSet = ExcelHelper.Read("Read.xlsx", true);
  18.     Assert.Equal(3, dataSet.Tables.Count);
  19.     table1 = dataSet.Tables[1];
  20.     var columus = table1.Columns;
  21.     Assert.Equal("Sheet2", table1.TableName);
  22.     Assert.Equal("E", columus[0].ColumnName);
  23.     Assert.Equal("F", columus[1].ColumnName);
  24.     Assert.Equal("3", columus[2].ColumnName);
  25.     Assert.Equal("G", table1.Rows[0][0]);
  26.     Assert.Equal("H", table1.Rows[0][1]);
  27.     Assert.Equal("4", table1.Rows[0][2]);
  29.     //根据工作簿名称sheetName读取指定工作簿
  30.     dataSet = ExcelHelper.Read("Read.xlsx", true, "Sheet2");
  31.     Assert.Single(dataSet.Tables);
  32.     Assert.Equal("Sheet2", dataSet.Tables[0].TableName);
  34.     //通过工作簿名称sheetName读取不存在的工作簿
  35.     dataSet = ExcelHelper.Read("Read.xlsx", true, "Sheet99");
  36.     Assert.Empty(dataSet.Tables);
  38.     //同时指定sheetName和sheetNumber优先使用sheetName
  39.     dataSet = ExcelHelper.Read("Read.xlsx", true, "Sheet1", 2);
  40.     Assert.Single(dataSet.Tables);
  41.     Assert.Equal("Sheet1", dataSet.Tables[0].TableName);
  42.     //通过工作簿编号sheetNumber读取不存在的工作簿
  44.     dataSet = ExcelHelper.Read("Read.xlsx", true, null, 99);
  45.     Assert.Empty(dataSet.Tables);
  47.     //通过工作簿编号sheetNumber读取指定工作簿
  48.     dataSet = ExcelHelper.Read("Read.xlsx", true, null, 1);
  49.     Assert.Single(dataSet.Tables);
  50.     Assert.Equal("Sheet1", dataSet.Tables[0].TableName);
  51. }
  52. # ***02***、根据文件路径读取Excel到对象集合```
  54. 在上一章中我们实现了Excel与DataSet相互转换，而在前面TableHelper实现章节中我们已经实现了对象集合与表格DataTable的相互转换，因此我们只要把这两者结合起来就可以实现Excel与对象集合的相互转换。
  56. 因为Excel中有多个工作簿Sheet，而每一个工作簿Sheet代表一个表格DataTable，一个表格DataTable关联一个对象集合，因此我们约定本方法必须指定一个工作簿Sheet用来转换对象集合，如果没有指定则默认读取第一个工作簿Sheet。
  58. 而该方法通过文件完全路径读取到Excel文件流后，调用具体实现文件流处理重载方法，具体代码如下：
  60. ```csharp
  61. //根据文件路径读取Excel到对象集合
  62. //指定sheetName，sheetNumber则读取相应工作簿Sheet
  63. //如果不指定则默认读取第一个工作簿Sheet
  64. public static IEnumerable<T> Read<T>(string path, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
  65. {
  66.     using var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
  67.     return Read<T>(stream, IsXlsxFile(path), isFirstRowAsColumnName, sheetName, sheetNumber);
  68. }
复制代码
03、根据文件流、文件名读取Excel到对象集合

在有些场景下，我们直接得到的就是Excel文件流，因此更通用的处理方式就是处理ExceL文件流，由于无论怎样终极我们都是要拿到Excel文件流的。
该方法也是一个重载方法，为了方便哪些上传文件后，有文件流，有文件名，但是不想自己处理文件后缀格式的，提供一个便捷方法，因此该方法会通过文件名识别出文件具体后缀格式，再调用下一个重载方法，具体实现如下：
  1. //根据文件流读取Excel到对象集合
  2. //指定sheetName，sheetNumber则读取相应工作簿Sheet
  3. //如果不指定则默认读取第一个工作簿Sheet
  4. public static IEnumerable<T> Read<T>(Stream stream, string fileName, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
  5. {
  6.     return Read<T>(stream, IsXlsxFile(fileName), isFirstRowAsColumnName, sheetName, sheetNumber);
  7. }
复制代码
04、根据文件流、文件后缀读取Excel到对象集合

该方法是上面两个方法的终极实现，具体实现分为两步：
（1）读取指定工作簿Sheet到DataSet中；
（2）把DataSet中第一个表格DataTable转换为对象集合；
而这两步都是调用之前实现好的方法，具体代码如下：
  1. //根据文件流读取Excel到对象集合
  2. //指定sheetName，sheetNumber则读取相应工作簿Sheet
  3. //如果不指定则默认读取第一个工作簿Sheet
  4. public static IEnumerable<T> Read<T>(Stream stream, bool isXlsx, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
  5. {
  6.     //读取指定工作簿Sheet至DataSet
  7.     var dataSet = CreateDataSetWithStreamOfSheet(stream, isXlsx, isFirstRowAsColumnName, sheetName, sheetNumber ?? 1);
  8.     if (dataSet == null || dataSet.Tables.Count == 0)
  9.     {
  10.         return [];
  11.     }
  13.     //DataTable转对象集合
  14.     return TableHelper.ToModels<T>(dataSet.Tables[0]);
  15. }
复制代码
下面我们针对上面三个方法做个简单的单元测试，代码如下：
  1. public class Student
  2. {
  3.     public string A { get; set; }
  4.     [Description("B")]
  5.     public string Name { get; set; }
  6.     [Description("1")]
  7.     public DateTime Age { get; set; }
  8. }
  10. [Fact]
  11. public void Read_FileName_T()
  12. {
  13.     //表格数据格式无法转为对象数据类型，则抛异常
  14.     Assert.Throws<FormatException>(() => ExcelHelper.Read<Student>("Read.xlsx", true, "Sheet1"));
  16.     //表格成功转为对象集合
  17.     var models = ExcelHelper.Read<Student>("Read.xlsx", true, "Sheet3");
  18.     Assert.Single(models);
  19.     var model = models.First();
  20.     Assert.Equal("C", model.A);
  21.     Assert.Equal("D", model.Name);
  22.     Assert.Equal(new DateTime(2024, 11, 29), model.Age);
  23. }
复制代码
05、把表格数组写入Excel文件流

该方法是先把表格数组生成Excel的IWorkbook，然后再写入内存流MemoryStream。
而表格数组转换为IWorkbook也很简单，在IWorkbook中创建工作簿Sheet，然后把每个表格数据添补至相应的工作簿Sheet中即可，具体代码如下：
  1. //把表格数组写入Excel文件流
  2. public static MemoryStream Write(DataTable[] dataTables, bool isXlsx, bool isColumnNameAsData)
  3. {
  4.     //表格数组写入Excel对象
  5.     using var workbook = CreateWorkbook(dataTables, isXlsx, isColumnNameAsData);
  6.     var stream = new MemoryStream();
  7.     workbook.Write(stream, true);
  8.     stream.Flush();
  9.     return stream;
  10. }
  12. //表格数组转为IWorkbook
  13. private static IWorkbook CreateWorkbook(DataTable[] dataTables, bool isXlsx, bool isColumnNameAsData)
  14. {
  15.     //根据Excel文件后缀创建IWorkbook
  16.     var workbook = CreateWorkbook(isXlsx);
  17.     foreach (var dt in dataTables)
  18.     {
  19.         //根据表格填充Sheet
  20.         FillSheetByDataTable(workbook, dt, isColumnNameAsData);
  21.     }
  23.     return workbook;
  24. }
复制代码
而根据表格添补工作簿Sheet实现也非常简单，只需遍历表格中每个单元格，把其值添补至对应工作簿Sheet中雷同的位置即可，当然其中表格列名是否要作为数据，必要单独处理，具体代码如下：
  1. //根据表格填充工作簿Sheet
  2. private static void FillSheetByDataTable(IWorkbook workbook, DataTable dataTable, bool isColumnNameAsData)
  3. {
  4.     var sheet = string.IsNullOrWhiteSpace(dataTable.TableName) ? workbook.CreateSheet() : workbook.CreateSheet(dataTable.TableName);
  5.     if (isColumnNameAsData)
  6.     {
  7.         //把列名加入数据第一行
  8.         var dataRow = sheet.CreateRow(0);
  9.         foreach (DataColumn column in dataTable.Columns)
  10.         {
  11.             dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
  12.         }
  13.     }
  15.     //循环处理表格的所有行数据
  16.     for (var i = 0; i < dataTable.Rows.Count; i++)
  17.     {
  18.         var dataRow = sheet.CreateRow(i + (isColumnNameAsData ? 1 : 0));
  19.         for (var j = 0; j < dataTable.Columns.Count; j++)
  20.         {
  21.             dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
  22.         }
  23.     }
  24. }
复制代码
06、把表格数组写入Excel文件

该方法必要注意的是对于Excel文件路径的处理，如果给定的Excel文件路径不存在，则本方法会主动创建相应的文件夹，如果给定的Excel文件路径中不包罗文件名称，则本方法会主动根据当前时间+4位随机数的方式+.xlsx的命名方式主动生成文件名。
处理好这些则只必要调用根据表格数组生成Excel对象方法，最后写入Excel文件中，具体代码如下：
  1. //把表格数组写入Excel文件
  2. public static void Write(DataTable[] dataTables, string path, bool isColumnNameAsData)
  3. {
  4.     //检查文件夹是否存在，不存在则创建
  5.     var directoryName = Path.GetDirectoryName(path);
  6.     if (!string.IsNullOrEmpty(directoryName) && !Directory.Exists(directoryName))
  7.     {
  8.         Directory.CreateDirectory(directoryName);
  9.     }
  11.     //检查是否指定文件名，没有则默认以“时间+随机数.xlsx”作为文件名
  12.     var fileName = Path.GetFileName(path);
  13.     if (string.IsNullOrEmpty(fileName))
  14.     {
  15.         directoryName = Path.GetFullPath(path);
  16.         fileName = DateTime.Now.ToString("yyyyMMdd-hhmmss-") + new Random().Next(0000, 9999).ToString("D4") + ".xlsx";
  17.         path = Path.Combine(directoryName, fileName);
  18.     }
  20.     //表格数组写入Excel对象
  21.     using var workbook = CreateWorkbook(dataTables, IsXlsxFile(path), isColumnNameAsData);
  22.     using var fs = new FileStream(path, FileMode.Create, FileAccess.Write);
  23.     workbook.Write(fs, true);
  24. }
复制代码
下面我们对上面两个写入方法进行具体的单元测试，具体如下：
  1. [Fact]
  2. public void Write_Table()
  3. {
  4.     var table = TableHelper.Create<Student>();
  5.     var row1 = table.NewRow();
  6.     row1[0] = "Id-11";
  7.     row1[1] = "名称-12";
  8.     row1[2] = new DateTime(2024, 11, 28);
  9.     table.Rows.Add(row1);
  11.     var row2 = table.NewRow();
  12.     row2[0] = "Id-21";
  13.     row2[1] = "名称-22";
  14.     row2[2] = new DateTime(2024, 11, 29);
  15.     table.Rows.Add(row2);
  17.     var message = "The column name of the table cannot be mapped to an object property, and the conversion cannot be completed.";
  19.     //把表格写入Excel，并且列名不作为数据行，结果重新读取Excel无法和对象完成转换
  20.     ExcelHelper.Write([table], "Write.xls", false);
  21.     var exception1 = Assert.Throws<NotSupportedException>(() => ExcelHelper.Read<Student>("Write.xls", true, "Sheet0"));
  22.     Assert.Equal(message, exception1.Message);
  24.     //把表格写入Excel，并且列名作为数据行，但是重新读取Excel时第一行没有作为列名，结果还是无法和对象完成转换
  25.     ExcelHelper.Write([table], "Write.xls", true);
  26.     var exception2 = Assert.Throws<NotSupportedException>(() => ExcelHelper.Read<Student>("Write.xls", false, "Sheet0"));
  27.     Assert.Equal(message, exception2.Message);
  29.     //重新读取Excel时第一行作为列名
  30.     var models = ExcelHelper.Read<Student>("Write.xls", true, "Sheet0");
  31.     Assert.Equal(2, models.Count());
  32.     var model = models.First();
  33.     Assert.Equal("Id-11", model.A);
  34.     Assert.Equal("名称-12", model.Name);
  35.     Assert.Equal(new DateTime(2024, 11, 28), model.Age);
  36.     File.Delete("Write.xls");
  37. }
复制代码
07、把对象集合写入Excel文件流或Excel文件

到这里这两个方法就很好实现了，由于这两个方法必要的所有底子方法都已经实现，焦点思绪就是先把对象集合转换为表格DataTable，然后再通过调用相关把表格数组写入Excel的扩展方法实现即可，具体代码如下：
  1. //把对象集合写入Excel文件流
  2. public static MemoryStream Write<T>(IEnumerable<T> models, bool isXlsx, bool isColumnNameAsData, string? sheetName = null)
  3. {
  4.     //对象集合转为表格
  5.     var table = TableHelper.ToDataTable<T>(models, sheetName);
  6.     //表格数组写入Excel文件流
  7.     return Write([table], isXlsx, isColumnNameAsData);
  8. }
  10. //把对象集合写入Excel文件
  11. public static void Write<T>(IEnumerable<T> models, string path, bool isColumnNameAsData, string? sheetName = null)
  12. {
  13.     //对象集合转为表格
  14.     var table = TableHelper.ToDataTable<T>(models, sheetName);
  15.     //表格数组写入Excel文件
  16.     Write([table], path, isColumnNameAsData);
  17. }
复制代码
最后我们再进行一次具体的单元测试，代码如下：
  1. [Fact]
  2. public void Write_T()
  3. {
  4.     //验证正常情况
  5.     var students = new List<Student>();
  6.     var student1 = new Student
  7.     {
  8.         A = "Id-11",
  9.         Name = "名称-12",
  10.         Age = new DateTime(2024, 11, 28)
  11.     };
  12.     students.Add(student1);
  14.     var student2 = new Student
  15.     {
  16.         A = "Id-21",
  17.         Name = "名称-22",
  18.         Age = new DateTime(2024, 11, 29)
  19.     };
  20.     students.Add(student2);
  22.     var message = "The column name of the table cannot be mapped to an object property, and the conversion cannot be completed.";
  24.     //把对象集合写入Excel，并且列名不作为数据行，结果重新读取Excel无法和对象完成转换
  25.     ExcelHelper.Write<Student>(students, "Write_T.xls", false);
  26.     var exception1 = Assert.Throws<NotSupportedException>(() => ExcelHelper.Read<Student>("Write_T.xls", true, "Sheet0"));
  27.     Assert.Equal(message, exception1.Message);
  29.     //把对象集合写入Excel，并且列名作为数据行，但是重新读取Excel时第一行没有作为列名，结果还是无法和对象完成转换
  30.     ExcelHelper.Write<Student>(students, "Write_T.xls", true);
  31.     var exception2 = Assert.Throws<NotSupportedException>(() => ExcelHelper.Read<Student>("Write_T.xls", false, "Sheet0"));
  32.     Assert.Equal(message, exception2.Message);
  34.     //重新读取Excel时第一行作为列名
  35.     var models = ExcelHelper.Read<Student>("Write_T.xls", true, "Sheet0");
  36.     Assert.Equal(2, models.Count());
  37.     var model = models.First();
  38.     Assert.Equal("Id-11", model.A);
  39.     Assert.Equal("名称-12", model.Name);
  40.     Assert.Equal(new DateTime(2024, 11, 28), model.Age);
  41.     File.Delete("Write_T.xls");
  42. }
复制代码
到这里我们整个Excel封装就完成了，相信通过对象集合完成Excel导入导出能满足大多数业务开发需求。当然如果有更复杂的业务需求，还必要我们自己去研究相应的第三方库。
注：测试方法代码以及示例源码都已经上传至代码库，有兴趣的可以看看。https://gitee.com/hugogoos/Ideal

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

魏晓东

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表