RadPivotGrid Export to Excel Column Heading

[Steve]

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>

[Viktor Tachev]

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:

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

Regards,
Viktor Tachev
Telerik

[systems]

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

[systems]

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

[Viktor Tachev]

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:

• http://docs.telerik.com/devtools/aspnet-ajax/controls/pivotgrid/functionality/export/excel

Regards,
Viktor Tachev
Telerik

[systems]

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

[Steve]

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

[Viktor Tachev]

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

[Steve]

Thanks Viktor.

 This worked

 

 Steve

[systems]

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

 

[Viktor Tachev]

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

[systems]

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

[Viktor Tachev]

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

[systems]

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

[Viktor Tachev]

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