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();