Problem creating charts from Pivot table with custom number format

Aspose.Cells Product Family
1

I’m working on creating a chart with the datasouce as Pivot Table. The following is the process I use to create the chart.


First, I print the raw table, then create the pivot table from the raw table range. Next, I create the chart from the pivot table.

chart.setPivotSource(pivotTableName);

The problem I have noticed is that when I check the getDataBodyRange() the EndRow value is wrong, hence the chart is not properly created as the end row is calculated wrong.

I’m using Java version 8.8.0

Thank you
2

Hi,


Thanks for your posting and using Aspose.Cells.

Please provide us your source Excel file, your actual output Excel file, your expected output Excel file. You can create expected output Excel file manually using Microsoft Excel.

Please also provide us your sample code, which we could run at our end and observe the issue.

Since, you are using the older version, please also download and try the latest version and see if it makes any difference in resolution of this issue. Let us know your feedback.

Latest Version Link:


3

Thank you for the quick reply, I tried the latest version and the problem persists. I will upload the sample code shortly.

4

Here is the small program I have written describing the issue.


Take a look at the pivotTable.getDataBodyRange() after pivot table calculation, the EndRow property is wrong which seems to be the problem why the pivot chart is not being calculated. We see this problem when using date formats in pivot table.
5

Hi,


Thanks for your posting and using Aspose.Cells.

Please add this line

chart.calculate();

and it will fix your issue.

For more elaboration, please change these lines of code

// Setting the pivot chart data source
chart.setPivotSource(“PivotTable1”);
chart.setHidePivotFieldButtons(false);

into these lines of code. (Notice the red line)

// Setting the pivot chart data source
chart.setPivotSource(“PivotTable1”);
chart.setHidePivotFieldButtons(false);
chart.calculate();

I have also attached the output Excel file generated by the code after this modification for your reference.

6

Thank you Shakeel.


I added chart.calculate() and the chart that gets created, is not correct. You can see the same problem occurs in the file you attached too. I have noticed that generally while using dates the pivot chart is not calculated correcting.

The problem being the range of the pivot table is not passed correctly to the chart creation. Can you please take a look at this?

For reference, please take a look at the worksheet named expected above.
7

Hi,


Thanks for your posting and using Aspose.Cells.

Since, your data contains formulas, you must also calculate formula method of workbook (i.e. Workbook.calculateFormula()) before you start creating your pivot table.

Here is a complete code. Please check the red lines for your reference.

Java
import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.Chart;
import com.aspose.cells.ChartType;
import com.aspose.cells.ListObject;
import com.aspose.cells.ListObjectCollection;
import com.aspose.cells.PivotFieldType;
import com.aspose.cells.PivotTable;
import com.aspose.cells.PivotTableCollection;
import com.aspose.cells.PivotTableStyleType;
import com.aspose.cells.Style;
import com.aspose.cells.TableStyleType;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import java.util.Arrays;
import java.util.List;

/**

  • @author vsriram
    */
    public class PivotChartCreation {

    public static void main(String args[]) {

     try {
     buildWorkbook();
 } catch (Exception e) {
     System.out.println("There's a problem!!! " + e.getMessage());
 }

    }

    private static void buildWorkbook() throws Exception {

     // The path to the documents directory.
 String dataDir = ""; //Utils.getDataDir(PivotChartCreation.class);

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

 Worksheet sheet = workbook.getWorksheets().get(0);
 sheet.setName("Data");

 Cells cells = sheet.getCells();
 int endRow = printRawData(cells);

//Added========================================
workbook.calculateFormula();

ListObjectCollection listObjectCollection = sheet.getListObjects();
int tableIndex = listObjectCollection.add(0, 0, endRow, 2, true);
ListObject listObject = listObjectCollection.get(tableIndex);
listObject.setTableStyleType(TableStyleType.NONE);

    // Adding a new sheet
    int pivotIndex = workbook.getWorksheets().add();
    Worksheet sheet2 = workbook.getWorksheets().get(pivotIndex);
    sheet2.setName("PivotTable");

    // Getting the pivottables collection in the sheet
    PivotTableCollection pivotTables = sheet2.getPivotTables();
    int index = pivotTables.add("=Data!A1:C24", "B35", "PivotTable1");

    PivotTable pivotTable = pivotTables.get(index);
    pivotTable.setRowGrand(false);
    pivotTable.setColumnGrand(false);
    pivotTable.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_22);
    pivotTable.setShowPivotStyleRowHeader(true);
    pivotTable.setShowPivotStyleColumnHeader(true);
    pivotTable.setShowPivotStyleRowStripes(true);

    pivotTable.addFieldToArea(PivotFieldType.ROW, 0);
    pivotTable.getRowFields().get(0).setNumber(17);
    pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1);
    pivotTable.addFieldToArea(PivotFieldType.DATA, 2);
    pivotTable.getDataFields().get(0).setNumber(1);

    sheet2.autoFitColumns();

    pivotTable.refreshData();
    pivotTable.calculateData();

    // Adding a column chart
    int chartId = sheet2.getCharts().add(ChartType.COLUMN, 2, 1, 30, 11);
    Chart chart = sheet2.getCharts().get(chartId);

    // Setting the pivot chart data source
    chart.setPivotSource("PivotTable1");
    chart.setHidePivotFieldButtons(false);

