| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
XL.Format.SetNumFormat
Sets the number format identifier.
| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
| XL | 3.5 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameters
| Parameter | Description | Example |
|---|---|---|
| Book | The reference to the workbook. Please use XL.LoadBook to load a file. | $ref |
| Format | The index of the format in the book to target. | 0 |
| Value | The number format to use. | 1 |
Result
Returns OK or error.
Description
Sets the number format identifier.The identifier must be a valid built-in number format identifier or the identifier of a custom number format. To create a custom format use XL.Book.AddCustomNumFormat.
See built-in values in the table on this page:
http://www.libxl.com/format.html
| Constant | Value | Description | Example |
| NUMFORMAT_GENERAL | 0 | general format | |
| NUMFORMAT_NUMBER | 1 | general number | 1000 |
| NUMFORMAT_NUMBER_D2 | 2 | number with decimal point | 1000.00 |
| NUMFORMAT_NUMBER_SEP | 3 | number with thousands separator | 100,000 |
| NUMFORMAT_NUMBER_SEP_D2 | 4 | number with decimal point and thousands separator | 100,000.00 |
| NUMFORMAT_CURRENCY_NEGBRA | 5 | monetary value, negative in brackets | (1000$) |
| NUMFORMAT_CURRENCY_NEGBRARED | 6 | monetary value, negative is red in brackets | (1000$) |
| NUMFORMAT_CURRENCY_D2_NEGBRA | 7 | monetary value with decimal point, negative in brackets | ($1000.00) |
| NUMFORMAT_CURRENCY_D2_NEGBRARED | 8 | monetary value with decimal point, negative is red in brackets | ($1000.00) |
| NUMFORMAT_PERCENT | 9 | percent value, multiply the cell value by 100 | 75% |
| NUMFORMAT_PERCENT_D2 | 10 | percent value with decimal point, multiply the cell value by 100 | 75.00% |
| NUMFORMAT_SCIENTIFIC_D2 | 11 | scientific value with E character and decimal point | 10.00E+1 |
| NUMFORMAT_FRACTION_ONEDIG | 12 | fraction value, one digit | 10 1/2 |
| NUMFORMAT_FRACTION_TWODIG | 13 | fraction value, two digits | 10 23/95 |
| NUMFORMAT_DATE | 14 | date value, depends on OS settings | 3/11/2009 |
| NUMFORMAT_CUSTOM_D_MON_YY | 15 | custom date value | 11-Mar-09 |
| NUMFORMAT_CUSTOM_D_MON | 16 | custom date value | 11-Mar |
| NUMFORMAT_CUSTOM_MON_YY | 17 | custom date value | Mar-09 |
| NUMFORMAT_CUSTOM_HMM_AM | 18 | custom date value | 8:30 AM |
| NUMFORMAT_CUSTOM_HMMSS_AM | 19 | custom date value | 8:30:00 AM |
| NUMFORMAT_CUSTOM_HMM | 20 | custom date value | 8:30 |
| NUMFORMAT_CUSTOM_HMMSS | 21 | custom date value | 8:30:00 |
| NUMFORMAT_CUSTOM_MDYYYY_HMM | 22 | custom datetime value | 3/11/2009 8:30 |
| NUMFORMAT_NUMBER_SEP_NEGBRA | 37 | number with thousands separator, negative in brackets | (4,000) |
| NUMFORMAT_NUMBER_SEP_NEGBRARED | 38 | number with thousands separator, negative is red in brackets | (4,000) |
| NUMFORMAT_NUMBER_D2_SEP_NEGBRA | 39 | number with thousands separator and decimal point, negative in brackets | (4,000.00) |
| NUMFORMAT_NUMBER_D2_SEP_NEGBRARED | 40 | number with thousands separator and decimal point, negative is red in brackets | (4,000.00) |
| NUMFORMAT_ACCOUNT | 41 | account value | 5,000 |
| NUMFORMAT_ACCOUNTCUR | 42 | account value with currency symbol | $5,000 |
| NUMFORMAT_ACCOUNT_D2 | 43 | account value with decimal point | 5,000.00 |
| NUMFORMAT_ACCOUNT_D2_CUR | 44 | account value with currency symbol and decimal point | $5,000.00 |
| NUMFORMAT_CUSTOM_MMSS | 45 | custom time value | 30:55 |
| NUMFORMAT_CUSTOM_H0MMSS | 46 | custom time value | 20:30:55 |
| NUMFORMAT_CUSTOM_MMSS0 | 47 | custom time value | 30:55.0 |
| NUMFORMAT_CUSTOM_000P0E_PLUS0 | 48 | custom value | 15.2E+3 |
| NUMFORMAT_TEXT | 49 | text value | any text |
Examples
Use a number format:
# create new format
Set Variable [$format; Value:MBS( "XL.Book.AddFormat"; $book)]
# use existing number format
Set Variable [$r; Value:MBS( "XL.Format.SetNumFormat"; $book; $format; 7)]
# and use format
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $book; $sheet; 5; 3; 123; $format)]
Apply a new format to a column:
#create new format
Set Variable [$format; Value:MBS( "XL.Book.AddFormat"; $BookRef )]
Set Variable [$r; Value:MBS( "XL.Format.SetNumFormat"; $BookRef; $format; 7)]
#set as format for column
Set Variable [$r; Value:MBS( "XL.Sheet.SetColumn"; $BookRef; 0; 6; 6; 50 ; $format; 0 )]
#loop over all rows
Set Variable [$lastRow; Value:MBS( "XL.Sheet.GetLastRow"; $BookRef; $sheet )]
Set Variable [$row; Value:1]
Loop
Set Variable [$r; Value:MBS( "XL.Sheet.SetCellFormat"; $BookRef; $sheet; $row; 6; $format )]
#next
Set Variable [$row; Value:$row + 1]
Exit Loop If [$row > $lastRow]
End Loop
See also
- XL.Book.AddCustomNumFormat
- XL.Book.AddFormat
- XL.Date.Pack
- XL.Format.GetNumFormat
- XL.LoadBook
- XL.Sheet.CellWriteNumber
- XL.Sheet.GetLastRow
- XL.Sheet.SetCellFormat
- XL.Sheet.SetCol
- XL.Sheet.SetColumn
Release notes
- Version 12.5
- Fixed XL.Format.SetNumFormat and related functions to not crash if you pass an invalid format number.
Example Databases
Blog Entries
This function checks for a license.
Created 18th August 2014, last changed 30th March 2024
