Replace RowLabels, ColumnLabels with actual column names in Pivot excel xlsx format

Aspose.Cells Product Family
,
1

Hi, I have xlsx file having pivots, which is displaying labels i.e. RowLabels/ColumnLabels instead of actual column names. It is fine if the file format is xls issue is with xlsx file format. The sample code is,

public static void main(String[] args) {
setAsposeExcelLicense();

    Workbook book = new Workbook();
    Worksheet sheet = book.getWorksheets().get(0);
    Cells cells = sheet.getCells();
    cells.get(0, 0).setValue("fruit");
    cells.get(1, 0).setValue("grape");
    cells.get(2, 0).setValue("blueberry");
    cells.get(3, 0).setValue("kiwi");
    cells.get(4, 0).setValue("cherry");
    cells.get(5, 0).setValue("grape");
    cells.get(6, 0).setValue("blueberry");
    cells.get(7, 0).setValue("kiwi");
    cells.get(8, 0).setValue("cherry");

    cells.get(0, 1).setValue("year");
    cells.get(1, 1).setValue(2020);
    cells.get(2, 1).setValue(2020);
    cells.get(3, 1).setValue(2020);
    cells.get(4, 1).setValue(2020);
    cells.get(5, 1).setValue(2021);
    cells.get(6, 1).setValue(2021);
    cells.get(7, 1).setValue(2021);
    cells.get(8, 1).setValue(2021);

    cells.get(0, 2).setValue("amount");
    cells.get(1, 2).setValue(50);
    cells.get(2, 2).setValue(60);
    cells.get(3, 2).setValue(70);
    cells.get(4, 2).setValue(80);
    cells.get(5, 2).setValue(90);
    cells.get(6, 2).setValue(100);
    cells.get(7, 2).setValue(110);
    cells.get(8, 2).setValue(120);

    PivotTableCollection pivots = sheet.getPivotTables();

    int pivotIndex = pivots.add("=Sheet1!A1:C9", "A12", "TestPivotTable");
    PivotTable pivot = pivots.get(pivotIndex);
    pivot.addFieldToArea(PivotFieldType.ROW, "fruit");
    pivot.addFieldToArea(PivotFieldType.COLUMN, "year");
    pivot.addFieldToArea(PivotFieldType.DATA, "amount");

    pivot.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_MEDIUM_10);

    //Change PivotField's attributes
    //PivotField rowField = pivot.getRowFields().get(0);
    //rowField.setDisplayName("custom display name");

    //Add PivotFilter
    int index = pivot.getPivotFilters().add(0, PivotFilterType.COUNT);
    PivotFilter filter = pivot.getPivotFilters().get(index);
    filter.getAutoFilter().filterTop10(0, false, false, 2);

    //Add PivotFormatCondition
    int formatIndex = pivot.getPivotFormatConditions().add();
    PivotFormatCondition pfc = pivot.getPivotFormatConditions().get(formatIndex);
    FormatConditionCollection fcc = pfc.getFormatConditions();
    fcc.addArea(pivot.getDataBodyRange());
    int idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
    FormatCondition fc = fcc.get(idx);
    fc.setFormula1("100");
    fc.setOperator(OperatorType.GREATER_OR_EQUAL);
    fc.getStyle().setBackgroundColor(com.aspose.cells.Color.getRed());

    pivot.refreshData();
    try {
		pivot.calculateData();
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

    //do your business

    try {
		book.save("pivot-xlsx-out.xlsx");
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	

	
}

labels-pivot-xlsx.png (13.5 KB)

and enclosed the screenshot.

2

@koteswaragunda,

Thanks for the screenshot.

After an initial testing, I am able to reproduce the issue as you mentioned by using your sample code segment. I found “Row Labels” and “Column Labels” are displayed instead of actual row/column names in the pivot table when saving to XLSX file format, it works fine though if we save to XLS.

We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-46062

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

@koteswaragunda
Default layout is compact as MS Excel now.
So please call PivotTable.showInTabularForm() method

int pivotIndex = pivots.add("=Sheet1!A1:C9", "A12", "TestPivotTable");
		PivotTable pivot = pivots.get(pivotIndex);
		pivot.addFieldToArea(PivotFieldType.ROW, "fruit");
		pivot.addFieldToArea(PivotFieldType.COLUMN, "year");
		pivot.addFieldToArea(PivotFieldType.DATA, "amount");
		pivot.showInTabularForm();
4

Thanks @simon.zhao calling PivotTable.showInTabularForm() resolved the problem.

5

@koteswaragunda
You are welcome. Thank you for your feedback. I’m glad your issue has been resolved by calling PivotTable.showInTabularForm() method. If you have any questions, please feel free to contact us at any time.