PivotTable with custom formatting

Aspose.Cells Product Family
1

Hi,

I wanted to apply custom formatting for a generated PivotTable via AsposeCells. Let me know how do I do that?

As of now I use the below command to apply one of the existing formatting like pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT_5);

How do I apply a custom formatting?

Regards

Sundhar

ManthanSystems.

2
Hi,

I think you may use PivotTable.formatAll(style) and PivotTable.format(row, col, style) methods accordingly.

Sample code:

...
PivotTable pt = (PivotTable)worksheet.getPivotTables().get(0);
Style dxfStyle = workbook.createStyle();
Font font = dxfStyle.getFont();
dxfStyle.setCustom("0.00_);[Red](0.00)");
font.setSize(8);
// Your code goes here regarding further formatting.
pt.formatAll(dxfStyle);
...
3

Hi,

The format(row, col, style) method applies to specific cell. Instead is there any way where I can set a cutom defined style like predefined style.

Otherwise row lables specific, column label specific, values specific., something like ... which will reduce iterating the pivot table by each cell.

We have user based theme customizations, wherein the generated PivotTable should have user specific coloring schemes. So, it will make complex to set stlyes for each cell in a pivottable.

Sundhar

4

Hi,


Well, we have only these two methods for setting customized(user defined) styles. If you need to set your desired formatting, you need to define your custom Style object, then use PivotTable.formatAll(style) method accordingly, it will format the whole pivot table, see the sample code in my previous post.

If you need to apply predefined MS Excel pivot Table styles, you may try to use setPivotTableStyleType() and setPivotTableStyleName() methods for your needs.
e.g.

Sample code:

pivotTable.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_1);
(for reference see the PivotTableStyleType fields for all the predefined styles of pivot table).

Thank you.
5

Hi,

Ok, I can understand the above suggesstions.

In Excel 2007, I can create a new styles using New PivotTable Style option, with which I can define my own styles for rows, columns and others seeprately.., Is it possible to use that feature with Aspose. Let me know how can it be done.

Regards

Sundhar

ManthanSystems

6

Hi,


Well, for your information, the PivotTable.format(row, col, style) and PivotTable.formatAll(style) API does the same when you click on “New PivotTable Style” in MS Excel. In MS Excel, when you click on this option, you will still have to specify your desired style formatting, e.g after selecting pivot table element, you will click on the “Format” button, now you have to specify your desired Font attributes, Borders or Fill (shading) style options.

We recommend you to kindly check the following documents on how to specify styles regarding Fonts, Borders and Shading formattings:
http://www.aspose.com/docs/display/cellsjava/Dealing+with+Font+Settings

http://www.aspose.com/docs/display/cellsjava/Adding+Borders+to+Cells
http://www.aspose.com/docs/display/cellsjava/Colors++and++Background+Patterns

thank you.
7

Hi Amjad,

I am not sure, if u really understand my question.

If I had to use PivotTable.format(row, col, style) - just image a pivot table having 1000 rows and 100 columns, does that mean I have to iterate 10000 cells and apply styles., This will be too complex to apply different styles for each category.,

or

If I had to use .formatAll() - I am forced to apply only one style to the entire table.,

But, the "New PivotTable Style" option in excel has the flexibility to set a style to a category not to a cell. This means, a style to pivottable row lables., another style to all column lables., another style to the background of the table.

If you open this in excel you wll find 25 categories where you can apply styles for each of them., like "Whole Table", "Header Row", "First Column", "Page Filed Lables", "First Column Stripe", "Second Column Stripe" are few.., This is the functionality that I wanted to apply via Aspose.

If am correct, what you mentioned earlier was the "Format" button for a cell, but I talking about the PivotStyle option that comes in Excel 2007. With this, even if the data has more than 10000 cells, the styles will be applied properly and easily., PFA snapshots of it.

Kindly clarify., Correct me if am wrong at any place.

Regards

Sundhar

ManthanSystems

8

Hi,


Thanks for the clarifications and screen shots.

I understand your requirement now. I have logged a ticket for with an id: CELLSJAVA-40182 for your needs if we can add more elements for the Table as you described. Once we have any update, we will let you know here.

Thank you.
9

Hi,

Does this mean, new PivotTable Style feature does not exists with latest release?

If so, any idea how much time it takes to get this feature available.

Regards

Sundhar

ManthanSystems

10

Hi,


The feature already exists but we have only two methods e.g you may custom format your pivot table at table level or cell level using formatAll(style) and format(row, col, style) methods. We do not have other elements for the table as you mentioned in your previous post.

We will analyze it first, once we have any update about it, we will let you know here immediately.

Thank you.
11
Hi,
Please try the new fix Aspose.Cells for Java v7.2.0.6 (attached).
Please try this sample code with the fix:
The input and output files are also attached here.

private static void Testjava40182()throws Exception
{
Workbook wb = new Workbook("D:\\tttt.xlsx");

//add pivot table style
Style style1 = wb.createStyle();
com.aspose.cells.Font font1 = style1.getFont();
font1.setColor(Color.getRed());
Style style2 = wb.createStyle();
com.aspose.cells.Font font2 = style2.getFont();
font2.setColor( Color.getBlue());
int i = wb.getWorksheets().getTableStyles().addPivotTableStyle("tt");
TableStyle ts = wb.getWorksheets().getTableStyles().get(i);
int index = ts.getTableStyleElements().add(TableStyleElementType.FIRST_COLUMN);
TableStyleElement e = ts.getTableStyleElements().get(index);
e.setElementStyle(style1);
index = ts.getTableStyleElements().add(TableStyleElementType.GRAND_TOTAL_ROW);
e = ts.getTableStyleElements().get(index);
e.setElementStyle(style2);

//set pivot table style name
PivotTable pt = wb.getWorksheets().get(0).getPivotTables().get(0);
pt.setPivotTableStyleName ("tt");
wb.save("D:\\java40182.xlsx");
}

12

The issues you have found earlier (filed as CELLSJAVA-40182) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
13

Hi, Thanks for the update.,