Can you let me know from where this fields data is being extracted into the excel file using MSOGenerateExcelFile activity. If possible can you share the screen shots of your use case to understand the issue in detail, which helps us to analyse.
I am trying to fetch the property value from a clipboard page (say ExcelPage.pxResults) and each of the pxResults has values for Price A, Price B and Total. The values are getting posted to the excel file properly.
However, if I try to write the formula - SUM() - in the template excel file, I cannot specify the property reference as the cell must then contain only the formula.
Does this provide the information that you were looking for?
I don't know how you would go about using MSOGenerateExcelFile to achieve this : my understanding of that Activity is that it is an OOTB Activity designed for a specific purpose (used within PRPC Wizards etc): so it probably doesn't support the inclusion of Excel Formulas.
As far as generating an EXCEL file using other mechanisms - you could build your own mechanism based on the Apache POI library : this library is included in PRPC.
There is an example here of using Apache POI from PRPC - to generate an EXCEL file.
This is what cause Microsoft Windows to recognize this XML file as being of type EXCEL when you save the file to your desktop etc.
Note also the format of the formula:
There the references are not made in the traditional alpha/numeric cross-reference : rather they are in (one-bound, not zero-bound) R and C (Row, and Col) coordinates.
The 'ONE!' reference is for the SHEET.
You can use PRPC XML Stream Rules to create dynamic versions of this XML : you would need to calculate the 'R' and 'C' values as you track through your 'pxResults' page in order to create your formula-references.
You will also need to (probably) set the HTTP Headers to a suitable type - to ensure the EXCEL automatically launches to load this file when you click-to-download.
Even if this all works: you will still need to solve the issue of how to deal with uploaded Excel files of course..... (Which may suggest the Apache POI route [although probably more work initially] may be a better solution - as you can work with the 'native' binary formats throughout).....
Sorry for the delayed response. Thanks for the approach. This definitely appears to be an alternative approach worth trying.
We didn't get to try it as a part of the project due to timeline constraints and went ahead with the MSOGenerateExcelFile feature (without formulas in the excel) and instead setup declare expressions in the application to handle this.
Thanks once again for the potential solution approach. Will try it out for learning purpose