When using named references in formula columns on the UltraGrid is it possible to export them to Excel when using the ExportFormulas option. The formulas are being exported with an error.
Hi,
No, this is currently not supported. Only references to other cells in the grid are exported, because there's no way to translate external references (such as other controls on the form) into Excel.
In theory, as long as a NamedReference doesn't reference anything outside of the grid, it could be exported into a hidden worksheet and referenced by the Excel sheet, but this is currently not implemented.
You should Submit a feature request to Infragistics
It would seem that the easiest way to replicate a NamedReference would be to define a Name in Excel with the formula being equal to the NamedReference value at the time of export. I will definitely create a feature request for this because as it stands it is impossible to export a grid column that references a NamedReference in its formula.
That makes sense and it seems like it should be a viable workaround. :)
Yes, you are right that it is indeed possible to export the values but not the actual formulas
I think the approach I am going to take is to create a visible worksheet in the workbook that contains a table of NamedReferences. The table will include the name and value of the NamedReference and since my NamedReferences are all simply "=5" this should be straightforward.
Then I will override the FormulaExporting event and simply parse the string for any NamedReferences and update the Excel formula to point to the cell I created in the NamedReference worksheet.
bperkins24 said:One more question, is it possible to get low-level access to the different pieces parsed from a formula? Basically I want to know if there is some UltraFormula object or something where I could do formula.Contains(ultraCalcManager1.NamedReference["key"]). Just want to know if this exists because I think it is cleaner than parsing the string for [//key].
I don't think there's any easy way to do this. Parsing the text is probably your best option.
By the way... exporting with NamedReferences is not quite "impossible" as you said above. You could turn off the exporting of formulas by setting ExportFormulas on the UltraGridExcelExporter to false. In that case, the exporter will export the current values to Excel instead of the formulas. The exported Excel file would then be accurate. The obvious down-side, of course, is that if the user edits the Excel file, nothing will be re-calculated.
It's not quite as simple as it seems, because the NamedReference could contain references to other controls on the form, other named References, or even references back to the grid.
But in the case there the NamedReference does not reference anything external, it's reasonable for the exporter to handle it.
In fact, the exporter already creates a hidden worksheet in the workbook to store certain data and create lists of data in a block, rather than split up by outlining or child bands. So the hidden worksheet could easily contain the NamedReferences, or as you said, we could create Excel NameReferences.
One more question, is it possible to get low-level access to the different pieces parsed from a formula? Basically I want to know if there is some UltraFormula object or something where I could do formula.Contains(ultraCalcManager1.NamedReference["key"]). Just want to know if this exists because I think it is cleaner than parsing the string for [//key].