Changing the format when exporting Datagridview to Excel

0

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.

    
asked by Luippo 22.03.2018 в 14:59
source

0 answers