開源 - Ideal庫 - Excel幫助類，ExcelHelper實現（五）

IT规划师發表於2024-12-06
IdeaExcel

書接上回，我們繼續來聊聊ExcelHelper的具體實現。

01、讀取Excel到DataSet單元測試

在上一章我們主要講解了讀取Excel到DataSet的三個過載方法具體實現，還沒來得及做單元測試，因此我們首先對這三個方法做個單元測試。具體程式碼如下：

[Fact]
public void Read_FileName_DataSet()
{
    //讀取所有工作簿
    var dataSet = ExcelHelper.Read("Read.xlsx");
    Assert.Equal(3, dataSet.Tables.Count);
    var table1 = dataSet.Tables[0];
    Assert.Equal("Sheet1", table1.TableName);
    Assert.Equal("A", table1.Rows[0][0]);
    Assert.Equal("B", table1.Rows[0][1]);
    Assert.Equal("1", table1.Rows[0][2]);
    Assert.Equal("C", table1.Rows[1][0]);
    Assert.Equal("D", table1.Rows[1][1]);
    Assert.Equal("2", table1.Rows[1][2]);

    //讀取所有工作簿，並且首行資料作為表頭
    dataSet = ExcelHelper.Read("Read.xlsx", true);
    Assert.Equal(3, dataSet.Tables.Count);
    table1 = dataSet.Tables[1];
    var columus = table1.Columns;
    Assert.Equal("Sheet2", table1.TableName);
    Assert.Equal("E", columus[0].ColumnName);
    Assert.Equal("F", columus[1].ColumnName);
    Assert.Equal("3", columus[2].ColumnName);
    Assert.Equal("G", table1.Rows[0][0]);
    Assert.Equal("H", table1.Rows[0][1]);
    Assert.Equal("4", table1.Rows[0][2]);

    //根據工作簿名稱sheetName讀取指定工作簿
    dataSet = ExcelHelper.Read("Read.xlsx", true, "Sheet2");
    Assert.Single(dataSet.Tables);
    Assert.Equal("Sheet2", dataSet.Tables[0].TableName);

    //透過工作簿名稱sheetName讀取不存在的工作簿
    dataSet = ExcelHelper.Read("Read.xlsx", true, "Sheet99");
    Assert.Empty(dataSet.Tables);

    //同時指定sheetName和sheetNumber優先使用sheetName
    dataSet = ExcelHelper.Read("Read.xlsx", true, "Sheet1", 2);
    Assert.Single(dataSet.Tables);
    Assert.Equal("Sheet1", dataSet.Tables[0].TableName);
    //透過工作簿編號sheetNumber讀取不存在的工作簿

    dataSet = ExcelHelper.Read("Read.xlsx", true, null, 99);
    Assert.Empty(dataSet.Tables);

    //透過工作簿編號sheetNumber讀取指定工作簿
    dataSet = ExcelHelper.Read("Read.xlsx", true, null, 1);
    Assert.Single(dataSet.Tables);
    Assert.Equal("Sheet1", dataSet.Tables[0].TableName);
}
# ***02***、根據檔案路徑讀取Excel到物件集合```

在上一章中我們實現了Excel與DataSet相互轉換，而在前面TableHelper實現章節中我們已經實現了物件集合與表格DataTable的相互轉換，因此我們只要把這兩者結合起來就可以實現Excel與物件集合的相互轉換。

因為Excel中有多個工作簿Sheet，而每一個工作簿Sheet代表一個表格DataTable，一個表格DataTable關聯一個物件集合，因此我們約定本方法必須指定一個工作簿Sheet用來轉換物件集合，如果沒有指定則預設讀取第一個工作簿Sheet。

而該方法透過檔案完全路徑讀取到Excel檔案流後，呼叫具體實現檔案流處理過載方法，具體程式碼如下：

