Create a PivotTable Using Aspose.Cells for .NET (C#)

I Suppose I Won't Dispose of Aspose (or Make A Rhyme out of "Noses" and "Roses" - I Mean It!)

I have tried various ways and means of generating Excel spreadsheets in C#. I've used the de facto Excel Interop, which is very full-featured but ponderous and a bit perverse; I've also taken Spreadsheet Light for a spin, but found it wanting (it is too light, on features); penultimately, I use EPPlus quite a bit and like it the best of all those mentioned so far. However, it fails to meet my semi-tough standards when it comes to PivotTables. I was just never able to get EPPlus PivotTables to jump through all the necessary hoops with EPPlus, so for the scenario in which I really need rather complex Pivot Tables, I reverted to utilizing the "tried and true" Excel Interop.

Recently, though, I decided to give Aspose Cells a try, because very large PivotTables would not generate using Excel Interop code no matter how long I let them run (even over the weekend in one case).

I am duly impressed with Aspose Cells; I was able to generate a PivotTable with just a few lines of code and, like EPPlus, it seems very logical in the way it does things, without making you feel like you're being ridden out of town on a rail (I'm looking at you, Excel Interop). A PivotTable that would not generate in Excel Interop even over the entire weekend was generated with Aspose.Cells in 21 minutes.

Here is all you need to do to generate a PivotTable using Aspose.Cells, using Visual Studio in a C# Winforms app:

Install the evaluation version of Aspose Cells. The easiest way to do so is to right-click your project and select "Manage NuGet Packages", then enter "Aspose Cells" in the search box, and select the latest version that appears at the top of the search results.

Add a using:

using Aspose.Cells;

Add a Workbook and two Worksheets (one for the source data, and one for the PivotTable).

private static Workbook workBook;
private static Worksheet sourceDataSheet; 
private static Worksheet pivotTableSheet;

Initialize these somewhere, such as in the class' constructor:

workBook = new Workbook();
// The first sheet is created by default
sourceDataSheet = workBook.Worksheets[0];
sourceDataSheet.Name = "sourceDataSheet";
// Other sheets need to be manually created
int i = workBook.Worksheets.Add();
pivotTableSheet = workBook.Worksheets[i];
pivotTableSheet.Name = "PivotTableSheet";

Populate a sheet with source data like so (the code to actually retrieve the data and populate a list is left as an exercise to the reader):

private void PopulatePivotTableDataSheet()
{
    foreach (ProduceUsagePivotData pupd in _produceUsagePivotDataList)
    {
        AddPivotData(pupd.ItemCode, pupd.ItemDescription, pupd.Unit, pupd.MonthYear, pupd.Quantity,
                     pupd.TotalPrice, pupd.IsContractItem, pupd.Percentage, pupd.MonthlyPercentage);
    }
}

private void AddPivotData(String ItemCode, String ItemDescription, 
String Unit, String MonthYear, int Quantity, Decimal TotalPrice, 
Boolean IsContractItem, Double PercentageOfTotal, Double MonthlyPercentage)
{
    Cell cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 0];
    cell.PutValue(ItemCode);

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 1];
    cell.PutValue(ItemDescription);

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 2];
    cell.PutValue(Unit);

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 3];
    cell.PutValue(ConvertToMMDDYYYY(MonthYear));

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 4];
    cell.PutValue(Quantity);

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 5];
    cell.PutValue(Math.Round(TotalPrice, 2));

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 6];
    cell.PutValue(IsContractItem);

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 7];
    cell.PutValue(PercentageOfTotal);

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 8];
    cell.PutValue(MonthlyPercentage);

    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 9];
    cell.PutValue(grandTotalPrice);

    decimal avgPrice = 0.0M;
    if ((TotalPrice > 0.0M) && (Quantity > 0))
    {
        avgPrice = TotalPrice/Quantity;
    }
    cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 10];
    cell.PutValue(avgPrice);
    
    _lastRowAddedPivotTableData++;
}

Coder Take Note: Aspose Cells use 0 as the index beginning for columns and rows (which is "right", but which differs from the way Microsoft does it with Excel Interop; EPPlus follows suit with that user-friendly but coder-unfriendly way of numbering indexes). The reason this can be confusing is that the spreadsheet row numbers start with 1 (the columns with "A"), not 0. So visually it's one greater than what it is programmatically (row 1 is row 0 from a programmatic point of view). This can "bite" you, especially when you're porting code that uses other libraries over to Aspose.Cells.

Now add the PivotTable sheet, drawing from the data above:

private void PopulatePivotTableSheet()
{
    int DESCRIPTION_COLUMN = 1;
    int MONTHYR_COLUMN = 3;
    int TOTALQTY_COLUMN = 4;
    int TOTALPRICE_COLUMN = 5;
    int PERCENTOFTOTAL_COLUMN = 7; 
    int AVGPRICE_COLUMN = 10;
    int COLUMNS_IN_DATA_SHEET = 11;

    Aspose.Cells.Pivot.PivotTableCollection pivotTables = pivotTableSheet.PivotTables;
    int colcount = COLUMNS_IN_DATA_SHEET; 
    string lastColAsStr = GetExcelColumnName(colcount);
    int rowcount = sourceDataSheet.Cells.Rows.Count;
    string sourceDataArg = string.Format("sourceDataSheet!A1:{0}{1}", lastColAsStr, rowcount);
    int index = pivotTableSheet.PivotTables.Add(sourceDataArg, "A7", "PivotTableSheet");
    Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, DESCRIPTION_COLUMN);

    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, MONTHYR_COLUMN);

    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, TOTALQTY_COLUMN);
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, TOTALPRICE_COLUMN);
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, AVGPRICE_COLUMN);
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, PERCENTOFTOTAL_COLUMN);
}

When that is done, a PivotTable like this is generated:

It still could use some work, especially formatting (changing "Column Labels" to "Months", "Row Labels" to "Description", reducing the number of values shown after the decimal point for "Sum of AvgPrice", etc), but this shows how a PivotTable can be created with a moderate amount of code using Aspose Cells.

That Nifty Helper Method

Here is the method that returns the appropriate alpha value for an integer column index:

// Pass "1", get "A", etc.; from 
// http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa
public static string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;

    while (dividend > 0)
    {
        var modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (dividend - modulo) / 26;
    }
    return columnName;
}

Bad News for Misers

The only "bad" thing about Aspose is that it costs infinity times as much as Excel Interop and EPPlus (since they are free); but it has the advantages of being able to create PivotTables easier than Excel Interop, with more features than EPPlus, and it is much faster than Excel Interop. It also has easy-to-grok and well-thought-out online documentation - as concise as possible, and logically organized.

If the advantages of Aspose.Cells are a big deal to you (ease of development, speed of spreadsheet generation), simply search for them on the Interwebs to find out the details as to price and either purchase post haste or add an evaluation version to your project via "Manage NuGet Packages" as mentioned at the outset.