Hello,
I am converting an existing winforms application that uses Excel COM Automation to a new WPF application using XamSpreadSheet and Infragistics Excel Engine.
We have a lot of formulae and named references in our existing data. Loading these into the new version raises errors like - Formulae should have sheet names qualified for named references.
I am not sure how to go about that properly, as existing named references could have other named references and cell references in formulae.
Should the named references assume the same worksheet as they are being evaluated on, if no explicit sheet name is specified.
Hello Prateek,
Thank you for your post!
I do not believe that this behavior that you are seeing is expected, as I have discussed this with my colleagues and the ruling was that if Excel can open the Workbook, we should be able to as well. As such, I have a couple of questions for you on this matter. Can you please provide some information on the following?
1. What version of Infragistics for WPF are you using in the application trying to load the Workbook?
2. Are you able to provide a copy of a Workbook that reproduces the behavior you are seeing? If the data is potentially sensitive and you do not want it visible publicly on the forums, I can create a private support case for you on this matter.
Please let me know if you have any other questions or concerns on this matter.
InfragisticsEval.zip
Hi Andrew,
Please see attached solution for a demonstration of the issue. If you load an Excel file, it works OK.
But if you try to programatically assign a cell reference such as "=C15" or "=IF(MyCondition, F29, X77)" then it fails with the exception as shown in the attached image.
Let me know if you need more info.
We are in the process of evaluating Infragistics upgrade for our application platform and excel compatibility is must for us.
Thanks
I have been investigating into the behavior you are looking to achieve, and it appears that the sheet name currently is necessary, as this is the same way it works within Excel.
If you create a named reference within Excel, then internally this named reference still has the Excel Worksheet name in it. As such, we require you to add the Worksheet name as we cannot assume to utilize this on the currently active Worksheet within the Workbook.
Many thanks. It did not solve my issue as my data does not have the sheet name; however I was able to regex the formulas and add the sheet name to all the cell references prior to assigning them.
So got it working with a workaround
One more query ... When I have a formula, excel highlights the referenced cells so the user can see what cells the formula is dependent upon. this is a very useful and admired feature. I was unable to find that in the XamSpreadsheet. Is it available or can be activated somehow?
Thank you for your update on this matter. I am glad you were able to get this working!
Regarding the formula highlight feature from Excel, unfortunately the XamSpreadsheet does not currently support this, outside of parsing the formula yourself and applying a CellFormat to the affected cells. If you would like to see this potentially implemented in a future version of the control, I would recommend suggesting a new product idea for this at our WPF Ideas Site, here. This will place you in direct communication with our product management teams who plan and prioritize upcoming features and development based on community and user feedback.