如何解决Excel转DataTable为空 并实现后续转对象列表功能

核心问题诱因 #

• 硬编码工作表名称[Sheet1$]，实际Excel文件的工作表名称多为自定义，导致查询不到内容
• 系统未安装对应位数（32/64位）的Access Database Engine驱动，驱动不匹配会出现无数据也不报错的情况
• Excel文件被Office/WPS等进程锁定，OLEDB无读取权限
• 吞掉异常导致无法定位错误，原代码仅打印异常但不抛出，上层调用无法感知读取失败

修正后的VB.NET代码 #

第一步：连接字符串构建 #

Public Function BuildConnectionString(excelPath As String) As String
    Dim ext As String = System.IO.Path.GetExtension(excelPath).ToLower()
    If ext = ".xlsx" Then
        Return $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelPath};Excel 12.0;HDR=YES;IMEX=1"
    Else
        Return $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={excelPath};Excel 8.0;HDR=YES;IMEX=1"
    End If
End Function

第二步：Excel转DataTable（修复空表问题） #

' 原方法名命名错误，此方法处理Excel而非CSV，已重命名
Private Function ConvertExcelToDataTable(ByVal path As String) As DataTable
    Dim dt As DataTable = New DataTable()
    ' 先校验文件存在性
    If Not System.IO.File.Exists(path) Then
        Throw New System.IO.FileNotFoundException("Excel文件不存在", path)
    End If
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection(BuildConnectionString(path))
        Try
            con.Open()
            ' 动态读取第一个工作表名称，避免硬编码Sheet1
            Dim dtSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)
            Dim firstSheetName As String = dtSchema.Rows(0)("TABLE_NAME").ToString()
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand($"SELECT * FROM [{firstSheetName}]", con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    da.Fill(dt)
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
            Throw ' 抛出异常方便上层定位问题，不要吞错
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using
    Return dt
End Function

第三步：DataTable转对象列表（增加空值判断） #

Private Function ConvertDataTableToISAACServiceList(dt As DataTable, lst As List(Of ISAACServiceExcel)) As List(Of ISAACServiceExcel)
    If dt Is Nothing OrElse dt.Rows.Count = 0 Then
        Return lst
    End If
    For Each row As DataRow In dt.Rows
        Dim AnzahlParse As Double : Double.TryParse(row(NameOf(Anzahl))?.ToString(), AnzahlParse)
        Dim EinzelkostenParse As Double : Double.TryParse(row(NameOf(Einzelkosten))?.ToString(), EinzelkostenParse)
        Dim TotalParse As Double : Double.TryParse(row(NameOf(Total))?.ToString(), TotalParse)
        Dim ISAAC As New ISAACServiceExcel(
            row(NameOf(Leistungscode))?.ToString(), 
            row(NameOf(KostenArt))?.ToString(), 
            row(NameOf(UANR))?.ToString(), 
            row(NameOf(Ueberbegriff))?.ToString(), 
            row(NameOf(Benennung))?.ToString(), 
            AnzahlParse, 
            row(NameOf(Einheit))?.ToString(), 
            EinzelkostenParse, 
            row(NameOf(Summencode))?.ToString(), 
            row(NameOf(AufPos))?.ToString(), 
            row(NameOf(Komponente))?.ToString(), 
            row(NameOf(Projektbeteiligter))?.ToString(), 
            row(NameOf(Chefblattposition))?.ToString(), 
            TotalParse
        )
        lst.Add(ISAAC)
    Next
    Return lst
End Function

C#版本完整实现 #

using System.Data;
using System.Data.OleDb;
using System.IO;

public class ExcelConverter
{
    public string BuildConnectionString(string excelPath)
    {
        string ext = Path.GetExtension(excelPath).ToLower();
        return ext switch
        {
            ".xlsx" => $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelPath};Excel 12.0;HDR=YES;IMEX=1",
            _ => $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={excelPath};Excel 8.0;HDR=YES;IMEX=1"
        };
    }

    public DataTable ConvertExcelToDataTable(string path)
    {
        DataTable dt = new DataTable();
        if (!File.Exists(path))
            throw new FileNotFoundException("Excel文件不存在", path);
        
        using (OleDbConnection con = new OleDbConnection(BuildConnectionString(path)))
        {
            try
            {
                con.Open();
                DataTable dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string firstSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
                using (OleDbCommand cmd = new OleDbCommand($"SELECT * FROM [{firstSheetName}]", con))
                using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                throw;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }
        }
        return dt;
    }

    public List<ISAACServiceExcel> ConvertDataTableToISAACServiceList(DataTable dt, List<ISAACServiceExcel> lst)
    {
        if (dt == null || dt.Rows.Count == 0)
            return lst;
        
        foreach (DataRow row in dt.Rows)
        {
            double.TryParse(row[nameof(Anzahl)]?.ToString(), out double AnzahlParse);
            double.TryParse(row[nameof(Einzelkosten)]?.ToString(), out double EinzelkostenParse);
            double.TryParse(row[nameof(Total)]?.ToString(), out double TotalParse);
            
            lst.Add(new ISAACServiceExcel(
                row[nameof(Leistungscode)]?.ToString(),
                row[nameof(KostenArt)]?.ToString(),
                row[nameof(UANR)]?.ToString(),
                row[nameof(Ueberbegriff)]?.ToString(),
                row[nameof(Benennung)]?.ToString(),
                AnzahlParse,
                row[nameof(Einheit)]?.ToString(),
                EinzelkostenParse,
                row[nameof(Summencode)]?.ToString(),
                row[nameof(AufPos)]?.ToString(),
                row[nameof(Komponente)]?.ToString(),
                row[nameof(Projektbeteiligter)]?.ToString(),
                row[nameof(Chefblattposition)]?.ToString(),
                TotalParse
            ));
        }
        return lst;
    }
}

额外注意事项 #

• 请安装与程序运行位数匹配的Access Database Engine驱动，32位程序装32位驱动，64位程序装64位驱动
• 读取前确认Excel文件未被其他进程打开占用
• 如果Excel第一行不是表头，将连接字符串中的HDR=YES修改为HDR=NO

内容的提问来源于stack exchange，提问作者user17151986