Pivot Table Format

Aspose.Cells Product Family
1

Hi Team,

I created pivot table based on excel sheet by following code.
pivotTables.add("=Advanced!A8:O"+maxColumn, “A1”, “Advanced CrossTab”);

Now, I want to format pivot table rows and columns with custom colors, It is not working in pivot table.

My simple code ::

Style style = workbook.createStyle();
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.getWhite());
pivotTable.formatAll(style);
style = workbook.createStyle();
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.getRed());
for (int col = 0; col < 5; col++) {
pivotTable.format(1, col, style);
}

here pivotTable.formatAll is working but not able color header column.
Please find attached document for
1)working style code for normal excel
2)required format of excel
3)currently getting format

int maxColumnval = sheet2.getCells().getMaxColumn();
int maxRowval = sheet2.getCells().getMaxRow();
Also not showing max row column to iterate loop, please tell me how to find dynamic row and column size.

And, Please let me now how to copy pivot table from one worksheet to another sheet.
Please do need full.

Thank you
Javeed Ali Khan A





2

Hi Javeed,


Thank you for contacting Aspose support.

Please note, in order to format a specific cell or range from a Pivot Table, you first need to identify it. Aspose.Cells has exposed several APIs that return specific cell areas from any Pivot Table. Please check the following piece of code for elaboration.

Java

//DataBodyRange returns CellArea that represents range between the header row & insert row
CellArea dataBodyRange = pivot.getDataBodyRange();
System.out.println(dataBodyRange);
//TableRange1 returns complete Pivot Table area except page fields
CellArea tableRange1 = pivot.getTableRange1();
System.out.println(tableRange1);
//TableRange2 returns complete Pivot Table area including page fields
CellArea tableRange2 = pivot.getTableRange2();
System.out.println(tableRange2);
//ColumnRange returns range that represents the column area of the Pivot Table
CellArea columnRange = pivot.getColumnRange();
System.out.println(columnRange);
//RowRange returns range that represents the row area of the Pivot Table
CellArea rowRange = pivot.getRowRange();
System.out.println(rowRange);

Once you have identified the cell area that you wish to format, the rest of the process is more or less similar as formatting an ordinary cell or range, however, you will be using the PivotTable.format method with appropriate cell reference. Please check the following piece of code that paints only the header rows of a Pivot Table.

Please note, I was not able to find any attachments with your post therefore I have used a sample spreadsheet of my own for demonstration purposes.

Java

Workbook book = new Workbook(dir + "pivotTable_test.xls");
Worksheet sheet = book.getWorksheets().get("PivotTable");
PivotTable pivot = sheet.getPivotTables().get(0);
CellArea range = pivot.getTableRange1();
System.out.println(range);
for (int r = range.StartRow; r < range.StartRow + 2; r++)
{
for (int c = range.StartColumn; c < range.EndColumn + 1; c++)
{
Style style = sheet.getCells().get(r, c).getStyle();
style.setBackgroundColor(Color.getBeige());
style.setPattern(BackgroundType.SOLID);
pivot.format(r, c, style);
}
}
book.save(dir + "output.xlsx");
3

Hi again,


jk5:
And, Please let me now how to copy pivot table from one worksheet to another sheet.

Regarding the requirement as quoted above, I think it is not possible to copy just the Pivot Table onto a new Worksheet, however, you create a copy of the whole worksheet that contains the Pivot Table. Please check the following piece of code and its resultant spreadsheet as attached.

Java

Workbook book = new Workbook(dir + “Geeting+format.xlsx”);
Worksheet sheet = book.getWorksheets().get(“Advanced CrossTab”);
book.getWorksheets().get(book.getWorksheets().add()).copy(sheet);;
book.save(dir + “output.xlsx”);

We humbly request you to always create separate threads for distinct inquiries. This would simply help us to track your requests more efficiently. Thank you for your cooperation.
4

Hi ,

Thank you for given code, I tried to generate pivot as per given code. But i think so, its not getting correct ranged values.


Please find ranges i get it from pivot as per given code.

