Accounting Format

Spread.Sheets supports accounting format on cells to avoid mistakes while reading currency values. This feature is supported in the same way as in Excel.

The accounting format facilitates enhanced readability by allowing users to align the currency symbols and decimal points for numbers in a column. Negative numbers are displayed in parentheses and zeros are displayed as dashes.

The following table lists the accounting formatting characters and the formatting characters that are provided for using the accounting format:

Character	Description
_	You can use the underscore character "_" to create a space that is the width of a character in a number format. For example, _) causes positive numbers to line up with negative numbers that are enclosed in parentheses.
*	Use the asterisk "" in the number format to repeat the next character so that it fills the column width. For example, type 0- to include enough dashes after a number to fill the cell. Type *0 before any format to include leading zeros.
,	The comma displays the thousands separator in a number. Spread separates thousands by commas if the format contains a comma that is enclosed by number signs "#" or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number $12200.0 is displayed.
%	Use the percent sign "%" in the number format to display numbers as a percentage of 100. For example, to display .08 as 8%.
?	This digit placeholder follows the same rules as 0 (zero); however, Spread adds a space for insignificant zeros on either side of the decimal point.

The following list describes some of the things that you need to take care of while using accounting format:

If word wrap is allowed and the cell contains multiple lines, then the repeat characters do not take effect.
Repeat characters are ignored if the cell supports text overflow.
The repeat characters are not used with automatic fit.
The repeat character does not become blank with shrink to fit.
The blank area does not display a text decoration.
The supported cell types for accounting format includes: Text cell type, Button cell type, ComboBox cell type and Header Cell cell type.

Using Code

This example sets the accounting format for cells using the setValue method.

JavaScript	Copy Code
var sheet = spread.getActiveSheet(); sheet.setValue(1, 0, 'Standard Accounting Format : _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'); sheet.setValue(2, 0, 'Value'); sheet.setValue(3, 0, 12); sheet.setValue(4, 0, -12); sheet.setValue(5, 0, 0); sheet.setValue(6, 0, 'Text'); sheet..setValue(2, 2, 'Formatted Result'); sheet.getRange(3, 2, 4, 1).formatter('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'); sheet.setValue(3, 2, 12); sheet.setValue(4, 2, -12); sheet.setValue(5, 2, 0); sheet.setValue(6, 2, 'Text');

JavaScript

Copy Code

var sheet = spread.getActiveSheet();

sheet.setValue(1, 0, 'Standard Accounting Format : _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)');

sheet.setValue(2, 0, 'Value');
sheet.setValue(3, 0, 12);
sheet.setValue(4, 0, -12);
sheet.setValue(5, 0, 0);
sheet.setValue(6, 0, 'Text');
sheet..setValue(2, 2, 'Formatted Result');
sheet.getRange(3, 2, 4, 1).formatter('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)');
sheet.setValue(3, 2, 12);
sheet.setValue(4, 2, -12);
sheet.setValue(5, 2, 0);
sheet.setValue(6, 2, 'Text');

This example uses the setFormatter method.

JavaScript	Copy Code
var sheet = spread.getActiveSheet(); sheet.setValue(1, 2, 12.34); sheet.setFormatter(1, 2, '$* #.##');

JavaScript

Copy Code

var sheet = spread.getActiveSheet();

sheet.setValue(1, 2, 12.34);
sheet.setFormatter(1, 2, '$* #.##');