Hi,

1) How to pivot columns as in attached sample file(We are using java cells.)

Regards

Raj

Hi Raj,

Well, you may create pivot table based on your data source and later group the columns and rows for your requirement.

For creating pivot tables, please check:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/pivot-table.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/how-to-create-a-pivottable.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/setting-pivottables-appearance.html

And since there is some problem with grouping columns, we will look into it fix it soon.

Thank you.

Hi Raj,

  As Amjad said, you can create pivot table first and then group the columns and rows. For example, to group the columns as your template file shown, you can use code like following:

cells.groupColumns(2, 5);
cells.groupColumns(7, 10);
cells.groupColumns(12, 15);

Hi Raj,

  As Amjad said, you can create pivot table first and then group the columns and rows. For grouping feature, for example, to group the columns as your template file shown, you can use code like following:

cells.groupColumns(2, 5);
cells.groupColumns(7, 10);
cells.groupColumns(12, 15);

Im trying to pivot as my sample sheet.

1) Im able to pivot all the columns in a different worksheet area.

2)  What Im expecting is(From my previous sample)

• pivot only in the same area.
• It should not display header like "Sum of / Total " etc...
• I want to pivot only 2 columns, but not all the columns.

Please can u give sample code to pivot(for eample show only 2 rows). 

Im attaching 

1) Excel how Im expecting for as sheet1

2) How Aspose will generate report as sheet2

Regards

Raj 

Hi,

Thanks for considering Aspose.

For your queries.

• pivot only in the same area.

Could you elaborate what do you mean by pivot only the same area, you may show a sample to explain it better.

• It should not display header like "Sum of / Total " etc...

See the attached sample example.

• I want to pivot only 2 columns, but not all the columns.

Perhpas you mean you want to drag two fields to the ROW area in the pivot table report, see the example below and attached sample example for your reference.

Im attaching 

1) Excel how Im expecting for as sheet1

Please refer to the example below. In it, I try to create a pivot table based on a data set to mimic your results in the first worksheet of you template file (you posted), so, you may create the one based on your atcual data.

Well, we are still not very sure about your actual requirement as you don't provide us your actual data source. If you still could not obtain your desired resuts after consulting the two examples here, we should be grateful if you could post the excel file containing actual data source range and your desired pivot table created in MS Excel. So, that we may mimic your desired pivot table based on your actual data range using Aspose.Cells for Java APIs.

Sample Example1:

