Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Formulas in Cells / Auto Format Formulas
In This Topic
    Auto Format Formulas
    In This Topic

    Spread for Winforms provides support for automatic formatting of formula values based on the appropriate data type returned by the function in the same way as in Excel 2019 and Excel for Office 365. This feature can be used when users want the Spread control to automatically format the cells based on the formula return value.

    The following image depicts how the formulas are automatically formatted when the Auto Formatting feature is enabled in the spreadsheet.

    Auto Formatting feature enabled 

    Note: The auto formatting feature works only when the user inputs the formula directly into the cell in flat style mode (LegacyBehaviors.Style is not used). If users edit a cell in flat style mode, the detected cell format is always applied regardless of the AutoFormatting property.

    Date Format - Users can use the formula functions that return date values (e.g. TODAY, DATE, etc.) and date formatting will be automatically applied in the cells of the spreadsheet just like in Excel if the AutoFormatting property is set to "True".

    Number Format - Users can use the formula functions that return number values (e.g. SUM, SUBTOTAL, RATE, IRR, DB, FV etc.) and number formatting will be automatically applied to the cells of the spreadsheet just like in Excel if the AutoFormatting property is set to "True".

    Cell Reference Format - Users can use a cell reference in the formula and cell reference format will be automatically applied to the cells of the spreadsheet in the same way as in Excel if the AutoFormatting property is set to "True".

    Example - For example, in the above image, when the AutoFormatting property is set to False, cell C4 shows the date as an integer value (incorrect format). But when the AutoFormatting property is enabled i.e. the value of this property is set to true, the date is displayed in the correct format (MM/DD/YY).

    Simillarly, the cell C5 containing RATE formula doesn't display the data in percentage format but once the Auto formatting fetaure is enabled, cell C5 displays the correct output of RATE formula in the percentage format.

    Also, the cells C2 and C3 containing the AVERAGE formula and the MEDIAN formula of the cell range (A2:A6) respectively take up the format of the cells on which they are dependent.

    Enable Auto Formatting

    Users can enable the Auto Formatting feature by setting the value of the AutoFormatting property to "true", as shown in the following code snippet.

    C#
    Copy Code
    // Enable AutoFormatting
    fpSpread1.AsWorkbook().Features.AutoFormatting = true;
    

    Using Code

    This example code shows how formulas can be automatically formatted in the worksheet.

    C#
    Copy Code
    // Enable AutoFormatting
    fpSpread1.AsWorkbook().Features.AutoFormatting = true;
    
    fpSpread1.AsWorkbook().ActiveSheet.Cells[0, 0].Text = "AutoFormatting is set to True";
    fpSpread1.ActiveSheet.Cells[0, 0].BackColor = Color.Thistle;
    fpSpread1.ActiveSheet.AddSpanCell(0, 0, 1, 3);
    fpSpread1.AsWorkbook().ActiveSheet.Cells[1, 0].Text = "$0.20";
    fpSpread1.AsWorkbook().ActiveSheet.Cells[2, 0].Text = "$0.10";
    fpSpread1.AsWorkbook().ActiveSheet.Cells[3, 0].Text = "$0.25";
    fpSpread1.AsWorkbook().ActiveSheet.Cells[4, 0].Text = "$0.20";
    fpSpread1.AsWorkbook().ActiveSheet.Cells[5, 0].Text = "$0.30";
    fpSpread1.AsWorkbook().ActiveSheet.Cells[1, 1].Text = "AVERAGE(A2:A6)";
    fpSpread1.AsWorkbook().ActiveSheet.Cells[2, 1].Text = "MEDIAN(A2:A6)";
    fpSpread1.AsWorkbook().ActiveSheet.Cells[3, 1].Text = "Today()";
    fpSpread1.ActiveSheet.Columns[1, 3].Width = 130;
    // Formula cells gets formatted dependent on cells they are referring to
    // 1. Example of Cell Reference Format
    fpSpread1.AsWorkbook().ActiveSheet.Cells["C2"].Formula = "AVERAGE(A2:A6)";
    fpSpread1.AsWorkbook().ActiveSheet.Cells["C3"].Formula = "MEDIAN(A2:A6)";
    // 2. Example of Number Format
    fpSpread1.AsWorkbook().ActiveSheet.Cells["B5"].Text = "RATE(60,-5,150)";
    fpSpread1.AsWorkbook().ActiveSheet.Cells["C5"].Formula = "RATE(60,-5,150)";
    // 3. Example of Date Format
    fpSpread1.AsWorkbook().ActiveSheet.Cells["C4"].Formula = "Today()";