This project is read-only.

Create row and relative formula reference

Mar 20, 2013 at 2:13 PM

CopyRow function does not update relative references in formulas for the cells in the new row.
Is this a correct behavior or is there something I have not taken into account?
Unless CopyRow update references, is there any good way to solve my problem?

Yours sincerely
Jul 22, 2013 at 4:58 PM
Edited Jul 22, 2013 at 5:10 PM
I dont know how CopyRow actually works (internally).. cause I've implemented my own copy cell/row which works by copying a cell or row or column, to a different row and column (if you want to full code just let me know), but when it comes to update a Formula this is what I do:

To add some context, you basically would have the SourceCell from which you're copying the data and the newCell. Now, if the CellType of the cell is Formula, I do this....
case CellType.FORMULA:
                        var evahssfwb = HSSFEvaluationWorkbook.Create(hssfwb);
                        var tokens = FormulaParser.Parse(sourceCell.CellFormula, evahssfwb);

                        foreach (var token in tokens)
                            if (token is RefPtg)
                                var refFormula = (RefPtg)token;
                                refFormula.Row = refFormula.IsRowRelative && sourceCell.RowIndex != newCell.RowIndex ? newCell.RowIndex - (sourceCell.RowIndex - refFormula.Row) : refFormula.Row;
                                refFormula.Column = refFormula.IsColRelative && sourceCell.ColumnIndex != newCell.ColumnIndex ? newCell.ColumnIndex - (sourceCell.ColumnIndex - refFormula.Column) : refFormula.Column;

                        newCell.SetCellFormula(FormulaRenderer.ToFormulaString(evahssfwb, tokens));

RefPtf Contains the Cell Reference of the Formulas. So basically what we are doing is getting the tokens of the sourceCell Formula, updating the row or columns according to the destination Cell (newCell) and setting that value on the newCell.

Let me know if this helps