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

Excel export number format

9 Answers 126 Views
PivotGrid and PivotFieldList
This is a migrated thread and some comments may be shown as answers.
Alex Dybenko
Top achievements
Rank 2
Alex Dybenko asked on 06 Feb 2018, 08:36 AM

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

 

9 Answers, 1 is accepted

Sort by
0
Hristo
Telerik team
answered on 07 Feb 2018, 03:06 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Alex Dybenko
Top achievements
Rank 2
answered on 08 Feb 2018, 09:53 AM

Hi Hristo,

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

Thanks,

Alex

0
Accepted
Hristo
Telerik team
answered on 08 Feb 2018, 04:32 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Alex Dybenko
Top achievements
Rank 2
answered on 27 Feb 2018, 01:31 PM

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

 

0
Hristo
Telerik team
answered on 01 Mar 2018, 01:48 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Alex Dybenko
Top achievements
Rank 2
answered on 05 Mar 2018, 07:25 AM

Thanks Hristo,

that makes sense!

Alex

 

0
Alex Dybenko
Top achievements
Rank 2
answered on 07 Mar 2018, 02:21 PM

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

 

 

 

 

0
Accepted
Hristo
Telerik team
answered on 07 Mar 2018, 04:27 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Alex Dybenko
Top achievements
Rank 2
answered on 12 Mar 2018, 08:30 AM

Thanks Hristo,

works fine, I have only changed RowScroller to ColumnScroller

Alex

 

Tags
PivotGrid and PivotFieldList
Asked by
Alex Dybenko
Top achievements
Rank 2
Answers by
Hristo
Telerik team
Alex Dybenko
Top achievements
Rank 2
Share this question
or