Issue with validation formula

Technical support for Spire.XLS

Moderators: iceblue support, Flash, Manager

Issue with validation formula

Postby ahmadi_rad@yahoo.com » Mon Apr 17, 2017 6:54 am

Hello

I've attached a workbook in which I'm testing/learning the software. Cell E15 contains a custom validation formula (E15=F15). DataValidation.Formula1 shows an incorrect formula(I29=J29).

Could you please verify the case?

Regards
Attachments
test.rar
(21.7 KiB) Downloaded 21 times
ahmadi_rad@yahoo.com
 
Posts: 27
Joined: Thu Apr 13, 2017 3:53 pm

Re: Issue with validation formula

Postby Betsy.jiang » Mon Apr 17, 2017 7:20 am

Hi,

Thanks for your feedback.
I have noticed the issue and posted it to our Dev team. We will inform you once there is any progress.
Sorry for inconvenience caused.

Sincerely,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1513
Joined: Tue Sep 06, 2016 8:30 am

Re: Issue with validation formula

Postby Betsy.jiang » Wed May 03, 2017 7:53 am

Hi,

Thanks for waiting.
Now the issue has been fixed in Spire.XLS Pack Hotfix Version:7.12.7, welcome to test it.
Looking forward to your feedback.

Sincerely,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1513
Joined: Tue Sep 06, 2016 8:30 am

Re: Issue with validation formula

Postby ahmadi_rad@yahoo.com » Thu Aug 24, 2017 12:56 pm

Hello

After a while struggling with other issues, I'm back to the matter of validation.

Please take a look at validation formula on cell E10 to E14. Value must be greater than F10 to F14, but validation.formula1 is refering to cells E10 to E14.

Please also be informed that validation of E14 has also formula2 which is referring to incorrect cell.

I think the issue is not fully covered by current fixes. Now I'm using version 7.12.76.
ahmadi_rad@yahoo.com
 
Posts: 27
Joined: Thu Apr 13, 2017 3:53 pm

Re: Issue with validation formula

Postby Betsy.jiang » Fri Aug 25, 2017 2:13 am

Hi,

Thanks for your feedback.
Sorry that there is still the issue, and I have posted it to our Dev team. We will inform you as soon as it is fixed.

Sincerely,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1513
Joined: Tue Sep 06, 2016 8:30 am

Re: Issue with validation formula

Postby ahmadi_rad@yahoo.com » Sun Sep 24, 2017 7:57 am

Hello

Something about this excel file draw my attention: Well known open source packages also produce the same error that I had. Each of these packages come from another branch in industry but all share the same error. Is it possible that the problem is caused by Excel 2016? Is there any possibility that older versions don't produce this problem?

Thanks
ahmadi_rad@yahoo.com
 
Posts: 27
Joined: Thu Apr 13, 2017 3:53 pm

Re: Issue with validation formula

Postby Betsy.jiang » Mon Sep 25, 2017 2:41 am

Hello,

Thanks for your feedback.
I checked with our Dev team, and knew that the issue the validation formulas obtained were wrong was not caused by Excel 2016. And our Dev team has been fixed it yet we found a little issue after testing. Our Dev team is working on it and we will let you know as soon as the hotfix is available.

Sincerely,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1513
Joined: Tue Sep 06, 2016 8:30 am

Re: Issue with validation formula

Postby Betsy.jiang » Thu Sep 28, 2017 8:23 am

Hello,

Thanks for waiting.
Now the issue you mentioned has been fixed in Spire.XLS Pack Hotfix Version:7.12.90. In addition, the little issue I mentioned in last post, which is that the validation formula is wrong after saving document, has been solved, and it is under test phase.
Looking forward to your feedback.

Sincerely,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1513
Joined: Tue Sep 06, 2016 8:30 am

Re: Issue with validation formula

Postby ahmadi_rad@yahoo.com » Wed Oct 04, 2017 7:29 am

Hello
Is this issue also solved in new hotfix 7.12.90?
ahmadi_rad@yahoo.com
 
