Pivot charts?

KevinHunter · January 7, 2009, 9:02am

Ok this is my last question , honest !

I am trying to replace an existing report that i generate using office automation. The spreadsheet gathers a lot of data and produces a pivot table and pivot chart.

I have successfully duplicated the existing report using aspose.cells but I can't see how to duplicate the pivot chart.

I generate 100% of the spreadsheet. No templates are used and i would prefer not to use a template.

Im using VB.net and a winforms application.

Thanks

Kevin

amjad.sahi · January 7, 2009, 11:15am

Hi Kevin,

Yes, you can create pivot charts based on pivot table data source using Aspose.Cells APIs. May the following sample code help you to implement your requirements, kindly consult it.

Sample code:

'Instantiating an Workbook object
Dim workbook As New Workbook()
'Obtaining the reference of the newly added worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim cells As Cells = sheet.Cells
'Setting the value to the cells
Dim cell As Cell = cells("A1")
cell.PutValue("Sport")
cell = cells("B1")
cell.PutValue("Quarter")
cell = cells("C1")
cell.PutValue("Sales")

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

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

cell = cells("C2")
cell.PutValue(1500)
cell = cells("C3")
cell.PutValue(2000)
cell = cells("C4")
cell.PutValue(600)
cell = cells("C5")
cell.PutValue(1500)
cell = cells("C6")
cell.PutValue(4070)
cell = cells("C7")
cell.PutValue(5000)
cell = cells("C8")
cell.PutValue(6430)
Dim pivotTables As PivotTables = sheet.PivotTables
'Adding a PivotTable to the worksheet
Dim index As Integer = pivotTables.Add("=A1:C8", "E20", "PivotTable1")
'Accessing the instance of the newly added PivotTable
Dim pivotTable As PivotTable = pivotTables(index)
'Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0)
'Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1)
'Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2)
index = sheet.Charts.Add(ChartType.Column, 0, 5,18, 13)
sheet.Charts(index).PivotSource = "Sheet1!PivotTable1"
sheet.Charts(index).HidePivotFieldButtons = False
'Saving the Excel file
workbook.Save("f:\test\pivotchar_test.xls")

Thank you.

pawel.wozniak · January 20, 2009, 10:24am

Hi Amjad,

index = sheet.Charts.Add(ChartType.Column, 0, 5,18, 13)
sheet.Charts(index).PivotSource = “Sheet1!PivotTable1”
sheet.Charts(index).HidePivotFieldButtons = False

I cannot find similar solution in Java.

Can you help me?

Regards,
Pawel

amjad.sahi · January 20, 2009, 11:23am

Hi,

Well, I am afraid similar APIs are not available for the Java version of the product, we will make them soon. But I think you may try to create your desired chart separately using Chart related APIs provided by Aspose.Cells for Java, check the section: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/creating-charts.html

Thank you.

amjad.sahi · January 21, 2009, 12:58am

Hi,

Please try the attached version. We have supported Pivot Charts by providing some new APIs i.e.., Chart.setPivotSource/getPivotSource, Chart.hidePivotFieldButton/isHiddenPivotFieldButton,

The corresponding code for pivot chart can be:

Chart chart = sheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 20, 2, 28, 5);
chart.setPivotSource("Sheet1!PivotTable1");
chart.hidePivotFieldButton(false);

Thank you.

pawel.wozniak · January 22, 2009, 3:01pm

Hi Amjad,

Thank you for your help. The Pivot Chart works correctly, but I have another problem.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().getSheet(0);
sheet.setName(“TEST”);

Cells cells = sheet.getCells();
Cell cell = cells.getCell(“A1”);
cell.setValue(“Column0”);
cell = cells.getCell(“B1”);
cell.setValue(“Column1”);
cell = cells.getCell(“C1”);
cell.setValue(“Column2”);
cell = cells.getCell(“D1”);
cell.setValue(“Column3”);

cell = cells.getCell(“A2”);
cell.setValue(“Element1”);
cell = cells.getCell(“A3”);
cell.setValue(“Element1”);
cell = cells.getCell(“A4”);
cell.setValue(“Element2”);
cell = cells.getCell(“A5”);
cell.setValue(“Element3”);
cell = cells.getCell(“A6”);
cell.setValue(“Element2”);
cell = cells.getCell(“A7”);
cell.setValue(“Element2”);
cell = cells.getCell(“A8”);
cell.setValue(“Element1”);

cell = cells.getCell(“B2”);
cell.setValue(3100);
cell = cells.getCell(“B3”);
cell.setValue(200);
cell = cells.getCell(“B4”);
cell.setValue(600);
cell = cells.getCell(“B5”);
cell.setValue(15000);
cell = cells.getCell(“B6”);
cell.setValue(4070);
cell = cells.getCell(“B7”);
cell.setValue(5000);
cell = cells.getCell(“B8”);
cell.setValue(6430);

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

cell = cells.getCell(“D2”);
cell.setValue(15000);
cell = cells.getCell(“D3”);
cell.setValue(200);
cell = cells.getCell(“D4”);
cell.setValue(670);
cell = cells.getCell(“D5”);
cell.setValue(17000);
cell = cells.getCell(“D6”);
cell.setValue(4770);
cell = cells.getCell(“D7”);
cell.setValue(5700);
cell = cells.getCell(“D8”);
cell.setValue(6730);

