Export datagridview to excel

Hi;

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

Thanks

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.

  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

Select allOpen in new window

I see that you are using NumberFormat for many columns. Have you tried for the decimal columns as well:

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

Select allOpen in new window

Will try and get back
Also
if the column is telephone and the value is 078888
It gets exported as 7888

Will try Thanks

Eric,
objXlData(iRow, iCol) = "'" + dtGridData.Rows(iRow).Item(iCol) is giving an error when the value is double.

which error? Try replacing the + with &

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?

Thanks!

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