Prevent deleting or renaming sheets


#1

Hi

By using the Protect function I can prevent people from changing the data in a sheet. But it is still possible to delete the sheet or to change the sheet’s name.

Is there a way to prevent this also?

Tx, Stief


#2

Please try this attached version. You can protect the workbook to avoid deleting sheet or changing sheet name.

excel.Protect(ProtectionType.All, "123");


#3

Thanks, that did the trick.

I downloaded the evaluation version 3.7.2.0. In this version I could only protect the sheets, not de workbook itself.

In a couple of weeks I will be purchasing the Aspose.Cells component, will I then have this latest version?

Stief


#4

Sure you can. Now you can use this attached version for evaluation.


#5

Hi Laurence

Last week you send me a new version of the Cells component because I could not protect everything in the document. This new version solved those problems, but it also caused other problems I didn’t have with the old version.

With the 3.7.2.0 version I could copy sheets and replace formulas with values using this code:
if(cell.IsFormula) cell.PutValue(cell.Value);

With the new 3.8.0.2 version this code no longer works, cell.Value is always empty.

Can this be solved?

Tx, Stief


#6

Could you please post a simple test project to show the problem? Thank you.


#7

Here is a small sample project to show the problem. Our program logic consists of 2 steps.

Step 1: Excel file 'T10.xls' is opened and some data is filled in on the sheet named 'PV Français' (cells J4, J5 and C6). These items are automatically repeated on the next 2 sheets using Excel formulas. This steps works perfectly.
Step 2: Sheet named 'PV Néerlandais' is copied to the 'report.xls' file. All formulas must be replaced by its value. This step fails on the replacement of the formulas.

You can perform these 2 steps using the first 2 buttons on the screen. After clicking the second button I print a table on screen with every cell, its formula and its value. In this table you can see the formulas where the values are not detected.

In the 'lib' folder you find 2 Aspose.Cells dll files. The old one where everything works fine and the new dll you send me last week which causes the problems.

Hope you understand everything.


#8

Thank you for the sample project.

This problem is caused by Worksheet.Copy method. In the new dll, when copy formulas from a worksheet to another worksheet, I changed the routine to make it faster. However, I only copied the formula, not copying the result of formulas at the same time.

I fixed this problem. Please try this attached version.


#9

Sorry Laurence, the same problem still exists. I ran my sample project with the new dll but the result was the same.

In the attached screenshot you can see which formulas have no resulting value. But in the Excel file they all have a result after step 1.

Hope you can find the problem.


#10

If you open the T10.xls and save it in MS Excel, it will work fine.

If you don't want to do this action, please add a line of code in CopySheet method:

Excel sourceExcel = new Excel();
sourceExcel.Open(location + sourceFileName);

//Add this line of code

sourceExcel.CalculateFormula(true);


#11

Hi Laurence

Thanks for the tip, we are finally getting to the correct sollution.

Can I ask one more question? In the screenshot attached to this post you can see a remarkable difference between the 2 files. The extra borders are no problem, that’s correct. But the problems are the formulas that generate a 0 as a result. In the original files, these formulas generate an empty string, but after the sheet copy and recalculating the formulas they all turn into a 0.

Any ideas?


#12

I will check this issue in the calculation engine.


#13

Please try to use this attached fix.

In your project, please make sure the new dll is deployed and the template file is reset.


#14

My output screenshot.


#15

Hi Laurence

I think you reached perfection :wink:

Thanks for all the help.

Stief