Export datagridview to excel

Hello,
When i export datagridview to excel the decimal values get exported as integer.
Any suggestions?

Cheers
RIASAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
because leading 0 are often useless so they are removed.

you should export those values as text so they don't lose there leading 0.

a way to force to export as text is to add a ' before the value:

objXlData(iRow, iCol) = "'" + dtGridData.Rows(iRow).Item(iCol)

Open in new window

1
 
Fernando SotoRetiredCommented:
Hi;

Can you post the code you are using to export to Excel.

Thanks
0
 
Éric MoreauSenior .Net ConsultantCommented:
we need to see how you export your values!

when you look in Excel for a specific cell, do you see in the input bar the decimal value or still the integer value? That might just be a formatting issue. Very often Excel will format the whole column depending on the value of the first row.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RIASAuthor Commented:
  Function ExportToExcel(ByVal intCreateNew As Integer, ByVal dtGridData As DataTable, ByVal FilePath As String, ByVal StrSheetname As String, Optional ByVal xlApp As Excel.Application = Nothing) As String
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Application.DoEvents()
        Try
            FrmMain.Cursor = Cursors.WaitCursor
            xlApp.DisplayAlerts = False
            If intCreateNew = 0 Then

                xlApp.Workbooks.Add()
            End If
            If IsNothing(FilePath) = True Then
                xlWorkSheet = Nothing
                xlWorkBook = Nothing
                xlApp = Nothing
                FrmMain.Cursor = Cursors.Default
                Return "Cancelled"
                Exit Function
            End If
            xlApp.Workbooks(1).SaveAs(FilePath)
            Application.DoEvents()
            xlWorkBook = xlApp.Workbooks.Open(FilePath)
            Application.DoEvents()
            If intCreateNew = 0 Then
                xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
            Else
                xlWorkBook.Worksheets.Add(After:=xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
                xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
            End If
            xlWorkSheet.Name = StrSheetname
            With xlWorkSheet.PageSetup
                .PrintGridlines = True
                .CenterHeader = StrSheetname
                .Zoom = False
                .Orientation = Excel.XlPageOrientation.xlLandscape
            End With
            Dim dtRowCount As Integer = dtGridData.Rows.Count
            Dim dtColCount As Integer = dtGridData.Columns.Count

            Dim objXlColHeaderData(1, dtGridData.Columns.Count) As Object
            Application.DoEvents()
            For i As Integer = 0 To dtColCount - 1
                objXlColHeaderData(0, i) = dtGridData.Columns(i).ColumnName
            Next
            Dim objXlData(dtRowCount, dtColCount) As Object
            For iRow As Integer = 0 To dtRowCount - 1
                Application.DoEvents()
                For iCol As Integer = 0 To dtColCount - 1
                    Application.DoEvents()
                    If Not IsDBNull(dtGridData.Rows(iRow).Item(iCol)) Then
                        objXlData(iRow, iCol) = dtGridData.Rows(iRow).Item(iCol)
                    Else
                        objXlData(iRow, iCol) = ""
                    End If
                Next
            Next
            Dim xlRange As Excel.Range = xlWorkSheet.Range("A1")
            xlRange = xlRange.Resize(dtRowCount, dtColCount)

            xlRange.Value2 = objXlColHeaderData
            xlWorkSheet.Range(xlWorkSheet.Cells(1, 1), xlWorkSheet.Cells(1, dtColCount)).Font.Bold = True
            Select Case StrSheetname.ToUpper
                'Formatting the dates on excel please check the column names  refer stored procedure for columns 
                '[MEDICAL_Select_Correspondence]
                Case "DIARY"
                    xlRange = xlWorkSheet.Range("C1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("D1")
                    'xlRange.EntireColumn.NumberFormat = "hh:mm"
                    'xlRange = xlWorkSheet.Range("E1")
                    'xlRange.EntireColumn.NumberFormat = "hh:mm"
                    For Each strX As String In New String() {"D", "E"}
                        xlRange = xlWorkSheet.Range(strX + "1")
                        xlRange.EntireColumn.NumberFormat = "hh:mm"
                    Next
                Case "CORRESPONDENCE"
                    xlRange = xlWorkSheet.Range("A1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"

                Case "SHORT_LET"
                    'xlRange = xlWorkSheet.Range("C1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("D1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("K1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("O1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("V1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("W1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("Y1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"


                    For Each strX As String In New String() {"C", "D", "K", "O", "V", "W", "Y1"}
                        xlRange = xlWorkSheet.Range(strX + "1")
                        xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    Next

                Case "CLIENT_REFERENCE"
                    xlRange = xlWorkSheet.Range("E1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"

                Case "SPECIAL_PAYMENT_REQUEST"
                    xlRange = xlWorkSheet.Range("C1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                Case "CAR"
                    xlRange = xlWorkSheet.Range("B1")
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"

                Case "CLINIC"
                    'xlRange = xlWorkSheet.Range("C1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("G1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    'xlRange = xlWorkSheet.Range("J1")
                    'xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"


                    For Each strX As String In New String() {"C", "G", "J"}
                        xlRange = xlWorkSheet.Range(strX + "1")
                        xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy"
                    Next

            End Select



            xlRange = xlWorkSheet.Range("A2")
            xlRange = xlRange.Resize(dtRowCount, dtColCount)
            ' xlRange = xlRange.Resize(dtRowCount + 1, dtColCount + 1)
            xlRange.Value2 = objXlData

            'With xlWorkSheet
            '    .Range(.Cells(1, 1), .Cells(1, 1)).Select()
            'End With
            With xlWorkSheet.Application.ActiveWindow
                .SplitColumn = 0
                .SplitRow = 1
            End With

            xlWorkSheet.Cells.EntireColumn.AutoFit()
            xlWorkSheet.Cells.EntireRow.AutoFit()
            xlWorkSheet.Application.ActiveWindow.FreezePanes = True
            FrmMain.Cursor = Cursors.WaitCursor
            Application.DoEvents()
            CType(xlApp.ActiveWorkbook.Sheets(1), Excel.Worksheet).Select()
            xlWorkBook.Save()

        Catch ex As Exception
            MessageBox.Show(ex.Message, "ErrorIn ExportToExcel", MessageBoxButtons.OK, MessageBoxIcon.Error)
            xlWorkSheet = Nothing
            xlWorkBook = Nothing
            xlApp = Nothing
            FrmMain.Cursor = Cursors.Default
            Return "False"
        Finally
            FrmMain.Cursor = Cursors.Default
        End Try
        Return "True"
    End Function

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
I see that you are using NumberFormat for many columns. Have you tried for the decimal columns as well:

xlRange.EntireColumn.NumberFormat = "#,##0.00"

Open in new window

1
 
RIASAuthor Commented:
Will try and get back
Also
if the column is telephone and the value is 078888
It gets exported as 7888
0
 
RIASAuthor Commented:
Will try Thanks
0
 
RIASAuthor Commented:
Eric,
objXlData(iRow, iCol) = "'" + dtGridData.Rows(iRow).Item(iCol) is giving an error when the value is double.
0
 
Éric MoreauSenior .Net ConsultantCommented:
which error? Try replacing the + with &
0
 
RIASAuthor Commented:
That worked Eric,
Thanks but ,
 objXlData(iRow, iCol) = "'" & dtGridData.Rows(iRow).Item(iCol) and
 xlRange.EntireColumn.NumberFormat = "#,##0.00"
Gives numberws inthe format
25.0000
with four decimals
Any suggestion?
0
 
RIASAuthor Commented:
Thanks!
0
 
Éric MoreauSenior .Net ConsultantCommented:
If you set your columns to be text (using the ') you cannot use NumberFormat on them.

You should only send the columns for which you want  to keep the leading 0 with a ' in front of them
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.