Excel export number format

10 posts, 2 answers
  1. Alex Dybenko
    Alex Dybenko avatar
    161 posts
    Member since:
    Jan 2005

    Posted 06 Feb 2018 Link to this post

    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

     

  2. Hristo
    Admin
    Hristo avatar
    1522 posts

    Posted 07 Feb 2018 Link to this post

    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.
  3. Alex Dybenko
    Alex Dybenko avatar
    161 posts
    Member since:
    Jan 2005

    Posted 08 Feb 2018 Link to this post

    Hi Hristo,

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

    Thanks,

    Alex

  4. Answer
    Hristo
    Admin
    Hristo avatar
    1522 posts

    Posted 08 Feb 2018 Link to this post

    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.
  5. Alex Dybenko
    Alex Dybenko avatar
    161 posts
    Member since:
    Jan 2005

    Posted 27 Feb 2018 in reply to Hristo Link to this post

    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

     

  6. Hristo
    Admin
    Hristo avatar
    1522 posts

    Posted 01 Mar 2018 Link to this post

    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.
  7. Alex Dybenko
    Alex Dybenko avatar
    161 posts
    Member since:
    Jan 2005

    Posted 05 Mar 2018 in reply to Hristo Link to this post

    Thanks Hristo,

    that makes sense!

    Alex

     

  8. Alex Dybenko
    Alex Dybenko avatar
    161 posts
    Member since:
    Jan 2005

    Posted 07 Mar 2018 in reply to Alex Dybenko Link to this post

    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

     

     

     

     

  9. Answer
    Hristo
    Admin
    Hristo avatar
    1522 posts

    Posted 07 Mar 2018 Link to this post

    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.
  10. Alex Dybenko
    Alex Dybenko avatar
    161 posts
    Member since:
    Jan 2005

    Posted 12 Mar 2018 in reply to Hristo Link to this post

    Thanks Hristo,

    works fine, I have only changed RowScroller to ColumnScroller

    Alex

     

Back to Top