Hi Team,
Please find the attached solution which populates the data in proper level hierarchy way.
Below is the requirement. Please let me know how it can be achieved
1. Run the application
2. Click export to Excel
3. Data displayed (Here Vehicle Type headers are shown)
Expected Result : Excel Data should be populated for all the hierarchy exposed to the grid with proper outline i.e. Vehicle Name & Rating as with outline as well.
Excel should show data in proper hierarchical format (3 outline should be shown as below)
----------+
-------+
----+
Vehicle Type
Vehicle Name
Rating
Below is the solution i figured out
// Build Column Header foreach (PivotColumnHeaderCell cell in pivotGrid.GridLayout.ColumnHeaderCells.OrderByDescending(x => x.ColumnSpan)) { ColumnSpan = cell.ColumnSpan; ColumnId = pivotGrid.GridLayout.Columns .IndexOf(cell.Column as PivotDataColumn) + LeftHeaderWidth;
RowSpan = cell.RowSpan; RowId = pivotGrid.GridLayout .PrivateRowsForColumnPanel.IndexOf(cell.Row as PivotHeaderRow) + currentRow;
CellValue = cell.Member.Caption; if (!levelNamesList.ContainsKey(cell.Member.LevelName)) { levelNamesList.Add(cell.Member.LevelName, i); i++; } if (cell.Member.IsTotal && cell.IsToggleVisible == false) CellValue += " " + "Total";
if (ColumnSpan > 1 || RowSpan > 1) { sheet.MergedCellsRegions.Add( RowId, ColumnId, RowId + RowSpan - 1, ColumnId + ColumnSpan - 1 );
//Outline for Excel for (int j = ColumnId; j <= ColumnId + ColumnSpan - 2; j++) { sheet.Columns[j].OutlineLevel = levelNamesList.FirstOrDefault(x => x.Key.Equals(cell.Member.LevelName)).Value; sheet.Columns[j].Hidden = true; } } if (CellValue.Equals("N/A")) { sheet.Columns[ColumnId].Hidden = true; continue; } this.SetCellValue(sheet.Rows[RowId].Cells[ColumnId], CellValue, "TopHeader"); }
Thanks,