Format issue in pivot table

Aspose.Cells Product Family
1
Workbook workbook = new Workbook("C://Users//test.xlsx");

//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.getWorksheets().get(1);
PivotTableCollection pivotTables = sheet.getPivotTables();

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(0);
//Create another style object with yellow color
Style style = workbook.createStyle();
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.getYellow());

//Format the cells of the first row of the pivot table with yellow color
for (int row = 4; row <= 5; row++)
{
pivotTable.format(row, 4, style);
}
for (int row = 4; row <= 5; row++)
{
pivotTable.format(row, 5, style);
}

//Saving the Excel file
workbook.save("C://Users//test.xlsx");


------------------------------------------
you can see the attchment excel (test.xlsx) , the result seems not our expactation.
Expected result is E5-E6 and F5-F6 should be in yellow background.

Questions:
1: may I know how to handle this case?
2: the param col and row in pivotTable.format api seems the absolute position, may any api with relative position for pivot table?
3: it seems can't add hyperlink in pivotTable cell , is it?
2

Hi,


Please try our latest version/ fix: Aspose.Cells for Java v8.3.1.2

Please see the following sample code for your needs for your complete reference. I have also attached the input and output files for your reference:
e.g
Sample code:

Workbook workbook = new Workbook(“test1.xlsx”);

//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.getWorksheets().get(1);
PivotTableCollection pivotTables = sheet.getPivotTables();

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(0);

//Create another style object with yellow color
Style style = workbook.createStyle();
style.setBackgroundColor(Color.getYellow());
style.setPattern(BackgroundType.SOLID);


//Apply formatting to the Row range of PivotTable.
CellArea area = pivotTable.getRowRange();
int startRow = area.StartRow;
System.out.println(startRow);
int startCol = area.StartColumn;
System.out.println(startCol);
for (int i = pivotTable.getRowRange().StartRow; i <= pivotTable.getRowRange().EndRow; i++)
{

for (int j = pivotTable.getRowRange().StartColumn; j <= pivotTable.getRowRange().EndColumn; j++)
{
pivotTable.format(i, j, style);

}
}
pivotTable.setRefreshDataFlag(true);
pivotTable.refreshData();
pivotTable.calculateData();
pivotTable.calculateRange();
pivotTable.setRefreshDataFlag(false);

//Saving the Excel file
workbook.save(“out1test2.xlsx”);


Hope, it helps a bit.

Thank you.

3

hi, thanks for your reply, it works well with you example code.
But when I tried another case in which I create pivot table through coding instead of from excel directly it would fail.



here is my example code:
--------------------------
//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet sheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = sheet.getCells();

//Setting the value to the cells
Cell cell = cells.get(“A1”);
cell.setValue(“Sport”);
cell = cells.get(“B1”);
cell.setValue(“Quarter”);
cell = cells.get(“C1”);
cell.setValue(“Sales”);
cell = cells.get(“D1”);
cell.setValue(“Names”);


cell = cells.get(“A2”);
cell.setValue(“Golf”);
cell = cells.get(“A3”);
cell.setValue(“Golf”);
cell = cells.get(“A4”);
cell.setValue(“Tennis”);
cell = cells.get(“A5”);
cell.setValue(“Tennis”);
cell = cells.get(“A6”);
cell.setValue(“Tennis”);
cell = cells.get(“A7”);
cell.setValue(“Tennis”);
cell = cells.get(“A8”);
cell.setValue(“Golf”);

cell = cells.get(“B2”);
cell.setValue(“Qtr3”);
cell = cells.get(“B3”);
cell.setValue(“Qtr4”);
cell = cells.get(“B4”);
cell.setValue(“Qtr3”);
cell = cells.get(“B5”);
cell.setValue(“Qtr4”);
cell = cells.get(“B6”);
cell.setValue(“Qtr3”);
cell = cells.get(“B7”);
cell.setValue(“Qtr4”);
cell = cells.get(“B8”);
cell.setValue(“Qtr3”);

cell = cells.get(“C2”);
cell.setValue(1500);
cell = cells.get(“C3”);
cell.setValue(2000);
cell = cells.get(“C4”);
cell.setValue(600);
cell = cells.get(“C5”);
cell.setValue(1500);
cell = cells.get(“C6”);
cell.setValue(4070);
cell = cells.get(“C7”);
cell.setValue(5000);
cell = cells.get(“C8”);
cell.setValue(6430);
cell = cells.get(“D2”);
cell.setValue(“Ken”);
cell = cells.get(“D3”);
cell.setValue(“Ken”);
cell = cells.get(“D4”);
cell.setValue(“Sam”);
cell = cells.get(“D5”);
cell.setValue(“Sam”);
cell = cells.get(“D6”);
cell.setValue(“Sam”);
cell = cells.get(“D7”);
cell.setValue(“Sam”);
cell = cells.get(“D8”);
cell.setValue(“Ken”);

