Laurence,
Recently you provided us with an update to try and solve the drawing objects mysteriously appearing in our generated sheets. It was version 3.8.0.2. After we starting using this revision we began to notice strange things happening with our formula resolutions. We store the definitions for our columns in XML as you will see below. We have to escape any ampersands that we use for concatenation using &. What we are now seeing with this new revision is that if there is a quote mark in the formula definition that is next to the escaped ampersand (it doesn't matter what size the quote is on) the formula will be generated with an extra quote mark inserted, therefore breaking the formula. We have changed nothing in the definitions to break this. The only thing we have done is implement 3.8.0.2.
Example #1
XML - =IF(MID(P@ROW@,1,1)="E","0-"&(BD3*100)&"%",IF(MID(P@ROW@,1,1)="A","0-"&(BD4*100)&"%"," 0%"))
Resolves To...
=IF(MID(P22,1,1)="E","0-""&(BD3*100)&""%",IF(MID(P22,1,1)="A","0-""&(BD4*100)&""%"," 0%"))
and it used to resolve to...
=IF(MID(P22,1,1)="E","0-"&(BD3*100)&"%",IF(MID(P22,1,1)="A","0-"&(BD4*100)&"%"," 0%"))
Example #2
XML - =TRIM(MID(K@ROW@ & M@ROW@ & AO@ROW@ & " " & AC@ROW@ & " " & L@ROW@ & " ",1,14))
Resolves To...
=TRIM(MID(K19&M19&AO19&" ""&AC19&"" ""&L19&"" ",1,14))
and it used to resolve to...
=TRIM(MID(K19&M19&AO19&" "&AC19&" "&L19&" ",1,14))
Again the only change was the new dll revision.
Thanks,
Scott
Laurence,
More information....
This formula is right in the template and there is no escaped ampersand or formula resolution from xml involved....
=CONCATENATE("Merit Workbook for ",AD1, " (",AD2, ")")
it's resolving to this, when aspose opens the sheet.....
=CONCATENATE("Merit Workbook for ",AD1," ("",AD2,"")")
Its adding extra quote marks when it builds that sheets.
thanks
Does this problem exist in previous version?
Please try this latest version.
Laurence,
This just started happening when you sent us 3.8.0.2 last week to solve the arrow issue. I will test the one you just sent and get back to you right away.
-Scott
Laurence,
It fixed the issue with the extra quotes in the template. However the two coulmns that have their firmulas built on the fly R & BM still have the extra quotes, and try and drop down the perf rating culumn P. Whatever you did brought back the arrow issue again. I have attached a sheet. as an example.
I am going to go back and try previous versions you sent me to let you know when both things were working.
OK, I rolled back to version 3.7.1.3 and the issue with drawing objects disappeared, and the issue with the extra quotes went away in the columns but it is still a problem in the formula that is directly in the template which you just fixed with version 3.8.1. So I'll summarize ....
3.7.1.3 - (1 issue - a)
a. The issue with the extra quotes in the template formula is present.
b. the issue with the extra quotes in the columns that get their formula from the xml is not there.
c. The issue with the arrows and the drop downs is not there for the most part.
3.8.0.2 - (2 issues - a,b )
The issue with the extra quotes in the template formula is present.
the issue with the extra quotes in the columns that get their formula from the xml is present.
The issue with the arrows and the drop downs is not there for the most part.
3.8.1 - (b,c)
The issue with the extra quotes in the template formula is fixed.
the issue with the extra quotes in the columns that get their formula from the xml is present.
The issue with the arrows and the drop downs has re-appeared.
It seems like one fix is breaking others. Please Advise.
Thank you very much for the information.
Actually I don't make changes on arrows and drop downs. I will check this issue again. However, is it possible to create a simple sampe project to show this problem? Your previous project is too complex and I cannot find the deep root for this problem. And I don't find other user report this problem.
For other issue, I will check and fix them soon.
Please try this attached fix. Formula issue should be solved.
I checked v3.8.0.2 and v3.8.1 and just found one place that may do change to arrow and drop down issue. I rolled it back. However, I don't know if this issue is solved for I don't have a workable test case to verify it.
Laurence,
That fixed the issue with the formula's both in the template as well as in the definitions. The drawing object issue is still there. And is the same as the sheet I attached a few post ago in this thread. I am a little torn what to do because the The formula thing was discovered in a production system and you have fixed it in this version, but I can't put it out there because of the drop down thing. I am going to have to roll-back to 3.7.3 for now and change the formula so there are no quotes.
The project i sent you last time was about as simple as I could make it using our excel generator. I am not sure what to do. Something is differnet between 3.7.3 and the 3.8 versions. You had the drop down thing fixed and something has btought it back.
Take alook at the spreadsheet I have attached. The interference is nowith the comments in column n anymore. it is a rogue arrow that shows up in column p when you try to drop down the list.
Please try this attached fix. This problem may be related to Worksheet.Copy method.
If the problem still exist there, could you please:
1. Post your template file here. Does it includes image, comment or data validations?
2. In your previous project, try to remove all code to access database and reproduce the problem. I don't have an Oracle database and I think this problem has nothing to do with the data.
And please have a try on this version.