Add operator for custom objects

Posted by: anna-fp on 8 September 2017, 2:06 pm EST

  • Posted 8 September 2017, 2:06 pm EST


    I have a custom class with <span>overloaded "+" operator</span>:

    <font color="#0000ff">public class</font> <font color="#006600">MyItem</font>
            <font color="#0000ff">public</font> <font color="#0000ff">int </font>Value { <font color="#0000ff">get</font>; <font color="#0000ff">set</font>; }
            <font color="#0000ff">public static</font> <font color="#006600">MyItem </font><font color="#0000ff">operator </font>+(<font color="#006600">MyItem </font>left, <font color="#006600">MyItem </font>right) { <font color="#0000ff">return new</font> <font color="#006600">MyItem</font>{Value = left.Value + right.Value };}
            <font color="#0000ff">public override</font> <font color="#0000ff">string </font>ToString() { <font color="#0000ff">return </font>Value.ToString(); }

    Collection of MyItem's objects is data for spreadsheet.

                <font color="#0000ff">var</font> item1 = <font color="#0000ff">new </font><font color="#006600">MyItem </font>{Value = 1};
                <font color="#0000ff">var </font>item2 = <font color="#0000ff">new </font><font color="#006600">MyItem </font>{ Value = 2 };

                <font color="#0000ff">var </font>model = <font color="#0000ff">new </font><font color="#006600">DefaultSheetDataModel</font>(2, 2);
                model.SetValue(0, 0, item1); // <font color="#000099">see "1" </font>
                model.SetValue(0, 1, item2); // <font color="#000099">see "2" </font>

                fpSpread1_Sheet1.Models.Data = model;

    If you run this code, you see grid where A1= 1 and B1=2.

    Add the following code:

                <font color="#006600">MyItem </font>result = item1 + item2;
                model.SetValue(1, 0, result);// <font color="#000099">see "3"</font>

                model.SetFormula(1, 1, <font color="#cc0000">"A1+B1"</font>); // <font color="#ff0000"><b>see #VALUE! , but </b></font><b><font color="#cc0000">A1+B1 = item1 + item2 = result</font></b>

    How can I use standard operations/functions for custom objects?

    "Add" operator doesn't work for strings too!!!


  • Replied 8 September 2017, 2:06 pm EST


    The spreadsheet is working as intended.  The spreadsheet's built-in Excel-like operators and functions work with Excel-like data types (i.e. number, string, boolean, and error values).  The built-in operators and functions do not know about custom data types and thus do not work with custom data types.  The custom data types and their operators are defined in your C# application code but are not defined in the spreadsheet.  When an unrecognized data type is encountered, the formula will evaluate to a #VALUE! error.  You would need to create your own custom functions to work with your custom data types.  This would involve deriving a custom function class from the FunctionInfo class and then calling the AddCustomFunction with an instance of the custom function class.

    The spreadsheet pretty much follows Excel's rules for when a string values can be passed to operators or functions expecting number values.  For example, Excel's + operator converts string arguments to number values but SUM function ignores string arguments passed by reference.  Spread does the same...

                DefaultSheetDataModel model = (DefaultSheetDataModel)fpSpread1.Sheets[0].Models.Data;
                model.SetValue(0, 0, "1");
                model.SetValue(1, 0, "2");
                model.SetFormula(2, 0, "A1+A2"); // evaluates to 3
                model.SetFormula(3, 0, "SUM(A1,A2)"); // evaluates to 0

  • Replied 8 September 2017, 2:06 pm EST

    Thanks, bobbyo.

    Only one comment: I have checked "A1+A2" and "SUM(A1,A2)" functions into Excel for A1="1" and A2="2" => SUM(A1,A2) = 0, BUT A1+A2=#VALUE!


  • Replied 8 September 2017, 2:06 pm EST


    I checked both Excel 2000 and Excel 2007 (the two versions of Excel that I have on my computer).  In both versions, if A1 = "1" and A2 = "2" then SUM(A1,A2) = 0 and A1+A2 = 3.  I am not sure what you and I are doing differently in Excel to get different results for A1+A2.

    Note that Excel uses your computer's regional settings for converting strings to numbers.  If A1 = "1.0" and A2 = "2.0" then A1+A2 = 3 with "English (United States)" regional setting which uses period "." for decimal symbol but A1+A2 = #VALUE! with "German (Germany)" regional setting which uses comma "," for decimal symbol.

Need extra support?

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

Learn More

Forum Channels