// Create a new workbook.
   Workbook wb = new Workbook();

   // Create a worksheet object and obtain the first sheet.
  Worksheet sheet = wb.getWorksheets().getSheet(0);

   Cells cells = sheet.getCells();
   //Setting the value to the cells
   Cell cell = cells.getCell("A1");
   cell.setValue("Employee");
   cell = cells.getCell("B1");
   cell.setValue("Quarter");
   cell = cells.getCell("C1");
   cell.setValue("Product");
   cell = cells.getCell("D1");
   cell.setValue("Continent");
   cell = cells.getCell("E1");
   cell.setValue("Country");
   cell = cells.getCell("F1");
   cell.setValue("Sale");

   cell = cells.getCell("A2");
   cell.setValue("David");
   cell = cells.getCell("A3");
   cell.setValue("David");
   cell = cells.getCell("A4");
   cell.setValue("David");
   cell = cells.getCell("A5");
   cell.setValue("David");
   cell = cells.getCell("A6");
   cell.setValue("James");
   cell = cells.getCell("A7");
   cell.setValue("James");
   cell = cells.getCell("A8");
   cell.setValue("James");
   cell = cells.getCell("A9");
   cell.setValue("James");
   cell = cells.getCell("A10");
   cell.setValue("James");
   cell = cells.getCell("A11");
   cell.setValue("Miya");
   cell = cells.getCell("A12");
   cell.setValue("Miya");
   cell = cells.getCell("A13");
   cell.setValue("Miya");
   cell = cells.getCell("A14");
   cell.setValue("Miya");
   cell = cells.getCell("A15");
   cell.setValue("Miya");
   cell = cells.getCell("A16");
   cell.setValue("Miya");
   cell = cells.getCell("A17");
   cell.setValue("Miya");
   cell = cells.getCell("A18");
   cell.setValue("Elvis");
   cell = cells.getCell("A19");
   cell.setValue("Elvis");
   cell = cells.getCell("A20");
   cell.setValue("Elvis");
   cell = cells.getCell("A21");
   cell.setValue("Elvis");
   cell = cells.getCell("A22");
   cell.setValue("Elvis");
   cell = cells.getCell("A23");
   cell.setValue("Elvis");
   cell = cells.getCell("A24");
   cell.setValue("Elvis");
   cell = cells.getCell("A25");
   cell.setValue("Jean");
   cell = cells.getCell("A26");
   cell.setValue("Jean");
   cell = cells.getCell("A27");
   cell.setValue("Jean");
   cell = cells.getCell("A28");
   cell.setValue("Ada");
   cell = cells.getCell("A29");
   cell.setValue("Ada");
   cell = cells.getCell("A30");
   cell.setValue("Ada");

   cell = cells.getCell("B2");
   cell.setValue("1");
   cell = cells.getCell("B3");
   cell.setValue("2");
   cell = cells.getCell("B4");
   cell.setValue("3");
   cell = cells.getCell("B5");
   cell.setValue("4");
   cell = cells.getCell("B6");
   cell.setValue("1");
   cell = cells.getCell("B7");
   cell.setValue("2");
   cell = cells.getCell("B8");
   cell.setValue("3");
   cell = cells.getCell("B9");
   cell.setValue("4");
   cell = cells.getCell("B10");
   cell.setValue("4");
   cell = cells.getCell("B11");
   cell.setValue("1");
   cell = cells.getCell("B12");
   cell.setValue("1");
   cell = cells.getCell("B13");
   cell.setValue("2");
   cell = cells.getCell("B14");
   cell.setValue("2");
   cell = cells.getCell("B15");
   cell.setValue("3");
   cell = cells.getCell("B16");
   cell.setValue("4");
   cell = cells.getCell("B17");
   cell.setValue("4");
   cell = cells.getCell("B18");
   cell.setValue("1");
   cell = cells.getCell("B19");
   cell.setValue("1");
   cell = cells.getCell("B20");
   cell.setValue("2");
   cell = cells.getCell("B21");
   cell.setValue("3");
   cell = cells.getCell("B22");
   cell.setValue("3");
   cell = cells.getCell("B23");
   cell.setValue("4");
   cell = cells.getCell("B24");
   cell.setValue("4");
   cell = cells.getCell("B25");
   cell.setValue("1");
   cell = cells.getCell("B26");
   cell.setValue("2");
   cell = cells.getCell("B27");
   cell.setValue("3");
   cell = cells.getCell("B28");
   cell.setValue("1");
   cell = cells.getCell("B29");
   cell.setValue("2");
   cell = cells.getCell("B30");
   cell.setValue("3");

   cell = cells.getCell("C2");
   cell.setValue("Maxilaku");
   cell = cells.getCell("C3");
   cell.setValue("Maxilaku");
   cell = cells.getCell("C4");
   cell.setValue("Chai");
   cell = cells.getCell("C5");
   cell.setValue("Maxilaku");
   cell = cells.getCell("C6");
   cell.setValue("Chang");
   cell = cells.getCell("C7");
   cell.setValue("Chang");
   cell = cells.getCell("C8");
   cell.setValue("Chang");
   cell = cells.getCell("C9");
   cell.setValue("Chang");
   cell = cells.getCell("C10");
   cell.setValue("Chang");
   cell = cells.getCell("C11");
   cell.setValue("Geitost");
   cell = cells.getCell("C12");
   cell.setValue("Chai");
   cell = cells.getCell("C13");
   cell.setValue("Geitost");
   cell = cells.getCell("C14");
   cell.setValue("Geitost");
   cell = cells.getCell("C15");
   cell.setValue("Maxilaku");
   cell = cells.getCell("C16");
   cell.setValue("Geitost");
   cell = cells.getCell("C17");
   cell.setValue("Geitost");
   cell = cells.getCell("C18");
   cell.setValue("Ikuru");
   cell = cells.getCell("C19");
   cell.setValue("Ikuru");
   cell = cells.getCell("C20");
   cell.setValue("Ikuru");
   cell = cells.getCell("C21");
   cell.setValue("Ikuru");
   cell = cells.getCell("C22");
   cell.setValue("Ipoh Coffee");
   cell = cells.getCell("C23");
   cell.setValue("Ipoh Coffee");
   cell = cells.getCell("C24");
   cell.setValue("Ipoh Coffee");
   cell = cells.getCell("C25");
   cell.setValue("Chocolade");
   cell = cells.getCell("C26");
   cell.setValue("Chocolade");
   cell = cells.getCell("C27");
   cell.setValue("Chocolade");
   cell = cells.getCell("C28");
   cell.setValue("Chocolade");
   cell = cells.getCell("C29");
   cell.setValue("Chocolade");
   cell = cells.getCell("C30");
   cell.setValue("Chocolade");

   cell = cells.getCell("D2");
   cell.setValue("Asia");
   cell = cells.getCell("D3");
   cell.setValue("Asia");
   cell = cells.getCell("D4");
   cell.setValue("Asia");
   cell = cells.getCell("D5");
   cell.setValue("Asia");
   cell = cells.getCell("D6");
   cell.setValue("Europe");
   cell = cells.getCell("D7");
   cell.setValue("Europe");
   cell = cells.getCell("D8");
   cell.setValue("Europe");
   cell = cells.getCell("D9");
   cell.setValue("Europe");
   cell = cells.getCell("D10");
   cell.setValue("Europe");
   cell = cells.getCell("D11");
   cell.setValue("America");
   cell = cells.getCell("D12");
   cell.setValue("America");
   cell = cells.getCell("D13");
   cell.setValue("America");
   cell = cells.getCell("D14");
   cell.setValue("America");
   cell = cells.getCell("D15");
   cell.setValue("America");
   cell = cells.getCell("D16");
   cell.setValue("America");
   cell = cells.getCell("D17");
   cell.setValue("America");
   cell = cells.getCell("D18");
   cell.setValue("Europe");
   cell = cells.getCell("D19");
   cell.setValue("Europe");
   cell = cells.getCell("D20");
   cell.setValue("Europe");
   cell = cells.getCell("D21");
   cell.setValue("Oceania");
   cell = cells.getCell("D22");
   cell.setValue("Oceania");
   cell = cells.getCell("D23");
   cell.setValue("Oceania");
   cell = cells.getCell("D24");
   cell.setValue("Oceania");
   cell = cells.getCell("D25");
   cell.setValue("Africa");
   cell = cells.getCell("D26");
   cell.setValue("Africa");
   cell = cells.getCell("D27");
   cell.setValue("Africa");
   cell = cells.getCell("D28");
   cell.setValue("Africa");
   cell = cells.getCell("D29");
   cell.setValue("Africa");
   cell = cells.getCell("D30");
   cell.setValue("Africa");

   cell = cells.getCell("E2");
   cell.setValue("China");
   cell = cells.getCell("E3");
   cell.setValue("India");
   cell = cells.getCell("E4");
   cell.setValue("Korea");
   cell = cells.getCell("E5");
   cell.setValue("India");
   cell = cells.getCell("E6");
   cell.setValue("France");
   cell = cells.getCell("E7");
   cell.setValue("France");
   cell = cells.getCell("E8");
   cell.setValue("Germany");
   cell = cells.getCell("E9");
   cell.setValue("Italy");
   cell = cells.getCell("E10");
   cell.setValue("France");
   cell = cells.getCell("E11");
   cell.setValue("U.S.");
   cell = cells.getCell("E12");
   cell.setValue("U.S.");
   cell = cells.getCell("E13");
   cell.setValue("Brazil");
   cell = cells.getCell("E14");
   cell.setValue("U.S.");
   cell = cells.getCell("E15");
   cell.setValue("U.S.");
   cell = cells.getCell("E16");
   cell.setValue("Canada");
   cell = cells.getCell("E17");
   cell.setValue("U.S.");
   cell = cells.getCell("E18");
   cell.setValue("Italy");
   cell = cells.getCell("E19");
   cell.setValue("France");
   cell = cells.getCell("E20");
   cell.setValue("Italy");
   cell = cells.getCell("E21");
   cell.setValue("New Zealand");
   cell = cells.getCell("E22");
   cell.setValue("Australia");
   cell = cells.getCell("E23");
   cell.setValue("Australia");
   cell = cells.getCell("E24");
   cell.setValue("New Zealand");
   cell = cells.getCell("E25");
   cell.setValue("S.Africa");
   cell = cells.getCell("E26");
   cell.setValue("S.Africa");
   cell = cells.getCell("E27");
   cell.setValue("S.Africa");
   cell = cells.getCell("E28");
   cell.setValue("Egypt");
   cell = cells.getCell("E29");
   cell.setValue("Egypt");
   cell = cells.getCell("E30");
   cell.setValue("Egypt");

   cell = cells.getCell("F2");
   cell.setValue(2000);
   cell = cells.getCell("F3");
   cell.setValue(500);
   cell = cells.getCell("F4");
   cell.setValue(1200);
   cell = cells.getCell("F5");
   cell.setValue(1500);
   cell = cells.getCell("F6");
   cell.setValue(500);
   cell = cells.getCell("F7");
   cell.setValue(1500);
   cell = cells.getCell("F8");
   cell.setValue(800);
   cell = cells.getCell("F9");
   cell.setValue(900);
   cell = cells.getCell("F10");
   cell.setValue(500);
   cell = cells.getCell("F11");
   cell.setValue(1600);
   cell = cells.getCell("F12");
   cell.setValue(600);
   cell = cells.getCell("F13");
   cell.setValue(2000);
   cell = cells.getCell("F14");
   cell.setValue(500);
   cell = cells.getCell("F15");
   cell.setValue(900);
   cell = cells.getCell("F16");
   cell.setValue(700);
   cell = cells.getCell("F17");
   cell.setValue(1400);
   cell = cells.getCell("F18");
   cell.setValue(1350);
   cell = cells.getCell("F19");
   cell.setValue(300);
   cell = cells.getCell("F20");
   cell.setValue(500);
   cell = cells.getCell("F21");
   cell.setValue(1000);
   cell = cells.getCell("F22");
   cell.setValue(1500);
   cell = cells.getCell("F23");
   cell.setValue(1500);
   cell = cells.getCell("F24");
   cell.setValue(1600);
   cell = cells.getCell("F25");
   cell.setValue(1000);
   cell = cells.getCell("F26");
   cell.setValue(1200);
   cell = cells.getCell("F27");
   cell.setValue(1300);
   cell = cells.getCell("F28");
   cell.setValue(1500);
   cell = cells.getCell("F29");
   cell.setValue(1400);
   cell = cells.getCell("F30");
   cell.setValue(1000);
   
    
   PivotTables pivotTables = sheet.getPivotTables();
   //Adding a PivotTable to the worksheet
   int index = pivotTables.add("=A1:F30","H3","PivotTable2");
   //Accessing the instance of the newly added PivotTable
   PivotTable pivotTable = pivotTables.get(index);
   //Unshowing grand totals for rows.
   pivotTable.setRowGrand(false);
   pivotTable.setColumnGrand(true);
   //Draging fields to the row area.
   pivotTable.addFieldToArea(PivotFieldType.ROW,3);
   pivotTable.addFieldToArea(PivotFieldType.ROW,4);
   //Draging the third field to the column area.
   pivotTable.addFieldToArea(PivotFieldType.COLUMN,2);
   //Draging a field to the data area.
   pivotTable.addFieldToArea(PivotFieldType.DATA,5);
   //Setting the PivotTable report is automatically formatted
   pivotTable.setAutoFormat(true);
   //Setting the PivotTable atuoformat type.
   pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT4);
   //Saving the Excel file
   wb.save("e:\\files\\ptabletest2_1.xls");

Sample Example2 is attached, please check it too.

Thank you.

Hi,

Thanks for your sample. I got how to build Pivot table .

Im attaching xls.

1) Data sheet contains:-

      Data what we have. We need to display the data as in Expected sheet.

Please provide me sample code.

Regards

Raj

Hi Raj,

Well, you template file does not contain source data range, it only contains the resultant pivot table Report. Could you post the source data sheet.

Thank you.

Hi Sahi,

Our sheet does not contain separate data sheet and pivot area. It has only pivot area.

From your sample code this can done by specifying  like this

int index = pivotTables.add("=A1:F30","A1","PivotTable2");

Regards

Raj

Hi Raj,

  Do you mean your attached pivot report was created in MS Excel from the data area "A1:F30" and then the data area was replaced by the created pivot report? Please give us the data in area "A1:F30" before being replaced, we will try to create such a pivot report by Aspose.Cells API.