Export Pivot Table Styles And Format

GcExcel .NET allows users to save Excel files containing distinct pivot table styles and formats into a PDF file.

With extensive support for exporting pivot table styles and format, users can customize how the pivot table is displayed in the PDF format. This includes saving Excel files with custom pivot table layout, pivot table fields, orientation, page size etc. into PDF files as per your specific preferences.

The Style property of the IPivotTable interface can be used to get or set the pivot table style. While exporting PDFs with pivot table styles in GcExcel .NET, the following properties can be used:

Property	Description
IPivotTable.ShowTableStyleColumnHeaders	This property can be used to get or set whether the column headers should be displayed in the Pivot table.
IPivotTable.ShowTableStyleRowHeaders	This property can be used to get or set whether the row headers should be displayed in the Pivot table.
IPivotTable.ShowTableStyleColumnStripes	This property can be used to get or set whether the banded columns in which even columns are formatted differently from odd columns.
IPivotTable.ShowTableStyleRowStripes	This property can be used to get or set whether the banded rows in which even row are formatted differently from odd rows.
IPivotTable.ShowTableStyleLastColumn	This property can be used to get or set whether to display the grand total columns style.
ITableStyle.ShowAsAvailablePivotStyle	This property can be used to get or set whether the specified style is shown as available in the pivot styles gallery.
IPivotField.NumberFormat	This property can be used to get or set the current field's number format string.

Using Code

Refer to the following example code in order to export Excel files with pivot table styles and format.

// Initialize workbook
Workbook workbook = new Workbook();
        
// Fetch default worksheet 
IWorksheet worksheet = workbook.Worksheets[0];

// Create PivotTable
object[,] sourceData = new object[,] {
 { "Order ID", "Product", "Category", "Amount", "Date", "Country" },
 { 1,  "Carrots",  "Vegetables",  4270, new DateTime(2012, 1, 6),  "United States" },
 { 2,  "Broccoli", "Vegetables",  8239, new DateTime(2012, 1, 7),  "United Kingdom" },
 { 3,  "Banana",   "Fruit",       617,  new DateTime(2012, 1, 8),  "United States" },
 { 4,  "Banana",   "Fruit",       8384, new DateTime(2012, 1, 10), "Canada" },
 { 5,  "Beans",    "Vegetables",  2626, new DateTime(2012, 1, 10), "Germany" },
 { 6,  "Orange",   "Fruit",       3610, new DateTime(2012, 1, 11), "United States" },
 { 7,  "Broccoli", "Vegetables",  9062, new DateTime(2012, 1, 11), "Australia" },
 { 8,  "Banana",   "Fruit",       6906, new DateTime(2012, 1, 16), "New Zealand" },
 { 9,  "Apple",    "Fruit",       2417, new DateTime(2012, 1, 16), "France" },
 { 10, "Apple",    "Fruit",       7431, new DateTime(2012, 1, 16), "Canada" },
 { 11, "Banana",   "Fruit",      8250, new DateTime(2012, 1, 16), "Germany" },
 { 12, "Broccoli", "Vegetables", 7012, new DateTime(2012, 1, 18), "United States" },
 { 13, "Carrots",  "Vegetables", 1903, new DateTime(2012, 1, 20), "Germany" },
 { 14, "Broccoli", "Vegetables", 2824, new DateTime(2012, 1, 22), "Canada" },
 { 15, "Apple",    "Fruit",      6946, new DateTime(2012, 1, 24), "France" },
 };
 
worksheet.Range["A1:F16"].Value = sourceData;
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["H5"], "pivottable1");

// Create PivotTable style 
ITableStyle style = workbook.TableStyles.Add("pivotStyle");

// Set the table style as a pivot table style
style.ShowAsAvailablePivotStyle = true;
style.TableStyleElements[TableStyleElementType.WholeTable].Borders.LineStyle = BorderLineStyle.DashDotDot;
style.TableStyleElements[TableStyleElementType.WholeTable].Borders.Color = Color.FromArgb(204, 153, 255);
style.TableStyleElements[TableStyleElementType.WholeTable].Interior.Color = Color.FromArgb(169, 208, 142);
style.TableStyleElements[TableStyleElementType.WholeTable].Font.Italic = true;
style.TableStyleElements[TableStyleElementType.WholeTable].Font.ThemeColor = ThemeColor.Accent2;

// Apply the style to current pivot table
pivottable.Style = style;

// Configure pivot table settings for columns and rows
pivottable.ShowTableStyleColumnHeaders = true;
pivottable.ShowTableStyleRowHeaders = true;
pivottable.ShowTableStyleColumnStripes = true;
pivottable.ShowTableStyleRowStripes = true;
pivottable.ShowTableStyleLastColumn = true;

// Add pivot field and set number format code
        
var field_product = pivottable.PivotFields[1];
field_product.Orientation = PivotFieldOrientation.RowField;
var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;
        
// Set number format code
field_Amount.NumberFormat = "#,##0";

// Saving workbook to PDF
workbook.Save(@"PivotTableStyleAndNumberFormat.pdf", SaveFileFormat.Pdf);