SaveGridToWorkbook automatically create formula from cell value

[DougHennig]

I added some new functionality to SaveGridToWorkbook if you're interested. If the value of a cell in a grid starts with '=', treat it like a formula. Here's the code:

*-*     Add the cell data values and cell formatting
        SELECT &lcGridAlias
        SCAN
            lnRow = lnRow + 1
            RAISEEVENT(this, "OnShowStatusMessage", 3, lnRow)
            FOR lnCol=1 TO lnColCount
                loColumn = toGrid.Columns(laColOrder[lnCol, 2])
                lcField  = loColumn.ControlSource
                lxCellValue = EVALUATE(lcField)
                IF loGrid.Columns(laColOrder[lnCol, 2]).FieldPercent
                    lxCellValue = lxCellValue / 100
                ENDIF
*** DH 2024-02-23: treat a value starting with "=" as a formula
*               this.SetCellValue(lnWB, lnSh, lnRow, lnCol+tnBegCol-1, lxCellValue)
                if vartype(lxCellValue) = 'C' and left(lxCellValue, 1) = '='
                    This.SetCellFormula(lnWB, lnSh, lnRow, lnCol+tnBegCol-1, strtran(lxCellValue, '{ROW}', transform(lnRow), -1, -1, 1))
                else
                    this.SetCellValue(lnWB, lnSh, lnRow, lnCol+tnBegCol-1, lxCellValue)
                endif vartype(lxCellValue) = 'C' ...
*** DH 2024-02-23: end of change
                IF loGrid.Columns(laColOrder[lnCol, 2]).DynamicProperties

[ggreen86]

I will add this feature into the next release.

I am also considering how I might add formula support to SaveGridToWorkbook and SaveGridToWorkbookEx methods by adding a custom property to the grid column object. I use a custom class for the columns which is then assigned to the grid (also a custom class) via the MemberClass property. So, it is easy for me to define the property. It could also be added at run-time if not using a custom column class. I am thinking to name the property 'ColumnFormula' and the developer would put the desired formula; i.e., SUM, AVG, etc., to be added after the last row is outputted as the property value. What are your thoughts on this approach?

Greg

[DougHennig]

Sounds like a good idea.