Click here to Skip to main content
15,849,829 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Buenas tarde.. su apoyo por favor..
al momento de exportar los datos del datagridview a excel . me sale que la fecha que debes de ser 01/09/2021(correcto) sale al contrario 09/01/2021(incorrecto) y los numero por ejemplo 00284514 (correcto) sale 284514, me elimina los ceros de la izquierda.. porfavor ayuda...adjunto el codigo que copie de aqui.. el codigo enviado es el que estoy trabajando, al estar vacio el espacio de las sal error, como modificar para que pase por alto los registros vacios, al momento de exportar
muy agradecido de antemano
gracias

Translated:
Good afternoon. Your support please.
When exporting the data from the DataGridView to Excel. I get that the date that you must be 01/09/2021 (correct) comes out the opposite 09/01/2021 (incorrect) and I number them for example 00284514 (correct) comes out 284514, it eliminates the zeros on the left .. please help... I attach the code that I copy from here. The code sent is the one I am working on, being empty the space of the salt error, how to modify so that it ignores the empty records, at the time of exporting
very grateful in advance
Thank you

What I have tried:

Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
            MsgBox("datagrid vacio")
        End If
        'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To DataGridView1.ColumnCount - 1
            dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DataGridView1.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                dr1(j) = DataGridView1.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        Dim pexcel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

        wBook = pexcel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            pexcel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                pexcel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
        Next
        MsgBox("Espere la consulta esta siendo exportada")
        MsgBox("Cierre los Archivos de Excel de lo contrario no se Guardara", MsgBoxStyle.OkOnly)
        wSheet.Columns.AutoFit()
        Dim strFileName As String = "D:\Filtro.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        wBook.SaveAs(strFileName)
        pexcel.Workbooks.Open(strFileName)
        pexcel.Visible = True
        DataGridView1.DataSource = Nothing
        Button8.Enabled = False
    End Sub
Posted
Updated 6-Dec-22 18:41pm
v3
Comments
Kornfeld Eliyahu Peter 7-Dec-22 3:58am    
As for the '00284514 (correct) comes out 284514' part - leading zeroes in numbers are meaningless... so it is correct...
If you wish to keep those leading zeroes you should use a different type (probably string)...
Member 15856250 7-Dec-22 8:39am    
Mi estimado agradeciendo por apoyo..
lo de los ceros es que son numeros que no se pueden eliminar es como numero de identidad..osea empieza 00125879 que es lo normal ,,si pudieras apoyarme en esa forma como lo haria...
gracias

--TRANSLATION--
My dear thank you for support..
The thing about the zeros is that they are numbers that cannot be eliminated, it's like an identity number... I mean, it starts at 00125879, which is normal, if you could support me in that way, how would I do it...
Thank you
Kornfeld Eliyahu Peter 7-Dec-22 8:43am    
If they are identity and the leading zeroes ARE significant - it means they are not numbers (by definition)... Change them to strings in your EXCEL or in your code...
Member 15856250 7-Dec-22 10:33am    
DISCULPA MI IGNORANCIA,, COMO LO PUEDO CODIFICAR
Member 15856250 7-Dec-22 11:56am    
Estimado agradeciendote por el gran apoyo ya consegui el de los ceros
wSheet.Columns("h:h").NumberFormat = "@"
ese era el resultado..
mil gracias ...desde PERU

1 solution

Consegui la solcion para las fechas
en este caso la columna (I:I) , es donde se encuentra la fecha...
wSheet.Columns("I:I").NumberFormat = "MM/dd/yyyy;@"...
antes 01/09/2022 se cambiaba a 09/01/2022
ahora 01/09/2022 se traslado 01/09/2022 --- ahora todo correcto... solo ahora me faltaria los ceros que no se borren al exportar...
Muchas Gracias

Translation from Google Translate:
I got the solution for the dates
in this case the column (I:I) is where the date is located...
wSheet.Columns("I:I").NumberFormat = "MM/dd/yyyy;@"...
before 09/01/2022 it was changed to 01/09/2022
now 09/01/2022 was transferred to 09/01/2022 --- now everything is correct... only now I would be missing the zeros that are not deleted when exporting...
Thank you so much
 
Share this answer
 
v3
Comments
CHill60 7-Dec-22 12:00pm    
Please use English when posting to this site.
Utilice inglés cuando publique en este sitio.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900