We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Need to Implement Repeat Item Labels

Hi All,


Please help me to implement the “Repeat All Item Labels” and “Do Not Repeat Item Labels” In Pivot Excel.Currently we are using Java Aspose.cells

Please let us know the Classes names which will be used implement above options.

Thanks in Advance.

Thanks&Regards,
Chiranjeevi

Hi,


I think you may try to use PivotField.setRepeatItemLabels(boolean value) method to on/ off repeat items labels. The default value is false.

Thank you.


Hi Team,


I have verified with PivotField.setRepeatItemLabels(true) property.It didn't worked for me.Please suggest how can i proceed further on this.


Thanks&Reagrds
Chiranjeevi

Hi,


Could you provide us a sample JAVA program (runnable) with template files if you have any to reproduce the issue on our end, we will check it soon. Also provide us your output file containing the Pivot table via Aspose.Cells APIs, the output file should also have the Pivot table with your desired option on/off (you may create the Pivot table manually in MS Excel), it will help us to evaluate your issue more precisely to consequently figure it out soon.

PS. Please use our latest version i.e. v8.1.2 if you are not using it already.

Thank you.

Hi,


It would be great if you can test and provide us the sample JAVA program with Repeat Item Label option in Pivot Excel as i don’t have any sample Java program for Pivot Excel generation.


Thanks in Advance.


Thanks&Regards,
Chiranjeevi

Hi,


I have written a sample code for your requirements on how to use the repeat items labels for Pivot Row field in the Pivottable. I have used a template file (attached), please open it (use MS Excel 2010 or greater versions as this is new feature which may not work in Excel 2007 or earlier versions). I have also attached the output file for your reference. I have set the repeat items labels attribute for the “Netting ID” field in the existing Pivot table field in the template file and re-save the file, so you may check/ compare the values in that field of the designer spreadsheet Vs output Excel file for your reference:
e.g
Sample code:

Workbook workbook = new Workbook(“Bk_itemsrepeattest1.xlsx”);
//Get the pivot table
PivotTable pivot = workbook.getWorksheets().get(1).getPivotTables().get(0);
//get row PivotField collection
PivotFieldCollection rows = pivot.getRowFields();
//get PivotField named “Netting ID”
PivotField field = rows.get(“Netting ID”);

//set RepeatItemLabels is true
field.setRepeatItemLabels(true);
workbook.save(“outBkrepeatitemstest1.xlsx”);

Thank you.

Hi Team,


I tried above code using latest Aspose latest 8.3.0.It didn't worked for me.Please suggest how can i repeat items labels for Pivot Row field in the Pivotable proceed further on this.


Thanks&Reagrds
Anjaneyulu

Hi Anjaneyulu bathini,


Well, I tested the scenario/ case again using a sample Excel file that contains a pivot table in it, it works fine with v8.3.0 as well, please evaluate it using the sample code and template file in the previous post in this thread:
https://forum.aspose.com/t/74355


Note: The repeat items labels feature is available in MS Excel 2010 or greater versions only, so it won’t work in MS Excel 2007 or earlier versions.

If you still find the issue, kindly post your template file and sample code ( runnable - similar as my previous reply here) to reproduce the issue on our end, we will check it soon.


Thank you.
Hi Team,

I have given a sample code for your requirements on how to use the repeat items labels for Pivot Row field in the Pivot table. I have also attached the output file (template ) for your reference. I have set the repeat items labels attribute for the "Organization Name " & "Bill Item Name" field in the existing Pivot table field in the template file(with repeated labels.gif ).

i am calling this addPivotTable2Sheet while generating the pivot excel given in attached file
where i set following code to repeat the lables.

pivotFields = pivotTable.getDataFields();
for(int pIndex=0;pIndex<pivotFields.getCount() ;pIndex++)
{
PivotField pivotField = pivotFields.get(pIndex);
pivotField.setRepeatItemLabels(true);
}

Thanks in Advance.


Thanks&Reagrds
Anjaneyulu

Hi,


Thanks for the screen shots and sample code.

It looks like you are setting the repeat items labels option for the data fields, please set it for Row pivot fields. For example, you may change the line of code:
i.e.,
pivotFields = pivotTable.getDataFields();

for(int pIndex=0;pIndex<pivotFields.getCount() ;pIndex++)
{
PivotField pivotField = pivotFields.get(pIndex);
pivotField.setRepeatItemLabels(true);
}

