NPOI Real World Pivot Example

Question

I searched a lot, but i ended up here asking this question. There is no satisfying answer to my question: I want to create an excel pivot (WorkSheet1 as PivotTable, WorkSheet2 as Data Source) I don't have any problem with creating DataSource from DataTable. But there is no guiding document for adding Hierarchical Columns and Hierarchical Rows and Measures which is mapping backto DataSource (WorkSheet 2). I'm pretty sure about mapping is done with:

var pt = pivotSheet.CreatePivotTable(new AreaReference(new CellReference("A2"), new CellReference(rowCount, columnCount -1)), new CellReference("A1"), dataSheet);

But there is no evidence how to map Hierarchical Columns and Hierarchical Rows and Measures.

I have already wasted my time too much, so i need help very badly: Any help will be appreciated...

Regards efaruk...

Symposium

I don't need NPOI any more, EPPlus solved the problem smoothly ;) Far better documenatiton and community support... – Symposium

Answer 1

Here is my solution with EPPlus [*****]

public static class ExcelPivotHelper
{
    public static void CreateExcelPivot(Stream outputStream, DataTable data, PivotSettings settings)
    {
        using (var pckg = new ExcelPackage())
        {
            var book = pckg.Workbook;
            var pivotSheet = book.Worksheets.Add("Pivot");
            var dataSheet = book.Worksheets.Add("Data");
            var dataRange = GenerateDataSheet(data, dataSheet);
            GeneratePivotSheet(pivotSheet, dataRange, settings);
            pckg.SaveAs(outputStream);
        }
    }

    private static void GeneratePivotSheet(ExcelWorksheet pivotSheet, ExcelRangeBase dataRange, PivotSettings settings)
    {
        //var rowCount = data.Rows.Count;
        //var columnCount = data.Columns.Count;
        var pt = pivotSheet.PivotTables.Add(pivotSheet.Cells["A1"], dataRange, "PivotTable");

        pt.MultipleFieldFilters = true;
        pt.RowGrandTotals = true;
        pt.ColumGrandTotals = true;
        pt.Compact = true;
        pt.CompactData = true;
        pt.GridDropZones = false;
        pt.Outline = false;
        pt.OutlineData = false;
        pt.ShowError = true;
        pt.ErrorCaption = "[error]";
        pt.ShowHeaders = true;
        pt.UseAutoFormatting = true;
        pt.ApplyWidthHeightFormats = true;
        pt.ShowDrill = true;
        pt.DataOnRows = false;

        pt.FirstHeaderRow = 1;  // first row has headers
        pt.FirstDataCol = 1;    // first col of data
        pt.FirstDataRow = 2;    // first row of data

        pt.TableStyle = TableStyles.Medium6;

        //pt.ColumGrandTotals = true;
        //pt.RowGrandTotals = true;
        //pt.GrandTotalCaption = "Genel Toplam";

        // pt.RowHeaderCaption = "";
        foreach (var column in settings.Columns)
        {
            var field = pt.Fields[column.Field];
            var pivotField = pt.ColumnFields.Add(field);
            if (column.PivotDataType == PivotDataType.Date)
            {
                //pivotField.AddDateGrouping(eDateGroupBy.Days);
            }
        }

        foreach (var row in settings.Rows)
        {
            var field = pt.Fields[row.Field];
            var pivotField = pt.RowFields.Add(field);
            if (row.PivotDataType == PivotDataType.Date)
            {
                //pivotField.AddDateGrouping(eDateGroupBy.Days);
            }
        }

        foreach (var measurement in settings.Measurements)
        {
            var field = pt.Fields[measurement.Field];
            var pivotField = pt.DataFields.Add(field);
            pivotField.Format = measurement.DisplayFormat;
            pivotField.Function = DataFieldFunctions.Sum;
            pivotField.Name = measurement.Caption;
        }

    }

