I have the following code to export to Excel, from my Datagridview.
Private Sub EXPORTARToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles EXPORTARToolStripMenuItem.Click
Dim exApp As New Microsoft.Office.Interop.Excel.Application
Dim exLibro As Microsoft.Office.Interop.Excel.Workbook
Dim archSalida As String = ""
Dim sheetName As String = ""
Me.Cursor = Cursors.WaitCursor
archSalida = "C:CVCV_Informe_Registros" & ".xlsx"
sheetName = "LISTADO_RETIROS_3CV"
Try
exLibro = exApp.Workbooks.Open(archSalida, ReadOnly:=False, IgnoreReadOnlyRecommended:=True)
Dim exHoja As Microsoft.Office.Interop.Excel.Worksheet = CType(exLibro.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
Dim filai As Integer = 0
Dim filaExcel As Integer = 6
exHoja = exLibro.Sheets(1)
exLibro.Worksheets(sheetName).Cells(2, "E").Value = globalPPU
exLibro.Worksheets(sheetName).Cells(3, "E").Value = globalUN
exLibro.Worksheets(sheetName).Cells(4, "E").Value = Convert.ToString(globalIniDate + " - " + globalEndDate)
While filai < gvRegistros.Rows.Count
If gvRegistros.Rows(filai).Cells("col_num").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "A").Value = Trim(gvRegistros.Rows(filai).Cells("col_num").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_un").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "B").Value = Trim(gvRegistros.Rows(filai).Cells("col_un").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_fecret").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "C").Value = Trim(Format(DateTime.Parse(gvRegistros.Rows(filai).Cells("col_fecret").Value.ToString), "dd/MM/yyyy"))
End If
If gvRegistros.Rows(filai).Cells("col_codins").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "D").Value = Trim(gvRegistros.Rows(filai).Cells("col_codins").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_direcc").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "E").Value = Trim(gvRegistros.Rows(filai).Cells("col_direcc").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_comuna").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "F").Value = Trim(gvRegistros.Rows(filai).Cells("col_comuna").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_ppu").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "G").Value = Trim(gvRegistros.Rows(filai).Cells("col_ppu").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_busope").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "H").Value = Trim(gvRegistros.Rows(filai).Cells("col_busope").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_com3cv").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "I").Value = Trim(gvRegistros.Rows(filai).Cells("col_com3cv").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_marca").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "J").Value = Trim(gvRegistros.Rows(filai).Cells("col_marca").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_modelo").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "K").Value = Trim(gvRegistros.Rows(filai).Cells("col_modelo").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_ano").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "L").Value = Trim(gvRegistros.Rows(filai).Cells("col_ano").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_tipveh").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "M").Value = Trim(gvRegistros.Rows(filai).Cells("col_tipveh").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_norma").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "N").Value = Trim(gvRegistros.Rows(filai).Cells("col_norma").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_tipfil").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "O").Value = Trim(gvRegistros.Rows(filai).Cells("col_tipfil").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_fecins_fil").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "P").Value = Trim(gvRegistros.Rows(filai).Cells("col_fecins_fil").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_marfil").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "Q").Value = Trim(gvRegistros.Rows(filai).Cells("col_marfil").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_condic").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "R").Value = Trim(gvRegistros.Rows(filai).Cells("col_condic").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_foto").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "T").Value = Trim(gvRegistros.Rows(filai).Cells("col_foto").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_observ").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "U").Value = Trim(gvRegistros.Rows(filai).Cells("col_observ").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_infext").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "V").Value = Trim(gvRegistros.Rows(filai).Cells("col_infext").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_rester").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "W").Value = Trim(gvRegistros.Rows(filai).Cells("col_rester").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_conduc").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "W").Value = Trim(gvRegistros.Rows(filai).Cells("col_conduc").Value.ToString)
End If
If gvRegistros.Rows(filai).Cells("col_fecdig").Value IsNot Nothing Then
exLibro.Worksheets(sheetName).Cells(filaExcel, "x").Value = Trim(gvRegistros.Rows(filai).Cells("col_fecdig").Value.ToString)
End If
filaExcel = filaExcel + 1
filai = filai + 1
End While
exApp.Application.Visible = True
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error al exportar a Excel")
End Try
Me.Cursor = Cursors.Default
End Sub
The result of the Datagridview is as follows:
As shown in the image, the Datagrid is ordered by the last record entered (FEC. RETIRO), with the format "dd-MM-yyyy"
When exporting to an excel, it happens that some dates, are disordered and remain as MM-dd-yyyy, and others as dd-MM-yyyy, this makes the data get disordered.