to:
pivotFields = pivotTable.getRowFields();

for(int pIndex=0;pIndex<pivotFields.getCount() ;pIndex++)
{
PivotField pivotField = pivotFields.get(pIndex);
pivotField.setRepeatItemLabels(true);
}

If you still find the issue, kindly provide us your template Excel file here, we will check it soon.

Thank you.

Hi Team,


I am setting the repeat items labels option to row fileds,it’s didn’t worked for me.

i am attaching sample code please check .

Please suggest how can i repeat all items labels for Pivot Row fields in the Pivot table
proceed further on this.


Hi,


Thanks for the sample code segment.

I am not sure about your source data (in the template file) for the Pivot Table. As requested earlier, could you provide us your generated Excel file (containing the Pivot table) via Aspose.Cells APIs, it will help us to evaluate your issue precisely on our end.

Thank you.

Hi Team,


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

without_repeatedlables.gif image getting after generating the pivot excel and we are expecting the output is with_repeatedlables.gif

note:pivotField.setRepeatItemLabels(true);

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;
}
}



Thanks
Anjaneyulu.

Hi,


Thanks for the sample code and screen shots.

I cannot evaluate your issue properly as I do not have your template file “1008404_1813_1.csv” used in the code segment. By the way, I have tested your issue with my simple sample file with simplest lines of code, it works fine and sets repeat item labels option for the Pivot table row fields in the output Excel file. I am using our latest version of the product Aspose.Cells for Java v8.4.0. Please try our latest version/fix: Aspose.Cells for Java v8.4.0:
http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/entry614286.aspx

If you still find the issue with v8.4.0, kindly provide us the template file(s) here, we will check it soon.

Thank you.


Hi Amjad Sahi,

Sorry for the delay,

We tried with the latest version of the product Aspose.Cells for Java v8.4.0.
when we create pivot table through aspose repeat all item labels option is not working in the pivot sheet through programatically,

Please find the attached excel sheet for your reference and please find the sample code in the "616075 in reply to 615833" comments

Please let us know the route cause how to set repeat all item label through programatically using aspose.

please let us know if you have any queries on this.

Thanks &Regards
Anjaneyulu.

Hi,


Thanks for providing us template file.

After an initial test, I observed the issue as you mentioned by using your template file and following sample code. I found “Repeat All Item Labels” option is not working for the PivotTable in the template file.
e.g
Sample code:

Workbook workbook = new Workbook(“eld81213_Copy+of+BOM+Item+Component+Details+Report_1013416_115_1+(5).xlsx”);
//Get the pivot table
PivotTable pivot = workbook.getWorksheets().get(1).getPivotTables().get(0);
//get row PivotField collection
PivotFieldCollection rows = pivot.getRowFields();
//get the first PivotField
PivotField field = rows.get(0);

//set RepeatItemLabels is true
field.setRepeatItemLabels(true);


/*
PivotFieldCollection pivotFields = pivot.getDataFields();
for(int pIndex=0;pIndex<pivotFields.getCount() ;pIndex++)
{
PivotField pivotField = pivotFields.get(pIndex);
pivotField.setRepeatItemLabels(true);

}
*/


workbook.save(“out1.xlsx”);

I have logged a ticket with an id “CELLSJAVA-41551” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
Hi Team,

Can you please update for the above issue, we are waiting for the fix.

Thanks & regards
Anjaneyulu.

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no update for you regarding this issue at this moment. However, we have logged your comment in our database for product team consideration and requested the team to provide some fix or ETA for this issue. Once there is some news for you, we will let you know asap.

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java v8.6.2.1 and let us know your feedback.

Hi Team,

Thanks for the reply, we tried with the Aspose.Cells for Java v8.6.2.1 jar. when we try to open excel ouput it got corrupted Please find the attached screenshot excelIssue.jpg for the error.

Normally we are using the below jar files for excel generation but when we create pivot table through aspose repeat all item labels option is not working in the pivot sheet through programatically,

aspose-cells-8.6.2.jar
bcprov-jdk16-146.jar
jsr173_1.0_api.jar
dom4j-1.6.1.jar
woodstox-core-asl-4.1.1.jar
stax2-api-3.0.2.jar

Please let us know if anything needed from our side

Thanks and Regards,

Jayaraj.