Document Solutions for Excel, .NET Edition | Document Solutions
File Operations / Export to PDF / Export Pivot Table Styles And Format
In This Topic
    Export Pivot Table Styles And Format
    In This Topic

    DsExcel .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 DsExcel .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.

    C#
    Copy Code 
    // 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);