PivotTableCollection pivotTables = sheet.getPivotTables();

//Adding a PivotTable to the worksheet
int index = pivotTables.add("=A1:D8",“E3”,“PivotTable2”);

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);

//Unshowing grand totals for rows.
pivotTable.setRowGrand(false);

//Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW,0);
pivotTable.addFieldToArea(PivotFieldType.ROW,3);

//Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN,1);

//Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA,2);
//pivotTable setting
pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
//Row Labels setting
PivotFieldCollection rowFields = pivotTable.getRowFields();
if (rowFields != null) {
for (int i=0; i<rowFields.getCount(); i++) {
rowFields.get(i).setAutoSubtotals(false);
}
}
//Create another style object with yellow color
Style style = workbook.createStyle();
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.getYellow());

//CellArea area = pivotTable.getRowRange(); //highlight code line
pivotTable.format(3, 4, style);
pivotTable.format(3, 5, style);
pivotTable.format(3, 6, style);
pivotTable.format(4, 4, style);
pivotTable.format(4, 5, style);
pivotTable.format(4, 6, style);
pivotTable.format(5, 4, style);
pivotTable.format(5, 5, style);
pivotTable.format(5, 6, style);
pivotTable.setRefreshDataFlag(true);
pivotTable.refreshData();
pivotTable.calculateData();
pivotTable.calculateRange();
pivotTable.setRefreshDataFlag(false);

//Saving the Excel file
workbook.save(“C://Users//test.xlsx”);


1:the result in test.xlsx is not our expected result.
2: after I added //highlight code line, the result is correct in test_with_highlight_line.xlsx.

any idear about this strange case? thanks for you attention .

4

Hi,


Thanks for the template files and sample code.

I observed the strange behavior regarding formatting in the output
PivotTable report when using and without using the line of code
(“CellArea area = pivotTable.getRowRange();”) that corresponds to
retrieving Row range. When commenting the line of code, it does not
format the PivotTable report items Ok. When using the line of code, it
does format the table fine.


e.g


Sample code:


//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet sheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = sheet.getCells();

//Setting the value to the cells
Cell cell = cells.get("A1");
cell.setValue("Sport");
cell = cells.get("B1");
cell.setValue("Quarter");
cell = cells.get("C1");
cell.setValue("Sales");
cell = cells.get("D1");
cell.setValue("Names");


cell = cells.get("A2");
cell.setValue("Golf");
cell = cells.get("A3");
cell.setValue("Golf");
cell = cells.get("A4");
cell.setValue("Tennis");
cell = cells.get("A5");
cell.setValue("Tennis");
cell = cells.get("A6");
cell.setValue("Tennis");
cell = cells.get("A7");
cell.setValue("Tennis");
cell = cells.get("A8");
cell.setValue("Golf");

cell = cells.get("B2");
cell.setValue("Qtr3");
cell = cells.get("B3");
cell.setValue("Qtr4");
cell = cells.get("B4");
cell.setValue("Qtr3");
cell = cells.get("B5");
cell.setValue("Qtr4");
cell = cells.get("B6");
cell.setValue("Qtr3");
cell = cells.get("B7");
cell.setValue("Qtr4");
cell = cells.get("B8");
cell.setValue("Qtr3");

cell = cells.get("C2");
cell.setValue(1500);
cell = cells.get("C3");
cell.setValue(2000);
cell = cells.get("C4");
cell.setValue(600);
cell = cells.get("C5");
cell.setValue(1500);
cell = cells.get("C6");
cell.setValue(4070);
cell = cells.get("C7");
cell.setValue(5000);
cell = cells.get("C8");
cell.setValue(6430);

cell = cells.get("D2");
cell.setValue("Ken");
cell = cells.get("D3");
cell.setValue("Ken");
cell = cells.get("D4");
cell.setValue("Sam");
cell = cells.get("D5");
cell.setValue("Sam");
cell = cells.get("D6");
cell.setValue("Sam");
cell = cells.get("D7");
cell.setValue("Sam");
cell = cells.get("D8");
cell.setValue("Ken");

PivotTableCollection pivotTables = sheet.getPivotTables();

//Adding a PivotTable to the worksheet
int index = pivotTables.add("=A1:D8","E3","PivotTable2");

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);

