Cell Formula displays over all cells in the row when editing formula

Posted by: kevin.keller on 27 April 2020, 10:48 pm EST

    • Post Options:
    • Link

    Posted 27 April 2020, 10:48 pm EST - Updated 30 September 2022, 4:33 am EST

    When Editing a Cell Formula that is a large formula the cell where the formula is located displays the entire formula so it bleeds over into all the cell needed to display that formula.

    When this happens trying to evaluate how the formulas results are determined becomes impossible in many cases because the values, if on the same row, are no longer visible and if you were editing and trying to click on cells to include them as part of the formula you can’t because it is blocked by the displayed formula.

    Attached is a sample image from our demo data showing the issue.

    Is there a way to have the cell not display the entire formula when using the formula Text box for editing the cell formula.

    Thanks,

    Kevin

  • Posted 29 April 2020, 4:26 am EST

    Hello Kevin,

    This is happening because when in edit mode the formula in the current cell is overflowing into adjacent cells.

    You can prevent this by setting the AllowEditOverflow property to false.

    Thanks,

    Ruchir

  • Posted 29 April 2020, 10:40 pm EST

    I am making a custom control that contains the spread control the formula box and the named range box. I did set property to false on the spread control inside my custom control and that was not working. I now added a line setting the property, after a successful load of a spread file into the control, and it now works.

    Does loading a file change that property?

    It is fine if it does just want to make sure something else didn’t cause that property to change.

    Thanks for your response,

    Kevin

  • Posted 30 April 2020, 3:32 pm EST

    Hello Kevin,

    AllowEditOverflow property is independent of loading file. You can check that at your end as well, by comparing the value of this property at just after Spread is initialized and after loading a file, in a new application.

    Thanks,

    Ruchir

  • Posted 30 April 2020, 10:39 pm EST

    I just tested it and it is false before this statement and true after it.

    If FpSpread.OpenExcel(strFileName, ExcelOpenFlags.ColumnHeaders Or ExcelOpenFlags.TruncateEmptyRowsAndColumns Or ExcelOpenFlags.DataValidationAsCombo) Then

    so In my case it does change it.

    Kevin

  • Posted 4 May 2020, 12:55 am EST

    Hi Kevin,

    I see. Upon further looking into it, I found that the value of AllowEditOverflow is always true in *.xlsx file so, it seems the Spread is not preserving the value of this property.

    Regarding this, I am contacting the developer [Sp.Net 6454] and requesting him more details on this. I will get back to you once we receive some information.

    Regards,

    Ruchir

  • Posted 4 May 2020, 7:41 pm EST

    Hi Kevin,

    Following is the response I have received from the team:

    By design, Spread will set AllowEditOverflow as TRUE after opening an XLSX file because it’s the behavior of Excel. It isn’t a bug of Spread.

    So,

    1. If the XLSX file is not created by customer, he must set AllowEditOverflow to FALSE explicitly.
    2. If the XLSX file is created by customer, he can export it with Exchangable flag. The value of AllowEditOverflow will be exported to XLSX file and then, when he open that file, it’ll keep the original value.

    Regards,

    Ruchir

Need extra support?

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

Learn More

Forum Channels