Need to ask if Aspose cells can do this?

Aspose.Cells Product Family
1

Hi,


I want to ask if it is possible to get Rows in such a way that I am able to construct the whole Pivot table just from rows.

I want aspose do all the calculations etc. I just after the process is through, can iterate all the rows that Aspose has designed for me and show it on my web page.

I am attaching a file which have data and a sample of the type of Pivot table that are being used.


When I was coding to create Pivot table, I was getting errors to for “Diff in Days” and “Diff in hours” columns.

I am Java developer but never have done such a thing before.

I find that there are
getRowFields() and getColumnFields() methods but that will not give us the design, the format.


The code is below:




import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import com.aspose.cells.AsposeLicenseException;
import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.License;
import com.aspose.cells.PivotFieldType;
import com.aspose.cells.PivotTable;
import com.aspose.cells.PivotTableAutoFormatType;
import com.aspose.cells.PivotTables;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;


public class Tiger2 {

/**
* @param args
*/
public static void main(String[] args) {

//Creating a file input stream to reference the license file

/FileInputStream fstream = null;
try {
fstream = new FileInputStream(“Aspose.Cells.lic”);
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

//Create a License object

License license=new License();

//Set the license of Aspose.Cells to avoid the evaluation

//limitations

try {
license.setLicense(fstream);
} catch (AsposeLicenseException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

/

//Instantiating an Workbook object

Workbook workbook = new Workbook();

try {
workbook.open(“ProcessData.xlsx”,FileFormatType.XLSX);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

//Obtaining the reference of the first worksheet

Worksheet sheet = workbook.getWorksheets().getSheet(“DATA”);

Cells cells = sheet.getCells();


Workbook workbook2 = new Workbook();
//Adding a new sheet

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

//Naming the sheet

sheet2.setName(“PivotTable”);

//Getting the pivottables collection in the sheet

PivotTables pivotTables = sheet2.getPivotTables();

//Adding a PivotTable to the worksheet

int index = pivotTables.add("=DATA!A1:W1000", “B3”, “PivotTable1”);

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables.get(index);

//Showing the grand totals

pivotTable.setRowGrand(true);

///pivotTable.setColumnGrand(true);

//Setting the PivotTable report is automatically formatted

///pivotTable.setAutoFormat(true);

//Setting the PivotTable autoformat type.

pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT10);

//Draging the first field to the row area.

pivotTable.addFieldToArea(PivotFieldType.PAGE, 8);

//Draging the third field to the row area.

pivotTable.addFieldToArea(PivotFieldType.ROW, 19);

//THESE ARE THE TWO FIELDS WHICH IF I ADD, GIVE ERROR AND NO DATA APPEARS
/// pivotTable.addFieldToArea(PivotFieldType.ROW, 20);
// pivotTable.addFieldToArea(PivotFieldType.ROW, 21);

//Draging the second field to the row area.

pivotTable.addFieldToArea(PivotFieldType.ROW, 0);

//Draging the fourth field to the column area.

///pivotTable.addFieldToArea(PivotFieldType.ROW, 20);

//Draging the fifth field to the data area.

// pivotTable.addFieldToArea(PivotFieldType.ROW, 21);

//Setting the number format of the first data field

///System.out.println(pivotTable.getRowFields().get(2).getName());
///pivotTable.getRowFields().get(2).setNumberFormat("#00.00");

//Saving the Excel file

try {
workbook.save(“pivotTable_2.xlsx”,FileFormatType.XLSX);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}




}

}


2

If it is a helpful API for us, then we will be buying it.

Thanks in advance,

Ahere

3

Hi,

Thanks for providing us sample code with details plus template file.

We are looking into your requirement now. I have logged a ticket with an id: CELLSJAVA-27214. There is some number formatting or other issue involved for it and we are already working on it. We will get back to you soon.

Thank you.

4



Thanks. And I hope that you folks keep on eye on our requirement. We need to display Pivot table in browser using Vaadin ie Swing - like UI api.

I need to show data as it is usually structured in an Excel file. There must be some methods that help in going through the Pivot table as it is constructed by Aspose cells and extract line by line information…(Cells value etc).


like

Type(Show for)
WORK
---------
PENDING


Task/Transitions Total in Days Total in Hours Average Days Average Hours
+ …
…
…

And so forth


Is there any method that will tell me to make a cell expand and collapse?


Thanks

5

Hi,

Thanks for your further elaboration. We have logged your requirement.

6

Thank you very much,

And it is also in sialkot too?

7

Hi,

Aspose operates globally.

8

Hi,

Regarding your issue with setting Number Format for row Fields in Pivot Table. You need to call pivotTable.setRefreshDataOnOpeningFile(true) before saving the excel file to disk. Hopefully this will solve your problem. See the code snippet below,

//System.out.println(pivotTable.getRowFields().get(2).getName()); //pivotTable.getRowFields().get(2).setNumberFormat("#00.00"); pivotTable.setRefreshDataOnOpeningFile(true);

//Saving the Excel file
try
{
workbook.save(“pivotTable_2.xlsx”,FileFormatType.XLSX);
}

catch (IOException e)
{
e.printStackTrace();
}


Regarding your query about Expanded and Collapsed Cells. We will provide following methods for you in our next fix release to set and check whether one pivot item is hidden or not.

Sample Code:
pivot.getRowFields().get(0).hideItemDetail(0,false);
pivot.getRowFields().get(0).isHiddenItemDetail(0);

Thanks.

9

Hi,

We will provide new methods to check and set visibility of pivotfield’s items:

PivotField.hideItemDetail(index, isHidden);
PivotField.isHiddenItemDetail(index);

But for applying pivottable’s style automatically to cells in pivottable, I am afraid it is complicated for us and we cannot support it soon.

10
Hi,

In the new fix version of Aspose.Cells JAVA v2.5.3.6 [Attached] we provide following methods to check and set visiblity of pivotfield's items:

PivotField.hideItemDetail(index, isHidden);
PivotField.isHiddenItemDetail(index);

Please share your feedback on this. Thank you
11

The issues you have found earlier (filed as 27214) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.