Сохранение значений типа Date / DateTime в таблицах данных

Вопрос или проблема

Dim wb1 As IXLWorkbook = New XLWorkbook("C:\Users\oxy\Downloads\Test\Test2.xlsx")
Dim ws As IXLWorksheet = wb1.Worksheet(1)

Dim wb2 As IXLWorkbook = New XLWorkbook(TextBox1.Text)
Dim ws2 As IXLWorksheet = wb2.Worksheet(1)
ws2.Range("A:G").CopyTo(ws.Cell("A1"))

Dim wb3 As IXLWorkbook = New XLWorkbook(TextBox2.Text)
Dim ws3 As IXLWorksheet = wb3.Worksheet(1)
ws3.Range("A:N").CopyTo(ws.Cell("F1"))

Dim dt1 As New DataTable
Dim dt2 As New DataTable

Dim firstrow As Boolean = True 
For Each row As IXLRow In ws2.Rows.Skip(0)

    If firstrow Then
        For Each cell As IXLCell In row.Cells
            dt1.Columns.Add(cell.Value.ToString)
        Next
        firstrow = False
        dt1.Columns("Date").DataType = GetType(Date)
        dt1.Columns("First Check In").DataType = GetType(Date)
        dt1.Columns("Last Check Out").DataType = GetType(Date)

    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
        i += 1
    Next
Next

    dt1.Columns("Date").DataType = GetType(Date)
    dt1.Columns("First Check In").DataType = GetType(Date)
    dt1.Columns("Last Check Out").DataType = GetType(Date) 

dt1.Rows(dt1.Rows.Count - 1)(i) = cell.Value

Ответ или решение

Сохранение значений типа Date / DateTime в таблицах данных

При работе с данными, особенно когда дело касается взаимодействия между Excel и базой данных, задача корректного преобразования и сохранения форматов даты и времени может вызвать множество трудностей. В вашем случае проблема заключается в неправильной интерпретации значений даты, поступающих из Excel в DataTable. Давайте разберем основные моменты и возможные решения, чтобы эффективно сохранить значения типа Date / DateTime в вашей базе данных.

1. Проблема с форматом данных

Ваш код использует библиотеку ClosedXML для извлечения данных из рабочего листа Excel. Судя по ошибке:

'Unable to cast object of type 'ClosedXML.Excel.XLCellValue' to type 'System.IConvertible'. Couldn't store <2024-04-16> in Date Column. Expected type is DateTime.'

проблема в том, что значения, извлекаемые из ячеек, сохраняются как XLCellValue, которые не могут быть напрямую преобразованы в тип DateTime. Вам нужно сначала конвертировать значения.

2. Как правильно извлекать и конвертировать даты

Для правильной обработки значений даты сначала извлеките текстовое представление даты, а затем преобразуйте его в формат DateTime. Замените строку, где вы добавляете значение в DataTable:

Dim cellValue As String = cell.GetFormattedString()
If DateTime.TryParse(cellValue, dt1.Rows(dt1.Rows.Count - 1)(i)) Then
    ' Успешная конвертация, значение добавлено
Else
    ' Обработка ошибки: если дата невалидна, можно задать значение по умолчанию или логировать ошибку
    dt1.Rows(dt1.Rows.Count - 1)(i) = DBNull.Value
End If

3. Установка типа данных для столбцов

Установите тип данных столбцов до добавления значений. Это поможет избежать конфликта типов на этапе вставки данных. В вашем коде:

dt1.Columns("Date").DataType = GetType(DateTime)
dt1.Columns("First Check In").DataType = GetType(DateTime)
dt1.Columns("Last Check Out").DataType = GetType(DateTime)

Этот код гарантирует, что при попытке вставить данные в таблицу будут использованы верные типы данных.

4. Обработка значений времени

Проблемы аналогичного характера могут возникнуть и с временем, например, для столбцов "First Check In" и "Last Check Out". Убедитесь, что вы правильно обрабатываете их также:

Dim timeValue As String = cell.GetFormattedString()
If DateTime.TryParse(timeValue, dt1.Rows(dt1.Rows.Count - 1)(i)) Then
    ' Успешная конвертация времени
Else
    dt1.Rows(dt1.Rows.Count - 1)(i) = DBNull.Value
End If

5. Пример кода

Обновленный фрагмент кода будет выглядеть следующим образом:

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()
        If dt1.Columns(i).DataType Is GetType(DateTime) Then
            Dim cellValue As String = cell.GetFormattedString()
            If Not DateTime.TryParse(cellValue, dt1.Rows(dt1.Rows.Count - 1)(i)) Then
                dt1.Rows(dt1.Rows.Count - 1)(i) = DBNull.Value
            End If
        Else
            dt1.Rows(dt1.Rows.Count - 1)(i) = cell.Value
        End If
        i += 1
    Next
Next

Заключение

Следуя данным рекомендациям, вы сможете корректно сохранять значения типа Date / DateTime в вашей базе данных. Обратите внимание на правильный формат данных на этапе извлечения и убедитесь, что каждая дата или время проверяются на валидность до добавления в DataTable. Это позволит избежать ошибок и облегчит дальнейшую работу с данными в вашем приложении.