.NET C# 读写Excel及转换DataTable

.NET C# 读写Excel及转换DataTable

1. 依赖库

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

2. Nuget包与版本

3. ExcelUtil

3.1 Excel sheet 转 DataTable

/// <summary>
/// Excel sheet 转 DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="sheetName">Sheet名称</param>
/// <returns>结果DataTable</returns>
public static DataTable? FromExcel(string excelFilePath, string sheetName)
{DataTable dataTable = new DataTable(sheetName);IWorkbook wb = GetWorkbook(excelFilePath);if (wb == null){return null;}ISheet ws = wb.GetSheet(sheetName);if (ws == null){return null;}if (ws.LastRowNum < 1){return dataTable;}int maxColumnNum = 0;int rowNum = ws.LastRowNum + 1;for (int rowIdx = 0; rowIdx < rowNum; rowIdx++){IRow row = ws.GetRow(0);if (row != null && row.LastCellNum > maxColumnNum){maxColumnNum = row.LastCellNum;}}IRow headerRow = ws.GetRow(0);for (int columnIdx = 0; columnIdx < maxColumnNum; columnIdx++){string columnName = string.Empty;if (headerRow != null){ICell cell = headerRow.GetCell(columnIdx);if (cell == null){continue;}columnName = cell.StringCellValue;}if (string.IsNullOrEmpty(columnName)){columnName = $"column_{columnIdx + 1}";}string columnTempName = columnName;int tag = 0;while (dataTable.Columns.Contains(columnTempName)){columnTempName = columnName + $"_{++tag}";}dataTable.Columns.Add(columnTempName);}if (rowNum <= 1){return dataTable;}for (int rowIdx = 1; rowIdx < rowNum; rowIdx++){DataRow dataRow = dataTable.NewRow();for (int columnIdx = 0; columnIdx < maxColumnNum; columnIdx++){IRow row = ws.GetRow(rowIdx);if (row == null){continue;}ICell cell = row.GetCell(columnIdx);if (cell == null){continue;}dataRow[columnIdx] = GetCellValue(cell);}dataTable.Rows.Add(dataRow);}return dataTable;
}

3.2 Excel sheet 转 DataSet

/// <summary>
/// Excel sheet 转 DataSet
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <returns>结果DataSet</returns>
public static DataSet? FromExcel(string excelFilePath)
{IWorkbook wb = GetWorkbook(excelFilePath);if (wb == null){return null;}DataSet ds = new DataSet();for (int i = 0; i < wb.NumberOfSheets; i++){ISheet sheet = wb.GetSheetAt(i);DataTable? dt = FromExcel(excelFilePath, sheet.SheetName);if (dt == null){continue;}ds.Tables.Add(dt);}return ds;
}

3.3 DataTable 转 Excel sheet

/// <summary>
/// DataTable 转 Excel sheet
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="dataTable">DataTable实例</param>
/// <param name="sheetName">Sheet名称</param>
/// <returns>转换结果</returns>
public static bool ToExcel(string excelFilePath, DataTable dataTable, string sheetName = "")
{IWorkbook wb = GetWorkbook(excelFilePath);if (wb == null){return false;}if (string.IsNullOrEmpty(sheetName)){if (string.IsNullOrEmpty(dataTable.TableName)){sheetName = "Sheet";}else{sheetName = dataTable.TableName;}}int numberOfSheets = wb.NumberOfSheets;if (numberOfSheets > 0){List<string> sheetNames = new List<string>();for (int sheetIdx = 0; sheetIdx < numberOfSheets; sheetIdx++){sheetNames.Add(wb.GetSheetName(sheetIdx).ToLower());}int tag = 0;string sheetTempName = sheetName;while (sheetNames.Contains(sheetTempName.ToLower())){sheetTempName = $"{sheetName}_{++tag}";}sheetName = sheetTempName;}ISheet ws = wb.CreateSheet(sheetName);IRow headerRow = ws.CreateRow(0);for (int columnIdx = 0; columnIdx < dataTable.Columns.Count; columnIdx++){string columnName = dataTable.Columns[columnIdx].ColumnName;ICell newCell = headerRow.CreateCell(columnIdx);newCell.SetCellValue(columnName);}for (int rowIdx = 0; rowIdx < dataTable.Rows.Count; rowIdx++){IRow row = ws.CreateRow(rowIdx + 1);for (int columnIdx = 0; columnIdx < dataTable.Columns.Count; columnIdx++){object value = dataTable.Rows[rowIdx][columnIdx];string cellStringValue = value?.ToString() ?? string.Empty;ICell cell = row.CreateCell(columnIdx);cell.SetCellValue(cellStringValue);}}FileStream fs = File.OpenWrite(excelFilePath);try{wb.Write(fs, false);return true;}catch (Exception ex){// 异常处理...return false;}finally {try { fs?.Close(); } catch { } }
}

3.4 DataSet 转 Excel

/// <summary>
/// DataSet 转 Excel
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="dataSet">DataSet实例</param>
/// <returns>转换结果</returns>
public static bool ToExcel(string excelFilePath, DataSet dataSet)
{bool allSuccess = true;foreach (DataTable dataTable in dataSet.Tables){bool success = ToExcel(excelFilePath, dataTable);if (!success){allSuccess = false;}}return allSuccess;
}

3.5 私有方法

private static IWorkbook GetWorkbook(string excelFilePath)
{string extension = Path.GetExtension(excelFilePath);IWorkbook wb = null;FileStream fs = null;try{if (!File.Exists(excelFilePath)){if (extension == ".xlsx" || extension == "xlsx")wb = new XSSFWorkbook();else if (extension == ".xls" || extension == "xls")wb = new HSSFWorkbook();else{AppLogger.Instance.Error($"错误文件类型{extension}！");return null;}}else{fs = File.Open(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);if (extension == ".xlsx" || extension == "xlsx")wb = new XSSFWorkbook(fs);else if (extension == ".xls" || extension == "xls")wb = new HSSFWorkbook(fs);else{AppLogger.Instance.Error($"错误文件类型{extension}！");return null;}}return wb;}catch (Exception ex){AppLogger.Instance.Error("读取Excel文件失败！", ex);return null;}finally { if (fs != null) try { fs.Close(); } catch { } }
}
static object? GetCellValue(ICell cell)
{if (cell == null)return null;switch (cell.CellType){case CellType.Blank: //BLANK:  return null;case CellType.Boolean: //BOOLEAN:  return cell.BooleanCellValue;case CellType.Numeric: //NUMERIC:  return cell.NumericCellValue;case CellType.String: //STRING:  return cell.StringCellValue;case CellType.Error: //ERROR:  return cell.ErrorCellValue;case CellType.Formula: //FORMULA:  default:return "=" + cell.CellFormula;}
}

cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return “=” + cell.CellFormula;
}
}