BuyLog In
Example E2231
Visible to All Users

Pivot Grid for WinForms - How to Create a Custom Exporter for PivotGridControl with the XtraReport Suite

This example illustrates how to create a custom report dynamically based on the PivotGridControl content. This exporter supports the AutoRowHeight, BestFit, and FitToPage features.

Pivot Grid for WinForms - Custom Exporter

This example also implements the scenarios introduced in the following suggestions:

Files to Review

More Examples

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)

Example Code

Report_at_Runtime/Form1.cs(vb) 
C#
using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using DevExpress.XtraReports.UI;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraEditors;

namespace WindowsApplication1 {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
            radioGroup1.Properties.Items[0].Value = ReportGeneratorType.SinglePage;
            radioGroup1.Properties.Items[1].Value = ReportGeneratorType.FixedColumnWidth;
            radioGroup1.Properties.Items[2].Value = ReportGeneratorType.BestFitColumns;
            radioGroup1.EditValue = ReportGeneratorType.SinglePage;
        }
        private void button1_Click(object sender, EventArgs e) {
            XtraReport rep = PivotReportGenerator.GenerateReport(pivotGridControl1, ((ReportGeneratorType)radioGroup1.EditValue), Convert.ToInt32(spinEdit1.EditValue), checkEdit2.Checked);
            rep.ShowPreviewDialog();
        }
        private void Form1_Load(object sender, EventArgs e) {
            this.customerReportsTableAdapter.Fill(this.nwindDataSet.CustomerReports);
            fieldProductName1.FilterValues.ValuesIncluded = fieldProductName1.GetUniqueValues().Take(7).ToArray();
            pivotGridControl1.BestFit();
        }
        private void radioGroup1_SelectedIndexChanged(object sender, EventArgs e) {
            switch (((ReportGeneratorType)((RadioGroup)sender).EditValue)) {
                case ReportGeneratorType.SinglePage:
                    spinEdit1.Enabled = false;
                    checkEdit2.Enabled = false;
                    break;
                case ReportGeneratorType.FixedColumnWidth:
                    spinEdit1.Enabled = true;
                    checkEdit2.Enabled = true;
                    break;

                case ReportGeneratorType.BestFitColumns:
                    spinEdit1.Enabled = false;
                    checkEdit2.Enabled = true;
                    break;
            }
        }
    }
}
Report_at_Runtime/PivotReportGenerator.cs(vb) 
C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using DevExpress.XtraReports.UI;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraEditors;
using System.Text;
using DevExpress.Drawing;
using DevExpress.XtraPrinting;

