Unable to see the pivot Data in my pivot Excel Sheet if I protect it

[padarthi]

Hi,

If I protect the pivot sheet.I am unable to get the pivot Data in my Excel pivot sheet.

Please tell me how to get this done.

Below is the code I used to protect my sheets

public Workbook protectworkbook(writeWorkbook){

for(int i=0; i<writeWorkbook.getWorksheets().getCount(); i++)

{

Worksheet sheet = (Worksheet)writeWorkbook.getWorksheets().get(i);

sheet.getProtection().setAllowEditingContent(false);

sheet.getProtection().setUsingPivotTablesAllowed(true);

// sheet.protect(ProtectionType.ALL); //tested by removing this line and keeping this line

}

return writeWorkbook;

}

After getting downloaded the Excel file,

I opened it. Then Its giving error Messge Like

"The command cannot be performed while a protected sheet sheet contains another pivottable report based on the same source data".

And its saying to remove protection for pivot sheet.

Please help.

[babar.raza]

Hi Vishnu,

Thank you for contacting Aspose support.

It would be of great help in understanding your presented scenario, if you can provide your current input spreadsheet along with your desired results. You can manually create the desired results in Excel application and share it here to show what you wish to achieve with Aspose.Cells APIs.

[babar.raza]

Hi again,

This is to update you that we have investigated the matter by creating the spreadsheet from scratch. The code snippet provided at the bottom of this post creates 2 worksheets where the first one contains the data and the second one contains the Pivot Table whose source is in first worksheet. After protecting the both worksheets we are able to observe the said warning in Excel interface while loading the resultant spreadsheet. Please check the snapshot for your reference.

Please note, the behaviour is same if we manually protect both worksheets in Excel, such that first worksheet is completely protected whereas the second worksheet only allows to use the PivotTable & PivotChart. If you save such spreadsheet and reopen with Excel, you will encounter exactly same warning. In order to avoid this problem, we request you to protect only the data sheet, and not the one containing the Pivot Table.

In case I have misunderstood your requirements then please share the desired results, that you may manually create in Excel application.

Java

//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
//Setting the value to the cells
Cell cell = cells.get(“A1”);
cell.setValue(“Sport”);
cell = cells.get(“B1”);
cell.setValue(“Quarter”);
cell = cells.get(“C1”);
cell.setValue(“Sales”);
cell = cells.get(“A2”);
cell.setValue(“Golf”);
cell = cells.get(“A3”);
cell.setValue(“Golf”);
cell = cells.get(“A4”);
cell.setValue(“Tennis”);
cell = cells.get(“A5”);
cell.setValue(“Tennis”);
cell = cells.get(“A6”);
cell.setValue(“Tennis”);
cell = cells.get(“A7”);
cell.setValue(“Tennis”);
cell = cells.get(“A8”);
cell.setValue(“Golf”);
cell = cells.get(“B2”);
cell.setValue(“Qtr3”);
cell = cells.get(“B3”);
cell.setValue(“Qtr4”);
cell = cells.get(“B4”);
cell.setValue(“Qtr3”);
cell = cells.get(“B5”);
cell.setValue(“Qtr4”);
cell = cells.get(“B6”);
cell.setValue(“Qtr3”);
cell = cells.get(“B7”);
cell.setValue(“Qtr4”);
cell = cells.get(“B8”);
cell.setValue(“Qtr3”);
cell = cells.get(“C2”);
cell.setValue(1500);
cell = cells.get(“C3”);
cell.setValue(2000);
cell = cells.get(“C4”);
cell.setValue(600);
cell = cells.get(“C5”);
cell.setValue(1500);
cell = cells.get(“C6”);
cell.setValue(4070);
cell = cells.get(“C7”);
cell.setValue(5000);
cell = cells.get(“C8”);
cell.setValue(6430);

//Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
sheet = workbook.getWorksheets().get(sheetIndex);

PivotTableCollection pivotTables = sheet.getPivotTables();
//Adding a PivotTable to the worksheet
int index = pivotTables.add("=Sheet1!A1:C8",“E3”,“PivotTable2”);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);
//Hide grand totals for rows.
pivotTable.setRowGrand(false);
//Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW,0);
//Dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN,1);
//Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA,2);
pivotTable.calculateData();
pivotTable.refreshData();

//Protecting all worksheets
for(int i=0; i<workbook.getWorksheets().getCount(); i++)
{
Worksheet worksheet = (Worksheet)workbook.getWorksheets().get(i);
worksheet.getProtection().setAllowEditingContent(false);
worksheet.getProtection().setAllowUsingPivotTable(true);
}
//Saving the Excel file
workbook.save(dir + “Aspose.xls”);