This is a migrated thread and some comments may be shown as answers.

Export to Excel column formatting

2 Answers 276 Views
Grid
This is a migrated thread and some comments may be shown as answers.
JSaultz
Top achievements
Rank 1
JSaultz asked on 02 Apr 2010, 07:29 PM
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 ObjectByVal 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 ObjectByVal 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 ObjectByVal 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 




2 Answers, 1 is accepted

Sort by
0
Accepted
Daniel
Telerik team
answered on 05 Apr 2010, 04:29 PM
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.
0
JSaultz
Top achievements
Rank 1
answered on 05 Apr 2010, 06:00 PM
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.
Tags
Grid
Asked by
JSaultz
Top achievements
Rank 1
Answers by
Daniel
Telerik team
JSaultz
Top achievements
Rank 1
Share this question
or