1. Home
  2. Forum
  3. Blazor
  4. When exporting the number format is not maintained. There is also weird text added in the top left box

When exporting the number format is not maintained. There is also weird text added in the top left box

When exporting a pivot into excel the numbers are not formatted the same as the report generated.  I am using Format="###0.0" which gives me for example 2.2.  When exporting I get for example 2.2929382 so it's not rounding up to one decimal point.


When exporting the pivot to either excel or csv I'm getting weird text in the blank cell that's on the top left corner.



13 Replies
MM Manikandan Murugesan Syncfusion Team March 21, 2022 02:10 PM UTC

Hi Jason, 
 
We are unable to reproduce the reported issue that you encountered. Meanwhile, we've prepared a sample for your convenience. 
 
 
If the problem persists, please reproduce it in the provided sample and revert to us (or) send your sample that replicates the problem. This would allow us to investigate the reported problem at our end and provide a solution as soon as possible. 

Regards, 
Manikandan 


JB Jason Bishop March 22, 2022 06:51 PM UTC

It does it when using a calculated field.  I have updated your sample.


Attachment: PivotServerDemo939823091_7959ad63.zip

AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team March 23, 2022 03:39 PM UTC

Hi Jason, 

Calculated fields in a pivot table should always have a unique field name. As a result, please use a unique name for the calculated field to resolve the reported issue at your end. Please see the example code below. 

Code Example: 
 
 
<SfPivotView TValue="ProductDetails> 
    <PivotViewDataSourceSettings DataSource="@dataSource"> 
                   <PivotViewCalculatedFieldSettings> 
            <PivotViewCalculatedFieldSetting Name="Total" Formula="@calcTotalPrice" FormatString="###0.0"> 
       </PivotViewCalculatedFieldSetting> 
        </PivotViewCalculatedFieldSettings> 
    </PivotViewDataSourceSettings> 
</SfPivotView> 
 
@code{ 
    public string calcTotalPrice { get; set; } = "\"" + "Sum(Amount)" + "\""; 
} 
 
Meanwhile, we have prepared a sample in net 6 for your reference. Please find it from below link. 

Also, please refer the following documentation for more details about “CalculatedField”. 

Please let us know if you have any concerns. 

Regards, 
Angelin Faith Sheeba. 


JB Jason Bishop March 23, 2022 11:17 PM UTC

I ran your example.  In your new example the calculated field is not formatted correctly.  It should only have 1 decimal place.




AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team March 24, 2022 06:59 PM UTC

Hi Jason,


Currently, we are analyzing this query at our end, and we will update further details within two business days (March 28, 2022).


Regards,

Angelin Faith Sheeba.


AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team March 28, 2022 07:44 PM UTC

Hi Jason,


The new or existing calculated field can be formatted through its UI and also through code behind. In code-behind, you can set the desired format using the ”PivotViewFormatSettings” property. Furthermore, the "FormatString" property is only used to format the calculated field for OLAP, not relational. Please see the example code below.


Code Example:


 

<SfPivotView TValue="ProductDetails">

    <PivotViewDataSourceSettings>

        <PivotViewFormatSettings>

            <PivotViewFormatSetting Name="Total" Format="###0.0"></PivotViewFormatSetting>

        </PivotViewFormatSettings>

        <PivotViewCalculatedFieldSettings>

            <PivotViewCalculatedFieldSetting Name="Total" Formula="@totalPrice"></PivotViewCalculatedFieldSetting>

        </PivotViewCalculatedFieldSettings>

    </PivotViewDataSourceSettings>

</SfPivotView>


Meanwhile, we have modified your sample for your reference.

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/PivotServerDemo-796517940


Also, please refer the following documentation for more details about “CalculatedField”.


Document: https://blazor.syncfusion.com/documentation/pivot-table/calculated-field#apply-the-format-to-the-calculated-field-values


Please let us know if you have any concerns.


Regards, 

Angelin Faith Sheeba.


JB Jason Bishop March 29, 2022 06:14 PM UTC

I think this is just going off in a tangent.   When a calculated field is included and you format it using ##0.0 to where you only want one decimal place.  That format is not being maintained when exporting to excel.


AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team March 31, 2022 08:26 AM UTC


Hi Jason,


We tested the same scenario you mentioned, and we are unable to reproduce the issue at our end. We've included a video below for your convenience.


Video: https://www.syncfusion.com/downloads/support/directtrac/general/ze/calculatedField-formatting823283790


Meanwhile, we've prepared a sample for the same below.


Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/PivotServerDemo-1980070101


If the problem persists, please reproduce it in the provided sample and revert to us (or) send your sample that replicates the problem. This would allow us to investigate the reported problem at our end and provide a solution as soon as possible.


Regards,

Angelin Faith Sheeba.


JB Jason Bishop March 31, 2022 03:49 PM UTC

I can't run the sample.  


System.IO.DirectoryNotFoundException: 'C:\Users\AngelinFaithSheebaPa\Downloads\PivotServerDemo939823091\blazor\PivotServerDemo\PivotServerDemo\wwwroot\'



AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team April 1, 2022 09:52 AM UTC

Hi Jason,


We have checked the sample provided and we are unable to reproduce the reported problem at our end. However, we believe the problem occurs due to  the Blazor App which we have shared with .Net 6 version, resulting version mismatch at your end. So, we have prepared a sample with .NET 5 version for your convenience. Please find the sample link below.


Sample using .NET 5: https://www.syncfusion.com/downloads/support/directtrac/general/ze/net5-2074014206


Moreover, if you are using .NET6, please check the sample link below.


Sample using .NET 6 : https://www.syncfusion.com/downloads/support/directtrac/general/ze/PivotServerDemo-1055189680


Regards,

Angelin faith Sheeba.


JB Jason Bishop April 1, 2022 02:33 PM UTC

I have uploaded changes to your demo where the issues do happen.  Run project then export to excel.  Values are not formatted and "0.formattedText" is in the top left cell.



Attachment: net52074014206_d5528b58.zip

AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team April 4, 2022 11:25 AM UTC

Hi Jason,


Thanks for the sample. We can able to reproduce the reported problem at our end. We have considered this as a bug “Pivot Table values are not formatted properly while Excel exporting”. And this fix will be available in our 2022, Volume 1 SP release which is estimated to be rolled out in the first week of May 2022. The following link will help you keep track of it.


Feedback: https://www.syncfusion.com/feedback/33917/pivot-table-values-are-not-formatted-properly-while-excel-exporting


Regards,

Angelin Faith Sheeba.


AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team May 16, 2022 06:24 AM UTC

Hi Jason,


We are glad to announce that our Essential Studio 2022 Volume 1 Service Pack Release V20.1.0.55 is rolled out and fix for the issue “Pivot Table values are not formatted properly while Excel exporting” has been included in this release. Please refer to the following sample.


Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/PivotTable(net6.0)1465745714


We thank you for your support and appreciate your patience in waiting for this release. Please contact us if you require any further assistance.


Regards,

Angelin Faith Sheeba


Loader.
Up arrow icon