public static class PivotReportGenerator
{
    public static XtraReport GenerateReport(PivotGridControl pivot, ReportGeneratorType kind, int columnWidth, bool repeatRowHeader)
    {
        XtraReport rep = new XtraReport();
        rep.Landscape = true;
        rep.DataSource = FillDataset(pivot);
        rep.DataMember = ((DataSet)rep.DataSource).Tables[0].TableName;
        InitBands(rep);
        InitStyles(rep);
        InitDetailsBasedonXRTable(rep, kind, columnWidth, repeatRowHeader);
        return rep;
    }
    public static DataSet FillDataset(PivotGridControl pivot)
    {
        DataSet dataSet1 = new DataSet();
        dataSet1.DataSetName = "PivotGridColumns";
        DataTable dataTable1 = new DataTable();
        dataSet1.Tables.Add(dataTable1);
        FillDatasetColumns(pivot, dataTable1);
        FillDatasetExtracted(pivot, dataTable1);

        return dataSet1;
    }
    #region PreparingDataSet
    private static void FillDatasetExtracted(PivotGridControl pivot, DataTable dataTable1)
    {
        List<object> rowvalues = new List<object>();
        string tempRowText = "";
        List<PivotGridField> fieldsInRowArea = GetFieldsInArea(pivot, PivotArea.RowArea);
        for (int i = 0; i < pivot.Cells.RowCount; i++)
        {
            PivotCellEventArgs pcea = pivot.Cells.GetCellInfo(0, i);
            if (pcea.RowValueType == PivotGridValueType.Value)
            {
                foreach (PivotGridField item in fieldsInRowArea)
                    tempRowText += pcea.GetFieldValue(item).ToString() + " | ";//add formatting if it's necessary
                tempRowText = tempRowText.Remove(tempRowText.Length - 3, 3);
            }
            else
                tempRowText = pcea.RowValueType.ToString();
            rowvalues.Clear();
            rowvalues.Add(tempRowText);
            tempRowText = "";
            for (int j = 0; j < pivot.Cells.ColumnCount; j++)
            {
                pcea = pivot.Cells.GetCellInfo(j, i);
                if (pcea.Value != null)
                    rowvalues.Add(pcea.Value);
                else
                    rowvalues.Add(DBNull.Value);
            }
            dataTable1.Rows.Add(rowvalues.ToArray());
        }
    }
    private static void FillDatasetColumns(PivotGridControl pivot, DataTable dataTable1)
    {
        dataTable1.Columns.Add("RowFields", typeof(string));
        StringBuilder sb = new StringBuilder();
        List<PivotGridField> fieldsInColumnArea = GetFieldsInArea(pivot, PivotArea.ColumnArea);

        bool multipleDataField = pivot.GetFieldsByArea(PivotArea.DataArea).Count > 1;
        for (int i = 0; i < pivot.Cells.ColumnCount; i++)
        {
            PivotCellEventArgs pcea = pivot.Cells.GetCellInfo(i, 0);
            foreach (PivotGridField field in pcea.GetColumnFields())
                sb.AppendFormat("{0} | ", field.GetDisplayText( pcea.GetFieldValue(field) ));//add formatting if it's necessary
            if (multipleDataField)
                sb.AppendFormat("{0} | ", pcea.DataField);
            if (pcea.ColumnValueType == PivotGridValueType.Value)
                sb.Remove(sb.Length - 3, 3);
            else
                sb.Append(pcea.ColumnValueType.ToString());
            dataTable1.Columns.Add(sb.ToString(), typeof(object));
            sb.Clear();
        }
    }
    private static List<PivotGridField> GetFieldsInArea(PivotGridControl pivot, PivotArea area)
    {
        List<PivotGridField> fields = new List<PivotGridField>();
        for (int i = 0; i < pivot.Fields.Count; i++)
            if (pivot.Fields[i].Area == area)
                fields.Add(pivot.Fields[i]);
        return fields;
    }
    #endregion
    public static void InitBands(XtraReport rep) {
        // Create bands
        DetailBand detail = new DetailBand();
        PageHeaderBand pageHeader = new PageHeaderBand();
        ReportFooterBand reportFooter = new ReportFooterBand();
        detail.Height = 20;
        reportFooter.Height = 380;
        pageHeader.Height = 20;

        // Place the bands onto a report
        rep.Bands.AddRange(new Band[] { detail, pageHeader, reportFooter });
    }
    public static void InitStyles(XtraReport rep) {
        // Create different odd and even styles
        XRControlStyle oddStyle = new XRControlStyle();
        XRControlStyle evenStyle = new XRControlStyle();

        // Specify the odd style appearance
        oddStyle.BackColor = System.Drawing.Color.LightBlue;
        oddStyle.StyleUsing.UseBackColor = true;
        oddStyle.StyleUsing.UseBorders = false;
        oddStyle.Name = "OddStyle";

        // Specify the even style appearance
        evenStyle.BackColor = System.Drawing.Color.LightPink;
        evenStyle.StyleUsing.UseBackColor = true;
        evenStyle.StyleUsing.UseBorders = false;
        evenStyle.Name = "EvenStyle";

        // Add styles to report's style sheet
        rep.StyleSheet.AddRange(new XRControlStyle[] { oddStyle, evenStyle });
    }
    public static void InitDetailsBasedonXRTable(XtraReport rep, ReportGeneratorType kind, float columnWidth, bool repeatRowHeader) {
        if (!repeatRowHeader || kind == ReportGeneratorType.SinglePage)
            InitDetailsBasedonXRTableWithoutRepeatingRowHeader(rep, kind, columnWidth);
        else
            InitDetailsBasedonXRTableRepeatingRowHeader(rep, kind, columnWidth);
    }

