Hi Team,
We are try to show between rank data from dynamic excel . through addFieldToArea pushing that column in to pivot table ,But because format of rank data we convert into numbers and after push into pivot set as the rank through setNumberFormat .
Rank data format : 1-3,4-7,8+,NA,NR etc,
Code for set values:
pivotTable.getDataFields().get(indexFormat(Dynamic Excel sheet column no)).setNumberFormat("[=1]1-3;[=2]4-7;8+;");
Issue is,
- when click on rank in formula box showing number instead of rank in formula bar,but we need to show rank
- setNumberFormat not supporting more then 3 given ranks,we need to show 6 to 7 rank values
Second one is, we no need to show total row value for dynamic rank column ,How to hide total value for rank column only, by using below code hide complete total value for all columns but i need particular rank index only dynamic data.
pivotTable.getRowFields().get(indexFormat(Dynamic Excel sheet column no)).setSubtotals(PivotFieldSubtotalType.NONE,true);
Please let us know solution for given issue, If they have any alternate to show rank data and hide total column also,
Please replay ASAP, waiting for your response.
Thank you.
Hi,
Thanks for sharing details.
Could you please provide us your output Excel file (containing the PivotTable) by Aspose.Cells APIs. Also provide an Excel file (containing your desired formatted PivotTable), you may create your desired PivotTable in MS Excel manually. Moreover, please provide us your complete JAVA program (runnable) with your template file (e.g input file) if you are using any. We will check it soon.
Thank you.
Hi,
Please find code for pivot and in previous code we generate excel for sheet 1 and attached file for reference.And, in first sheet you get dynamic excel and sheet 3 for pivot.
String maxColumn = rowNum+"";
int sheetIndex2 = 2;
Worksheet sheet2 = workbook.getWorksheets().get(sheetIndex2);
// worksheet.setVisible(false);
// sheet2.setVisible(true);
sheet2.setName(“Advanced CrossTab”);
PivotTableCollection pivotTables = sheet2.getPivotTables();
int index = pivotTables.add("=Advanced!A8:O"+maxColumn, “A1”, “Advanced CrossTab”);
PivotTable pivotTable = pivotTables.get(index);
pivotTable.setEnableWizard(true);
pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
pivotTable.setAutoFormat(true);
pivotTable.setAutoFormatType(PivotTableAutoFormatType.TABLE_10);
pivotTable.setEnableFieldList(false);
if(pivotRows == null || pivotRows == “”){
pivotTable.addFieldToArea(PivotFieldType.ROW, 15);
}else{
List dataValues = getingColumnValues(pivotRows);
log.info(“dataValues for row::”+dataValues);
for(int i=0;i<dataValues.size();i++){
pivotTable.addFieldToArea(PivotFieldType.ROW, dataValues.get(i));
}
}
if(pivotColoumns == null || pivotColoumns == “”){
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 15);
}else{
List dataValues = getingColumnValues(pivotColoumns);
for(int i=0;i<dataValues.size();i++){
log.info(“dataValues pivotColoumns.get(i)::”+dataValues.get(i));
pivotTable.addFieldToArea(PivotFieldType.COLUMN, dataValues.get(i));
}
}
if(nameValue == null || nameValue == “”){
pivotTable.addFieldToArea(PivotFieldType.DATA, 15);
}else{
List dataValues = getingColumnValues(nameValue);
for(int i=0;i<dataValues.size();i++){
log.info(“dataValues.get(i)::”+dataValues.get(i));
if(dataValues.get(i) == 10 || dataValues.get(i) == 13 || dataValues.get(i) == 12){
if(dataValues.get(i) == 10){//SOW
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
pivotTable.getDataFields().get(indexFormat).setNumberFormat(“0.000%”);
}
if(dataValues.get(i) == 13){//GAP
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
String dataFilterValues = getingColumnFilterValues(dataValues.get(i));
int opratoindex = listVal.indexOf(dataFilterValues);
String opeartorvalue=listVal.get(opratoindex+1);
log.info(“opeartorvalue matched string opeartor”+opeartorvalue);
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(“max”));
}
if(dataValues.get(i) == 12){//Rank
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=1]1-3;[=2]4-7;8+;"); //;[=184]8-10;[=185]11+;
}
}else{
log.info(“listVal::”+listVal);
String dataFilterValues = getingColumnFilterValues(dataValues.get(i));
int opratoindex = listVal.indexOf(dataFilterValues);
String opeartorvalue=listVal.get(opratoindex+1);
log.info(“opeartorvalue matched string opeartor”+opeartorvalue);
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
// pivotTable.getDataFields().get(indexFormat).setFunction(ConsolidationFunction.MAX);
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(opeartorvalue));
}
}
}
pivotTable.getColumnFields().add(pivotTable.getDataField());
sheet2.getCells().deleteBlankColumns();
sheet2.getCells().deleteBlankRows();
DateFormat dateFormat1 = new SimpleDateFormat(“ddMMyyyy_HHmm”);
Date date1 = new Date();
String today1 = dateFormat1.format(date1);
String fileName = “Coalition_Advance_Cross_Tab_”+today1+".xlsx";
response.setHeader(“Content-Disposition”,
“attachment; filename=”+fileName);
workbook.save(out, com.aspose.cells.SaveFormat.XLSX);
out.flush();out.close();licenseFile.close();
Hi,
Please find code for pivot and in previous code we generate excel for sheet 1 and attached file for reference.And, in first sheet you get dynamic excel and sheet 3 for pivot.
String maxColumn = rowNum+"";
int sheetIndex2 = 2;
Worksheet sheet2 = workbook.getWorksheets().get(sheetIndex2);
// worksheet.setVisible(false);
// sheet2.setVisible(true);
sheet2.setName(“Advanced CrossTab”);
PivotTableCollection pivotTables = sheet2.getPivotTables();
int index = pivotTables.add("=Advanced!A8:O"+maxColumn, “A1”, “Advanced CrossTab”);
PivotTable pivotTable = pivotTables.get(index);
pivotTable.setEnableWizard(true);
pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
pivotTable.setAutoFormat(true);
pivotTable.setAutoFormatType(PivotTableAutoFormatType.TABLE_10);
pivotTable.setEnableFieldList(false);
if(pivotRows == null || pivotRows == “”){
pivotTable.addFieldToArea(PivotFieldType.ROW, 15);
}else{
List dataValues = getingColumnValues(pivotRows);
log.info(“dataValues for row::”+dataValues);
for(int i=0;i<dataValues.size();i++){
pivotTable.addFieldToArea(PivotFieldType.ROW, dataValues.get(i));
}
}
if(pivotColoumns == null || pivotColoumns == “”){
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 15);
}else{
List dataValues = getingColumnValues(pivotColoumns);
for(int i=0;i<dataValues.size();i++){
log.info(“dataValues pivotColoumns.get(i)::”+dataValues.get(i));
pivotTable.addFieldToArea(PivotFieldType.COLUMN, dataValues.get(i));
}
}
if(nameValue == null || nameValue == “”){
pivotTable.addFieldToArea(PivotFieldType.DATA, 15);
}else{
List dataValues = getingColumnValues(nameValue);
for(int i=0;i<dataValues.size();i++){
log.info(“dataValues.get(i)::”+dataValues.get(i));
if(dataValues.get(i) == 10 || dataValues.get(i) == 13 || dataValues.get(i) == 12){
if(dataValues.get(i) == 10){//SOW
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
pivotTable.getDataFields().get(indexFormat).setNumberFormat(“0.000%”);
}
if(dataValues.get(i) == 13){//GAP
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
String dataFilterValues = getingColumnFilterValues(dataValues.get(i));
int opratoindex = listVal.indexOf(dataFilterValues);
String opeartorvalue=listVal.get(opratoindex+1);
log.info(“opeartorvalue matched string opeartor”+opeartorvalue);
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(“max”));
}
if(dataValues.get(i) == 12){//Rank
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
pivotTable.getDataFields().get(indexFormat).setNumberFormat("[=1]1-3;[=2]4-7;8+;"); //;[=184]8-10;[=185]11+;
}
}else{
log.info(“listVal::”+listVal);
String dataFilterValues = getingColumnFilterValues(dataValues.get(i));
int opratoindex = listVal.indexOf(dataFilterValues);
String opeartorvalue=listVal.get(opratoindex+1);
log.info(“opeartorvalue matched string opeartor”+opeartorvalue);
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
// pivotTable.getDataFields().get(indexFormat).setFunction(ConsolidationFunction.MAX);
pivotTable.getDataFields().get(indexFormat).setFunction(opearatorFunction(opeartorvalue));
}
}
}
pivotTable.getColumnFields().add(pivotTable.getDataField());
sheet2.getCells().deleteBlankColumns();
sheet2.getCells().deleteBlankRows();
DateFormat dateFormat1 = new SimpleDateFormat(“ddMMyyyy_HHmm”);
Date date1 = new Date();
String today1 = dateFormat1.format(date1);
String fileName = “Coalition_Advance_Cross_Tab_”+today1+".xlsx";
response.setHeader(“Content-Disposition”,
“attachment; filename=”+fileName);
workbook.save(out, com.aspose.cells.SaveFormat.XLSX);
out.flush();out.close();licenseFile.close();
Than you.
Hi,
Thanks for the sample Excel file and code snippet.
I checked your provided file and opened into MS Excel. Probably this is your output Excel file by Aspose.Cells APIs. I checked the Rank pivot field and I manually tried to input the custom formatting “[=1]1-3;[=2]4-7;8+;[=184]8-10;[=185]11+” in MS Excel but got the error, see the screenshot:
Infact, I cannot input more than three rank elements. So, this cannot be done in MS Excel, so Aspose.Cells cannot accomplish your task either as it follows MS Excel standards and specifications in creating or rendering PivotTables.
In previous post, I requested you to provide an Excel file with your desired PivotTable that you may manually create in MS Excel, so kindly provide us the file, we will check it soon. Also provide complete details (if possible, you may attach some screenshots, etc.) on how could you accomplish the task in MS Excel manually, so we could mimic the same thing via Aspose.Cells APIs.
Thank you.
Hi Amjad Sahi,
Please find Pivot table and screenshot for issues,
1)we need to show rank in formula bar instead of number on click rank in pivot , for set rank we used setNumberFormat
2)and no need to show total value for only rank column,need to show blank for dynamic data.
Thank you
Hi,
Thanks for the screenshot.
Well, I requested you to kindly provide an Excel file (created in MS Excel manually) which shows your desired PivotTable report as per your instructions in the screenshot but you again provided an Excel file where you have not performed your requirements. How could you perform any task via Aspose.Cells APIs which cannot be done in MS Excel manually.
1) I think it is MS Excel’s behavior for which the formula bar always shows the unformatted underlying values instead of formatted/calculated values. This cannot be changed for MS Excel application.
2) I am not sure if this can be done using PivotTable options in MS Excel manually. Anyways, if you are successful to accomplish the task, kindly do provide your desired Excel file (please unprotect the PivotTable sheet), we will check it how to do it via Aspose.Cells APIs.
Thank you.
Hi Amjad Sahi,
Thank you for fast replay. Sorry for saying that I’m unable to create required pivot Table manually as per requirement, we getting rank as it is in sheet 1 and if try to convert that column in to pivot table not supporting, Please refer previous files for reference. Can u please tell me if they have any alternative to get rank data in Pivot table as it is.
In Screen show detailed
1)in Dashboard we able to show rank as it is.
2)But if go to pivot Table the values going to different,
Currently we using
pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));
to get column from dynamic excel So, they have any alternative to get data as it is in pivot table also.
Thank you
Hi,
If you could not accomplish the task using MS Excel manually, I am afraid, you cannot do that via Aspose.Cells APIs. Also, if you want to use actual data for Rank, you should not set numbers formatting for Rank field in code via Aspose.Cells APIs. What is Dashboard? It does not look like MS Excel application. I am afraid, Aspose.Cells is a spreadsheet management library and only mimics MS Excel behavior for different options/tasks as it only uses MS Excel file formats (XLS/XLSX, etc.). If you are successful in performing your tasks in MS Excel manually, kindly do provide Excel file with details and we will check on how to do in Aspose.Cells APIs.
Thank you.