Aspose.Cells.CellArea(B2:G14)[1,1,13,6]
INFO [http-bio-8080-exec-9] BaseFormController.exportDatabasedataTrigger(1537) | dataBodyRange::Aspose.Cells.CellArea(B2:G14)[1,1,13,6]
Aspose.Cells.CellArea(A1:G14)[0,0,13,6]
INFO [http-bio-8080-exec-9] BaseFormController.exportDatabasedataTrigger(1541) | tableRange1::Aspose.Cells.CellArea(A1:G14)[0,0,13,6]
Aspose.Cells.CellArea(A1:G14)[0,0,13,6]
INFO [http-bio-8080-exec-9] BaseFormController.exportDatabasedataTrigger(1545) | tableRange2::Aspose.Cells.CellArea(A1:G14)[0,0,13,6]
Aspose.Cells.CellArea(B1:G1)[0,1,0,6]
INFO [http-bio-8080-exec-9] BaseFormController.exportDatabasedataTrigger(1549) | columnRange::Aspose.Cells.CellArea(B1:G1)[0,1,0,6]
Aspose.Cells.CellArea(A2:A14)[1,0,13,0]
INFO [http-bio-8080-exec-9] BaseFormController.exportDatabasedataTrigger(1553) | rowRange::Aspose.Cells.CellArea(A2:A14)[1,0,13,0]
Aspose.Cells.CellArea(A1:G14)[0,0,13,6]
INFO [http-bio-8080-exec-9] BaseFormController.exportDatabasedataTrigger(1558) | rangeAspose.Cells.CellArea(A1:G14)[0,0,13,6]




And, Please find generated excel sheet with above code .

And my code for get values into pivot from excel sheet.

if(pivotRows == null || pivotRows == “”){
pivotTable.addFieldToArea(PivotFieldType.ROW, 15);
}else{
List dataValues = getingColumnValues(pivotRows);
log.info(“dataValues for row::”+dataValues);
for(int i=0;i<dataValues.size();i++){
pivotTable.addFieldToArea(PivotFieldType.ROW, dataValues.get(i));
}
}

if(pivotColoumns == null || pivotColoumns == “”){
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 15);
}else{
List dataValues = getingColumnValues(pivotColoumns);
for(int i=0;i<dataValues.size();i++){
log.info(“dataValues pivotColoumns.get(i)::”+dataValues.get(i));
pivotTable.addFieldToArea(PivotFieldType.COLUMN, dataValues.get(i));
}
}

if(nameValue == null || nameValue == “”){
pivotTable.addFieldToArea(PivotFieldType.DATA, 15);
}else{
List dataValues = getingColumnValues(nameValue);
for(int i=0;i<dataValues.size();i++){
log.info(“dataValues.get(i)::”+dataValues.get(i));
// pivotTable.addFieldToArea(PivotFieldType.UNDEFINED, dataValues.get(i));
///log.info(“dataValues pivotFilters.get(i)::”+dataValues.get(i));
if(dataValues.get(i) == 10 || dataValues.get(i) == 13 || dataValues.get(i) == 12){
if(dataValues.get(i) == 10){//SOW
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
pivotTable.getDataFields().get(indexFormat).setNumberFormat(“0.000%”);
}
if(dataValues.get(i) == 13){//GAP
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
String dataFilterValues = getingColumnFilterValues(dataValues.get(i));
int opratoindex = listVal.indexOf(dataFilterValues);
String opeartorvalue=listVal.get(opratoindex+1);
log.info(“opeartorvalue matched string opeartor”+opeartorvalue);
// pivotTable.getDataFields().get(indexFormat).setFunction(ConsolidationFunction.MAX);
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(“max”));
// pivotTable.getRowFields().get(indexFormat).setSubtotals(PivotFieldSubtotalType.NONE,true);
// sheet.getPivotTables().get(0).getRowFields().get(0).hideDetail(true);
}
if(dataValues.get(i) == 12){//Rank
//int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
// pivotTable.getDataFields().get(indexFormat).setFunction(ConsolidationFunction.STD_DEVP);
///pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(“max”));
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=1]1-3;[=2]4-7;8+;"); //;[=184]8-10;[=185]11+;
pivotTable.setSubtotalHiddenPageItems(true);
// pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=184]8-10;[=185]11+;NA"); //;[=184]8-10;[=185]11+;
}
}else{
log.info(“listVal::”+listVal);
String dataFilterValues = getingColumnFilterValues(dataValues.get(i));
int opratoindex = listVal.indexOf(dataFilterValues);
String opeartorvalue=listVal.get(opratoindex+1);
log.info(“opeartorvalue matched string opeartor”+opeartorvalue);
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
// pivotTable.getDataFields().get(indexFormat).setFunction(ConsolidationFunction.MAX);
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(opeartorvalue));
}
}
}
pivotTable.getColumnFields().add(pivotTable.getDataField());
sheet2.getCells().deleteBlankColumns();
sheet2.getCells().deleteBlankRows();




Thank you
Javeed Ali Khan A
5

Hi Javeed,


Thank you for writing back.

