Protection issue

Aspose.Cells Product Family
1

Hi,


I’ve been using ASPOSE for quite a while now and I’m very impressed, great product!

I do have a question. I’m trying to use the Protection class to protect a spreadsheet. I searched for examples and got my answer. My problem is this, when I try to use the constructor, per the examples, I get Protection class not visible error in Eclipse. I put together a small bare-bones program to test it in case I was doing something incorrect…

The sample program has the same issue. I think I’m using the latest cells jar, v17.02.0.jar.

Any thoughts?

Thanks,

Perry
==================================

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Futura} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Futura; min-height: 14.0px} span.s1 {color: #931a68}

import java.io.BufferedWriter;

import java.io.ByteArrayInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;


import com.aspose.cells.Protection;

import com.aspose.cells.Workbook;


p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Futura; color: #931a68} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Futura; min-height: 14.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Futura; color: #3933ff} p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Futura; color: #4f76cb} p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Futura; color: #777777} p.p6 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Futura} span.s1 {color: #000000} span.s2 {color: #931a68} span.s3 {color: #0326cc} span.s4 {color: #91afcb} span.s5 {color: #3933ff} span.s6 {color: #7e504f} span.s7 {text-decoration: underline ; color: #931a68} span.s8 {text-decoration: underline} span.Apple-tab-span {white-space:pre}

public class IFXL {


@SuppressWarnings("unused")

public static void main(String[] args) throws Exception

{

FileInputStream fstream = null;

Workbook workbook = null;

com.aspose.cells.License license = new com.aspose.cells.License();

license.setLicense("Aspose.Total.Java.lic");


fstream = new FileInputStream(args[2]);

workbook = new Workbook(fstream);

Protection p = new Protection(); // error: The constructor Protection() is not visible

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px '.SF NS Text'}


return;

}

}

2

Hi,


Thanks for your posting and using Aspose.Cells.

You do not need to use the Protection class directly because it is an internal class of Aspose.Cells. Please see the following sample code that explains how to make use of Protection class. The code first adds some data in cells A1:B4 and then it sets the cells A1:A4 as unlocked. After that it protects all the worksheet and allows selecting/editing of unlocked cell.

Please check the output excel file generated with this code. If you will click on the cells A1:A4, you will be able to edit them but if you click on the cells B1:B4 or any other worksheet cells, you will get a prompt that sheet is protected and you cannot edit it.

Java
//Create a workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Add some data to cells A1 till A4
ws.getCells().get(“A1”).putValue(“Unlocked”);
ws.getCells().get(“A2”).putValue(“Unlocked”);
ws.getCells().get(“A3”).putValue(“Unlocked”);
ws.getCells().get(“A4”).putValue(“Unlocked”);

//Add some data to cells B1 till B4
ws.getCells().get(“B1”).putValue(“Locked”);
ws.getCells().get(“B2”).putValue(“Locked”);
ws.getCells().get(“B3”).putValue(“Locked”);
ws.getCells().get(“B4”).putValue(“Locked”);

//Create style object and set it unlock
Style st = wb.createStyle();
st.setLocked(false);

//Unlock all the cells with above style object
//true means, we only want to change lock property
ws.getCells().get(“A1”).setStyle(st, true);
ws.getCells().get(“A2”).setStyle(st, true);
ws.getCells().get(“A3”).setStyle(st, true);
ws.getCells().get(“A4”).setStyle(st, true);
//Apply protection, after applying it you will only able to change
//cells A1:A4 and you will not be able to select any other cell including B1:B4
ws.getProtection().setAllowSelectingUnlockedCell(true);
ws.protect(ProtectionType.ALL);

//Save the workbook in xlsx format
wb.save(dirPath + “output.xlsx”, SaveFormat.XLSX);

C#
//Create a workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Add some data to cells A1 till A4
ws.Cells[“A1”].PutValue(“Unlocked”);
ws.Cells[“A2”].PutValue(“Unlocked”);
ws.Cells[“A3”].PutValue(“Unlocked”);
ws.Cells[“A4”].PutValue(“Unlocked”);

//Add some data to cells B1 till B4
ws.Cells[“B1”].PutValue(“Locked”);
ws.Cells[“B2”].PutValue(“Locked”);
ws.Cells[“B3”].PutValue(“Locked”);
ws.Cells[“B4”].PutValue(“Locked”);

//Create style object and set it unlock
Style st = wb.CreateStyle();
st.IsLocked = false;

//Unlock all the cells with above style object
//true means, we only want to change lock property
ws.Cells[“A1”].SetStyle(st, true);
ws.Cells[“A2”].SetStyle(st, true);
ws.Cells[“A3”].SetStyle(st, true);
ws.Cells[“A4”].SetStyle(st, true);
//Apply protection, after applying it you will only able to change
//cells A1:A4 and you will not be able to select any other cell including B1:B4
ws.Protection.AllowSelectingUnlockedCell = true;
ws.Protect(ProtectionType.All);

//Save the workbook in xlsx format
wb.Save(“output.xlsx”);

3

Thank you Shakeel,


This all makes perfect sense! The examples I found on the net were misleading.

Thanks again,

Perry
4

Hi,


Good to know that your issue is sorted out by the suggested sample code. We would also like to know which examples you are talking about in the Docs, could you give us details, urls, etc. so we could evaluate and enhance them (if possible).

Thank you.
5

Hi,


Is it possible to disable the un-hiding of worksheets or taking away unhide functionality all together?

I have a scratch pad worksheet that has a lot of support and proprietary data on it, it is also used to generate charts etc. I’d like to prevent a non-authorized user from unhiding the sheet and seeing the contents.

I assume it can be locked via a password or similar method but don’t see how.

Thank you!

Perry
6

Hi,


Yes, you may do that, you may disable the “Unhide” right-click menu (Sheet tab bar) item. You may set your desired worksheet (which you want your specific user(s) could not Unhide it) as “Very Hidden”. See the sample code with comments using the template file (attached) for your reference:
e.g
Sample code:

Workbook workbook = new Workbook(“BkProtection1.xlsx”);

//Get the second worksheet “Sheet2” and set it as Very Hidden, so a user cannot simply
//unhide using MS Excel UI (right click on Sheet tab bar and click “Unhide”).
//The user can only unhide the sheet via code (programmatically) or using its Visual Basic //Editor.
Worksheet worksheet = workbook.getWorksheets().get(1);
worksheet.setVisibilityType(VisibilityType.VERY_HIDDEN);

workbook.save(“out1BkProtection121.xlsx”);

The output file is also attached.

Hope, this helps a bit.

Thank you.
7

Thank you so much!


Excellent service as always!!!

Perry
8

Hi,


Good to know that the suggested code figures out your issue. Feel free to write us back in case you have further comments or questions, we will be happy to assist you soon.

Thank you.