Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Jan 29, 2013 4:52 pm

I am hoping someone can help me here, I have sent two emails to e-iceblue already with no response. I am using Spire.XLS for .Net Standard Edition v6.9.16 and what I am trying to do is very simple. I want to open an excel spreadsheet that has a locked sheet, unlock it with Spire.XLS, and save it back to a file. After this I am doing some other processing without Spire.XLS which will populate data into the now unlocked sheet. Once the data is populated, I want to read the excel file back into Spire.XLS and relock the same sheet that was unlocked before. With Spire.XLS all I am doing is an unlock and then a lock again. Simple, right?

I have attached a very simple excel file that can be used to test. Here is some sample vb.net code to test with:

Code: Select all
Imports Spire.Xls

Module Module1

    Sub Main()
        Dim workbook As New Workbook
        workbook.LoadFromFile("c:\locked.xls")
        workbook.Worksheets("FirstSheet").Unprotect("test")
        workbook.SaveToFile("c:\unlocked.xls", ExcelVersion.Version97to2003)

        workbook = New Workbook
        workbook.LoadFromFile("c:\unlocked.xls")
        workbook.Worksheets("FirstSheet").Protect("test")
        workbook.SaveToFile("c:\lock.xls", ExcelVersion.Version97to2003)
    End Sub
End Module


If you try to run this code on my sample excel file, it will throw an error on the line with: workbook.LoadFromFile("c:\unlocked.xls"). The error is: ArgumentOutOfRangeException – The Number must be between 10 and 400. Parameter name: ZoomScaleNormal.

I am not making any changes to ZoomScaleNormal field, in fact I tried to set it to 100 in a couple places to try and work around this problem but it doesn’t seem to help.

The other interesting thing is that it doesn’t seem to have anything to do with the locking/unlocking that I intend to do. If you comment out the lines that do the lock and unlock, the same error occurs, even though the application is only opening the file and then saving it without doing anything. This leads me to believe that for some reason the Spire.XLS component only allows a particular excel file to be opened and saved once with the dll, as after that point it will give the ZoomScaleNormal error.

I really don't think I am doing anything too complicated. I assume this is a bug because I never touch the ZoomScaleNormal field, but maybe if someone can help me find a workaround or else show me what I am doing wrong so that I can get on with my project!

Thanks!
Attachments
locked.zip
Test excel file with locked sheet
(14.45 KiB) Downloaded 362 times

daytonahomes
 
Posts: 4
Joined: Sat Jan 05, 2013 12:41 am

Wed Jan 30, 2013 9:40 am

Hello,

Thanks for your inquiry.
We have reproduced your problem. We are so sorry for the inconvenience. Our dev team are fixing it.
Once it has been fixed, we will tell you immediately.

Thanks &Best Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Thu Jan 31, 2013 9:27 am

Hello,

Thanks for your waiting.
We have fixed the problem. Our test team will do some tests for new hotfix of Spire.Xls.
If the hotfix is ok after finish the tests, we will release the hotfix and inform you immediately.

Thanks &Best Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Fri Feb 01, 2013 9:39 am

Hello,

Thanks for your waiting.
New hotfix of Spire.Xls which fix your problem has been released. Please download spire.xls_hotfix_7.0.8 from the link http://www.e-iceblue.com/downloads/hot_fix/spire.xls_hotfix_7.0.8.zip and test it.
Please feel free to contact us when you encounter any problem.

Thanks &Best Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Mon Feb 04, 2013 5:11 pm

Hi Amy, thanks for the reply. I can confirm that the ZoomScaleNormal error has now been fixed with this newest update but I still have a problem. As mentioned above, I am using Spire.XLS for .Net Standard Edition, and the hotfix you provided does not work with my unlock code.

I also notice another issue that may be by design but I find it to be problematic. I ran the same code above that unlocks a worksheet, saves it, and then opens it again and locks it again. It got all the way to the end but failed on the final save with an error saying "Name of worksheet must be unique in a workbook". The problem is that the "Evaluation Warning" worksheet is trying to be inserted a 2nd time, and it can't because it already exists from the first save.

I understand that the idea is to have people buy the product, but I feel that it prevents people from doing simple tests which involve saving more than once. My recommendation would be to do some sort of check before writing the evaluation warning sheet and either if the name already exists you could just write another "Evaluation Warning 2" and so on. Otherwise you could get fancy and check the contents of the "Evaluation Warning" to see if it contains the contents that you expect, and if it does then don't write anything, otherwise create a new one with a different name.

I was able to get around the "Name of worksheet must be unique in a workbook" error by adding a single line: workbook.Worksheets("Evaluation Warning").Remove() before saving the 2nd time. I just felt that I shouldn't have to do this, and it wouldn't be immediately apparent to new customers that this is required.

I hope you can get back to me soon with the fix for Spire.XLS for .Net Standard Edition. I've been sitting on this problem for 2 weeks now!

daytonahomes
 
Posts: 4
Joined: Sat Jan 05, 2013 12:41 am

Tue Feb 05, 2013 12:16 am

Any update on when we might have the Spire.XLS for .NET Standard Edition Hotfix posted? I'm hoping to have this in time to do a software rollout tomorrow morning. Is that wishful thinking? :mrgreen:

daytonahomes
 
Posts: 4
Joined: Sat Jan 05, 2013 12:41 am

Tue Feb 05, 2013 3:36 am

Hello,

Thanks for your recommendation. We will consider doing the change for writing "Evaluation Warning" according to your recommendation in the future.
Please download Spire.XLS for .NET Standard Edition Version:7.0 from the link http://www.e-iceblue.com/Download/download-excel-for-net-now.html and test it. Please tell us when you encounter any problem.

Thanks &Best Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Tue Feb 05, 2013 6:46 pm

Hi Amy. Everything is working perfect for me now. Thank you again for your help!

daytonahomes
 
Posts: 4
Joined: Sat Jan 05, 2013 12:41 am

Wed Feb 06, 2013 1:39 am

Hello,

Thanks for your reply.
We are happy to hear that. Please feel free to contact us if you need any assistance.
Have a nice day!

Best regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Return to Spire.XLS