Forums Ultimate UI for WPF
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Pratik
305
Export To Excel - With hierarchy
posted

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

SampleDynamicData.zip
  • 305
    Verified Answer
    posted

    Hi Team,

    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,

  • 305
    posted

    Please help me with this at earliest. Its needed urgently.

    I want to expand all hierarchies before exporting. 

    and the excel should show proper outline for all hierarchy levels.

    Sincerely,

    Vish