.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;
}
}