- Spread for WPF Overview
- Key Features
- Getting Started
- Quick Start
- Designer
- Features
- Formula Reference
- Assembly Reference
AutoFormat Formulas
The Auto-Format Formula feature in Spread for WPF automatically formats the result values based on the data type returned by a formula, similar to Excel 2019 and Excel for Office 365. This functionality simplifies formatting by dynamically adjusting it according to the formula's output.
To enable the auto-format feature, you can set the AutoFormatting property of the Features class. Depending on the function arguments, the return value could be a date, number, or cell reference, and the corresponding format is applied automatically.
Date Format: Functions that return date values (e.g., TODAY, DATE) automatically apply a date format to the worksheet’s cell.
Number Format: Functions that return numeric values (e.g., SUM, SUBTOTAL, RATE, IRR, DB, FV) automatically apply a number format to the worksheet’s cell.
Cell Reference Format: Formulas containing cell references automatically apply the cell reference format to the corresponding worksheet’s cell.
The following image illustrates how formulas are automatically formatted when the AutoFormatting feature is enabled or disabled.
|
|
|---|---|
Refer to the following example code to set the AutoFormatting property to true, enabling automatic formatting of formulas based on the returned data type.
C#
// Set Auto-format for formula.
spreadSheet1.Workbook.Features.AutoFormatting = true;
spreadSheet1.Workbook.ActiveSheet.Cells[0, 0].Text = "Set AutoFormatting property to True.";
spreadSheet1.Workbook.ActiveSheet.Cells[0, 0].Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LavenderBlush);
spreadSheet1.Workbook.ActiveSheet.Cells[0, 0, 0, 3].Merge(true, true, true);
spreadSheet1.Workbook.ActiveSheet.Columns[1, 2].ColumnWidth = 130;
// Number format.
spreadSheet1.Workbook.ActiveSheet.Cells["A2"].Text = "RATE(60,-5,150)";
spreadSheet1.Workbook.ActiveSheet.Cells["B2"].Formula = "RATE(60,-5,150)";
// Date format.
spreadSheet1.Workbook.ActiveSheet.Cells["A3"].Text = "Today()";
spreadSheet1.Workbook.ActiveSheet.Cells["B3"].Formula = "Today()";VB
' Set Auto-format for formula.
spreadSheet1.Workbook.Features.AutoFormatting = True
spreadSheet1.Workbook.ActiveSheet.Cells(0, 0).Text = "Set AutoFormatting property to True."
spreadSheet1.Workbook.ActiveSheet.Cells(0, 0).Interior.Color = GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.LavenderBlush)
spreadSheet1.Workbook.ActiveSheet.Cells(0, 0, 0, 3).Merge(True, True, True)
spreadSheet1.Workbook.ActiveSheet.Columns(1, 2).ColumnWidth = 130
' Number format.
spreadSheet1.Workbook.ActiveSheet.Cells("A2").Text = "RATE(60,-5,150)"
spreadSheet1.Workbook.ActiveSheet.Cells("B2").Formula = "RATE(60,-5,150)"
' Date format.
spreadSheet1.Workbook.ActiveSheet.Cells("A3").Text = "Today()"
spreadSheet1.Workbook.ActiveSheet.Cells("B3").Formula = "Today()"