Validation doesn't work at first

Hello,

I am getting some very strange behavior generating a spreadsheet where the validation (drop down) for a particular column is set in the template we are using to generate the sheet from. First off we are using version 3.6.2. The validation is set using the following formula:

=OFFSET(INDIRECT("LEVEL" & $BA1),0,0,ROWS(INDIRECT("LEVEL" & $BA1)),1)

What it is doing is getting the value from a cell in column BA and concatenating it to the word level. This corresponds to a named cell range on the sheet that it gets the values for the drop down list from. In other words the drop down list will potentially be different for each row of data that is inserted because the formula will possibly resolve to a different named range.

When the sheet generates the drop down is there with a small box in it which is obviously worng. When I open the validation for the column again and do nothing but hit OK, I can then see all the drop down lists populated properly. As you can imagine this is maddening.

Is it possible that the validation is being applied before the named ranges are set? or the data that the named ranges are referencing is not populated yet? I don't think so since all that stuff is on the template. However the validation is also on the template and it is acting very strange.

I have attached the template. The validation is set on column AJ. the data that will determine which named list is used is in column BA. starting at row 19, if you add a number between 1 and 17 to BA you will see the drop downs in AJ change dynamically to correspond to the named range.

This is the behavior that I cannot duplicate when the sheet is generated by aspose without opening the validation for the row and clicking OK after the sheet is generated (When excel is doing the work).

I am eagerly anticipating your findings.

Thanks In Advance,

Scott

Hi Scott,

This formula has some errors in Data Validation. I click on AJ1, and select "Data"->"Validation", then click "OK". I got the following error:

"The source currently evaluates to an error. ..."

In your template, BA1 is blank. That caused this problem.

Could you try to simplify your validation formula?

Laurence,

The formula that I gave is the formula that I put into the validation at the column level. So when I type the formula in there I use $AJ1 as the reference and then excel populates the forrmula for the validation for every cell in the column incrementing the row number. So for instance if you were to check the formul on AJ19 you would see the appropriate row populated in the formula.

And yes, in the template the rows in BA are blank (hence the error). Aspose populates these rows (starting at row 19) whene the sheet is populated. If you manually add a number between 1 and 17 to to any of the rows in column BA you will see the drop downs are appropriately populated.

This all works fine in the template. When aspose generates the sheet, for every row that is populated with data, BA has a number. However the drop down on AJ just shows a small box in it when it is clicked. If I highlight the cell in question or the row header and open the validation menu and do nothing more than click ok (change nothing), then all is working again.

It's like aspose is not compiling the formula at runtime, but when I click ok after the sheet has been generated then Excel takes over it handles it.

Thanks,

Scott

Hi Scott,

This problem is really very strange. I will check and fix it in a few days.

Thanks Laurence,

I eagerly anticipate your findings. Your excel product has been a champion for us!

-Scott

Hi Laurence,

Any movement on this issue? I am ready to deploy and this is one of the only outstanding issues I have.

Thanks In Advance,

-Scott

Hi Scott,

Please try this attached fix.

Laurence,

The fix worked nicely thank you. The formula for the validation on the column is now working as expected as soon as the sheet opens. Great Work!!

I am experiencing something else that i thought the fix you were putting in would resolve but, alas, the issue remains. It is weird and has to do with cell comments and drop downs existing on the same sheet. It seems that when enough rows are populated with comments in a particular column that when a user tries to access the drop down the comment interferes. Now it only happens when a certain number of comments have been populated. I have attached 3 examples, the first two were generated with the template at the beginning of this thread, the last with a slightly different template. All were generated with the new dll that you provided in the above post. I will explain the behavior as it relates to each example.

Example 1 - Comments are populated in column N. There is a drop down populated in Column P that has 5 choices. The detail rows begin on row 19, and if you notice not every row has a comment in Column N. If you try to access the drop down in Column P you will notice that there is interference from the comment in (N,32). This comment is the 11th row that has a comment in it. This behavior is consitent with this template and this drop down. It always shows interference on the 11th comment populated regardless of what row it falls on. If a sheet is downloaded with 10 or less comments populated on the entire sheet then the anomoly never shows up. It is only after the 10th comment do we see interference and it happens every time.

Example 2 - Same template as #1 but different detail rows and the same behavior is exhibited. It just so happens that the row (32) is the same as example #1 but that is just coincidence. It always happens on the 11th Comment.

Example 3 - This is generated with a similar, but different template. The choices in the drop down are different and there are 4 instead of 5 this time. This time the interfering comment is on row 27. Using this template, the interfering comment always happens on the 8th comment.

The reason why I thought the above fix would also solve this problem is that if you go to the cell that the interfering comment resides on and delete the comment, then everything works great like excel then takes over and makes sure everything works ok.

