Handle UltraWinGrid when Export for Datetime format

Today i had issue when i run application for inquiry report, when the grid (ultrawingrid) have column with date value. I want to export to excel file. when i opened that excel file i cann’t update my date value to dateformat.

Until i found this rootcause :D [gladly]
it because the column value on string type not on datetime format.



so i use this datatable to fill that grid (see column name – calendar date) :

Private Function SetDataTableColumn() As DataTable
        Dim dtDesign As New DataTable
        With dtDesign.Columns
            .Add("EmployeeNo")
            .Add("EmployeeName")
            ' Menambahkan kolom dengan format date ( agar excelnya bisa dimodif)
            Dim idColumn As DataColumn = New DataColumn()
            idColumn.DataType = System.Type.GetType("System.DateTime")
            idColumn.ColumnName = "CalendarDate"
            .Add(idColumn)
            .Add("WDCode")
            .Add("WDSplitCode")
            .Add("AbsentCode1")
            .Add("AbsentCode2")
            .Add("AbsentCode3")
            .Add("DatetimeIn")
            .Add("DatetimeOut")
            .Add("OTBefStart")
            .Add("OTBefEnd")
            .Add("OTAftStart")
            .Add("OTAftEnd")
            .Add("OTTypeA")
            .Add("OTTypeB")
            .Add("OTTypeC")
            .Add("OTTypeD")
            .Add("TotalOT")
            .Add("TransactionPeriod")
        End With
        Return dtDesign
    End Function

After that you can export that rows with this code:

Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
        If Not grdATDReport.Rows.Count = 0 Then
            Dim fsave As New Windows.Forms.SaveFileDialog
            fsave.Filter = "Excel Files (*.xls)|*.xls|All files (*.*)|*.*)"
            fsave.ShowDialog()
            If Not (fsave.FileName = "") Then
                Exporter.Export(grdATDReport, fsave.FileName)
                MessageBox.Show("Data has been exported !", "Export OK", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Else
            If grdATDReport.Rows.Count = 0 Then
                MessageBox.Show("No data to export !", "WARNING", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
            If grdATDReport.Rows.Count >= 65001 Then
                MessageBox.Show(String.Format("Cannot export more than 65000 attendance data (there are {0} data)", grdATDReport.Rows.Count), "Export Fail", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        End If
    End Sub

Thank to God because He opened my knowledge for this.