    private static readonly Type[] NumericTypes = { typeof(decimal), typeof(double), typeof(float), typeof(int), typeof(long), typeof(short), typeof(byte) };
    private static ExcelRangeBase GenerateDataSheet(DataTable data, ExcelWorksheet dataSheet)
    {
        dataSheet.Hidden = eWorkSheetHidden.VeryHidden;
        var range = dataSheet.Cells["A1"].LoadFromDataTable(data, true, TableStyles.Medium6);
        range.AutoFitColumns();
        for (var i = 0; i < data.Columns.Count; i++)
        {
            var ix = i + 1;
            var cell = dataSheet.Cells[2, ix, data.Rows.Count + 1, ix];
            var column = data.Columns[i];
            if (NumericTypes.Contains(column.DataType))
            {
                cell.Style.Numberformat.Format = "#,##0";
            }
            else if (column.DataType == typeof(DateTime))
            {
                cell.Style.Numberformat.Format = "dd.MM.yyyy";
            }
        }

        return range;
    }
}


public class PivotSettings
{
    private string _generalTotalText = "Genel Toplam";
    private string _yesText = "Evet";
    private string _noText = "Hayır";
    private string _fileExtension = ".xlsx";

    /// <summary>
    ///     Default ctor
    /// </summary>
    public PivotSettings()
    {
        Columns = new List<PivotItemSetting>(10);
        Rows = new List<PivotItemSetting>(10);
        Measurements = new List<MeasureSetting>(10);
    }

    /// <summary>
    /// Column Item Settings
    /// </summary>
    public List<PivotItemSetting> Columns { get; set; }

    /// <summary>
    /// Row Item Settings
    /// </summary>
    public List<PivotItemSetting> Rows { get; set; }

    /// <summary>
    /// Measure Settings
    /// </summary>
    public List<MeasureSetting> Measurements { get; set; }

    /// <summary>
    ///     Display text for 'Genel Toplam' word. Default is 'Genel Toplam'
    /// </summary>
    public string GeneralTotalText
    {
        get { return _generalTotalText; }
        set { _generalTotalText = value; }
    }

    /// <summary>
    ///     Display text for 'Evet' word. Default is 'Evet'
    /// </summary>
    public string YesText
    {
        get { return _yesText; }
        set { _yesText = value; }
    }

    /// <summary>
    ///     Display text for 'Hayır' word. Default is 'Hayır'
    /// </summary>
    public string NoText
    {
        get { return _noText; }
        set { _noText = value; }
    }

    /// <summary>
    ///     Excel File Extension
    /// </summary>
    public string FileExtension
    {
        get { return _fileExtension; }
        set { _fileExtension = value; }
    }
}


/// <summary>
///     Setting for Pivot Column and Row
/// </summary>
public class PivotItemSetting
{
    private string _displayFormat = "{0}";

    /// <summary>
    ///     Field Name
    /// </summary>
    public string Field { get; set; }

    /// <summary>
    ///     Desired data type of the field
    /// </summary>
    public PivotDataType PivotDataType { get; set; }

    /// <summary>
    ///     Desired display format string for field
    /// </summary>
    public string DisplayFormat
    {
        get { return _displayFormat; }
        set { _displayFormat = value; }
    }

    /// <summary>
    ///     Automatically filled by generation process. Don't fill manually.
    /// </summary>
    public int CalculatedItemCount { get; set; }
}

/// <summary>
///     Pivot Measure Setting
/// </summary>
public class MeasureSetting
{
    private string _displayFormat = "#,##0";

    /// <summary>
    ///     Field Name
    /// </summary>
    public string Field { get; set; }

    /// <summary>
    ///     Measure Caption
    /// </summary>
    public string Caption { get; set; }

    /// <summary>
    ///     Type of Measure
    /// </summary>
    public MeasureType MeasureType { get; set; }

    /// <summary>
    ///     Desired display format string for measure
    /// </summary>
    public string DisplayFormat
    {
        get { return _displayFormat; }
        set { _displayFormat = value; }
    }
}

/// <summary>
///     Masure type
/// </summary>
public enum MeasureType
{
    /// <summary>
    ///     Sum of the values for measure field
    /// </summary>
    Sum
}

Update

Yep DataTable is normal DataTable as you ques... Stream is any type of Stream depends on how you want to use it: In my case MemoryStream to create ASP.Net MVC Response as file download...

Here is custom types:

/// <summary>
///     Settings for Pivot Table
/// </summary>
public class PivotSettings
{
    private string _generalTotalText = "Genel Toplam";
    private string _yesText = "Evet";
    private string _noText = "Hayır";
    private string _fileExtension = ".xlsx";