I will post sample #2 and #3 in The next two posts.

Thanks, Scott

Could you post your code to set these comments? Thank you.

Laurence,

Here is where we are adding the comment to the cell.......

ps.....

sacell = Aspose.Excel.Cell, oSheet = Aspose.Excel.Worksheet, oComment = our own class to hold comment specific info.

Dim commentIndex As Integer = Me.oSheet.Comments.Add(sacell.Name)

Dim oAsposeComment As Aspose.Excel.Comment = Me.oSheet.Comments(commentIndex)oAsposeComment.Note = oComment.Note

If oComment.Height > 0 Then

oAsposeComment.Height = oComment.Height

End If

If oComment.Width > 0 Then

oAsposeComment.Width = oComment.Width

End If

If Not oComment.Font Is Nothing Then

oComment.Font.SetAsposeFont(oAsposeComment.Font)

End If

Thanks,

Scott

Hi Scott,

Are those commments created at run time?

What happens if you comment out the following code:

If oComment.Height > 0 Then

oAsposeComment.Height = oComment.Height

End If

If oComment.Width > 0 Then

oAsposeComment.Width = oComment.Width

End If

If Not oComment.Font Is Nothing Then

oComment.Font.SetAsposeFont(oAsposeComment.Font)

End If

Is it possible to tailer your code to show this problem? I cannot re-create it in my place.

Hi Laurence,


To pick this up. We thought we had fixed this issue by re-doing our template. It seems that the template we were using was created by a sheet that was first generated by aspose. so we created the template from scratch and the comment interference issue went away when the sheet was initially downloaded.

Here is the issue that has arisen again. When the sheet is first genrated from Aspose the sheet opens fine and there are no issues. However if the sheet is saved (excel is doing the saving now) and any cell is selected in any column other than the drop down column, the interference is there the next time the sheet is opened.

We thought it was a template problem again, so we pointed the application at a blank workbook to be used as the template. so the sheet generates now to a blank template and the behavior remains. When it is opened after generation there is no interference like you saw in the above attached examples. However if we then let Excel save it and then re-open it again all the interfernece is back.

Could there be something with the way aspose is generating the drop downs so that when excel does the saving it doesn't understand something and wires get crossed? We are at a loss and unfortunately we are getting ready to scrap Aspose because the product has to use Excel as the UI and drop down selections are an integral part of the functionality, however when excel saves the sheet after Aspose has generated it, Something breaks badly.

I am attaching an example, The attachement in this post is a workbook generated by aspose and you can see that the cell comments in column N are fine, and the drop downs in cells P,V,Z,AD and AK are all functional. This sheet was generated by Aspose. Go ahead and select any cell (like M19) except in the drop down columns, and save it (now excel is doing the saving and re-compiling I guess?). Now click on any of the drop down columns. and you will see the behavior. However if you save in excel with no cell selected or a cell in one of the drop down columns the behavior does not happen. Something is going on when excel does the save. Any Ideas.

Thanks,

Scott

Laurence,

Another thought that I want to add on to the post above....... When Aspose Generates the sheet it seems the cells are wired to their events properly. When it is re-opened and saved in excel (with a cell selected remember) It's almost like some of the event handlers inside excel get re-wired. Because then when you click on the drop down the event fires to show the arrow to view the drop down, but it also fires an event to view the comment.

Still looking, Scott

Hi Scott,

Thanks for your file.

Yes, this is a very weired problem. I will check and fix it ASAP.

Laurence,

Thanks for the heads up. We are going into production in the AM and for the time being, have switched the validations to validate against a non-drop down list. While this works with no interference it really lessens the effect of the UI as a whole.

We eagerly await your fix and I will put it out there as soon as I get it!!

-Scott

Hi Scott,

Could you tell me the password which protected your worksheet? If you unprotect worksheet first, then save the file. Will this problem still show?

Hi Scott,

This problem must exist in some place but I cannot reproduce it in my machine. Could you zip your project and template file then send it to nanjing@aspose.com? That will greatly help me to solve this problem. Thank you very much.

Laurence,

the password is mypassword for this particular sheet. it makes no difference if the sheet is protected or not. The template we used to generate the issue was a blank template. Nothing in it.

What exactly would you like us to send you as far as the project goes. Right no the generation of the sheets is part of a pretty large that spans about 8 seperate projects.

Maybe I could create a temporary project to just gerate a sheet, however there will be no data for you to poulate it with. Or do you just want to look at the classes that create the sheet?

-Scott

Hi Scott,

Yes. The password doesn't matter with this problem.

A temp project to reproduce this problem is enough. I think that data is not needed. Thank you very much.

Laurence,

I will try to get you out a temp project today or tomorrow.

-Scott