Aspose Cell Java - Customize Labels

Aspose.Cells Product Family
1

Dear Support,
We are testing Aspose Cell before buy commercial licence. We try to generate Pivot table, everything is OK but there are some labels we don’t know how to override them, they are generated automatically according to machine’s language like “Etiquette de lignes”, “Etiquette de colonnes”,… Please see enclosed my simple code and screent-shot of generated pivot
Best regards
image.png (18.5 KB)

package com.wexlog.cell;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.ConsolidationFunction;
import com.aspose.cells.PivotField;
import com.aspose.cells.PivotFieldCollection;
import com.aspose.cells.PivotFieldSubtotalType;
import com.aspose.cells.PivotFieldType;
import com.aspose.cells.PivotTable;
import com.aspose.cells.PivotTableCollection;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class TestCell {
public static void main(String[] args) throws Exception {
	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("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);

	PivotTableCollection pivotTables = sheet.getPivotTables();

	//Adding a PivotTable to the worksheet
	int index = pivotTables.add("=A1:C8", "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);

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

	//Dragging the third field to the data area.
	int idx = pivotTable.addFieldToArea(PivotFieldType.DATA, 2);
	//pivotTable.add
	PivotFieldCollection fields = pivotTable.getDataFields();
	PivotField pivotField = fields.get(idx);
	pivotField.setFunction(ConsolidationFunction.COUNT);
	//Saving the Excel file
	workbook.save("AsposeTest.xlsx");
}

}
2

@tbphan

Thanks for considering Aspose APIs.

Please see the following article that will fulfill your needs. Please also read the specific information given below.

This is possible using the GlobalizationSettings class. It has various methods which you can override or implement. For your case, you will implement these two methods.

  • getRowLablesName()
  • getColumnLablesName()

Here is the sample code that implements the above two methods of GlobalizationSettings class.

Java

class GS extends GlobalizationSettings
{
    public String getRowLablesName()
    {
        return "Etiquettes de lignes";
    }

    public String getColumnLablesName()
    {
        return "Etiquettes de collones";
    }
}

Download Link:
Input and Output Excel Files.zip (17.9 KB)

The following code loads the sample Excel file and executes the following code and saves it as output Excel file. Please also see the screenshot given below for a reference.

Java

// Load sample Excel file
Workbook wb = new Workbook(dirPath + "sampleAsposeTest.xlsx");

// Set globalization setting
// >>>>>>>>>> The following line is important
wb.getSettings().setGlobalizationSettings(new GS());

// Access second worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Access the pivot table, refresh and calculate its data
PivotTable pt = ws.getPivotTables().get(0);
pt.setRefreshDataFlag(true);
pt.refreshData();
pt.calculateData();
pt.setRefreshDataFlag(false);

// Do not refresh pivot table on opening
pt.setRefreshDataOnOpeningFile(false);

// Save to output Excel file
wb.save(dirPath + "outputAsposeTest.xlsx");

Screenshot showing the Output Excel File