//Added========================================
chart.setType(ChartType.COLUMN_STACKED);
chart.calculate();

workbook.save(dataDir + “pivotChart_actual.xlsx”);
}

private static int printRawData(Cells cells) {

    int rawDataStart = 1;
    // Setting the values to the cells
    Cell cell = cells.get("A" + rawDataStart);
    cell.setValue("Booking Date");
    cell = cells.get("B" + rawDataStart);
    cell.setValue("Room Type");
    cell = cells.get("C" + rawDataStart);
    cell.setValue("Booking");

    List<String> dates = Arrays.asList(
            "=DATE(2018,12,1)",
            "=DATE(2017,12,1)",
            "=DATE(2016,12,1)",
            "=DATE(2016,9,1)",
            "=DATE(2016,6,1)",
            "=DATE(2016,3,1)",
            "=DATE(2016,1,1)",
            "=DATE(2015,12,1)",
            "=DATE(2015,11,1)",
            "=DATE(2015,11,1)",

“=DATE(2015,10,1)”,
“=DATE(2015,10,1)”,
“=DATE(2015,10,1)”,
“=DATE(2015,9,1)”,
“=DATE(2015,7,1)”,
“=DATE(2015,4,1)”,
“=DATE(2015,1,1)”,
“=DATE(2014,12,1)”,
 “=DATE(2014,12,1)”,
“=DATE(2014,6,1)”,
“=DATE(2014,1,1)”,
“=DATE(2013,1,1)”,
“=DATE(2012,1,1)”);

    for (int i = 0; i < dates.size(); i++) {
        cell = cells.get("A" + ((rawDataStart + 1) + i));
        cell.setFormula(dates.get(i));

        Style style = cell.getStyle();
        style.setNumber(17);
        cell.setStyle(style);
    }

    List<String> roomType = Arrays.asList(
            "single",
            "double",
            "double",
            "double",
            "double",
            "double",
            "single",
            "double",
            "suite",
            "single",
            "double",
            "suite",
            "single",
            "double",
            "double",
            "single",
            "single",
            "double",
            "single",
            "double",
            "double",
            "single",
            "single");

    for (int i = 0; i < roomType.size(); i++) {
        cell = cells.get("B" + ((rawDataStart + 1) + i));
        cell.setValue(roomType.get(i));

        cell = cells.get("C" + ((rawDataStart + 1) + i));
        cell.setValue(1);
    }

    return dates.size();
}

}



8

Thanks for getting back, Shakeel.


I tried the above code and it works. The chart gets created but the format does not get applied. I tried using setNumber() or setNumberFormatLinked() properties for <span style=“font-family: “Courier New”; font-size: small;”>chart.getCategoryAxis().getTickLabels() but they don’t seem to work for category axis, value axis or legends. Any API documentation on that?

9

Hi,


Thanks for your posting and using Aspose.Cells.

Please check these two excel files

1 - Expected Excel File (provided by you)
2 - Actual Excel File (generated by Aspose.Cells)

I tried to fix the category axis issue in the actual Excel file manually but I am unable to do so. Could you please open the actual Excel file and fix the category axis using MS-Excel and then let us know the steps. It will help us look into this issue further and we will find the equivalent Aspose.Cells APIs code to achieve your desired results.

10

Hi Shakeel,


The steps to fix will be:

1. Click on the category axis, and format axis.
2. Apply date format, it should apply the right selected format example: “mmm-yy”.

I am working on a pivot chart which has category axis, value axis and legends (3 columns).

Using chart.getCategoryAxis().getTickLabels.setNumber(15) does not format the category axis’s values to be same as the pivot table’s.

Note: When I have 2 columns with one column as date and then create a chart, the right data type which is date (mmm-yy) in this instance is applied correctly or atleast linked to source correctly in chart axis.
11

Hi,


Thanks for using Aspose.Cells.

It seems, Aspose.Cells is not generating the Pivot Chart properly. We need more time to investigate this issue properly. Please spare us some time, we will update you asap.

12

Thanks for the update Shekeel, I will look forward to your findings.

13

Hi,


Thanks for your posting and using Aspose.Cells.

We have looked into this issue further and changed the line into

chart.setPivotSource(“PivotTable!PivotTable1”);

and now it gives good result. Please check the output excel file generated with the following code for your reference. Please check the code highlighted in red color.

Java
import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.Chart;
import com.aspose.cells.ChartType;
import com.aspose.cells.ListObject;
import com.aspose.cells.ListObjectCollection;
import com.aspose.cells.PivotFieldType;
import com.aspose.cells.PivotTable;
import com.aspose.cells.PivotTableCollection;
import com.aspose.cells.PivotTableStyleType;
import com.aspose.cells.Style;
import com.aspose.cells.TableStyleType;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import java.util.Arrays;
import java.util.List;