Worksheet sheet1 = workbook.getWorksheets().addSheet();
sheet1.setName(“Test2”);

PivotTables pivotTables = sheet1.getPivotTables();
int index = pivotTables.add(“TEST!A1:D8”,“E3”,“PivotTable1”);
PivotTable pivotTable = pivotTables.get(index);

pivotTable.getRowFields().addByBaseIndex(0);
pivotTable.getDataFields().addByBaseIndex(1);
pivotTable.getDataFields().addByBaseIndex(2);
pivotTable.getDataFields().addByBaseIndex(3);

pivotTable.addFieldToArea(PivotFieldType.COLUMN,pivotTable.getDataField());

pivotTable.setAutoFormat(true);
pivotTable.setAutoFormatType(PivotTableAutoFormatType.TABLE1);

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

I need to sort the PivotTable by ‘Column3’.

Can you help me one more time?

Regards,
Pawel

nausherwan.aslam · January 23, 2009, 2:19am

Hi,

Thank you for considering Aspose.

You can use Pivot Fields sorting options to sort the Pivot table data. I have modified you code as per your requirement,

Workbook workbook = new Workbook();

Worksheet sheet = workbook.getWorksheets().getSheet(0);

sheet.setName(“TEST”);

Cells cells = sheet.getCells();

Cell cell = cells.getCell(“A1”);

cell.setValue(“Column0”);

cell = cells.getCell(“B1”);

cell.setValue(“Column1”);

cell = cells.getCell(“C1”);

cell.setValue(“Column2”);

cell = cells.getCell(“D1”);

cell.setValue(“Column3”);

cell = cells.getCell(“A2”);

cell.setValue(“Element1”);

cell = cells.getCell(“A3”);

cell.setValue(“Element1”);

cell = cells.getCell(“A4”);

cell.setValue(“Element2”);

cell = cells.getCell(“A5”);

cell.setValue(“Element3”);

cell = cells.getCell(“A6”);

cell.setValue(“Element2”);

cell = cells.getCell(“A7”);

cell.setValue(“Element2”);

cell = cells.getCell(“A8”);

cell.setValue(“Element1”);

cell = cells.getCell(“B2”);

cell.setValue(3100);

cell = cells.getCell(“B3”);

cell.setValue(200);

cell = cells.getCell(“B4”);

cell.setValue(600);

cell = cells.getCell(“B5”);

cell.setValue(15000);

cell = cells.getCell(“B6”);

cell.setValue(4070);

cell = cells.getCell(“B7”);

cell.setValue(5000);

cell = cells.getCell(“B8”);

cell.setValue(6430);

cell = cells.getCell(“C2”);

cell.setValue(1500);

cell = cells.getCell(“C3”);

cell.setValue(2000);

cell = cells.getCell(“C4”);

cell.setValue(600);

cell = cells.getCell(“C5”);

cell.setValue(1500);

cell = cells.getCell(“C6”);

cell.setValue(4070);

cell = cells.getCell(“C7”);

cell.setValue(5000);

cell = cells.getCell(“C8”);

cell.setValue(6430);

cell = cells.getCell(“D2”);

cell.setValue(15000);

cell = cells.getCell(“D3”);

cell.setValue(200);

cell = cells.getCell(“D4”);

cell.setValue(670);

cell = cells.getCell(“D5”);

cell.setValue(17000);

cell = cells.getCell(“D6”);

cell.setValue(4770);

cell = cells.getCell(“D7”);

cell.setValue(5700);

cell = cells.getCell(“D8”);

cell.setValue(6730);

Worksheet sheet1 = workbook.getWorksheets().addSheet();

sheet1.setName(“Test2”);

PivotTables pivotTables = sheet1.getPivotTables();

int index = pivotTables.add(“TEST!A1:D8”, “E3”, “PivotTable1”);

PivotTable pivotTable = pivotTables.get(index);

pivotTable.getRowFields().addByBaseIndex(0);

pivotTable.getDataFields().addByBaseIndex(1);

pivotTable.getDataFields().addByBaseIndex(2);

pivotTable.getDataFields().addByBaseIndex(3);

pivotTable.addFieldToArea(PivotFieldType.COLUMN , pivotTable.getDataField());

pivotTable.setAutoFormat(true );

pivotTable.setAutoFormatType(PivotTableAutoFormatType.TABLE1 );

pivotTable.setRowGrand(false );

pivotTable.setColumnGrand(false );

//Accessing the row fields.

PivotFields pivotFields = pivotTable.getRowFields();

//Accessing the first row field in the row fields.

PivotField pivotField = pivotFields.get(0);

//Setting autosort options.

//Setting the field auto sort.

pivotField.setAutoSort( true );

//Setting the field auto sort ascend.

pivotField.setAscendSort( true );

//Setting the field auto sort using the field 2.

pivotField.setAutoSortField(2);

workbook.save(“c:\test_pivot2.xls”);

Please do let us know if you face any problem.

Thank You & Best Regards,

pawel.wozniak · January 30, 2009, 2:07pm

									Hi Amjad,<br><br>Thank you for your help. Aspose Pivot Chart works correctly.<br><br>Regards, <br><br>Pawel<br>