```csharp
//根據檔案路徑讀取Excel到物件集合
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則預設讀取第一個工作簿Sheet
public static IEnumerable<T> Read<T>(string path, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
{
    using var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
    return Read<T>(stream, IsXlsxFile(path), isFirstRowAsColumnName, sheetName, sheetNumber);
}

03、根據檔案流、檔名讀取Excel到物件集合

在有些場景下，我們直接得到的就是Excel檔案流，因此更通用的處理方式就是處理ExceL檔案流，因為無論如何最終我們都是要拿到Excel檔案流的。

該方法也是一個過載方法，為了方便哪些上傳檔案後，有檔案流，有檔名，但是不想自己處理檔案字尾格式的，提供一個便捷方法，因此該方法會透過檔名識別出檔案具體字尾格式，再呼叫下一個過載方法，具體實現如下：

//根據檔案流讀取Excel到物件集合
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則預設讀取第一個工作簿Sheet
public static IEnumerable<T> Read<T>(Stream stream, string fileName, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
{
    return Read<T>(stream, IsXlsxFile(fileName), isFirstRowAsColumnName, sheetName, sheetNumber);
}

04、根據檔案流、檔案字尾讀取Excel到物件集合

該方法是上面兩個方法的最終實現，具體實現分為兩步：

（1）讀取指定工作簿Sheet到DataSet中；

（2）把DataSet中第一個表格DataTable轉換為物件集合；

而這兩步都是呼叫之前實現好的方法，具體程式碼如下：

//根據檔案流讀取Excel到物件集合
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則預設讀取第一個工作簿Sheet
public static IEnumerable<T> Read<T>(Stream stream, bool isXlsx, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
{
    //讀取指定工作簿Sheet至DataSet
    var dataSet = CreateDataSetWithStreamOfSheet(stream, isXlsx, isFirstRowAsColumnName, sheetName, sheetNumber ?? 1);
    if (dataSet == null || dataSet.Tables.Count == 0)
    {
        return [];
    }

    //DataTable轉物件集合
    return TableHelper.ToModels<T>(dataSet.Tables[0]);
}

下面我們針對上面三個方法做個簡單的單元測試，程式碼如下：

public class Student
{
    public string A { get; set; }
    [Description("B")]
    public string Name { get; set; }
    [Description("1")]
    public DateTime Age { get; set; }
}

[Fact]
public void Read_FileName_T()
{
    //表格資料格式無法轉為物件資料型別，則拋異常
    Assert.Throws<FormatException>(() => ExcelHelper.Read<Student>("Read.xlsx", true, "Sheet1"));

    //表格成功轉為物件集合
    var models = ExcelHelper.Read<Student>("Read.xlsx", true, "Sheet3");
    Assert.Single(models);
    var model = models.First();
    Assert.Equal("C", model.A);
    Assert.Equal("D", model.Name);
    Assert.Equal(new DateTime(2024, 11, 29), model.Age);
}

05、把表格陣列寫入Excel檔案流

該方法是先把表格陣列生成Excel的IWorkbook，然後再寫入記憶體流MemoryStream。

而表格陣列轉換為IWorkbook也很簡單，在IWorkbook中建立工作簿Sheet，然後把每個表格資料填充至相應的工作簿Sheet中即可，具體程式碼如下：

//把表格陣列寫入Excel檔案流
public static MemoryStream Write(DataTable[] dataTables, bool isXlsx, bool isColumnNameAsData)
{
    //表格陣列寫入Excel物件
    using var workbook = CreateWorkbook(dataTables, isXlsx, isColumnNameAsData);
    var stream = new MemoryStream();
    workbook.Write(stream, true);
    stream.Flush();
    return stream;
}

//表格陣列轉為IWorkbook
private static IWorkbook CreateWorkbook(DataTable[] dataTables, bool isXlsx, bool isColumnNameAsData)
{
    //根據Excel檔案字尾建立IWorkbook
    var workbook = CreateWorkbook(isXlsx);
    foreach (var dt in dataTables)
    {
        //根據表格填充Sheet
        FillSheetByDataTable(workbook, dt, isColumnNameAsData);
    }

    return workbook;
}

而根據表格填充工作簿Sheet實現也非常簡單，只需遍歷表格中每個單元格，把其值填充至對應工作簿Sheet中相同的位置即可，當然其中表格列名是否要作為資料，需要單獨處理，具體程式碼如下：

//根據表格填充工作簿Sheet
private static void FillSheetByDataTable(IWorkbook workbook, DataTable dataTable, bool isColumnNameAsData)
{
    var sheet = string.IsNullOrWhiteSpace(dataTable.TableName) ? workbook.CreateSheet() : workbook.CreateSheet(dataTable.TableName);
    if (isColumnNameAsData)
    {
        //把列名加入資料第一行
        var dataRow = sheet.CreateRow(0);
        foreach (DataColumn column in dataTable.Columns)
        {
            dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
        }
    }

    //迴圈處理表格的所有行資料
    for (var i = 0; i < dataTable.Rows.Count; i++)
    {
        var dataRow = sheet.CreateRow(i + (isColumnNameAsData ? 1 : 0));
        for (var j = 0; j < dataTable.Columns.Count; j++)
        {
            dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
        }
    }
}

06、把表格陣列寫入Excel檔案

該方法需要注意的是對於Excel檔案路徑的處理，如果給定的Excel檔案路徑不存在，則本方法會自動建立相應的資料夾，如果給定的Excel檔案路徑中不包括檔名稱，則本方法會自動根據當前時間+4位隨機數的方式+.xlsx的命名方式自動生成檔名。

處理好這些則只需要呼叫根據表格陣列生成Excel物件方法，最後寫入Excel檔案中，具體程式碼如下：

//把表格陣列寫入Excel檔案
public static void Write(DataTable[] dataTables, string path, bool isColumnNameAsData)
{
    //檢查資料夾是否存在，不存在則建立
    var directoryName = Path.GetDirectoryName(path);
    if (!string.IsNullOrEmpty(directoryName) && !Directory.Exists(directoryName))
    {
        Directory.CreateDirectory(directoryName);
    }

    //檢查是否指定檔名，沒有則預設以“時間+隨機數.xlsx”作為檔名
    var fileName = Path.GetFileName(path);
    if (string.IsNullOrEmpty(fileName))
    {
        directoryName = Path.GetFullPath(path);
        fileName = DateTime.Now.ToString("yyyyMMdd-hhmmss-") + new Random().Next(0000, 9999).ToString("D4") + ".xlsx";
        path = Path.Combine(directoryName, fileName);
    }

    //表格陣列寫入Excel物件
    using var workbook = CreateWorkbook(dataTables, IsXlsxFile(path), isColumnNameAsData);
    using var fs = new FileStream(path, FileMode.Create, FileAccess.Write);
    workbook.Write(fs, true);
}

下面我們對上面兩個寫入方法進行詳細的單元測試，具體如下：

[Fact]
public void Write_Table()
{
    var table = TableHelper.Create<Student>();
    var row1 = table.NewRow();
    row1[0] = "Id-11";
    row1[1] = "名稱-12";
    row1[2] = new DateTime(2024, 11, 28);
    table.Rows.Add(row1);

    var row2 = table.NewRow();
    row2[0] = "Id-21";
    row2[1] = "名稱-22";
    row2[2] = new DateTime(2024, 11, 29);
    table.Rows.Add(row2);

    var message = "The column name of the table cannot be mapped to an object property, and the conversion cannot be completed.";

    //把表格寫入Excel，並且列名不作為資料行，結果重新讀取Excel無法和物件完成轉換
    ExcelHelper.Write([table], "Write.xls", false);
    var exception1 = Assert.Throws<NotSupportedException>(() => ExcelHelper.Read<Student>("Write.xls", true, "Sheet0"));
    Assert.Equal(message, exception1.Message);

    //把表格寫入Excel，並且列名作為資料行，但是重新讀取Excel時第一行沒有作為列名，結果還是無法和物件完成轉換
    ExcelHelper.Write([table], "Write.xls", true);
    var exception2 = Assert.Throws<NotSupportedException>(() => ExcelHelper.Read<Student>("Write.xls", false, "Sheet0"));
    Assert.Equal(message, exception2.Message);

    //重新讀取Excel時第一行作為列名
    var models = ExcelHelper.Read<Student>("Write.xls", true, "Sheet0");
    Assert.Equal(2, models.Count());
    var model = models.First();
    Assert.Equal("Id-11", model.A);
    Assert.Equal("名稱-12", model.Name);
    Assert.Equal(new DateTime(2024, 11, 28), model.Age);
    File.Delete("Write.xls");
}

07、把物件集合寫入Excel檔案流或Excel檔案

到這裡這兩個方法就很好實現了，因為這兩個方法需要的所有基礎方法都已經實現，核心思路就是先把物件集合轉換為表格DataTable，然後再透過呼叫相關把表格陣列寫入Excel的擴充套件方法實現即可，具體程式碼如下：

//把物件集合寫入Excel檔案流
public static MemoryStream Write<T>(IEnumerable<T> models, bool isXlsx, bool isColumnNameAsData, string? sheetName = null)
{
    //物件集合轉為表格
    var table = TableHelper.ToDataTable<T>(models, sheetName);
    //表格陣列寫入Excel檔案流
    return Write([table], isXlsx, isColumnNameAsData);
}

//把物件集合寫入Excel檔案
public static void Write<T>(IEnumerable<T> models, string path, bool isColumnNameAsData, string? sheetName = null)
{
    //物件集合轉為表格
    var table = TableHelper.ToDataTable<T>(models, sheetName);
    //表格陣列寫入Excel檔案
    Write([table], path, isColumnNameAsData);
}

最後我們再進行一次詳細的單元測試，程式碼如下：

[Fact]
public void Write_T()
{
    //驗證正常情況
    var students = new List<Student>();
    var student1 = new Student
    {
        A = "Id-11",
        Name = "名稱-12",
        Age = new DateTime(2024, 11, 28)
    };
    students.Add(student1);

    var student2 = new Student
    {
        A = "Id-21",
        Name = "名稱-22",
        Age = new DateTime(2024, 11, 29)
    };
    students.Add(student2);

    var message = "The column name of the table cannot be mapped to an object property, and the conversion cannot be completed.";

    //把物件集合寫入Excel，並且列名不作為資料行，結果重新讀取Excel無法和物件完成轉換
    ExcelHelper.Write<Student>(students, "Write_T.xls", false);
    var exception1 = Assert.Throws<NotSupportedException>(() => ExcelHelper.Read<Student>("Write_T.xls", true, "Sheet0"));
    Assert.Equal(message, exception1.Message);

    //把物件集合寫入Excel，並且列名作為資料行，但是重新讀取Excel時第一行沒有作為列名，結果還是無法和物件完成轉換
    ExcelHelper.Write<Student>(students, "Write_T.xls", true);
    var exception2 = Assert.Throws<NotSupportedException>(() => ExcelHelper.Read<Student>("Write_T.xls", false, "Sheet0"));
    Assert.Equal(message, exception2.Message);

    //重新讀取Excel時第一行作為列名
    var models = ExcelHelper.Read<Student>("Write_T.xls", true, "Sheet0");
    Assert.Equal(2, models.Count());
    var model = models.First();
    Assert.Equal("Id-11", model.A);
    Assert.Equal("名稱-12", model.Name);
    Assert.Equal(new DateTime(2024, 11, 28), model.Age);
    File.Delete("Write_T.xls");
}

到這裡我們整個Excel封裝就完成了，相信透過物件集合完成Excel匯入匯出能滿足大多數業務開發需求。當然如果有更復雜的業務需求，還需要我們自己去研究相應的第三方庫。

注：測試方法程式碼以及示例原始碼都已經上傳至程式碼庫，有興趣的可以看看。https://gitee.com/hugogoos/Ideal

相關文章