RadPivotGrid Export to Excel Column Heading

15 posts, 1 answers
  1. Steve
    Steve avatar
    6 posts
    Member since:
    Feb 2012

    Posted 11 Jan Link to this post

    Hi,

    I have a RadPivotGrid where my  PivotGridColumn is a date field.  When I export to Excel, the column heading comes across as some generic numeric value and not the month name - year format that's displayed in the grid. 

    Is there anyway to fix this?

    Thank you

     Steve

     

    Here is my grid and attached is a sample of the data:

     

    <telerik:RadPivotGrid ID="gvSixMonthSalesReport" runat="server" ShowColumnHeaderZone="false" ShowRowHeaderZone="false" ShowDataHeaderZone="false" EnableZoneContextMenu="false"  ColumnHeaderCellStyle-ForeColor="#003366" EmptyValue="$0.00" >
        <Fields>
            <telerik:PivotGridRowField DataField="GroupID" ZoneIndex="0" IsHidden="true" ></telerik:PivotGridRowField>
            <telerik:PivotGridRowField DataField="GroupName" ZoneIndex="1" ></telerik:PivotGridRowField>
            <telerik:PivotGridRowField DataField="Sales_Person" ZoneIndex="2"></telerik:PivotGridRowField>
            <telerik:PivotGridColumnField DataField="MarginMonth" ZoneIndex="0" DataFormatString="{0:Y}"></telerik:PivotGridColumnField>
            <telerik:PivotGridAggregateField DataField="Margin" Aggregate="Sum" DataFormatString="{0:C}"></telerik:PivotGridAggregateField>
        </Fields>
        <ExportSettings FileName="Six-Month Sales Maging Report" IgnorePaging="true" />
    </telerik:RadPivotGrid>
  2. Viktor Tachev
    Admin
    Viktor Tachev avatar
    1488 posts

    Posted 14 Jan Link to this post

    Hello Steve,

    When Excel is showing a date it usually is formatting the value based on the culture of the machine where the file is opened. If you would like to explicitly specify how the date should be displayed you can use the PivotGridBiffExportnig event and set the style and format for the exported cells.

    Check out the following online example that outlines the approach:



    Regards,
    Viktor Tachev
    Telerik
    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 Feedback Portal and vote to affect the priority of the items
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. systems
    systems avatar
    70 posts
    Member since:
    Dec 2013

    Posted 15 Jan in reply to Viktor Tachev Link to this post

    Hello Victor,

    Following this - http://demos.telerik.com/aspnet-ajax/pivotgrid/examples/exporting/defaultcs.aspx  

    When exporting into excel, the column headers are coming as blank for Country,City,TransportType.

    How can I retain these column headers too in excel sheet after export?

    Thanks,

    Chetan

  5. systems
    systems avatar
    70 posts
    Member since:
    Dec 2013

    Posted 18 Jan in reply to systems Link to this post

    Hello there,

    Is there any way to show column headers in excel during RadPivotGrid export. It seems column headers which are grouped are getting omitted during export process.

    Looking fwd for any workaround,

    Chetan

  6. Viktor Tachev
    Admin
    Viktor Tachev avatar
    1488 posts

    Posted 20 Jan Link to this post

    Hi,

    If you would like to modify the default exported file you can use the PivotGridBifExporting event. The approach is described in the article below:



    Regards,
    Viktor Tachev
    Telerik
    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 Feedback Portal and vote to affect the priority of the items
  7. systems
    systems avatar
    70 posts
    Member since:
    Dec 2013

    Posted 21 Jan Link to this post

    Thanks for your reply Viktor,

    However it doesn't seems to solve our problem. The exported excel is still missing the columns headers for those where grouping is applied. (see attached)

    I am able to reproduce the exact case on this demo link too - 

    http://demos.telerik.com/aspnet-ajax/pivotgrid/examples/exporting/defaultcs.aspx 

    We are looking towards to have the columns headers in excel as well.

    Any thoughts?

    regards,

    Chetan

  8. Steve
    Steve avatar
    6 posts
    Member since:
    Feb 2012

    Posted 25 Jan in reply to Viktor Tachev Link to this post

    Viktor,

    The demo on Telerik's site shows how to add a new worksheet to the exported excel document.  I don't want to add a new worksheet, I want to change the information on the existing worksheet (The one where my PivotGrid is exporting to).  Can I do this?  If so, is there any example code that shows this?

    Thanks

    Steve

  9. Answer
    Viktor Tachev
    Admin
    Viktor Tachev avatar
    1488 posts

    Posted 26 Jan Link to this post

    Hi,

    The example illustrates how you can modify the exported file. Using similar approach you can add information to the current sheet.

    You should handle the PivotGridInfrastructureExporting and add the relevant information to the cells. The following code snippet should get you started.


    protected void RadPivotGrid1_PivotGridInfrastructureExporting(object sender, PivotGridInfrastructureExportingEventArgs e)
    {
        e.ExportStructure.Tables[0].ShiftRowsDown(1, 1);
     
        foreach (var field in RadPivotGrid1.Fields)
        {
            if (!field.IsHidden)
            {
                switch (field.FieldType)
                {
                    case "PivotGridRowField":
                        e.ExportStructure.Tables[0].Cells[1, 2].Value += field.DataField + " ";
                        break;
                    case "PivotGridColumnField":
                        e.ExportStructure.Tables[0].Cells[2, 1].Value += field.DataField + " ";
                        break;
                    case "PivotGridAggregateField":
                        e.ExportStructure.Tables[0].Cells[1, 1].Value += field.DataField + " ";
                        break;
                    default:
                        break;
     
                }
            }
        }
    }


    Regards,
    Viktor Tachev
    Telerik
    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 Feedback Portal and vote to affect the priority of the items
  10. Steve
    Steve avatar
    6 posts
    Member since:
    Feb 2012

    Posted 27 Jan in reply to Viktor Tachev Link to this post

    Thanks Viktor.

     This worked

     

     Steve

  11. systems
    systems avatar
    70 posts
    Member since:
    Dec 2013

    Posted 28 Jan Link to this post

    Hello Viktor, 

    As a follow up of my earlier question- How shall I retain the column headers in excel during export?

    Is there any specific way that i can use to do so? Pls advise

     

  12. Viktor Tachev
    Admin
    Viktor Tachev avatar
    1488 posts

    Posted 28 Jan Link to this post

    Hi,

    In order to set the text in the cells that are marked in the last screenshot you can use the approach from my previous post. With this method you can add the name of the DataField to the corresponding cell.


    Regards,
    Viktor Tachev
    Telerik
    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 Feedback Portal and vote to affect the priority of the items
  13. systems
    systems avatar
    70 posts
    Member since:
    Dec 2013

    Posted 29 Jan in reply to Viktor Tachev Link to this post

    Thanks Viktor, This worked for us too. 

    However, one strange thing I observed is excel is showing blank columns against 0 value in RadPivotGrid. (see attached)

    Excel should reflect the PivotGrid columns values as it is. 

    Are we missing anything here?

    Thanks,

    Chetan

  14. Viktor Tachev
    Admin
    Viktor Tachev avatar
    1488 posts

    Posted 01 Feb Link to this post

    Hello Chetan,

    This is most likely because the fields do not have any value. The zeroes are added via the EmptyValue property of the PivotGrid.

    This property can be used to substitute an empty value with the specified string. However, that does not change the actual value in the cell.

    Regards,
    Viktor Tachev
    Telerik
    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 Feedback Portal and vote to affect the priority of the items
  15. systems
    systems avatar
    70 posts
    Member since:
    Dec 2013

    Posted 02 Feb in reply to Viktor Tachev Link to this post

    Indeed the EmptyValue property is adding zeros. However the fields are blank post column grouping.

    Is there way(probably injecting any event)  to retain zeros during export as well. For some reason we don't want to get blanks in excel.

    Thanks,

    Chetab

  16. Viktor Tachev
    Admin
    Viktor Tachev avatar
    1488 posts

    Posted 03 Feb Link to this post

    Hi Chetab,

    Note that when exporting the actual values are passed to the Excel file. By default it does not display any value in the empty cells.

    If you would like to change the built-in behavior for can iterate through the cells in the exported file. Then, You can check if a cell does not have a value and if so - place a zero in it. The PivotGridInfrastructureExporting event  can be used for adding this custom logic.

    You can set the values for the cells using approach similar to the one illustrated in one of my previous posts.

    Regards,
    Viktor Tachev
    Telerik
    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 Feedback Portal and vote to affect the priority of the items
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017