How to format cells with 1 decimal point

[Dan]

Pivot table has AggregateDescription as:

' Cells
dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With { _
 .PropertyName = "IVResp", _
 .AggregateFunction = AggregateFunctions.Count, _
.TotalFormat = New PercentOfColumnTotal(), .CustomName = "Percentage of Responses", .StringFormat = "#0.0" _
})

Currently, the cells are showing like "20.52%". I want to round them to 1 decimal point: "20.5%". Would really appreciate some direction on this if anyone knows. Thanks.

[George]

Hi Dan,

Thank you for contacting us.

You can use the CellFormatting event of RadPivotGrid to format the text of the cells as you want. You can read more about this event of the following url: http://www.telerik.com/help/winforms/pivotgrid-formatting-appearance.html.

I hope this helps.

Regards,
George
Telerik

[Enrico]

Please,
I cannot find a way to format Data Cell. When I export Winforms RadPivotGrid1 to Microsoft Excel, data is always formatted as text, but when I want numeric values I don't know how to change format.
I mean: format equal to integer or decimal value.

Regards,
Enrico

[Stefan]

Hello Enrico,

Currently, the export to excel functionality of RadPivotGrid uses the Excel ML format, which means that we export in an excel compatible XML format. I have tested the exporter and all numeric values are being exported as such - you can check this by opening the exported file with a text editor and find some numeric value. Here is an example:

<Cell ss:StyleID="CellStyle5">
<Data ss:Type="Number">1060.67</Data></Cell>

We do have plans on introducing a new exporter utilizing our SpreadProcessing libraries, which will allow exporting to native excel format and fine grane customization of the exported file. A feature request is available here, make sure you cast your vote for it by hitting the like button: http://feedback.telerik.com/Project/154/Feedback/Details/110787.

I hope that you find this information useful.

Regards,
Stefan
Telerik

 

 

[Enrico]

Thanks Stefan,
I followed your advice, I opened xls, but all my data are formatted as "String".
My data source is a table adapter, "xsd" file, the source is a SQL Server table, a very simple select.
The Sql Server table is designed well, numeric fields are integer or float or decimal.
So I try to change the sql select in Visual Studio, using CAST or CONVERT, but nothing is changed.
All the columns are always as string...

Example:
<Cell ss:StyleID="CellStyle1">
<Data ss:Type="String">50</Data></Cell>

<Cell ss:StyleID="CellStyle3">
<Data ss:Type="String">1980,00</Data></Cell>
(My country use "," as decimal separator and "." as thousands separator.

Please help me !!!

[Stefan]

Hi Dan,

Thank you for the clarification.

I managed to reproduce the described case and I can confirm it is an issue. I have logged it in our feedback portal. You can add your vote and subscribe for status updates here: http://feedback.telerik.com/Project/154/Feedback/Details/152745.

We will try to address this for the upcoming service pack. Meanwhile, you can use the PivotExcelCellFormatting event to manually set the data type of the desired cells:

private void radButton1_Click(object sender, EventArgs e)
{
    PivotExportToExcelML exporter = new PivotExportToExcelML(radPivotGrid1);
    exporter.PivotExcelCellFormatting += exporter_PivotExcelCellFormatting;
    exporter.RunExport("C:\\temp\\asd.xls");
}

void exporter_PivotExcelCellFormatting(object sender, ExcelPivotCellExportingEventArgs e)
{
    double d;
    if (double.TryParse(e.Cell.Text, out d))
    {
        e.DataType = "double";
    }
}

Your Telerik Points have been updated for this report. 

Should you have any other questions or suggestions, do not hesitate to contact us.

Regards,
Stefan
Telerik

 

 

[Tapakah]

Hi. Plz help: how to in

void exporter_PivotExcelCellFormatting(object sender, ExcelPivotCellExportingEventArgs e)
{

}

set cell width? or set cell avto fit?

[Stefan]

Hi TaPaKaH,

May I please ask you to open up a new thread with your question, as it is no related to the initial subject of this thread. We are trying to keep one subject per thread.

Thank you for the understanding. 

Regards,
Stefan
Telerik