    static void InitDetailsBasedonXRTableRepeatingRowHeader(XtraReport rep, ReportGeneratorType kind, float columnWidth) {
        DXFont font = new DXFont("Tahoma", 9.75f);
        DataTable dataTable = ((DataSet)rep.DataSource).Tables[0];
        int processedPage = 0;
        float usablePageWidth = rep.PageWidth - (rep.Margins.Left + rep.Margins.Right);

        List<float> columnsWidth = null;
        if (kind == ReportGeneratorType.FixedColumnWidth)
            columnsWidth = DefineColumnsWidth(columnWidth, dataTable.Columns.Count);
        else
            columnsWidth = GetColumnsBestFitWidth(dataTable, font, rep.ReportUnit);

        XRTable tableHeader = null;
        XRTable tableDetail = null;
        InitNewTableInstancesAt(rep, font, out tableHeader, out tableDetail, new PointF(0, 0));
        tableHeader.BeginInit();
        tableDetail.BeginInit();
        int i = 1;
        AddCellsToTables(tableHeader, tableDetail, dataTable.Columns[0], columnsWidth[0], true);
        float remainingSpace = usablePageWidth - columnsWidth[0];
        do {
            if (columnsWidth[i] > remainingSpace) {
                processedPage++;
                tableHeader.WidthF = usablePageWidth - remainingSpace;
                tableDetail.WidthF = usablePageWidth - remainingSpace;
                tableHeader.EndInit();
                tableDetail.EndInit();
                InitNewTableInstancesAt(rep, font, out tableHeader, out tableDetail, new PointF(usablePageWidth * processedPage, 0));
                tableHeader.BeginInit();
                tableDetail.BeginInit();
                AddCellsToTables(tableHeader, tableDetail, dataTable.Columns[0], columnsWidth[0], true);
                remainingSpace = usablePageWidth - columnsWidth[0];
            }
            else {
                AddCellsToTables(tableHeader, tableDetail, dataTable.Columns[i], columnsWidth[i], false);
                remainingSpace -= columnsWidth[i];
                i++;
            }
        }
        while (i < columnsWidth.Count);
        tableHeader.WidthF = usablePageWidth - remainingSpace;
        tableDetail.WidthF = usablePageWidth - remainingSpace;
        tableHeader.EndInit();
        tableDetail.EndInit();
    }
    public static void AddCellsToTables(XRTable header, XRTable detail, DataColumn dc, float columnWidth, bool isFirstColumnInTable) {
        XRTableCell headerCell = new XRTableCell();
        headerCell.Text = dc.Caption;
        XRTableCell detailCell = new XRTableCell();
        detailCell.DataBindings.Add("Text", null, dc.Caption);
        headerCell.WidthF = columnWidth;
        detailCell.WidthF = columnWidth;
        if (isFirstColumnInTable) {
            headerCell.Borders = DevExpress.XtraPrinting.BorderSide.Left | DevExpress.XtraPrinting.BorderSide.Top | DevExpress.XtraPrinting.BorderSide.Bottom;
            detailCell.Borders = DevExpress.XtraPrinting.BorderSide.Left | DevExpress.XtraPrinting.BorderSide.Top | DevExpress.XtraPrinting.BorderSide.Bottom;
        }
        else {
            headerCell.Borders = DevExpress.XtraPrinting.BorderSide.All;
            detailCell.Borders = DevExpress.XtraPrinting.BorderSide.All;
        }
        // Place the cells into the corresponding tables
        header.Rows[0].Cells.Add(headerCell);
        detail.Rows[0].Cells.Add(detailCell);
    }
    public static void InitNewTableInstancesAt(XtraReport report, DXFont font, out XRTable header, out XRTable detail, PointF location) {
        header = InitXRTable(font, false);
        detail = InitXRTable(font, true);
        header.LocationF = location;
        detail.LocationF = location;
        XRTableRow headerRow = new XRTableRow();
        header.Rows.Add(headerRow);
        XRTableRow detailRow = new XRTableRow();
        detail.Rows.Add(detailRow);
        report.Bands[BandKind.PageHeader].Controls.Add(header);
        report.Bands[BandKind.Detail].Controls.Add(detail);
    }
    static XRTable InitXRTable(DXFont font, bool withStyles) {
        XRTable table = new XRTable();
        table.Font = font;
        table.Height = 20;
        if (withStyles) {
            table.EvenStyleName = "EvenStyle";
            table.OddStyleName = "OddStyle";
        }
        return table;
    }

