Export to Excel column formatting

[JSaultz]

Hello, I have recently migrated a scorecard system from the RadControls_Q1_2007_SP2 to RadControls_for_ASP.NET_AJAX_2009_3_1208_dev. One of the features that seemed to be inherently built in was a consistent format in the excel output. A large chunk of the data is in percent form. It displays fine in the grid, but when I am exporting it, it returns to a standard decimal format. The data stays correct, but I cannot get the percent format in the export. Here is the aspx for the column that is calculated from the data source.

                <telerik:GridBoundColumn Visible="False" DataField="AvgQA_Export" HeaderText="Avg. QA" UniqueName="colAvgQA_Export">
                    <HeaderStyle BackColor="#FFCC00" CssClass="rgYellow" HorizontalAlign="Center" />
                    <ItemStyle HorizontalAlign="Right" />
                </telerik:GridBoundColumn>

Here is the codebehind  to generate the value of the field:

 Protected Sub rgScorecard_ItemDataBound(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles rgScorecard.ItemDataBound
   If TypeOf e.Item Is GridDataItem Then
                Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
  ' QA
                ' --------------------------------------------
                If dataItem("colQAScore").Text = 0 Or dataItem("colQANumEvals").Text = 0 Then
                    dataItem("colAvgQA").Text = "<a href='#' class='info'><span class='info'>" & dataItem("colSupName").Text & "<br>'Avg. QA'<br>GOAL: " & FormatPercent(m.goal("AvgQA"), 3) & "<hr>Total QA Score: " & dataItem("colQAScore").Text & "<br># QAs: " & dataItem("colQANumEvals").Text & "<hr>" & dataItem("colQAScore").Text & "/" & dataItem("colQANumEvals").Text & "</span>" & FormatPercent(0, 3) & "</a>"
                    dataItem("colAvgQA_Export").Text = FormatPercent(0, 3)
                Else
                    dataItem("colAvgQA").Text = "<a href='#' class='info'><span class='info'>" & dataItem("colSupName").Text & "<br>'Avg. QA'<br>GOAL: " & FormatPercent(m.goal("AvgQA"), 3) & "<hr>Total QA Score: " & dataItem("colQAScore").Text & "<br># QAs: " & dataItem("colQANumEvals").Text & "<hr>" & dataItem("colQAScore").Text & "/" & dataItem("colQANumEvals").Text & "</span>" & FormatPercent(Double.Parse(dataItem("colQAScore").Text) / Integer.Parse(dataItem("colQANumEvals").Text) / 100, 3) & "</a>"
                    dataItem("colAvgQA_Export").Text = FormatPercent(Double.Parse(dataItem("colQAScore").Text) / Integer.Parse(dataItem("colQANumEvals").Text) / 100, 3)
                End If

As you can see, we have a column with a span that creates a popup, and then a column appended with "_Export" That holds just the data.

We adjust the visibility on export as follows:

 Protected Sub btExcel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles DateControl.Export

        rgscorecard.Rebind
        rgScorecard.Columns.FindByUniqueNameSafe("colAvgQA").Visible = False
        rgScorecard.Columns.FindByUniqueNameSafe("colAvgQA_Export").Visible = True
        rgScorecard.ExportSettings.OpenInNewWindow = True
        rgScorecard.ExportSettings.ExportOnlyData = False
        rgScorecard.ExportSettings.FileName = "Sups_Tiers_Scorecard_" & DateControl.SCFrom & "_thru_" & DateControl.SCTo
        rgScorecard.MasterTableView.ExportToExcel()
End Sub

I've tried modifying the ExportOnlyData field, and adding the following as well, nothing seems to work to get the percent formatted. Any help would be greatly appreciated.

    Private Sub rgScorecard_ExcelExportCellFormatting(ByVal source As Object, ByVal e As Telerik.Web.UI.ExcelExportCellFormattingEventArgs) Handles rgScorecard.ExcelExportCellFormatting
        If TypeOf e.FormattedColumn Is GridBoundColumn Then
            Dim column As GridBoundColumn = CType(e.FormattedColumn, GridBoundColumn)
            If column.HeaderText.Contains("%") Then 'ALL REQUIRED COLUMNS CONTAIN THE PERCENT SIGN IN THE HEADER
                column.DataFormatString = "{0:D4}"
            End If
        End If
    End Sub

[Daniel]

Hello Dean,

You can easily format the values in the desired way using the mso-number-format style:
Word/Excel export (HTML-based)

Protected Sub RadGrid1_ExcelExportCellFormatting(source As Object, e As Telerik.Web.UI.ExcelExportCellFormattingEventArgs) Handles RadGrid1.ExcelExportCellFormatting
    If e.FormattedColumn.UniqueName = "myColumnName" Then
        e.Cell.Style("mso-number-format") = "percent"
    End If
End Sub

Best regards,
Daniel
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[JSaultz]

Awesome!! That did it. For others who may have a similar issue, I have found a method that uses this. If there is a specific format that you want, format the data in excel, and save it as a web page. View the source and you can find the specific mso-number-format you want in the style tag. Thanks again.