Currency values are shown as non-formatted doubles when exporting from spreadsheet to pdf

2 Answers 53 Views
GridView
Martin Hamilton
Top achievements
Rank 1
Iron
Iron
Veteran
Martin Hamilton asked on 20 Nov 2023, 03:31 PM

I am exporting my grid to pdf.

The grid is showing 2 columns properly formatted to currency (E.g., $100.50) however, when I export to PDF they appear only as 100.5 (no dollar sign or proper decimal (2)... just 100.5 instead of $100.50  

The grid is displaying them as $100.50

How do I get the proper currency formatting to be written into the PDF?

2 Answers, 1 is accepted

Sort by
0
Nadya | Tech Support Engineer
Telerik team
answered on 23 Nov 2023, 11:32 AM

Hello, Martin,

According to the provided information, it seems that you are exporting RadGridView to pdf. This is why I changed the product in ticket info to RadGridView in order to be relevant to the topic of this thread. 

It is not clear which is the exact export that you use. I would recommend using the GridViewPdfExport since it is more powerful and feature rich than the old ExportToPdf object, because it utilizes the RadPdfProcessing library and works directly in the native pdf format. Additional information as well other styling options are documented in the following help article: Export to PDF - WinForms GridView Control - Telerik UI for WinForms

When using GridViewPdfExport, the decimal places for numeric values as well as the currency sign are exported to the pdf. In addition, this export offers ExportVisualSettings property and CellFormatting event that might be useful while exporting data. Please refer to the following code snippet:

Telerik.WinControls.Export.GridViewPdfExport pdfExporter = new Telerik.WinControls.Export.GridViewPdfExport(this.radGridView1);
pdfExporter.FileExtension = ".pdf";
pdfExporter.ExportVisualSettings = true;
pdfExporter.FitToPageWidth = false;
string fileName = @"..\..\DPLExportedData.pdf";
pdfExporter.RunExport(fileName, new Telerik.WinControls.Export.PdfExportRenderer());

The pdf file:

I hope this information helps. Should you have any other questions do not hesitate to contact me.

Regards,
Nadya | Tech Support Engineer
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Martin Hamilton
Top achievements
Rank 1
Iron
Iron
Veteran
commented on 24 Nov 2023, 02:48 PM

Hi Nadya,

Yes, I am using the GridViewPdfExport method... but I'm really stumped.

My application is rather complex so, I decided to just create a new form and test with the most basic code.

I created a Form (Form1) and added a single button named "BtnTest" and a GridView named"GridData".

Just 2 controls - very simple.  Please refer to TestGrid.png

Next I added only the most basic code (See below).

Private Sub ExportToPDF()
    Dim ExportFolder As String = "C:\Test\"
    Dim ExportFileName As String = "MyTest.pdf"

    Dim DtData As New DataTable
    DtData.Columns.Add("CurrentPrice", GetType(Double))
    For i As Integer = 0 To 9
        DtData.Rows.Add(10 * i)
    Next
    GridData.DataSource = DtData

    If GridData.Rows.Count > 0 Then
        If Not IO.Directory.Exists(ExportFolder) Then IO.Directory.CreateDirectory(ExportFolder)
        If IO.File.Exists(IO.Path.Combine(ExportFolder, ExportFileName)) Then IO.File.Delete((IO.Path.Combine(ExportFolder, ExportFileName)))
        Dim pdfExporter As New Telerik.WinControls.Export.GridViewPdfExport(GridData)
        pdfExporter.FileExtension = ".pdf"
        pdfExporter.ExportVisualSettings = True
        pdfExporter.FitToPageWidth = False
        pdfExporter.RunExport(IO.Path.Combine(ExportFolder, ExportFileName), New Telerik.WinControls.Export.PdfExportRenderer())
    End If
End Sub
Private Sub BtnTest_Click(sender As Object, e As EventArgs) Handles BtnTest.Click
    ExportToPDF()
End Sub
Private Sub GridData_ViewCellFormatting(sender As Object, e As Telerik.WinControls.UI.CellFormattingEventArgs) Handles GridData.ViewCellFormatting
    If e.ColumnIndex = 0 Then
        If e.CellElement.Value Is Nothing Then Exit Sub
        Try
            Dim value As Double = 0.0
            If Double.TryParse(e.CellElement.Value.ToString(), value) Then
                e.CellElement.Text = String.Format(Thread.CurrentThread.CurrentCulture, "{0:C}", value)
            End If
        Catch ex As Exception
        End Try
    End If
End Sub

Now, please look at TestPDF.png - and you'll see that the exported grid does not have the $ nor the decimal places whereas the grid 'does' have them.

If you wouldn't mind having a look and perhaps building "Form1" and then perform a copy/paste of my code - perhaps you can pinpoint where I am doing something wrong.

Thank you in advance for your help

 

Martin Hamilton
Top achievements
Rank 1
Iron
Iron
Veteran
commented on 24 Nov 2023, 03:13 PM

I should also say that I'm on the latest release of the Telerik controls
0
Nadya | Tech Support Engineer
Telerik team
answered on 24 Nov 2023, 03:39 PM

Hello, Martin,

Thank you for the provided code snippet. I was able to use it in my test project.

It seems that you are formatting the cell values to use the currency format in GridData_ViewCellFormatting event. This is valid approach, but I would like to note that GridViewDecimalColumn offers FormatString property which can set any custom format that you want to your decimal column. If you go and set this property to your column, the values in cells would be formatted and it is not neccessary to handle the GridData_ViewCellFormatting. The GridViewPdfExport would export the desired format into pdf as well. 

Dim decimalColumn As GridViewDecimalColumn = New GridViewDecimalColumn()
decimalColumn.FormatString = "{0:C}"

Another way is to set the format for the grid cells as you did, in the GridData_ViewCellFormatting event, this is also a valid approach. In this case, it would be necessary to handle the pdfExporter.CellFormatting as well and format the values accordingly. Please refer to the whole code snippet on my form:

Public Class RadForm1
    Sub New()
        ' This call is required by the designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
    End Sub

    Private Sub BtnTest_Click(sender As Object, e As EventArgs) Handles BtnTest.Click
        ExportToPDF()
    End Sub
    Private Sub ExportToPDF()
        Dim ExportFolder As String = "C:\Test\"
        Dim ExportFileName As String = "MyTest.pdf"

        Dim DtData As New DataTable
        DtData.Columns.Add("CurrentPrice", GetType(Double))
        For i As Integer = 0 To 9
            DtData.Rows.Add(10 * i)
        Next
        GridData.DataSource = DtData
        If GridData.Rows.Count > 0 Then
            If Not IO.Directory.Exists(ExportFolder) Then IO.Directory.CreateDirectory(ExportFolder)
            If IO.File.Exists(IO.Path.Combine(ExportFolder, ExportFileName)) Then IO.File.Delete((IO.Path.Combine(ExportFolder, ExportFileName)))
            Dim pdfExporter As New Telerik.WinControls.Export.GridViewPdfExport(GridData)
            pdfExporter.FileExtension = ".pdf"
            pdfExporter.ExportVisualSettings = True
            pdfExporter.FitToPageWidth = False
            AddHandler pdfExporter.CellFormatting, AddressOf pdfExporter_CellFormatting
            pdfExporter.RunExport(IO.Path.Combine(ExportFolder, ExportFileName), New Telerik.WinControls.Export.PdfExportRenderer())
        End If
    End Sub

    Private Sub pdfExporter_CellFormatting(sender As Object, e As PdfExportCellFormattingEventArgs)
        If e.ColumnIndex = 0 Then
            If e.CellElement Is Nothing Then Exit Sub
            Try
                Dim value As Double = 0.0
                If Double.TryParse(e.CellElement.Text.ToString(), value) Then
                    e.CellElement.Text = String.Format(Thread.CurrentThread.CurrentCulture, "{0:C}", value)
                End If
            Catch ex As Exception
            End Try
        End If
    End Sub

    Private Sub GridData_ViewCellFormatting(sender As Object, e As Telerik.WinControls.UI.CellFormattingEventArgs) Handles GridData.ViewCellFormatting
        If e.ColumnIndex = 0 Then
            If e.CellElement.Value Is Nothing Then Exit Sub
            Try
                Dim value As Double = 0.0
                If Double.TryParse(e.CellElement.Value.ToString(), value) Then
                    e.CellElement.Text = String.Format(Thread.CurrentThread.CurrentCulture, "{0:C}", value)
                End If
            Catch ex As Exception
            End Try
        End If
    End Sub
End Class

Feel free to use the approach that is most suitable for you.

I hope this helps. If you have any other questions, please let me know.

Regards,
Nadya | Tech Support Engineer
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Tags
GridView
Asked by
Martin Hamilton
Top achievements
Rank 1
Iron
Iron
Veteran
Answers by
Nadya | Tech Support Engineer
Telerik team
Share this question
or