    static List<float> DefineColumnsWidth(float columnWidth, int count) {
        List<float> columnsWidth = new List<float>();
        for (int i = 0; i < count; i++)
            columnsWidth.Add(columnWidth);
        return columnsWidth;
    }
    static void InitDetailsBasedonXRTableWithoutRepeatingRowHeader(XtraReport rep, ReportGeneratorType kind, float columnWidth) {
        DXFont font = new DXFont("Tahoma", 9.75f);
        DataSet ds = ((DataSet)rep.DataSource);
        int colCount = ds.Tables[0].Columns.Count;
        float colWidth = 0;



        XRTable tableHeader = null;
        XRTable tableDetail = null;
        InitNewTableInstancesAt(rep, font, out tableHeader, out tableDetail, new PointF(0, 0));


        List<float> columnsWidth = null;
        switch (kind) {
            case ReportGeneratorType.FixedColumnWidth:
                colWidth = columnWidth;
                tableHeader.WidthF = columnWidth * colCount;
                tableDetail.WidthF = columnWidth * colCount;
                break;
            case ReportGeneratorType.BestFitColumns:
                columnsWidth = GetColumnsBestFitWidth(ds.Tables[0], font, rep.ReportUnit);
                colWidth = 0;
                tableHeader.WidthF = GetTotalWidth(columnsWidth);
                tableDetail.WidthF = tableHeader.Width;
                break;
            default:
                colWidth = (rep.PageWidth - (rep.Margins.Left + rep.Margins.Right)) / colCount;
                tableHeader.WidthF = (rep.PageWidth - (rep.Margins.Left + rep.Margins.Right));
                tableDetail.WidthF = (rep.PageWidth - (rep.Margins.Left + rep.Margins.Right));
                break;
        }

        tableHeader.BeginInit();
        tableDetail.BeginInit();
        // Create table cells, fill the header cells with text, bind the cells to data
        for (int i = 0; i < colCount; i++) {
            AddCellsToTables(tableHeader, tableDetail, ds.Tables[0].Columns[i], kind == ReportGeneratorType.BestFitColumns ? columnsWidth[i] : colWidth, i == 0 ? true : false);
        }
        tableDetail.EndInit();
        tableHeader.EndInit();
        // Place the table onto a report's Detail band

    }

    static float GetTotalWidth(List<float> columnsWidth) {
        float i = 0;
        foreach (float colWidth in columnsWidth)
            i += colWidth;
        return i;
    }

    static List<float> GetColumnsBestFitWidth(DataTable dataTable, DXFont font, ReportUnit unit) {
        List<float> optimalColumnWidth = new List<float>();
        float maxWidth = 0;
        float tempWidth = 0;
        for (int i = 1; i < dataTable.Rows.Count; i++) {
            tempWidth = MeasureWidth(dataTable.Rows[i][0].ToString(), font, unit);
            maxWidth = maxWidth > tempWidth ? maxWidth : tempWidth;
        }
        optimalColumnWidth.Add(maxWidth);
        for (int i = 1; i < dataTable.Columns.Count; i++) {
            tempWidth = MeasureWidth(dataTable.Columns[i].ColumnName.ToString(), font, unit);
            maxWidth = 50 > tempWidth ? 50 : tempWidth;
            optimalColumnWidth.Add(maxWidth);
        }
        return optimalColumnWidth;
    }

    static float MeasureWidth(string candidate, DXFont font, ReportUnit unit) {
        return BestSizeEstimator.GetBoundsToFitText(candidate, new BrickStyle() { Font = font }, unit).Width;
    }
}
public enum ReportGeneratorType
{
    SinglePage,
    FixedColumnWidth,
    BestFitColumns,
}
Report_at_Runtime/Program.cs(vb) 
C#
using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace WindowsApplication1 {
    static class Program {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.