Posts: 27
Joined: Thu Apr 13, 2017 3:53 pm

Re: Issue with validation formula

Postby Gary.zhang » Wed Oct 04, 2017 10:26 am

The issue has also get resolved in the new hotfix 7.12.90.

Sincerely,
Gary
E-iceblue support team
User avatar
Gary.zhang
 
Posts: 1345
Joined: Thu Apr 04, 2013 1:30 am

Re: Issue with validation formula

Postby ahmadi_rad@yahoo.com » Wed Oct 04, 2017 10:48 pm

Hello

Maybe something is wrong in my side; I still have E10.DataValidation.Formula1="E10" and E10.DataValidation.Formula2="". The same for cells E11 and so on.
ahmadi_rad@yahoo.com
 
Posts: 27
Joined: Thu Apr 13, 2017 3:53 pm

Re: Issue with validation formula

Postby Betsy.jiang » Thu Oct 05, 2017 2:21 am

Hello,

Thanks for your feedback.
I checked the issue again, and it worked fine on my side. Please make sure the version you were using was correct.
Here is dlls information.
Spire.Common.dll v1.2.728.6040
Spire.licesne.dll v1.3.6.40
Spire.Pdf.dll v3.9.365.6040
Spire.Xls.dll V7.12.90.6040

Sincerely,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1513
Joined: Tue Sep 06, 2016 8:30 am

Re: Issue with validation formula

Postby ahmadi_rad@yahoo.com » Thu Oct 05, 2017 7:50 am

Problem happens after document is saved once:

Code: Select all
            byte[] b2 = File.ReadAllBytes(FilePath);
            MemoryStream stream2 = new MemoryStream(b2);
            stream2.Write(b2, 0, b2.Length);

            Spire.Xls.Workbook SpireWorkbook = new Spire.Xls.Workbook();
            SpireWorkbook.LoadFromStream(stream2);
            Spire.Xls.Worksheet SpireSheet = SpireWorkbook.Worksheets["Sheet1"];
            Spire.Xls.CellRange SpireCell = SpireSheet.Range["E10"];
            string s1 = SpireCell.DataValidation.Formula1;
            string s2 = SpireCell.DataValidation.Formula2;

            stream2 = new MemoryStream();
            SpireWorkbook.SaveToStream(stream2);
            File.WriteAllBytes(FilePath, stream2.ToArray());



After file is saved, even without any change, size of file is reduced. Then after opening the file once more, Formulas are changed.
Changing the size of file also makes the file unreadable for other libraries like openxml.
ahmadi_rad@yahoo.com
 
Posts: 27
Joined: Thu Apr 13, 2017 3:53 pm

Re: Issue with validation formula

Postby Betsy.jiang » Thu Oct 05, 2017 8:13 am

Hello,

Thanks for your response.
Yes, it indeed has that issue after saving. And I mentioned it in previous post, and it was under test phase. The hotfix doesn't include the fix. Now we are on national holiday 01/10/2017-08/10/2017 (GMT+8:00). We will prepare the hotfix when we come back, then let you know as soon as the hotfix is avalible.

For another issue changing the size of file also makes the file unreadable, how do you change the size ? Could you please share us the code you were trying ?

Sincerely,
Betsy
E-iceblue support team
User avatar
Betsy.jiang
 
Posts: 1513
Joined: Tue Sep 06, 2016 8:30 am

Re: Issue with validation formula

Postby ahmadi_rad@yahoo.com » Thu Oct 05, 2017 8:30 am

Sorry, I hadn't seen your post. Yes, You had mentioned it.
Congrats for your National holiday and enjoy your holidays.
About changing the size: I don't do anything. Please refer to my previous post and its code. I open the file, save it and when I open it again (run the code once more), size of byte array is reduced compared to original file.
ahmadi_rad@yahoo.com
 
Posts: 27
Joined: Thu Apr 13, 2017 3:53 pm

Next

Return to Spire.XLS

Who is online

Users browsing this forum: No registered users and 1 guest