書接上回，前面章節已經實現Excel幫助類的第一步TableHeper的物件集合與DataTable相互轉換功能，今天實現進入其第二步的核心功能ExcelHelper實現。
01、介面設計
下面我們根據第一章中講解的核心設計思路，先進行介面設計，確定ExcelHelper需要哪些介面即可滿足我們的要求，然後再一個一個介面實現即可。
先簡單回顧一下核心設計思路，主要涉及兩類操作：讀和寫，兩種轉換：DataTable與Excel轉換和物件集合與Excel轉換。
下面先看看設計的所有介面：
//根據檔案路徑讀取Excel到DataSet
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則讀取所有工作簿Sheet
public static DataSet Read(string path, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null);
//根據檔案流讀取Excel到DataSet
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則讀取所有工作簿Sheet
public static DataSet Read(Stream stream, string fileName, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null);
//根據檔案流讀取Excel到DataSet
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則讀取所有工作簿Sheet
public static DataSet Read(Stream stream, bool isXlsx, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null);
//根據檔案流讀取Excel到物件集合
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則預設讀取第一個工作簿Sheet
public static IEnumerable<T> Read<T>(string path, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null);
//根據檔案流讀取Excel到物件集合
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則預設讀取第一個工作簿Sheet
public static IEnumerable<T> Read<T>(Stream stream, string fileName, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null);
//根據檔案流讀取Excel到物件集合
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則預設讀取第一個工作簿Sheet
public static IEnumerable<T> Read<T>(Stream stream, bool isXlsx, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null);
//把表格陣列寫入Excel檔案流
public static MemoryStream Write(DataTable[] dataTables, bool isXlsx, bool isColumnNameAsData);
//把表格陣列寫入Excel檔案
public static void Write(DataTable[] dataTables, string path, bool isColumnNameAsData);
//把物件集合寫入Excel檔案流
public static MemoryStream Write<T>(IEnumerable<T> models, bool isXlsx, bool isColumnNameAsData, string? sheetName = null);
//把物件集合寫入Excel檔案
public static void Write<T>(IEnumerable<T> models, string path, bool isColumnNameAsData, string? sheetName = null);
02、根據檔案路徑讀取Excel到DataSet
該方法是透過Excel完全路徑直接讀取Excel檔案，因此我們首先讀取到檔案流，然後再呼叫具體處理檔案流實現方法。
因為Excel中工作簿Sheet正好對應DataSet中表格DataTable，因此在不指定讀取某個工作簿Sheet的情況下，預設是讀取Excel中所有工作簿Sheet。
指定工作簿方式也很簡單，只要傳引數指定工作簿名稱sheetName或者工作簿編號sheetNumber即可，提供兩個引數是考慮到可能名字不好記，但是第幾個工作簿Sheet會比較好記，也因此工作簿編號sheetNumber是從1開始。兩者會優先處理工作簿名稱sheetName。
因為表格DataTable是有列名的，透過這個列名我們可以把它和物件屬性關聯上，最後實現相互對映轉換，而工作簿Sheet則沒有這個概念，因此我們要想最終實現物件和工作簿Sheet的相互轉換，就需要人為指定這樣的資料。
通常的做法是以工作簿Sheet中第一行資料作為表格DataTable列名，因此我們在介面中設計了這個引數用來指定是否需要把第一行資料作為表格列名。
具體程式碼實現如下：
//根據檔案路徑讀取Excel到DataSet
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則讀取所有工作簿Sheet
public static DataSet Read(string path, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
{
using var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
return Read(stream, IsXlsxFile(path), isFirstRowAsColumnName, sheetName, sheetNumber);
}
03、根據檔案流、檔名讀取Excel到DataSet
在有些場景下，不需要我們直接讀取Excel檔案，而是直接給一個Excel檔案流。比如說檔案上傳，前端上傳檔案後，後端接收到的就是一個檔案流。
同時該方法還需要傳一個檔名的引數，這是因為我們Excel有兩種字尾格式即“.xls”和“.xlsx”，而兩種格式處理方式又不相同，因此我們需要透過名字來說識別Excel檔案流的具體格式，當然如果呼叫方法時已經明確知道檔案流是什麼格式，也可以直接呼叫下一個過載方法。
其他引數解釋上節以及詳細講解了，實現程式碼如下：
//根據檔案流讀取Excel到DataSet
//指定sheetName，sheetNumber則讀取相應工作簿Sheet
//如果不指定則讀取所有工作簿Sheet
public static DataSet Read(Stream stream, string fileName, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
{
return Read(stream, IsXlsxFile(fileName), isFirstRowAsColumnName, sheetName, sheetNumber);
}
04、根據檔案流、檔案字尾讀取Excel到DataSet
該方法是上面兩個方法的最終實現，該方法首先會識別讀取所有工作簿Sheet還是讀取指定工作簿Sheet，然後調不同的方法。而兩者差別也這是讀一個還是讀多個工作簿Sheet的差別，具體程式碼如下：
//根據檔案流讀取Excel到DataSet
public static DataSet Read(Stream stream, bool isXlsx, bool isFirstRowAsColumnName = false, string? sheetName = null, int? sheetNumber = null)
{
if (sheetName == null && sheetNumber == null)
{
//讀取所有工作簿Sheet至DataSet
return CreateDataSetWithStreamOfSheets(stream, isXlsx, isFirstRowAsColumnName);
}
//讀取指定工作簿Sheet至DataSet
return CreateDataSetWithStreamOfSheet(stream, isXlsx, isFirstRowAsColumnName, sheetName, sheetNumber ?? 1);
}
//讀取所有工作簿Sheet至DataSet
private static DataSet CreateDataSetWithStreamOfSheets(Stream stream, bool isXlsx, bool isFirstRowAsColumnName)
{
//根據Excel檔案字尾建立IWorkbook
using var workbook = CreateWorkbook(isXlsx, stream);
//根據Excel檔案字尾建立公式求值器
var evaluator = CreateFormulaEvaluator(isXlsx, workbook);
var dataSet = new DataSet();
for (var i = 0; i < workbook.NumberOfSheets; i++)
{
//獲取工作簿Sheet
var sheet = workbook.GetSheetAt(i);
//透過工作簿Sheet建立表格
var table = CreateDataTableBySheet(sheet, evaluator, isFirstRowAsColumnName);
dataSet.Tables.Add(table);
}
return dataSet;
}
//讀取指定工作簿Sheet至DataSet
private static DataSet CreateDataSetWithStreamOfSheet(Stream stream, bool isXlsx, bool isFirstRowAsColumnName, string? sheetName = null, int sheetNumber = 1)
{
//把工作簿sheet編號轉為索引
var sheetIndex = sheetNumber - 1;
var dataSet = new DataSet();
if (string.IsNullOrWhiteSpace(sheetName) && sheetIndex < 0)
{
//工作簿sheet索引非法則返回
return dataSet;
}
//根據Excel檔案字尾建立IWorkbook
using var workbook = CreateWorkbook(isXlsx, stream);
if (string.IsNullOrWhiteSpace(sheetName) && sheetIndex >= workbook.NumberOfSheets)
{
//工作簿sheet索引非法則返回
return dataSet;
}
//根據Excel檔案字尾建立公式求值器
var evaluator = CreateFormulaEvaluator(isXlsx, workbook);
//優先透過工作簿名稱獲取工作簿sheet
var sheet = !string.IsNullOrWhiteSpace(sheetName) ? workbook.GetSheet(sheetName) : workbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
//透過工作簿sheet建立表格
var table = CreateDataTableBySheet(sheet, evaluator, isFirstRowAsColumnName);
dataSet.Tables.Add(table);
}
return dataSet;
}
透過上圖實現工作簿Sheet轉換DataSet過程，可以發現大致分為三步：
第一步首先根據檔案格式以及檔案流獲取IWorkbook；
第二步再透過檔案格式以及IWorkbook獲取公式求值器；
第三步再實現把工作簿Sheet轉換為表格DataTable；
我們一起看看這三個程式碼實現：
//根據Excel檔案字尾建立IWorkbook
private static IWorkbook CreateWorkbook(bool isXlsx, Stream? stream = null)
{
if (stream == null)
{
return isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
}
return isXlsx ? new XSSFWorkbook(stream) : new HSSFWorkbook(stream);
}
//根據Excel檔案字尾建立公式求值器
private static IFormulaEvaluator CreateFormulaEvaluator(bool isXlsx, IWorkbook workbook)
{
return isXlsx ? new XSSFFormulaEvaluator(workbook) : new HSSFFormulaEvaluator(workbook);
}
//工作簿Sheet轉換為表格DataTable
private static DataTable CreateDataTableBySheet(ISheet sheet, IFormulaEvaluator evaluator, bool isFirstRowAsColumnName)
{
var dataTable = new DataTable(sheet.SheetName);
//獲取Sheet中最大的列數，並以此數為新的表格列數
var maxColumnNumber = GetMaxColumnNumber(sheet);
if (isFirstRowAsColumnName)
{
//如果第一行資料作為表頭，則先獲取第一行資料
var firstRow = sheet.GetRow(sheet.FirstRowNum);
for (var i = 0; i < maxColumnNumber; i++)
{
//嘗試讀取第一行每一個單元格資料，有值則作為列名，否則忽略
string? columnName = null;
var cell = firstRow?.GetCell(i);
if (cell != null)
{
cell.SetCellType(CellType.String);
if (cell.StringCellValue != null)
{
columnName = cell.StringCellValue;
}
}
dataTable.Columns.Add(columnName);
}
}
else
{
for (var i = 0; i < maxColumnNumber; i++)
{
dataTable.Columns.Add();
}
}
//迴圈處理有效行資料
for (var i = isFirstRowAsColumnName ? sheet.FirstRowNum + 1 : sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
var newRow = dataTable.NewRow();
//透過工作簿sheet行資料填充表格新行資料
FillDataRowBySheetRow(row, evaluator, newRow);
//檢查每單元格是否都有值
var isNullRow = true;
for (var j = 0; j < maxColumnNumber; j++)
{
isNullRow = isNullRow && newRow.IsNull(j);
}
if (!isNullRow)
{
dataTable.Rows.Add(newRow);
}
}
return dataTable;
}
在實現工作簿Sheet轉換為表格DataTable過程中，大致可以分為兩步：
第一步求出工作簿Sheet中所有有效行中最寬的列編號，並以此為列數建立表格；
第二步把工作簿Sheet中所有有效行資料填充至表格中；
下面我們看看具體實現程式碼：
//獲取工作簿Sheet中最大的列數
private static int GetMaxColumnNumber(ISheet sheet)
{
var maxColumnNumber = 0;
//在有效的行資料中
for (var i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
//找到最大的列編號
if (row != null && row.LastCellNum > maxColumnNumber)
{
maxColumnNumber = row.LastCellNum;
}
}
return maxColumnNumber;
}
//透過工作簿sheet行資料填充表格行資料
private static void FillDataRowBySheetRow(IRow row, IFormulaEvaluator evaluator, DataRow dataRow)
{
if (row == null)
{
return;
}
for (var j = 0; j < dataRow.Table.Columns.Count; j++)
{
var cell = row.GetCell(j);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = DBNull.Value;
break;
case CellType.Boolean:
dataRow[j] = cell.BooleanCellValue;
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
dataRow[j] = cell.DateCellValue;
}
else
{
dataRow[j] = cell.NumericCellValue;
}
break;
case CellType.String:
dataRow[j] = !string.IsNullOrWhiteSpace(cell.StringCellValue) ? cell.StringCellValue : DBNull.Value;
break;
case CellType.Error:
dataRow[j] = cell.ErrorCellValue;
break;
case CellType.Formula:
dataRow[j] = evaluator.EvaluateInCell(cell).ToString();
break;
default:
throw new NotSupportedException("Unsupported cell type.");
}
}
}
}
注：測試方法程式碼以及示例原始碼都已經上傳至程式碼庫，有興趣的可以看看。https://gitee.com/hugogoos/Ideal