.NET C# 读写Excel及转换DataTable

目录

• .NET C# 读写Excel及转换DataTable
• • 1. 依赖库
  • 2. Nuget包与版本
  • 3. ExcelUtil
  • • 3.1 Excel sheet 转 DataTable
    • 3.2 Excel sheet 转 DataSet
    • 3.3 DataTable 转 Excel sheet
    • 3.4 DataSet 转 Excel
    • 3.5 私有方法

.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

/// 

/// Excel sheet 转 DataTable
/// 
/// Excel文件路径
/// Sheet名称
/// 结果DataTable
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

/// 

/// Excel sheet 转 DataSet
/// 
/// Excel文件路径
/// 结果DataSet
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

/// 

/// DataTable 转 Excel sheet
/// 
/// Excel文件路径
/// DataTable实例
/// Sheet名称
/// 转换结果
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

/// 

/// DataSet 转 Excel
/// 
/// Excel文件路径
/// DataSet实例
/// 转换结果
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;
}
}