    /// <summary>
    ///     Default ctor
    /// </summary>
    public PivotSettings()
    {
        Columns = new List<PivotItemSetting>(10);
        Rows = new List<PivotItemSetting>(10);
        Measurements = new List<MeasureSetting>(10);
    }

    /// <summary>
    /// Column Item Settings
    /// </summary>
    public List<PivotItemSetting> Columns { get; set; }

    /// <summary>
    /// Row Item Settings
    /// </summary>
    public List<PivotItemSetting> Rows { get; set; }

    /// <summary>
    /// Measure Settings
    /// </summary>
    public List<MeasureSetting> Measurements { get; set; }

    /// <summary>
    ///     Display text for 'Genel Toplam' word. Default is 'Genel Toplam'
    /// </summary>
    public string GeneralTotalText
    {
        get { return _generalTotalText; }
        set { _generalTotalText = value; }
    }

    /// <summary>
    ///     Display text for 'Evet' word. Default is 'Evet'
    /// </summary>
    public string YesText
    {
        get { return _yesText; }
        set { _yesText = value; }
    }

    /// <summary>
    ///     Display text for 'Hayır' word. Default is 'Hayır'
    /// </summary>
    public string NoText
    {
        get { return _noText; }
        set { _noText = value; }
    }

    /// <summary>
    ///     Excel File Extension
    /// </summary>
    public string FileExtension
    {
        get { return _fileExtension; }
        set { _fileExtension = value; }
    }
}

/// <summary>
///     Setting for Pivot Column and Row
/// </summary>
public class PivotItemSetting
{
    private string _displayFormat = "{0}";

    /// <summary>
    ///     Field Name
    /// </summary>
    public string Field { get; set; }

    /// <summary>
    ///     Desired data type of the field
    /// </summary>
    public PivotDataType PivotDataType { get; set; }

    /// <summary>
    ///     Desired display format string for field
    /// </summary>
    public string DisplayFormat
    {
        get { return _displayFormat; }
        set { _displayFormat = value; }
    }

    /// <summary>
    ///     Automatically filled by generation process. Don't fill manually.
    /// </summary>
    public int CalculatedItemCount { get; set; }
}

/// <summary>
///     Pivot Measure Setting
/// </summary>
public class MeasureSetting
{
    private string _displayFormat = "#,##0";

    /// <summary>
    ///     Field Name
    /// </summary>
    public string Field { get; set; }

    /// <summary>
    ///     Measure Caption
    /// </summary>
    public string Caption { get; set; }

    /// <summary>
    ///     Type of Measure
    /// </summary>
    public MeasureType MeasureType { get; set; }

    /// <summary>
    ///     Desired display format string for measure
    /// </summary>
    public string DisplayFormat
    {
        get { return _displayFormat; }
        set { _displayFormat = value; }
    }
}

/// <summary>
///     Masure type
/// </summary>
public enum MeasureType
{
    /// <summary>
    ///     Sum of the values for measure field
    /// </summary>
    Sum
}

/// <summary>
///     Pivot Item type 
/// </summary>
public enum PivotDataType
{
    /// <summary>
    ///     Not Defined, Default
    /// </summary>
    None,
    /// <summary>
    ///     String
    /// </summary>
    String,
    /// <summary>
    /// Date Part of DateTime
    /// </summary>
    Date,
    /// <summary>
    /// DateTime
    /// </summary>
    DateTime,
    /// <summary>
    /// Numeric
    /// </summary>
    Numeric,
    /// <summary>
    /// Boolean
    /// </summary>
    Boolean
}

Regards...

Symposium

Could you also show an example of how you call CreateExcelPivot()? e.g., is the Stream a System.IO.Stream? Is the DataTable a System.Data.DataTable? – Mastic

Also, PivotDataType is unrecogized. – Mastic

PivotDataType an enum, you can use your own and yes Stream is IO.Stream and DataTable is regular datatable... – Symposium

Answer 2

NPOI did not fully support for pivot table at the moment. I use this to create pivot table for my excel file, or you can consider EPPLus (not free for commercial usage). For now I think of using VB to write excel functions and macros to automatically do my tasks. You should think of it as another option

Overrefinement