//Unshowing grand totals for rows.
pivotTable.setRowGrand(false);

//Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW,0);
pivotTable.addFieldToArea(PivotFieldType.ROW,3);

//Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN,1);

//Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA,2);


//pivotTable setting
pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);

//Row Labels setting
PivotFieldCollection rowFields = pivotTable.getRowFields();
if (rowFields != null) {
for (int i=0; i<rowFields.getCount(); i++) {
rowFields.get(i).setAutoSubtotals(false);
}
}


//Create another style object with yellow color
Style style = workbook.createStyle();
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.getYellow());


//CellArea area = pivotTable.getRowRange(); //highlight code line

pivotTable.format(3, 4, style);
pivotTable.format(3, 5, style);
pivotTable.format(3, 6, style);

pivotTable.format(4, 4, style);
pivotTable.format(4, 5, style);
pivotTable.format(4, 6, style);

pivotTable.format(5, 4, style);
pivotTable.format(5, 5, style);
pivotTable.format(5, 6, style);

pivotTable.setRefreshDataFlag(true);
pivotTable.refreshData();
pivotTable.calculateData();
pivotTable.calculateRange();
pivotTable.setRefreshDataFlag(false);

//Saving the Excel file
workbook.save("out1.xlsx");


We need to investigate it thoroughly to evaluate your issue. I have logged an investigation ticket with an id “CELLSJAVA-41151” for your issue. We will look into it soon.


Once we have any update on it, we will let you know here.

Thank you.
5
hi,
I have another format issue for pivot table, please kindly see sample code below:

sample code:
------------------// Workbook workbook = new Workbook("C://Users//test.xlsx");
Workbook workbook = new Workbook("C://Users//test.xlsx");

//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
PivotTableCollection pivotTables = sheet.getPivotTables();

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(0);
Style style = new Style();
style.setPattern(BackgroundType.SOLID);
style.setBackgroundColor(Color.getYellow());

//Apply formatting to the DataBodyRange of PivotTable.
CellArea dataArea = pivotTable.getDataBodyRange();
int dataAreaStartRow = dataArea.StartRow;
int dataAreaStartColumn = dataArea.StartColumn;
System.out.println("DataBody StartRow="+dataAreaStartRow + " StartColumn=" + dataAreatartColumn );

//confusing code 1:
pivotTable.format(dataAreaStartRow, dataAreaStartColumn+1, style);
//populate pivotTable to the sheet
pivotTable.setRefreshDataFlag(true);
pivotTable.refreshData();
pivotTable.calculateData();
pivotTable.calculateRange();
pivotTable.setRefreshDataFlag(false);
//confusing code 2:
System.out.println("@@@@@@@@@@@F12 value="+sheet.getCells().get("F12").getValue());
System.out.println("@@@@@@@@@@@F13 value="+sheet.getCells().get("F13").getValue());
System.out.println("@@@@@@@@@@@G10 value="+sheet.getCells().get("G10").getValue());
System.out.println("@@@@@@@@@@@G12 value="+sheet.getCells().get("G12").getValue());

//confusing code 3:
HyperlinkCollection hyperlinks = sheet.getHyperlinks();
hyperlinks.add(9,0,1,1,"www.baidu.com");
hyperlinks.add(10,0,1,1,"www.baidu.com");
//Saving the Excel file
workbook.save("C://Users////out_test.xlsx");



-------------------------
questions:
1:in confusing code 1,the style would be applied to F10, even though I use
pivotTable.format(dataAreaStartRow, dataAreaStartColumn+1, style) which cell is F11.
But if I use pivotTable.format(dataAreaStartRow, dataAreaStartColumn, style) , the F10 would have not style on it.

2:in confusing code 2: , you will see the values are not matched with out_test.xlsx.
As my requirement need to check the cell value, if the value is negative, we should change style to font red, so we need this feature, or any other solution?

3: in //confusing code 3:,, you will see I have applied A10 and A11 with hyperlink, but it doesn't work.


Thanks for your kindly attention.
6

Hi,

Thanks for providing us template files and code segment.

For your issues/ queries:

  1. After an initial test, I observed the issue as you mentioned by using the following
    sample code. I got the formatting issue with data area in the pivot
    table report, see the comments attached to the lines of codes. I used the template file provided by you.

e.g

Sample code:

Workbook workbook = new Workbook("test.xlsx"); 

