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.

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.

// Enable AutoFormatting
fpSpread1.AsWorkbook().Features.AutoFormatting = true;

Using Code

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

// 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()";