Workaround: Use the new PivotGridSpreadExport class: http://www.telerik.com/help/winforms/pivotgrid-export-srpead-export.html
To reproduce: 1. Add RadPivotGrid on the form and populate it with data 2. Add 2 buttons to export to xls and xlsx formats 3. Run the form and export to xls format 4. Minimize the form. You will see that one of row is disappeared 5. Repeat the same action and you will see that a second row is disappeared. The issue if observed with both exporter providers (PivotGridSpreadExport/PivotExportToExcelML) and export with visual settings. Workaround: In the handler of the SizeChanged event clear the cached elements: void Form1_SizeChanged(object sender, EventArgs e) { this.radPivotGrid1.PivotGridElement.PivotRowsContainer.ElementProvider.ClearCache(); }
To reproduce: Set the decimal separator to comma(,) in your regional settings and export the RadPivotGrid using PivotExportToExcellML. Open the exported file and you will see that the font size is very big. Workaround: Subscribe to the PivotExcelCellFormatting event and set the font size to an integer value: void excelExporter_PivotExcelCellFormatting(object sender, ExcelPivotCellExportingEventArgs e) { e.Cell.Font = new Font(e.Cell.Font.Name, (int)e.Cell.Font.Size, e.Cell.Font.Style); }
To reproduce: Resize some of the columns in PivotGrid and then export it with PivotExporttoExcelML. All columns in exported file have the same width. Workaround: Use PivotGridSpreadExport.
To reproduce: In RadPivotGrid set some cell values to start with "=" or "-". Export it using PivotGridSpreadExport. The exporter tries to convert the value to formula and if this is not successful an exception is thrown. Workaround: private void radButtonNewPivotToExcel_Click(object sender, EventArgs e) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; if (saveFileDialog.ShowDialog() == DialogResult.OK) { SpreadExportRenderer renderer = new SpreadExportRenderer(); MyPivotSpreadExport exporter = new MyPivotSpreadExport(this.radPivotGrid1, renderer); exporter.ExportFormat = SpreadExportFormat.Xlsx; exporter.RunExportAsync(saveFileDialog.FileName + ".xlsx", renderer); } } public class MyPivotSpreadExport : PivotGridSpreadExport { private ISpreadExportRenderer spreadExportRenderer; public MyPivotSpreadExport(RadPivotGrid pivotGrid, ISpreadExportRenderer renderer) : base(pivotGrid) { this.spreadExportRenderer = renderer; } protected override void AddRowToWorksheet(PivotGridExportRowElement rowElement) { foreach (PivotGridSpreadExportCellElement exportCell in rowElement.Cells) { this.spreadExportRenderer.CreateCellSelection(rowElement.Index, exportCell.ColumnIndex); Color borderColor = this.ShowGridLines ? exportCell.BorderColor : Color.Transparent; this.spreadExportRenderer.CreateCellStyleInfo(exportCell.BackColor, exportCell.ForeColor, exportCell.Font.FontFamily, exportCell.Font.Size, exportCell.Font.Bold, exportCell.Font.Italic, exportCell.Font.Underline, exportCell.TextAlignment, exportCell.TextWrap, BorderBoxStyle.SingleBorder, borderColor, Color.Empty, Color.Empty, Color.Empty, Color.Empty); if (exportCell.DataType == DataType.String || exportCell.DataType == DataType.Other) { this.spreadExportRenderer.SetCellSelectionFormat("@"); } this.spreadExportRenderer.SetCellSelectionValue(exportCell.DataType, exportCell.Value ?? exportCell.Text); } } }
Argument exception when a specific layout is loaded: "An item with the same key has already been added."
Wokaround: Private Sub GroupDescriptorElementCreating(sender As Object, e As Telerik.WinControls.UI.GroupDescriptorElementCreatingEventArgs) RemoveHandler e.GroupDescriptorElement.FilterPopup.TextBoxMenuItem.TextBox.TextChanged, AddressOf TextBoxMenuItemTextChanged AddHandler e.GroupDescriptorElement.FilterPopup.TextBoxMenuItem.TextBox.TextChanged, AddressOf TextBoxMenuItemTextChanged End Sub Private Sub TextBoxMenuItemTextChanged(sender As Object, e As EventArgs) Dim tb As RadTextBox = TryCast(sender, RadTextBox) Dim popup As PivotGroupFilterPopup = TryCast(tb.Parent, PivotGroupFilterPopup) popup.TreeViewMenuItem.TreeElement.TreeView.EndUpdate() End Sub
Workaround: public Form1() { InitializeComponent(); this.radPivotGrid1.GroupDescriptorElementCreating += radPivotGrid1_GroupDescriptorElementCreating; } private void radPivotGrid1_GroupDescriptorElementCreating(object sender, Telerik.WinControls.UI.GroupDescriptorElementCreatingEventArgs e) { e.GroupDescriptorElement.FilterPopup.PopupOpened -= FilterPopup_PopupOpened; e.GroupDescriptorElement.FilterPopup.PopupOpened += FilterPopup_PopupOpened; } private void FilterPopup_PopupOpened(object sender, EventArgs args) { PivotGroupFilterPopup filterPopup = (PivotGroupFilterPopup)sender; RadTreeView tree = filterPopup.TreeViewMenuItem.TreeElement.TreeView; ((RadScrollBarElement)tree.TreeViewElement.Children[2]).Value = 10; ((RadScrollBarElement)tree.TreeViewElement.Children[2]).Value = 0; }
Workaround: private void radPivotGrid1_GroupDescriptorElementCreating(object sender, Telerik.WinControls.UI.GroupDescriptorElementCreatingEventArgs e) { e.GroupDescriptorElement.FilterPopup.PopupOpened -= FilterPopup_PopupOpened; e.GroupDescriptorElement.FilterPopup.PopupOpened += FilterPopup_PopupOpened; } private void FilterPopup_PopupOpened(object sender, EventArgs args) { PivotGroupFilterPopup filterPopup = (PivotGroupFilterPopup)sender; RadTreeView tree = filterPopup.TreeViewMenuItem.TreeElement.TreeView; int height = this.CalculateHeight(tree); if (height > tree.Height) { ((RadScrollBarElement)tree.TreeViewElement.Children[2]).Value = 0; ((RadScrollBarElement)tree.TreeViewElement.Children[2]).Maximum = height; ((RadScrollBarElement)tree.TreeViewElement.Children[2]).Visibility = Telerik.WinControls.ElementVisibility.Visible; } } private int CalculateHeight(RadTreeView tree) { int height = 0; for (int i = 0; i < tree.Nodes[0].Nodes.Count + 1; i++) { height += tree.ItemHeight; } return height; }
To reproduce: 1. Add a RadPivotGrid and change its theme to TelerikMetro. This theme uses Segoe UI font for its cells. 2. Export the RadPivotGrid. 3. You will notice that the font is applied to some of the cells, but for others, e.g. Grand Total the default font is set to Microsoft Sans Serif Second scenario: use Demo application PivotGrid >> Export to Excel example. Use ControlDefault theme. 1. Ctrl + H to show the spy. Check font for row "Jun-1 >> Sum of Net", column "Printer stand >> 1 Free with 10". The font is Segoe UI. 2. Run the export and check the same cell in Excel. Its font is Microsoft Sans Serif. Workaround: use the PivotExcelCellFormatting event to specify the font.
Workaround: PivotExportToExcelML.ExportVisualSettings = false;
To reproduce: - Add calculated field - Try to set its number format, by clicking the Number Format option in the context menu in the field list (see image) Workaround: Private Sub RadPivotGrid1_CellFormatting(sender As Object, e As UI.PivotCellEventArgs) Handles RadPivotGrid1.CellFormatting If e.CellElement.Column.Name = "Commission" Then e.CellElement.Text = String.Format(System.Threading.Thread.CurrentThread.CurrentUICulture, "{0:C}", e.CellElement.Value) End If End Sub
Change the Decimal symbol/separator to "," and run Excel export for the RadPivotGrid. Note that all "0,00" cells are displayed as "1" in Excel and all decimal values are displayed without decimal separator. Another scenario is when you change the Format in the Regional settings to Russia. Then, when exporting data, the decimal separator for the GrandTotal column is not correct ".", instead of ",". However, for the data cells it is OK.
Workaround: void exporter_PivotExcelCellFormatting(object sender, ExcelPivotCellExportingEventArgs e) { double d; if (double.TryParse(e.Cell.Text, out d)) { e.DataType = "double"; } }
If you have an object with string property and for all the available objects in the RadPivotgrid you have numeric values for this property with leading zero digits (e.g. "002"), it is exported in Excel as a numeric cell.
To reproduce: use the following code snippet. It is reproducible in target framework less than .NET Framework 4.0. Sub New() InitializeComponent() Me.RadPivotGrid1.RowGroupDescriptions.Add(New DoubleGroupDescription() With { _ .PropertyName = "EmployeeID", _ .GroupComparer = New GroupNameComparer() _ }) UpdatePivotGrid() End Sub Private Sub UpdatePivotGrid() Dim ds As DataSet = New DataSet Dim conn As New SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True") Dim cmd As New SqlCommand("SELECT * FROM Orders", conn) conn.Open() Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) da.Fill(ds) ds.Dispose() Me.RadPivotGrid1.DataSource = ds.Tables(0) Me.RadPivotGrid1.Refresh() End Sub Please refer to the attached gif file. Workaround : use .NET Framework 4.0.
To reproduce: Create a RadPivotGrid with some data and export it by following the article http://www.telerik.com/help/winforms/pivotgrid-export-to-excel.html Set the following settings to the exporter: exporter.ShowGridLines = true; exporter.ExportVisualSettings = true; exporter.HeaderCellsFont = new System.Drawing.Font("Arial", 8); exporter.DataCellsFont = new System.Drawing.Font("Arial", 8); exporter.GridLinesColor = Color.Black; exporter.CellBackColor = Color.White; exporter.SheetName = this.ClientManager.CubeEngine.Title; exporter.ExportFlatData = false; Also subscribe to the PivotExcelCellFormatting event use the following code: e.Cell.TextAlignment = ContentAlignment.MiddleRight; e.Cell.DrawBorder = true; e.Cell.BorderColor = Color.Black; e.Cell.DrawText = true; e.Cell.DrawFill = true; You will see that the exported cells will not have borders, nor their font will be changed. Additionally, if you set a specific ForeColor, BackColor, it is not applied as well.
Workaround: private void radButton1_Click(object sender, EventArgs e) { radPivotGrid1.RowGroupsExpandBehavior = new GroupsExpandBehavior() { Expanded = true }; radPivotGrid1.ColumnGroupsExpandBehavior = new GroupsExpandBehavior() { Expanded = true }; radPivotGrid1.DataProvider.Refresh(); ExportTo.ExportToExcel(this); radPivotGrid1.RowGroupsExpandBehavior = new GroupsExpandBehavior() { Expanded = false }; radPivotGrid1.ColumnGroupsExpandBehavior = new GroupsExpandBehavior() { Expanded = false }; radPivotGrid1.DataProvider.Refresh(); }