//Obtaining the reference of the newly added worksheet 
Worksheet sheet = workbook.getWorksheets().get(0); 

PivotTableCollection pivotTables = sheet.getPivotTables(); 

//Accessing the instance of the newly added PivotTable 
PivotTable pivotTable = pivotTables.get(0); 

Style style = new Style(); 
style.setPattern(BackgroundType.SOLID); 
style.setBackgroundColor(Color.getYellow()); 

//Apply formatting to the DataBodyRange of PivotTable. 
CellArea dataArea = pivotTable.getDataBodyRange(); 
int dataAreaStartRow = dataArea.StartRow; 
int dataAreaStartColumn = dataArea.StartColumn; 
int dataAreaEndRow = dataArea.EndRow; 
int dataAreaEndColumn = dataArea.EndColumn; 

System.out.println("DataBody StartRow="+dataAreaStartRow + " StartColumn=" + dataAreaStartColumn + " EndRow=" + dataAreaEndRow + " EndColumn=" + dataAreaEndColumn ); //Ok 

pivotTable.format(dataAreaStartRow, dataAreaStartColumn, style);
// It should format F10 cell in the data area but it does not format the
cell at all.



//pivotTable.format(dataAreaStartRow, dataAreaStartColumn+1, style); // It does format F10 cell though.



//populate pivotTable to the sheet 
pivotTable.setRefreshDataFlag(true); 
pivotTable.refreshData(); 
pivotTable.calculateData(); 
pivotTable.calculateRange(); 
pivotTable.setRefreshDataFlag(false); 

//Saving the Excel file 
workbook.save("out_test1.xlsx");

I have logged a ticket with an id “CELLSJAVA-41158” for your issue into our database. We will look into it soon.

  1. After an initial test, I observed the issue as you mentioned by using the following sample code. I got the issue with calculating Pivot data. I used the template file provided by you.
    e.g
    Sample code:
Workbook workbook = new Workbook("test.xlsx"); 

//Obtaining the reference of the newly added worksheet 
Worksheet sheet = workbook.getWorksheets().get(0); 

PivotTableCollection pivotTables = sheet.getPivotTables(); 

//Accessing the instance of the newly added PivotTable 
PivotTable pivotTable = pivotTables.get(0); 

Style style = new Style(); 
style.setPattern(BackgroundType.SOLID); 
style.setBackgroundColor(Color.getYellow()); 

//Apply formatting to the DataBodyRange of PivotTable. 
CellArea dataArea = pivotTable.getDataBodyRange(); 
int dataAreaStartRow = dataArea.StartRow; 
int dataAreaStartColumn = dataArea.StartColumn; 
int dataAreaEndRow = dataArea.EndRow; 
int dataAreaEndColumn = dataArea.EndColumn; 

System.out.println("DataBody StartRow="+dataAreaStartRow + " StartColumn=" + dataAreaStartColumn + " EndRow=" + dataAreaEndRow + " EndColumn=" + dataAreaEndColumn ); //Ok 

pivotTable.format(dataAreaStartRow, dataAreaStartColumn+1, style); // It does format F10 cell though.



//populate pivotTable to the sheet 
pivotTable.setRefreshDataFlag(true); 
pivotTable.refreshData(); 
pivotTable.calculateData(); 
pivotTable.calculateRange(); 
pivotTable.setRefreshDataFlag(false); 


//It does not print expected values using the following lines of code segment 
System.out.println("@@@@@@@@@@@F12 value="+sheet.getCells().get("F12").getValue()); 
System.out.println("@@@@@@@@@@@F13 value="+sheet.getCells().get("F13").getValue()); 
System.out.println("@@@@@@@@@@@G10 value="+sheet.getCells().get("G10").getValue()); 
System.out.println("@@@@@@@@@@@G12 value="+sheet.getCells().get("G12").getValue());

I have logged a ticket with an id “CELLSJAVA-41159” for your issue into our database. We will look into it soon as well.

Once we have any update on any of the above mentioned issues, we will let you know here.

  1. I am afraid, this is not possible in Ms Excel either (you may confirm it too in MS Excel manually), so you can’t add hyperlinks to pivot items values.

Thank you.

7

Hi,

Thanks for using Aspose.Cells.

We have fixed this issue.

Please download and try the latest fix: Aspose.Cells
for Java v8.3.1.6 and let us know your feedback.

8

The issues you have found earlier (filed as CELLSJAVA-41151;CELLSJAVA-41158;CELLSJAVA-41159) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.