Document Solutions for Excel, Java 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 Java 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 getStyle() and the setStyle() methods of the IPivotTable interface can be used to get or set the pivot table style. While exporting PDFs with pivot table styles in DsExcel Java, refer to the complete listing of methods with their descriptions shared in the table below:

    Method Description

    getShowTableStyleColumnHeaders

    setShowTableStyleColumnHeaders

    These methods can be used to get or set whether the column headers should be displayed in the Pivot table.

    getShowTableStyleRowHeaders

    setShowTableStyleRowHeaders

    These methods can be used to get or set whether the row headers should be displayed in the Pivot table.

    getShowTableStyleColumnStripes

    setShowTableStyleColumnStripes

    These methods can be used to get or set whether the banded columns in which even columns are formatted differently from odd columns.            

    getShowTableStyleRowStripes

    setShowTableStyleRowStripes

    These methods can be used to get or set whether the banded rows in which even row are formatted differently from odd rows.            

    getShowTableStyleLastColumn

    setShowTableStyleLastColumn

    These methods can be used to get or set whether to display the grand total columns style.

    getShowAsAvailablePivotStyle

    setShowAsAvailablePivotStyle

    These methods can be used to get or set whether the specified style is shown as available in the pivot styles gallery.

    getNumberFormat

    setNumberFormat

    These methods 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.

    Java
    Copy Code 
    // Initialize workbook
Workbook workbook = new Workbook();
        
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Create PivotTable
Object sourceData = new Object[][] { 
{ "Order ID", "Product", "Category", "Amount", "Date", "Country" },
{ 1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2012, 1, 6), "United States" },
{ 2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2012, 1, 7), "United Kingdom" },
{ 3, "Banana", "Fruit", 617, new GregorianCalendar(2012, 1, 8), "United States" },
{ 4, "Banana", "Fruit", 8384, new GregorianCalendar(2012, 1, 10), "Canada" },
{ 5, "Beans", "Vegetables", 2626, new GregorianCalendar(2012, 1, 10), "Germany" },
{ 6, "Orange", "Fruit", 3610, new GregorianCalendar(2012, 1, 11), "United States" },
{ 7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2012, 1, 11), "Australia" },
{ 8, "Banana", "Fruit", 6906, new GregorianCalendar(2012, 1, 16), "New Zealand" },
{ 9, "Apple", "Fruit", 2417, new GregorianCalendar(2012, 1, 16), "France" },
{ 10, "Apple", "Fruit", 7431, new GregorianCalendar(2012, 1, 16), "Canada" },
{ 11, "Banana", "Fruit", 8250, new GregorianCalendar(2012, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2012, 1, 18),"United States" },
{ 13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2012, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2012, 1, 22), "Canada" },
{ 15, "Apple", "Fruit", 6946, new GregorianCalendar(2012, 1, 24), "France" }, };

worksheet.getRange("A1:F16").setValue(sourceData);
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("A1:F16"));
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, 
worksheet.getRange("H5"), "pivottable1");

// Create PivotTable style
ITableStyle style = workbook.getTableStyles().add("pivotStyle");

// Set the table style as a pivot table style
style.setShowAsAvailablePivotStyle(true);
style.getTableStyleElements().get(TableStyleElementType.WholeTable).getBorders()
.setLineStyle(BorderLineStyle.DashDotDot);
style.getTableStyleElements().get(TableStyleElementType.WholeTable).getBorders()
.setColor(com.grapecity.documents.excel.Color.FromArgb(204, 153, 255));
style.getTableStyleElements().get(TableStyleElementType.WholeTable).getInterior()
.setColor(com.grapecity.documents.excel.Color.FromArgb(169, 208, 142));
style.getTableStyleElements().get(TableStyleElementType.WholeTable).getFont().setItalic(true);
style.getTableStyleElements().get(TableStyleElementType.WholeTable)
.getFont().setThemeColor(ThemeColor.Accent2);

// Apply the style to current pivot table
pivottable.setStyle(style);

pivottable.setShowTableStyleColumnHeaders(true);
pivottable.setShowTableStyleRowHeaders(true);
pivottable.setShowTableStyleColumnStripes(true);
pivottable.setShowTableStyleRowStripes(true);
pivottable.setShowTableStyleLastColumn(true);

// Add pivot filed and set number format code
        
// Add two fileds
IPivotField field_product = pivottable.getPivotFields().get(1);
field_product.setOrientation(PivotFieldOrientation.RowField);
IPivotField field_Amount = pivottable.getPivotFields().get(3);
field_Amount.setOrientation(PivotFieldOrientation.DataField);
        
// Set number format code
field_Amount.setNumberFormat("#,##0");

// Saving workbook to xlsx
workbook.save("PivotTableStyleAndNumberFormat.pdf", SaveFileFormat.Pdf);