Our clients have been constantly configuring pivot grid layouts by dropping least three dimensions into the row fields drop zone (RowFieldsDropAreaControl).
Seeing it in the pivot grid is fine, because those row field indicators are on top, in other words, they know what each column represents in the row field.
However, in exporting to excel, only the row values show up and there's no header to indicate which row fields or dimensions the data represents. This gets a lot more confusing specially that one of the selected row fields is hierarchical in nature (date). When exported with selected nodes being expanded, it's really hard to know what fields they represent.
How can we make the selected row's fields show as headers in the exported excel spreadsheet?
Hello,
Thank you for your feedback. I am glad that you resolved your issue and I believe that other community members may benefit from this as well.
Thanks again.
Thanks for the direction. In the CreateWorkbook method from the link you gave me, I added the snippet (where indicated) inside the block of the if(includeRowHeaders):
if (includeRowHeaders) { // Build Row Header foreach (PivotRowHeaderCell cell in pivotGrid.GridLayout.RowHeaderCells) { ColumnSpan = cell.ColumnSpan; ColumnId = pivotGrid.GridLayout.PrivateColumnsForRowPanel.IndexOf(cell.Column as PivotHeaderColumn);
RowSpan = cell.RealRowSpan; RowId = pivotGrid.GridLayout.Rows.IndexOf(cell.Row as PivotDataRow) + TopHeaderHeight;//+1;
CellValue = cell.Member.Caption; //todo: place string in resource file if (cell.Member.IsTotal && cell.IsToggleVisible == false) CellValue += " " + "Total";
if (ColumnSpan > 1 || RowSpan > 1) { sheetOne.MergedCellsRegions.Add(RowId, ColumnId, RowId + RowSpan - 1, ColumnId + ColumnSpan - 1); } SetCellValue(sheetOne.Rows[RowId].Cells[ColumnId], CellValue, "LeftHeader");
//START SNIPPET //Build Headers for the Row Headers if (RowId == (TopHeaderHeight)) //if the current cell is the first row of the row header { if (ColumnSpan > 1) { sheetOne.MergedCellsRegions.Add(RowId - 1, ColumnId, RowId - 1, ColumnId + ColumnSpan - 1); } SetCellValue(sheetOne.Rows[RowId - 1].Cells[ColumnId], cell.Member.ParentLevel.ParentHierarchy.ParentDimension.Caption, "TopHeader"); } //END SNIPPET
} }
Thank you for your post. I have been looking into it and I can suggest you see the sample in the Samples Browser under xamPivotGrid / Data / Export to Excel section, where it is shown how to export to Excel. Since there is no built in functionality for this and the Workbook is created manually, you can add whatever Cells you want to the sheet. In the sample you can see how to add or not the Row and Column headers.
Hope this helps you.