Please find the below sample code method call to generate repeated all items lables in pivot table using aspose-cells-8.3.0.jar in microsoft excel 2010 format. highlighted in green color code is using to repeate the labels below is the image to get the output for reference.
Please suggest how to get the repeated lables in pivot table using aspose-cells-8.3.0.ja
package test.oracle.apps.ak.server;
import java.io.IOException;
import com.aspose.cells.;
public class piovttest {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
workbook.open(“D:\pivot\1008404_1813_1.csv”,FileFormatType.CSV);
piovttest p=new piovttest();
p.addPivotTable2Sheet(processID,writeWorkbook,format,pivotTableHT,pivotIndex);
//Saving the Excel file
workbook.save(“D:\pivot\1813.xlsx”,FileFormatType.XLSX);
}
public void addPivotTable2Sheet(String processID,Workbook writeWorkbook,String format,Hashtable pivotTableHT,int pivotIndex)throws Exception
{
WorksheetCollection writeWorkSheets = writeWorkbook.getWorksheets();
String pivotName = “Pivot “;
pivotName = pivotName.length()>30?pivotName.substring(0,30):pivotName;
Worksheet pivotWorkSheet = writeWorkSheets.add(pivotName);
String reportLayout=””;
try{
Hashtable pivotSubTotalHashTable = new Hashtable();
Hashtable pivotRowSubTotalHashTable = new Hashtable();
Hashtable pivotTableFormatHT = new Hashtable();
Hashtable pivotTableStyleOptionHT = new Hashtable();
Hashtable pivotTableLayoutHT = new Hashtable();
Hashtable pivotTableDataDisplayNameHT = new Hashtable();
List pivotTableRowArrayList = new ArrayList();
List pivotTablePageArrayList = new ArrayList();
List pivotTableDataArrayList = new ArrayList();
List pivotTableColumnArrayList = new ArrayList();
boolean autoSub = true, subatBottom = false, compactRL = true;
boolean outlineRL = false ;
if(pivotTableHT.containsKey(“Pivot Table Rows Fields”))
pivotTableRowArrayList = (List)pivotTableHT.get(“Pivot Table Rows Fields”);
if(pivotTableHT.containsKey(“Pivot Table column Fields”))
pivotTableColumnArrayList = (List)pivotTableHT.get(“Pivot Table column Fields”);
if(pivotTableHT.containsKey(“Pivot Table data Fields”))
pivotTableDataArrayList = (List)pivotTableHT.get(“Pivot Table data Fields”);
if(pivotTableHT.containsKey(“Pivot Table Page Fields”))
pivotTablePageArrayList = (List)pivotTableHT.get(“Pivot Table Page Fields”);
if(pivotTableHT.containsKey(“Pivot Table Formats”))
pivotTableFormatHT = (Hashtable)pivotTableHT.get(“Pivot Table Formats”);
if(pivotTableHT.containsKey(“Pivot Layout”))
pivotTableLayoutHT = (Hashtable)pivotTableHT.get(“Pivot Layout”);
if(pivotTableHT.containsKey(“Pivot Table Style Options”))
pivotTableStyleOptionHT = (Hashtable)pivotTableHT.get(“Pivot Table Style Options”);
if(!pivotTableRowArrayList.isEmpty() || !pivotTableColumnArrayList.isEmpty() || !pivotTablePageArrayList.isEmpty() || !pivotTableDataArrayList.isEmpty())
{
PivotTableCollection pivotTables = pivotWorkSheet.getPivotTables();
String cellString=“a1”;
if(format.indexOf(“2007”)!=-1)
if(noOfRows>=1048575)
cellString=CellsHelper.cellIndexToName(1048575,noOfCols-1);
else
cellString=CellsHelper.cellIndexToName(noOfRows,noOfCols-1);
else
if(noOfRows>65535)
cellString=CellsHelper.cellIndexToName(65535,noOfCols-1);
else
cellString=CellsHelper.cellIndexToName(noOfRows,noOfCols-1);
if(pivotTableFormatHT!= null && !pivotTableFormatHT.isEmpty())
{
if(pivotTableFormatHT.containsKey(“PivotTableName”))
pivotName = ((String)pivotTableFormatHT.get(“PivotTableName”)).trim().length()>0?(String)pivotTableFormatHT.get(“PivotTableName”):“Pivot”;
pivotName = pivotName+" - “+reportName;
pivotName = pivotName.length()>30?pivotName.substring(0,30):pivotName;
}
int index = pivotTables.add(datasheetName+”!$A$1:"+cellString,“A1”,pivotName);
PivotTable pivotTable = (PivotTable)pivotTables.get(index);
pivotTable.setName(pivotName);
pivotWorkSheet.setName(pivotName);
pivotTable.setRowGrand(false);
String param=null;
String value=null;
String arg=null;
//method to set pivot row labels
pivotTable.setGridDropZones(true);
for(int j=0;j<pivotTableColumnArrayList.size();j++)
{
pivotTable.addFieldToArea(PivotFieldType.COLUMN,pivotTableColumnArrayList.get(j).toString());
}
PivotFieldCollection pivotFields = pivotTable.getColumnFields();
for(int pIndex=0;pIndex<pivotFields.getCount();pIndex++)
{
PivotField pivotField = pivotFields.get(pIndex);
if(pivotFieldFormat.containsKey(pivotField.getName()) && !isNull((String)pivotFieldFormat.get(pivotField.getName())))
pivotField.setNumberFormat((String)pivotFieldFormat.get(pivotField.getName()));
}
for(int j=0;j<pivotTablePageArrayList.size();j++)
{
pivotTable.addFieldToArea(PivotFieldType.PAGE,pivotTablePageArrayList.get(j).toString());
}
String funName=null;
for(int j=0;j<pivotTableRowArrayList.size();j++)
{
value=pivotTableRowArrayList.get(j).toString();
if(value.indexOf(’(’)!=-1)
{
String rowFunName=value.substring(value.indexOf(’(’)+1,value.length()-1);
value=value.substring(0,value.indexOf(’(’));
if(rowFunName.trim().length()>0)
pivotRowSubTotalHashTable.put(String.valueOf(j), rowFunName);
}
pivotTable.addFieldToArea(PivotFieldType.ROW,value);
}
pivotTable.setAutoFormat(true);
boolean sort = false;
if(pivotTableStyleOptionHT!=null && !pivotTableStyleOptionHT.isEmpty())
{
if(pivotTableStyleOptionHT.containsKey(“RowHeader”))
{
pivotTable.setShowPivotStyleRowHeader(“0”.equals((String)pivotTableStyleOptionHT.get(“RowHeader”))?false:true);
}
if(pivotTableStyleOptionHT.containsKey(“ColumnHeader”))
{
pivotTable.setShowPivotStyleColumnHeader(“0”.equals((String)pivotTableStyleOptionHT.get(“ColumnHeader”))?false:true);
}
if(pivotTableStyleOptionHT.containsKey(“BandedRow”))
{
pivotTable.setShowPivotStyleRowStripes(“0”.equals((String)pivotTableStyleOptionHT.get(“BandedRow”))?false:true);
}
if(pivotTableStyleOptionHT.containsKey(“BandedColumn”))
{
pivotTable.setShowPivotStyleColumnStripes(“0”.equals((String)pivotTableStyleOptionHT.get(“BandedColumn”))?false:true);
}
if(pivotTableStyleOptionHT.containsKey(“Sort”))
{
sort = “1”.equals((String)pivotTableStyleOptionHT.get(“Sort”))?true:false;
}
if(pivotTableStyleOptionHT.containsKey(“PivotStyle”))
{
String style = (String)pivotTableStyleOptionHT.get(“PivotStyle”);
if(style.trim().length()>0)
pivotTable.setPivotTableStyleName(style);
}
}
boolean blankRow = false;
if(pivotTableLayoutHT!= null && !pivotTableLayoutHT.isEmpty())
{
if(pivotTableLayoutHT.containsKey(“SubTotals”))
{
String subTotals = (String)pivotTableLayoutHT.get(“SubTotals”);
switch(Integer.parseInt(subTotals))
{
case 0: autoSub = false;
break;
case 1: subatBottom =true;
break;
}
}
if(pivotTableLayoutHT.containsKey(“GrandTotals”))
{
String val[] = ((String)pivotTableLayoutHT.get(“GrandTotals”)).split("\|");
pivotTable.setColumnGrand(true);
pivotTable.setRowGrand(true);
if(“0”.equals(val[0]))
pivotTable.setRowGrand(false);
if(“0”.equals(val[1]))
pivotTable.setColumnGrand(false);
}
if(pivotTableLayoutHT.containsKey(“ReportLayouts”))
{
// modifed by anjaneyulu.T to fix the bug 10426
reportLayout = (String)pivotTableLayoutHT.get(“ReportLayouts”);
// end of the bug 10426
switch(Integer.parseInt(reportLayout))
{
case 0: compactRL = true;
break;
case 1: outlineRL = false;
break;
case 2: outlineRL = true;
break;
}
}
if(pivotTableLayoutHT.containsKey(“BlankRows”))
{
blankRow = “1”.equals((String)pivotTableLayoutHT.get(“BlankRows”))?true:false;
}
}
pivotFields = pivotTable.getRowFields();
pivotTable.setHasBlankRows(blankRow);
for(int j=0;j<pivotTableDataArrayList.size();j++)
{
value=pivotTableDataArrayList.get(j).toString();
if(value.indexOf(’(’)!=-1)
{
String subFun = value.substring(value.indexOf(’(’)+1,value.length()-1);
value = value.substring(0,value.indexOf(’(’));
if(value.indexOf(’|’)!=-1)
{
pivotTableDataDisplayNameHT.put(String.valueOf(j),value.substring(value.indexOf(’|’)+1));
value = value.substring(0,value.indexOf(’|’));
}
pivotSubTotalHashTable.put(String.valueOf(j), subFun);
}
pivotTable.addFieldToArea(PivotFieldType.DATA,value);
}
if(pivotTableHT.containsKey(“Summary Calculation Text”))
{
String summaryCalculationText = (String)pivotTableHT.get(“Summary Calculation Text”);
if(!isNull(summaryCalculationText))
{
String summaryTest[] = summaryCalculationText.split(",");
for(int summaryIndex = 0;summaryIndex<summaryTest.length; summaryIndex++)
{
if(summaryTest[summaryIndex].indexOf(’(’)!=-1)
{
int indexString = summaryTest[summaryIndex].indexOf(’(’);
String name = summaryTest[summaryIndex].substring(0,indexString);
summaryTest[summaryIndex] = summaryTest[summaryIndex].substring(indexString,summaryTest[summaryIndex].length());
if(summaryTest[summaryIndex].indexOf(’-’)!=-1 && summaryTest[summaryIndex].indexOf(’+’)==-1 &&
summaryTest[summaryIndex].indexOf(’’)==-1 && summaryTest[summaryIndex].indexOf(’/’)==-1)
summaryTest[summaryIndex] = summaryTest[summaryIndex].replaceAll("\(","").replaceAll("\)","");
String formula = summaryTest[summaryIndex].replaceAll(">","’").replaceAll("<","’");
pivotTable.addCalculatedField(name,"="+formula,true);
}
}
}
}
pivotFields = pivotTable.getDataFields();
for(int pIndex=0;pIndex<pivotFields.getCount() ;pIndex++)
{
PivotField pivotField = pivotFields.get(pIndex);
String subTotName=(String)pivotSubTotalHashTable.get(String.valueOf(pIndex));
//no need to display currency symbol.
if(pivotFieldFormat.containsKey(pivotField.getName()) && !isNull((String)pivotFieldFormat.get(pivotField.getName())))
pivotField.setNumberFormat((String)pivotFieldFormat.get(pivotField.getName()));
if(pivotTableDataDisplayNameHT.containsKey(String.valueOf(pIndex)) && !isNull((String)pivotTableDataDisplayNameHT.get(String.valueOf(pIndex))))
{
pivotField.setDisplayName((String)pivotTableDataDisplayNameHT.get(String.valueOf(pIndex))+" “);
}
if(!isNull(subTotName))
{
String subTotType[] = subTotName.split(”\|");
int length = subTotType.length;
if(“AVE”.equals(subTotType[0].toUpperCase()))
{
pivotField.setFunction(ConsolidationFunction.AVERAGE);
if(length>1)
pivotField.setDataDisplayFormat(Integer.parseInt((String)EISRSCAsposeConstants.fieldDisplayFMT.get(subTotType[1])));
}
if(“COUNT”.equals(subTotType[0].toUpperCase()))
{
pivotField.setNumberFormat("#,##0");
pivotField.setFunction(ConsolidationFunction.COUNT);
if(length>1)
pivotField.setDataDisplayFormat(Integer.parseInt((String)EISRSCAsposeConstants.fieldDisplayFMT.get(subTotType[1])));
}
if(“MAX”.equals(subTotType[0].toUpperCase()))
{
pivotField.setFunction(ConsolidationFunction.MAX);
if(length>1)
pivotField.setDataDisplayFormat(Integer.parseInt((String)EISRSCAsposeConstants.fieldDisplayFMT.get(subTotType[1])));
}
if(“MIN”.equals(subTotType[0].toUpperCase()))
{
pivotField.setFunction(ConsolidationFunction.MIN);
if(length>1)
pivotField.setDataDisplayFormat(Integer.parseInt((String)EISRSCAsposeConstants.fieldDisplayFMT.get(subTotType[1])));
}
if(“SUM”.equals(subTotType[0].toUpperCase()))
{
pivotField.setFunction(ConsolidationFunction.SUM);
if(length>1)
pivotField.setDataDisplayFormat(Integer.parseInt((String)EISRSCAsposeConstants.fieldDisplayFMT.get(subTotType[1])));
}
}
}
if(pivotTableDataArrayList!=null && pivotTableDataArrayList.size()>1)
pivotTable.addFieldToArea(PivotFieldType.COLUMN, pivotTable.getDataField());
pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT_1);
PivotTable pt = (PivotTable)pivotWorkSheet.getPivotTables().get(0);
Style dxfStyle = writeWorkbook.createStyle();
Font font = dxfStyle.getFont();
font.setSize(8);
pt.formatAll(dxfStyle);
pivotTable.setPageFieldOrder(PrintOrderType.DOWN_THEN_OVER);
// code to avoid out of memory error for page fields.
// pivotTable.setEnableFieldList(false);
pivotFields = pivotTable.getRowFields();
for(int pIndex=0;pIndex<pivotFields.getCount();pIndex++)
{
PivotField pivotField = pivotFields.get(pIndex);
pivotField.setAutoSort(sort);
pivotField.setInsertBlankRow(blankRow);
// pivotField.setShowInOutlineForm(outlineRL);
// added by Anjaneyulu.T to fix the bug number 10426
if(reportLayout!=null)
{
if(reportLayout.equals(“0”))
pivotField.setShowCompact(true);
else if(reportLayout.equals(“1”))
pivotField.setShowInOutlineForm(false);
else if(reportLayout.equals(“2”))
pivotField.setShowInOutlineForm(true);
else if(reportLayout.equals(“3”))
pivotField.setRepeatItemLabels(true);
}
// end of the bug number 10426
if(pivotFieldFormat.containsKey(pivotField.getName()) && !isNull((String)pivotFieldFormat.get(pivotField.getName())))
pivotField.setNumberFormat((String)pivotFieldFormat.get(pivotField.getName()));
if(pIndex==0)
{
pivotField.setAscendSort(true);
}
boolean isAutomatic = true;
int autoCount = 0;
if(!pivotRowSubTotalHashTable.isEmpty() && (autoSub || subatBottom))
{
String rowFun = (String)pivotRowSubTotalHashTable.get(String.valueOf(pIndex));
// updated by Anjaneyulu to fix the bug #13749
if(rowFun!=null){
switch(Integer.parseInt(rowFun))
{
case 0:pivotField.setSubtotals(PivotFieldSubtotalType.NONE,true);autoCount++; break;
case 1:pivotField.setSubtotals(PivotFieldSubtotalType.AUTOMATIC,true);break;
case 2:pivotField.setSubtotals(PivotFieldSubtotalType.SUM,true); autoCount++; break;
case 3:pivotField.setSubtotals(PivotFieldSubtotalType.COUNT,true); autoCount++; break;
case 4:pivotField.setSubtotals(PivotFieldSubtotalType.AVERAGE,true); autoCount++; break;
case 5:pivotField.setSubtotals(PivotFieldSubtotalType.MAX,true); autoCount++; break;
case 6:pivotField.setSubtotals(PivotFieldSubtotalType.MIN,true); autoCount++; break;
}
}
// end of the bug #13749
}
else
{
pivotField.setAutoSubtotals(autoSub);
pivotField.setShowSubtotalAtTop(subatBottom);
}
// pivotField.setShowCompact(compactRL);
// added by Anjaneyulu.T to fix the bug number 10426
if(reportLayout!=null)
{
if(reportLayout.equals(“0”))
pivotField.setShowCompact(true);
else if(reportLayout.equals(“1”))
pivotField.setShowInOutlineForm(false);
else if(reportLayout.equals(“2”))
pivotField.setShowInOutlineForm(true);
else if(reportLayout.equals(“3”))
pivotField.setRepeatItemLabels(true);
}
// end of the bug number 10426
}
// By Anjaneyulu.T to fix the balck band issue
String pTstyle=pivotTableStyleOptionHT.get(“PivotStyle”).toString();
// if(“NONE”.equalsIgnoreCase(pTstyle))
// pivotTable.setAutoFormatType(PivotTableAutoFormatType.NONE);
if(format.indexOf(“2007”)!=-1 && pTstyle.length()>0){
pivotTable.setAutoFormatType(PivotTableAutoFormatType.NONE);
pivotTable.setPivotTableStyleName(pTstyle);
}
// end of the fix balck band
pivotTable.setSaveData(true);
pivotTable.setPreserveFormatting(true);
if(outlineRL)
pivotTable.setRefreshDataFlag(true);
}
else
{
// bug 11068, Added by Sukesh
String isoLanguage = getISOLanguage(languageCode,con);
String[] temp = isoLanguage.split("_");
String isoLang = temp[0];
String isoTerr = temp[1];
Locale localeObject = new Locale(isoLang, isoTerr);
mlsUtil.setLangCode(languageCode);
pivotWorkSheet.getCells().get(0,0).setValue(mlsUtil.getPrompt(localeObject, “no_pivots_defined”));
// End of bug 11068
}
}
catch(CellsException e){
pivotWorkSheet.getCells().get(0,5).setValue(e.getMessage());
e.printStackTrace();
throw e;
}
}
Anjaneyulu.