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

RadPivotGrid Export to Excel Column Heading

14 Answers 361 Views
PivotGrid
This is a migrated thread and some comments may be shown as answers.
Steve
Top achievements
Rank 1
Steve asked on 11 Jan 2016, 12:26 PM

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>

14 Answers, 1 is accepted

Sort by
0
Viktor Tachev
Telerik team
answered on 14 Jan 2016, 11:03 AM
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
0
systems
Top achievements
Rank 1
answered on 15 Jan 2016, 09:19 AM

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

0
systems
Top achievements
Rank 1
answered on 18 Jan 2016, 09:53 AM

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

0
Viktor Tachev
Telerik team
answered on 20 Jan 2016, 08:12 AM
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
0
systems
Top achievements
Rank 1
answered on 21 Jan 2016, 03:29 PM

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

0
Steve
Top achievements
Rank 1
answered on 25 Jan 2016, 05:22 PM

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

0
Accepted
Viktor Tachev
Telerik team
answered on 26 Jan 2016, 09:59 AM
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
0
Steve
Top achievements
Rank 1
answered on 27 Jan 2016, 03:27 PM

Thanks Viktor.

 This worked

 

 Steve

0
systems
Top achievements
Rank 1
answered on 28 Jan 2016, 10:30 AM

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

 

0
Viktor Tachev
Telerik team
answered on 28 Jan 2016, 01:53 PM
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
0
systems
Top achievements
Rank 1
answered on 29 Jan 2016, 07:23 AM

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

0
Viktor Tachev
Telerik team
answered on 01 Feb 2016, 02:28 PM
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
0
systems
Top achievements
Rank 1
answered on 02 Feb 2016, 10:01 AM

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

0
Viktor Tachev
Telerik team
answered on 03 Feb 2016, 01:03 PM
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
Tags
PivotGrid
Asked by
Steve
Top achievements
Rank 1
Answers by
Viktor Tachev
Telerik team
systems
Top achievements
Rank 1
Steve
Top achievements
Rank 1
Share this question
or