Excel导入SQL时字符串转DateTime类型失败问题求助

问题描述 #

按「Excel文件→DataTable→DataSet→数据库数据表」流程导入数据时，反复遇到字符串转DateTime类型失败的错误：

• 初始代码报错行：Dim Date1 As DateTime = dtrow(1)，错误提示：Conversion from string "2024-04-15" to type 'Date' is not valid
• 改用参数化插入后，报错行变为adapter.Update(ds.Tables(0))，错误提示：Failed to convert parameter value from a String to a DateTime, FormatException: String '2024-04-15' was not recognized as a valid DateTime.

已尝试操作：

• 将Excel列设置为日期类型
• 移除填充DataTable时的.ToString()调用
• 确认数据库列及变量为日期相关类型
  但问题仍未解决。

初始完整代码 #

Dim dt1 As New DataTable
 Dim dt2 As New DataTable

 Dim firstrow As Boolean = True

 For Each row As IXLRow In ws2.Rows.Skip(1)

      If firstrow Then
          For Each cell As IXLCell In row.Cells
              dt1.Columns.Add(cell.Value.ToString)
          Next
          firstrow = False
      End If
      Exit For
  Next

  For Each row As IXLRow In ws2.Rows.Skip(2)
      dt1.Rows.Add()
      Dim i As Integer = 0
      For Each cell As IXLCell In row.Cells()
          dt1.Rows(dt1.Rows.Count - 1)(i) = cell.Value.ToString()
          i += 1
      Next
  Next

  'importing data from dt1 into db'

  ds.Tables.Add(dt1)
  da.TableMappings.Add("Table", "dbo.fingerprintslogs")
  ds.Tables(0).TableName = "Table"

  conn.Open()

  Dim query = " Delete From Fingerprintlogs"
  Dim cmd As New SqlCommand(query, conn)
  da.DeleteCommand = cmd
  da.DeleteCommand.ExecuteNonQuery()

  DataGridView1.DataSource = ds.Tables(0)
  For Each dtrow As DataRow In ds.Tables(0).Rows

      Dim EmployeeID As Integer = dtrow(0)
      Dim Date1 As DateTime = dtrow(1)
      Dim WeekDay = dtrow(2)
      Dim Firstcheckin As DateTime = dtrow(3)
      Dim Lastcheckout As DateTime = dtrow(4)
      Dim query1 = "INSERT INTO fingerprintslogs Values('" & EmployeeID & "','" & Date1 & "','" & WeekDay & "','" & Firstcheckin & "','" & Lastcheckout & "')"
      Dim cmd1 As New SqlCommand(query1, conn)
      cmd1.Parameters.Add("@EmloyeeID", SqlDbType.Int).Value = EmployeeID
      cmd1.Parameters.Add("@Date1", SqlDbType.DateTime).Value = Date1
      cmd1.Parameters.Add("@WeekDay", SqlDbType.NVarChar).Value = WeekDay
      cmd1.Parameters.Add("@Firstcheckin", SqlDbType.DateTime).Value = Firstcheckin
      cmd1.Parameters.Add("@Lastcheckout", SqlDbType.DateTime).Value = Lastcheckout
      cmd1.ExecuteNonQuery()
      da.InsertCommand = cmd1
      da.Update(ds.Tables(0))
  Next
  conn.Close()

更新后的参数化插入代码 #

Using command As New SqlCommand("INSERT INTO Fingerprintlogs (EmployeeID, Date, WeekDay, FirstCheckIn,LastCheckOut ) VALUES (@EmployeeID, @Date, @WeekDay, @FirstCheckIn, @LastCheckOut)", conn), adapter As New SqlDataAdapter With {.InsertCommand = command}
     With command.Parameters
         .Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID")
         .Add("@Date", SqlDbType.DateTime, 0, "Date")
         .Add("@WeekDay", SqlDbType.VarChar, 50, "WeekDay")
         .Add("@FirstCheckIn", SqlDbType.DateTime, 0, "FirstCheckIn")
         .Add("@LastCheckOut", SqlDbType.DateTime, 0, "LastCheckOut ")
     End With
     adapter.Update(ds.Tables(0))
 End Using

Excel样本数据 #

解决方案 #

1. 修复DataTable列类型定义 #

初始代码创建DataTable列时未指定类型，默认全部为字符串类型，这是转换失败的核心原因。需手动指定各列的DataType：

' 替换初始代码中创建列的逻辑
If firstrow Then
    ' 按实际业务类型定义列
    dt1.Columns.Add("EmployeeID", GetType(Integer))
    dt1.Columns.Add("Date", GetType(DateTime))
    dt1.Columns.Add("WeekDay", GetType(String))
    dt1.Columns.Add("FirstCheckIn", GetType(DateTime))
    dt1.Columns.Add("LastCheckOut", GetType(DateTime))
    firstrow = False
End If

2. 正确读取Excel值并处理空值/仅时间的情况 #

填充DataTable时，避免强制转字符串，针对不同类型做解析，同时处理Excel中的空值和仅时间的单元格：

For Each row As IXLRow In ws2.Rows.Skip(2)
    Dim newRow As DataRow = dt1.NewRow()
    
    ' 处理EmployeeID
    Dim empIdStr As String = row.Cells(0).Value.ToString()
    newRow("EmployeeID") = If(String.IsNullOrEmpty(empIdStr), DBNull.Value, Integer.Parse(empIdStr))
    
    ' 处理Date列：指定格式解析，避免区域格式影响
    Dim dateStr As String = row.Cells(1).Value.ToString()
    If Not String.IsNullOrEmpty(dateStr) Then
        newRow("Date") = DateTime.ParseExact(dateStr, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture)
    Else
        newRow("Date") = DBNull.Value
    End If
    
    ' 处理WeekDay
    newRow("WeekDay") = If(String.IsNullOrEmpty(row.Cells(2).Value.ToString()), DBNull.Value, row.Cells(2).Value.ToString())
    
    ' 处理FirstCheckIn：仅时间需结合Date列的日期
    Dim firstInStr As String = row.Cells(3).Value.ToString()
    If Not String.IsNullOrEmpty(firstInStr) AndAlso Not newRow.IsNull("Date") Then
        Dim datePart As DateTime = DirectCast(newRow("Date"), DateTime)
        Dim timePart As TimeSpan = TimeSpan.Parse(firstInStr)
        newRow("FirstCheckIn") = datePart.Add(timePart)
    Else
        newRow("FirstCheckIn") = DBNull.Value
    End If
    
    ' 处理LastCheckOut：同理处理仅时间的情况
    Dim lastOutStr As String = row.Cells(4).Value.ToString()
    If Not String.IsNullOrEmpty(lastOutStr) AndAlso Not newRow.IsNull("Date") Then
        Dim datePart As DateTime = DirectCast(newRow("Date"), DateTime)
        Dim timePart As TimeSpan = TimeSpan.Parse(lastOutStr)
        newRow("LastCheckOut") = datePart.Add(timePart)
    Else
        newRow("LastCheckOut") = DBNull.Value
    End If
    
    dt1.Rows.Add(newRow)
Next

3. 修正参数化代码的列名空格问题 #

原参数化代码中@LastCheckOut映射的列名末尾有空格（"LastCheckOut "），需去掉空格保证映射正确：

.Add("@LastCheckOut", SqlDbType.DateTime, 0, "LastCheckOut") ' 移除末尾空格

4. 确认数据库列允许空值 #

从Excel样本看，FirstCheckIn和LastCheckOut存在空值，需确保数据库中对应列设置为允许NULL，否则插入时会触发额外错误。

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