Excel export number format

[Alex Dybenko]

Hi,

If I apply a Number Format to aggregate values, e.g. #.00 €, this also applies to Print, but not to Export to xlsx (but applies to export to XML). Is is possible to apply number format to xlsx Export as well?

Thanks,

Alex

 

[Hristo]

Hi Alex,

Thank you for writing.

You can handle the PivotGridSpreadExport.CellFormating and depending on the value apply format it and set it to the Text property of the cell. The following documentation article provides an example of how the event can be handled: https://docs.telerik.com/devtools/winforms/pivotgrid/exporting-data/spread-export.  

Let me know if you have other questions.

Regards,
Hristo
Progress Telerik

[Alex Dybenko]

Hi Hristo,

That was my idea as well, but how can I get the  Number Format, which user set using PivotFieldList?

Thanks,

Alex

[Hristo]

Hi Alex,

The present behavior is actually considered an issue. At the moment data cells created out of an aggregation with an applied number format are exported to Excel as text which is not formatted. I have logged the issue on our feedback portal, here: FIX. RadPivotGrid - the data cells of aggregate descriptions with an applied format are exported by the PivotGridSpreadExport class as unformatted text cells. I have also updated your Telerik points. You can subscribe to the item and be updated when its status changes.

Regarding the CellFormatting event, it is a possible solution, however, with certain limitations. You cannot obtain the actual aggregate of the data cell passed in the event arguments. What you can do is validate the Text property of the cell against certain formatting symbols and then set the FormatString property of the cell: 

private void SpreadExport_CellFormatting(object sender, PivotGridSpreadExportCellFormattingEventArgs e)
{
    if (e.Cell.Text.StartsWith("$"))
    {
        e.Cell.FormatString = "$ #.00";
    }
    else if (e.Cell.Text.Contains("€"))
    {
        e.Cell.FormatString = "#.00 €";
    }
}

Let me know if you have other questions.

Regards,
Hristo
Progress Telerik

[Alex Dybenko]

Hi Hristo,

I have another issue: if I have number 12.3 and format it as "0.00" - in Excel I get it as 12.3. Is it possible to pass "12.30" as text to Excel, like you type in Excel '12.30 ? I tried for example:

e.Cell.Value=Nothing:e.Cell.Text="12.30"

or

e.Cell.Value="'12.30"

Perhaps you have other ideas?

 

Alex

 

[Hristo]

Hello Alex,

You can specify the data type of the export cells as a string:

private void SpreadExport_CellFormatting(object sender, PivotGridSpreadExportCellFormattingEventArgs e)
{
    e.Cell.DataType = DataType.String;
}

If you specify a number format string, "#.00" e.g., Excel would still interpret the value as a number and we have no control over that behavior.

Let me know if you have other questions.

Regards,
Hristo
Progress Telerik

[Alex Dybenko]

Thanks Hristo,

that makes sense!

Alex

 

[Alex Dybenko]

Hi,

have another related question - how can I get aggregate for each column in PivotGrid? Something like e.CellElement.AggregateDescription.DisplayName of CellFormatting event.

I have tried CellCreating event, but e.CellElement.Column is nothing there.

Also tried PivotGridGroupTraverser, but not sure how to get AggregateDescription from column name...

My idea is to get each column's aggregate .StringFormat and use if to format Excel after export.

Thanks

Alex

 

 

 

 

[Hristo]

Hello Alex,

The export cells do not provide information about the aggregate of the data cells. A possible solution is to persist a collection of the data rows and then use the RowIndex to get the group and its aggregate index: 

private void radButton2_Click(object sender, EventArgs e)
{

    PivotGridSpreadExport spreadExport = new PivotGridSpreadExport(this.radPivotGrid1);
    spreadExport.ExportVisualSettings = true;
    spreadExport.FileExportMode = FileExportMode.CreateOrOverrideFile;
    spreadExport.CellFormatting += SpreadExport_CellFormatting;
    SpreadExportRenderer renderer = new SpreadExportRenderer();

    PivotGridGroupTraverser rowTraverser = (PivotGridGroupTraverser)this.radPivotGrid1.PivotGridElement.RowScroller.Traverser.GetEnumerator();
    while (rowTraverser.MoveNext())
    {
        PivotGroupNode current = rowTraverser.Current;
        if (spreadExport.ExportSelectionOnly && !this.radPivotGrid1.PivotGridElement.RowContainsSelection(current))
        {
            continue;
        }

        if (!current.IsLeaf && spreadExport.LayoutType == PivotLayout.Tabular)
        {
            continue;
        }

        this.rows.Add(current);
    }
}

private List<PivotGroupNode> rows = new List<PivotGroupNode>();
private void SpreadExport_CellFormatting(object sender, PivotGridSpreadExportCellFormattingEventArgs e)
{
    PivotGridSpreadExport spreadExport = (PivotGridSpreadExport)sender;
    int rowHeaderColumnsCount = (spreadExport.RowsLayoutType == PivotLayout.Tabular) ? this.radPivotGrid1.PivotGridElement.RowDescriptorsArea.Children.Count : 1;
    int row = e.RowIndex - this.radPivotGrid1.PivotGridElement.ColumnDescriptorsArea.Children.Count - 1;
    int column = e.ColumnIndex - rowHeaderColumnsCount;
    if (row >= 0 && column >= 0)
    {
        int aggregate = (int)this.rows[row].Group.GetType().GetProperty("AggregateIndex").GetValue(this.rows[row].Group);
        Console.WriteLine(e.Cell.Text + ": " + aggregate);
    }
}

Let me know if you have other questions.

Regards,
Hristo
Progress Telerik

[Alex Dybenko]

Thanks Hristo,

works fine, I have only changed RowScroller to ColumnScroller

Alex