/**

  • @author vsriram
    */
    public class PivotChartCreation {

    public static void main(String args[]) {

     try {
     buildWorkbook();
 } catch (Exception e) {
     System.out.println("There's a problem!!! " + e.getMessage());
 }

    }

    private static void buildWorkbook() throws Exception {

     // The path to the documents directory.
 String dataDir = ""; //Utils.getDataDir(PivotChartCreation.class);

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

 Worksheet sheet = workbook.getWorksheets().get(0);
 sheet.setName("Data");

 Cells cells = sheet.getCells();
 int endRow = printRawData(cells);

 //Added========================================
 workbook.calculateFormula();
 
 ListObjectCollection listObjectCollection = sheet.getListObjects();
 int tableIndex = listObjectCollection.add(0, 0, endRow, 2, true);
 ListObject listObject = listObjectCollection.get(tableIndex);
 listObject.setTableStyleType(TableStyleType.NONE);

 // Adding a new sheet
 int pivotIndex = workbook.getWorksheets().add();
 Worksheet sheet2 = workbook.getWorksheets().get(pivotIndex);
 sheet2.setName("PivotTable");

 // Getting the pivottables collection in the sheet
 PivotTableCollection pivotTables = sheet2.getPivotTables();
 int index = pivotTables.add("=Data!A1:C24", "B35", "PivotTable1");

 PivotTable pivotTable = pivotTables.get(index);
 pivotTable.setRowGrand(false);
 pivotTable.setColumnGrand(false);
 pivotTable.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_22);
 pivotTable.setShowPivotStyleRowHeader(true);
 pivotTable.setShowPivotStyleColumnHeader(true);
 pivotTable.setShowPivotStyleRowStripes(true);

 pivotTable.addFieldToArea(PivotFieldType.ROW, 0);
 pivotTable.getRowFields().get(0).setNumber(17);
 pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1);
 pivotTable.addFieldToArea(PivotFieldType.DATA, 2);
 pivotTable.getDataFields().get(0).setNumber(1);

 sheet2.autoFitColumns();

 pivotTable.refreshData();
 pivotTable.calculateData();

 // Adding a column chart
 int chartId = sheet2.getCharts().add(ChartType.COLUMN, 2, 1, 30, 11);
 Chart chart = sheet2.getCharts().get(chartId);

 // Setting the pivot chart data source
 </font><font color="#ff0000"><b>chart.setPivotSource("PivotTable!PivotTable1");</b></font><font color="#800080">
 chart.setHidePivotFieldButtons(false);
 //Added========================================
 chart.setType(ChartType.COLUMN_STACKED);
 chart.calculate();

 workbook.save(dataDir + "pivotChart_actual.xlsx");

    }

    private static int printRawData(Cells cells) {

     int rawDataStart = 1;
 // Setting the values to the cells
 Cell cell = cells.get("A" + rawDataStart);
 cell.setValue("Booking Date");
 cell = cells.get("B" + rawDataStart);
 cell.setValue("Room Type");
 cell = cells.get("C" + rawDataStart);
 cell.setValue("Booking");

 List<String> dates = Arrays.asList(
         "=DATE(2018,12,1)",
         "=DATE(2017,12,1)",
         "=DATE(2016,12,1)",
         "=DATE(2016,9,1)",
         "=DATE(2016,6,1)",
         "=DATE(2016,3,1)",
         "=DATE(2016,1,1)",
         "=DATE(2015,12,1)",
         "=DATE(2015,11,1)",
         "=DATE(2015,11,1)",
         "=DATE(2015,10,1)",
         "=DATE(2015,10,1)",
         "=DATE(2015,10,1)",
         "=DATE(2015,9,1)",
         "=DATE(2015,7,1)",
         "=DATE(2015,4,1)",
         "=DATE(2015,1,1)",
         "=DATE(2014,12,1)",
         "=DATE(2014,12,1)",
         "=DATE(2014,6,1)",
         "=DATE(2014,1,1)",
         "=DATE(2013,1,1)",
         "=DATE(2012,1,1)");

 for (int i = 0; i < dates.size(); i++) {
     cell = cells.get("A" + ((rawDataStart + 1) + i));
     cell.setFormula(dates.get(i));

     Style style = cell.getStyle();
     style.setNumber(17);
     cell.setStyle(style);
 }

 List<String> roomType = Arrays.asList(
         "single",
         "double",
         "double",
         "double",
         "double",
         "double",
         "single",
         "double",
         "suite",
         "single",
         "double",
         "suite",
         "single",
         "double",
         "double",
         "single",
         "single",
         "double",
         "single",
         "double",
         "double",
         "single",
         "single");

 for (int i = 0; i < roomType.size(); i++) {
     cell = cells.get("B" + ((rawDataStart + 1) + i));
     cell.setValue(roomType.get(i));

     cell = cells.get("C" + ((rawDataStart + 1) + i));
     cell.setValue(1);
 }

 return dates.size();

    }
    }