C#利用NPOI和OpenXML将Excel文件转换为DataTable

结果与目标

本方案旨在提供一个统一的接口或流程，根据传入的文件路径自动识别后缀名，调用对应的解析逻辑，最终返回包含完整数据的 System.Data.DataTable 对象，便于后续绑定至Grid控件或进行业务逻辑处理。

准备条件

1. 环境依赖：需要安装以下两个常用的Excel解析库：

• NPOI：用于处理 .xls 格式（基于POI移植），需引用 ThoughtSpot.NPOI.dll 等相关依赖。
• OpenXML SDK：用于处理 .xlsx 格式（微软官方格式），通常建议引入 DocumentFormat.OpenXml 或通过Nuget安装封装较好的如 SpreadsheetLight 库，以简化API调用。下文示例采用更轻量且易用的SpreadsheetLight封装思路。

在项目中添加上述DLL引用或安装NuGet包后，确保代码顶部引入相应的命名空间：
using System.IO;
using System.Data;
using NPOI.HSSF.UserModel; (针对NPOI)
using SpreadsheetLight; (针对OpenXML封装)

真实操作顺序

第1步：统一入口调度

目的：根据文件扩展名分发任务，避免在一个方法中混杂过多逻辑。
输入：带有盘符绝对路径的字符串 fileName。
动作：使用 Path.GetExtension() 获取后缀，通过Switch语句区分处理。

private DataTable FileToDataTable(string fileName)
{
    DataTable dt = new DataTable();
    // 获取文件后缀名，例如 .xls 或 .xlsx
    string extendName = Path.GetExtension(fileName);
    
    switch (extendName.ToLower())
    {
        case ".xls":
            dt = XlsToDataTable(fileName);
            break;
        case ".xlsx":
            dt = XlsxToDataTable(fileName);
            break;
        default:
            // 可在此处抛出异常或记录日志，提示不支持该格式
            break;
    }
    return dt;
}

第2步：处理XLS格式（使用NPOI）

目的：解析二进制的Biff8格式Excel文件。
关键参数：HSSFWorkbook 接收文件流；GetSheetAt(0) 获取第一个工作表；GetRow(0) 提取表头。
完成状态：DataTable的列名由第一行决定，数据从第二行开始填充。

private DataTable XlsToDataTable(string fileName)
{
    DataTable dataTable = new DataTable();
    Stream stream = null;
    try
    {
        stream = File.OpenRead(fileName);
        // 创建Workbook对象
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
        // 获取活动工作表（通常为第一个）
        HSSFSheet hssfsheet = (HSSFSheet)hssfworkbook.GetSheetAt(hssfworkbook.ActiveSheetIndex);
        // 获取首行作为列头
        HSSFRow hssfrow = (HSSFRow)hssfsheet.GetRow(0);
        
        int lastCellNum = (int)hssfrow.LastCellNum;
        // 动态创建列
        for (int i = (int)hssfrow.FirstCellNum; i < lastCellNum; i++)
        {
            // 防止空指针，如果单元格为空可用默认名占位
            string colName = hssfrow.GetCell(i)?.ToString() ?? $"Column{i}";
            DataColumn column = new DataColumn(colName);
            dataTable.Columns.Add(column);
        }
        dataTable.TableName = hssfsheet.SheetName;
        
        int lastRowNum = hssfsheet.LastRowNum;
        // 遍历数据行，跳过表头（从索引1开始）
        for (int i = hssfsheet.FirstRowNum + 1; i <= hssfsheet.LastRowNum; i++)
        {
            HSSFRow row = (HSSFRow)hssfsheet.GetRow(i);
            if (row == null) continue;
            DataRow dataRow = dataTable.NewRow();
            for (int j = (int)row.FirstCellNum; j < lastCellNum; j++)
            {
                // 获取单元格值，需注意类型转换
                ICell cell = row.GetCell(j);
                dataRow[j] = cell != null ? cell.ToString() : DBNull.Value;
            }
            dataTable.Rows.Add(dataRow);
        }
    }
    catch (Exception ex)
    {
        // 实际生产中建议记录日志，而非直接弹窗
        Console.WriteLine($"Xls解析错误: {ex.Message}");
    }
    finally
    {
        if (stream != null)
        {
            stream.Close();
        }
    }
    return dataTable;
}

第3步：处理XLSX格式（使用SpreadsheetLight/OpenXML）

目的：解析ZIP结构的Office Open XML电子表格。
关键参数：SLDocument 是核心操作类；GetCellValueAsString() 直接将单元格拉平为字符串，降低类型处理复杂度。
注意：Excel坐标通常从(1,1)开始计算（1-based index），区别于数组的0-based。

public DataTable XlsxToDataTable(string vFilePath)
{
    DataTable dataTable = new DataTable();
    try
    {
        // 加载文档
        using (var doc = new SLDocument(vFilePath))
        {
            dataTable.TableName = doc.GetSheetNames()[0];
            SLWorksheetStatistics statistics = doc.GetWorksheetStatistics();
            
            // 确定范围
            int startCol = statistics.StartColumnIndex; // 通常为1
            int endCol = statistics.EndColumnIndex;
            int startRow = statistics.StartRowIndex;   // 通常为1
            int endRow = statistics.EndRowIndex;

            // 添加列（假设第一行为表头）
            for (int i = startCol; i <= endCol; i++)
            {
                string headerText = doc.GetCellValueAsString(1, i);
                dataTable.Columns.Add(new DataColumn(headerText, typeof(string)));
            }

            // 添加数据行（从第二行开始）
            for (int j = startRow + 1; j <= endRow; j++)
            {
                DataRow dataRow = dataTable.NewRow();
                for (int i = startCol; i <= endCol; i++)
                {
                    // 列索引在DataRow中为0-based，故减1
                    dataRow[i - 1] = doc.GetCellValueAsString(j, i);
                }
                dataTable.Rows.Add(dataRow);
            }
        }
    }
    catch (Exception ex)
    {
        throw new Exception($"Xlsx to DataTable Error: \n{ex.Message}");
    }
    return dataTable;
}

排错与优化建议

• 空单元格处理：在NPOI中，若单元格未定义内容，GetCell() 可能返回null，需做空值判断以免引发NullReferenceException。在OpenXML的封装库中，通常有默认的容错机制返回空字符串。
• 性能考量：对于百万级大数据量的Excel，上述全量加载至内存的方法会导致OOM（内存溢出）。此时应考虑逐行读取（StreamReader模式）或使用数据库导入工具（如SQL Server BCP）。
• 权限问题：确保运行代码的应用程序池用户或服务器进程对Excel文件所在目录具有读取权限。

简要总结

通过结合NPOI和OpenXML相关封装库，可以高效、稳定地完成C#环境下不同类型Excel文件向DataTable的转换。关键在于区分文件后缀并使用正确的解析器，同时处理好表头映射与空值边界情况。以上就是C#把EXCEL数据转换成DataTable的详细内容，更多关于C#文件处理、数据解析的资料请关注本站其它相关文章！