Click here to Skip to main content
16,015,726 members
Articles / Programming Languages / C#

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
17 Nov 2016CPOL3 min read 15.7K   4   1
Generate a PivotTable from raw data with minimal code using the Aspose.Cells 3rd Party Library

This article appears in the Third Party Products and Tools section. Articles in this section are for the members only and must not be used to promote or advertise products in any way, shape or form. Please report any spam or advertising.

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:

C#
using Aspose.Cells;

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

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

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

C#
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):

C#
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:

C#
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:

Image 1

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:

C#
// 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder Across Time & Space
United States United States
I am in the process of morphing from a software developer into a portrayer of Mark Twain. My monologue (or one-man play, entitled "The Adventures of Mark Twain: As Told By Himself" and set in 1896) features Twain giving an overview of his life up till then. The performance includes the relating of interesting experiences and humorous anecdotes from Twain's boyhood and youth, his time as a riverboat pilot, his wild and woolly adventures in the Territory of Nevada and California, and experiences as a writer and world traveler, including recollections of meetings with many of the famous and powerful of the 19th century - royalty, business magnates, fellow authors, as well as intimate glimpses into his home life (his parents, siblings, wife, and children).

Peripatetic and picaresque, I have lived in eight states; specifically, besides my native California (where I was born and where I now again reside) in chronological order: New York, Montana, Alaska, Oklahoma, Wisconsin, Idaho, and Missouri.

I am also a writer of both fiction (for which I use a nom de plume, "Blackbird Crow Raven", as a nod to my Native American heritage - I am "½ Cowboy, ½ Indian") and nonfiction, including a two-volume social and cultural history of the U.S. which covers important events from 1620-2006: http://www.lulu.com/spotlight/blackbirdcraven

Comments and Discussions

 
GeneralMy vote of 5 Pin
Farhad Reza17-Nov-16 22:45
Farhad Reza17-Nov-16 22:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.