Drag and Fill and Formulas (c#.Net)

Posted by: dylan-h on 8 September 2017, 3:17 pm EST

  • Posted 8 September 2017, 3:17 pm EST

    Hi,

    I want to allow drag and fill for formula like Ms Excel in my application. (c#.Net)

    I found some drag and fill properties for auto increment.

    However, I can not find drag and fill properties for Copying same formula to several cells.

    Would tell me the way for it?

    Thanks.
  • Replied 8 September 2017, 3:17 pm EST

    Hello Dylan,

    Please refer the attached sample which implements the copying a formula on a range of cells by dragging. Also refer the GIF contained in the attached ZIP.

    Hope it helps.

    Regards,
    Ankita
    2015/01/Spread_AutoFill_Handle.zip
  • Replied 8 September 2017, 3:17 pm EST

    thank you. it really helps.

    and now I try to make it specific functions using it.

    but, it little bit hard for me.

    I want same functions like MS Excel drag and fill formula, not the copy the formula.

    for example,

    if I declare the formula like "=sum(a1:b1)" at "c1" cell, and then drag down this cell to "c2",
    it would be automatically changed like "=sum(a2,b2)" at "c2" cell.

    could give me more hint for this function?

    I really thank you for your service, Ankita.
  • Replied 8 September 2017, 3:17 pm EST

    Hello Dylan,

    Unfortunately this wont be possible. However I have escalated this issue(add the drag and fill formula feature, as in Excel) to the development team as an enhancement request. We would let you know as soon as we receive any reply from them. The tracking id for the same is 107942.

    Regards,
    Ankita
  • Replied 8 September 2017, 3:17 pm EST

    thank you! Ankita.
  • Marked as Answer

    Replied 8 September 2017, 3:17 pm EST

    @Dylan.H said:
    thank you. it really helps.

    and now I try to make it specific functions using it.

    but, it little bit hard for me.

    I want same functions like MS Excel drag and fill formula, not the copy the formula.

    for example,

    if I declare the formula like "=sum(a1:b1)" at "c1" cell, and then drag down this cell to "c2",
    it would be automatically changed like "=sum(a2,b2)" at "c2" cell.

    could give me more hint for this function?

    I really thank you for your service, Ankita.


    Hi Dylan,

    It is actually possible to implement code to do that pretty easily.
    You just need to use the GetExpression/SetExpression methods instead of GetFormula/SetFormula; for example:
    fpSpread.Sheets[0].SetExpression(2, 0, fpSpread.Sheets[0].GetExpression(1, 0));

    When the formula is set to the cell, and it uses relative cell references (as your example formulas do -- e.g. "A1" is relative while "$A$1" is absolute), then the parsed Expression will contain a CellExpression with RowRelative/ColumnRelative = true, so that when the Expression is moved, those relative references will move with the expression.

    Hope that helps and is clear enough!

    Regards,
    -Sean
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels