Hide formula

Hi,


Im trying to hide formulas from user in a protected worksheet. To achieve this im building my formula cells as following:

final Cell cNamedFormula = worksheet.getCells().get(i, 3);
final Style style = cNamedFormula.getStyle();
style.setLocked(true);
style.setFormulaHidden(true);
cNamedFormula.setFormula(“range” + i + “* 2”);

Im also setting my worksheet as protected:

protection.setAllowEditingContent(false);
protection.setAllowEditingObject(false);
protection.setAllowEditingScenario(false);
protection.setPassword(“123”);

But in the generated xls file user is able to see the cell formula when clicking on it.


Can someone help?

Thanks.

Hi,

Thanks for your posting and considering Aspose.Cells.

Please create some sample excel file having this feature (i.e hide formula by protection) for our reference. We will analyze your sample excel file and achieve the same thing using Aspose.Cells APIs. You can create your sample excel file manually and attach it here for our analysis. Thanks for your cooperation.

Hi,


I found the solution. Here is a code that generates a excel file with forumla. For each formula cell I try to hide formula using cNamedFormula.getStyle().setFormulaHidden(true).

package com.agentrics;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;

import com.aspose.cells.Cell;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.OoxmlSaveOptions;
import com.aspose.cells.Protection;
import com.aspose.cells.Range;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;

@org.testng.annotations.Test
public class Test {

public void test() throws Exception{
final File file = new File(“H:/file.xlsx”);
final Workbook workbook = new Workbook(FileFormatType.XLSX);
final WorksheetCollection worksheets = workbook.getWorksheets();
final Worksheet worksheet = worksheets.get(0);

final Protection protection = worksheet.getProtection();
protection.setAllowEditingContent(false);
protection.setAllowEditingObject(false);
protection.setAllowEditingScenario(false);
protection.setPassword(“1234”);

//LIMIT 65535
for (int i = 0; i < 40; i++) {
final Cell c = worksheet.getCells().get(i, 1);
c.setValue(35);
final Range r = worksheet.getCells().createRange(i, 1, 1, 1);
r.setName(“range” + i);

// this way it works
// final Cell cFormula = worksheet.getCells().get(i, 2);
// cFormula.setFormula(“B” + (i+1) + “* 2”);

final Cell cNamedFormula = worksheet.getCells().get(i, 3);
cNamedFormula.setFormula(“range” + i + “* 2”);
cNamedFormula.getStyle().setFormulaHidden(true);
}
// it fails here, but if comment this line will
workbook.calculateFormula(false);
final OutputStream output = new FileOutputStream(file);
// also fails here too
workbook.save(output, new OoxmlSaveOptions(SaveFormat.XLSX));
}

}


You can see that in the generated file its still possible to see formula. I change the line
cNamedFormula.getStyle().setFormulaHidden(true);

By
final Style style = c.getStyle();
style.setFormulaHidden(true);
cNamedFormula.setStyle(style);


Seems like getStyle() returns a copy and not the reference to cell’s style.

Dear Tiago,

Thanks for your excellent finding, we appreciate it and using Aspose.Cells.

We have further explored the style.setFormulaHidden(true); property and was able to successfully hide the formulas.

We have tested this issue with the latest version: Aspose.Cells
for Java v8.6.0.4
.

Please see the following sample code and its output excel file for your reference. We have also attached the screenshot of the output excel file for you to view.

Java
Workbook workbook = new Workbook();

Worksheet worksheet = workbook.getWorksheets().get(0);

Cell cell = worksheet.getCells().get(“A3”);
cell.setFormula(“=Sum(A1:A2)”);

Style style = cell.getStyle();
style.setFormulaHidden(true);
cell.setStyle(style);

worksheet.protect(ProtectionType.ALL);

workbook.save(“output.xlsx”);