I have noticed the said problem as well, that is; the cell areas returned for different APIs are not correct. Reason being, the Pivot Table has not been refreshed. Your code suggests that you are creating the Pivot Table from scratch therefore you should first call the PivotTable.refreshData & calculateData methods (in same sequence) in order to refresh the Pivot Table, then you can access the correct cell areas. Here is the code for demonstration purposes.

Java

Workbook book = new Workbook(dir + “Coalition_Advance_Cross_Tab_24022017_2041.xlsx”);
Worksheet sheet = book.getWorksheets().get(“Advanced CrossTab”);
PivotTable pivot = sheet.getPivotTables().get(0);
pivot.refreshData();
pivot.calculateData();
//DataBodyRange returns CellArea that represents range between the header row & insert row
CellArea dataBodyRange = pivot.getDataBodyRange();
System.out.println("DataBodyRange " + dataBodyRange);
//TableRange1 returns complete Pivot Table area except page fields
CellArea tableRange1 = pivot.getTableRange1();
System.out.println("TableRange1 " + tableRange1);
//TableRange2 returns complete Pivot Table area including page fields
CellArea tableRange2 = pivot.getTableRange2();
System.out.println("TableRange2 " + tableRange2);
//ColumnRange returns range that represents the column area of the Pivot Table
CellArea columnRange = pivot.getColumnRange();
System.out.println("ColumnRange " + columnRange);
//RowRange returns range that represents the row area of the Pivot Table
CellArea rowRange = pivot.getRowRange();
System.out.println("RowRange " + rowRange);

Output
DataBodyRange Aspose.Cells.CellArea(C4:AF44)[3,2,43,31] TableRange1 Aspose.Cells.CellArea(A1:AF44)[0,0,43,31] TableRange2 Aspose.Cells.CellArea(A1:AF44)[0,0,43,31] ColumnRange Aspose.Cells.CellArea(C1:AF3)[0,2,2,31] RowRange Aspose.Cells.CellArea(A4:B44)[3,0,43,1]


Please note, in case you need to share the code snippets in Aspose forums, please always try to share executable piece of code (preferably a standalone console application) where we do not have to assume the values of certain variables or write our own implementation of missing classes/methods. This is to ensure that we are executing the exact same code on our side to replicate a specific problem.
6

Hi Team,

Thank you for solution, Now am getting colors in headers column.
Now am facing major issue,

we are sowing rank data into field values.
my rank data is :: 1-3 , 4-7, 8+ , 8-10 , 11+ , NA and NR.(Reading randomly rank data from dynamic excel column)

Because not able to read directly rank data in to pivot from dynamic excel column (getting wrong data if we read directly from excel) , I mapped data with ID values and insert Id values into pivot then again by using setNumberFormat converted ID to Rank.

Am using below code that read dynamic rank column from excel and setnumber format,


if(dataValues.get(i) == 12){
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(“max”));
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=1]1-3;[=2]4-7;8+;"); //;[=184]8-10;[=185]11+;
pivotTable.setSubtotalHiddenPageItems(true);
}



Now issue is,
1)If click on Rank in Pivot it is showing mapped ID in formula bar. But we need to show rank only.

2) setNumberformat not supporting for more then 3 rank conversion.

3) After apply style rank data will be showing as “0” in pivot.

Please provide any solution for read rank data from dynamic excel as it is . its will be shown as it in formula bar if click on rank also.

Please find attached files for reference.

Thank you.

7

Hi Javeed,


Thank you for the confirmation on previously reported problem.

Regarding your recent concerns, I would again request you to always create new threads while posting distinct problems for efficient investigation and response. Moreover, it is always appropriate to provide executable console application (covering your complete scenario) to demonstrate the problem. Please note, Aspose.Cells are quite complex APIs where only switching a simple property could yield entirely different results. Anyway, we have checked your concerns and following are our findings.

  1. It is Excel’s behaviour to show the underlying value in the formula bar. I am afraid, Aspose.Cells APIs neither can control this behaviour nor can change it.
  2. Please note, Excel does not support more than 3 rank conversion. As Aspose.Cells APIs follow Excel’s guidelines and specifications in its processes therefore they too cannot support more than 3 ranks.
  3. If you consider the custom format string as follow: [=1]1-3;[=2]4-7;8+; it means that when the value is 1, it will be displayed as “1-3”, when the value is 2, it will be displayed as “4-7” whereas other values will be displayed “8+” so the “0” value will be displayed as “8+” which is correct.

In case you do not agree with any of our findings, please provide us a sample spreadsheet showing the desired behaviour/values. You can create such sample manually in Excel application, and share the sample here along with steps to accomplish the task for further review.