Sets the format of the number in a cell.
The number format must be assigned to a format. Then the format can be assigned to one or more cells when using XLWriteïż½ functions. Before the number format attribute can be set to a format, the format must have been created by using the XLAddFormat function.
Parameters:
|
Name |
Type |
Value |
Description |
|
xlHandle |
Integer |
0 - n |
A handle to an Excel document. The document must have been created by using the function XLCreateFromTemplate and the Integer we obtained from it is used as xlHandle here. |
|
formatHandle |
Integer |
0 - n |
A handle to a format object. The formatHandle is is obtained by a call to XLAddFormat |
|
value |
String |
NumformatAccount |
account value: 5,000 |
|
|
|
NumformatAccountcur |
account value with currency symbol: $5,000 |
|
|
|
NumformatAccountD2 |
account value with decimal point: 5,000.00 |
|
|
|
NumformatAccountD2Cur |
account value with currency symbol and decimal point: $ 5,000.00 |
|
|
|
NumformatCurrencyD2Negbra |
monetary value with decimal point, negative in brackets: ($1000.00) |
|
|
|
NumformatCurrencyD2NegbraRed |
monetary value with decimal point, negative is red in brackets: ($1000.00) |
|
|
|
NumformatCurrencyNegbra |
monetary value, negative in brackets: (1000$) |
|
|
|
NumformatCurrencyNegbraRed |
monetary value, negative is red in brackets: (1000$) |
|
|
|
NumformatCustom000P0EPlus0 |
custom value: 15.2E+3 |
|
|
|
NumformatCustomDMon |
custom date value: 11-Mar |
|
|
|
NumformatCustomDMonYY |
custom date value: 11-Mar-09 |
|
|
|
NumformatCustomH0MMSS |
custom time value: 20:30:55 |
|
|
|
NumformatCustomHMM |
custom date value: 8:30 |
|
|
|
NumformatCustomHMMAM |
custom date value: 8:30 AM |
|
|
|
NumformatCustomHMMSS |
custom date value: 8:30:00 |
|
|
|
NumformatCustomHMMSSAM |
custom date value: 8:30:00 AM |
|
|
|
NumformatCustomMDYYYYHMM |
custom datetime value: 3/11/2009 8:30 |
|
|
|
NumformatCustoMMMSS |
custom time value: 30:55 |
|
|
|
NumformatCustoMMMSS0 |
custom time value: 30:55.0 |
|
|
|
NumformatCustomMonYY |
custom date value: Mar-09 |
|
|
|
NumformatDate |
date value, depends on OS settings: 3/11/2009 |
|
|
|
NumformatFractionOneDig |
fraction value, one digit: 10 1/2 |
|
|
|
NumformatFractionTwoDig |
fraction value, two digits: 10 23/95 |
|
|
|
NumformatGeneral |
general format |
|
|
|
NumformatNumber |
general number: 1000 |
|
|
|
NumformatNumberD2 |
number with decimal point: 1000.00 |
|
|
|
NumformatNumberD2SepNegbra |
number with thousands separator and decimal point, negative in brackets: (4,000.00) |
|
|
|
NumformatNumberD2SepNegbraRed |
number with thousands separator and decimal point, negative is red in brackets: (4,000.00) |
|
|
|
NumformatNumberSep |
number with thousands separator: 100,000 |
|
|
|
NumformatNumberSepD2 |
number with decimal point and thousands separator: 100,000.00 |
|
|
|
NumformatNumberSepNegbra |
number with thousands separator, negative in brackets: (4,000) |
|
|
|
NumformatNumberSepNegbraRed |
number with thousands separator, negative is red in brackets: (4,000) |
|
|
|
NumformatPercent |
percent value, multiply the cell value by 100: 75% |
|
|
|
NumformatPercentD2 |
percent value with decimal point, multiply the cell value by 100: 75.00% |
|
|
|
NumformatScientificD2 |
scientific value with E character and decimal point: 10.00E+1 |
|
|
|
NumformatText |
text value |
Available in:
|
WindowMain |
WindowLink |
|
YES |
NO |
Used in:
|
XojoScript Name |
GUID |
|
Save as Excel sheet : seminar invoice list |
6DDAFA87-2862-4129-8416-CBD57C5CDF9A |
|
Save as Excel sheet : seminar participants list |
03C22A3D-FF28-4E22-BB99-D7626BF5F0A4 |
|
Save As Excel sheet : participants list |
E50602AA-F1F7-42E6-A44A-78456DD9B778 |
Hint: You can find more references to XojoScripts which make use of this function by sending the following SQL statement to the database: Select id,GUID, ScriptName,ScriptCode from im_scripts where ScriptCode Like '%XLFormatSetNumFormat%'
Example XojoScript:
Dim hXL As Integer
// Get Handle to newly created Excel file
hXL = XLCreateFromTemplate("myXLtest.xls")
// Set default font for Excel sheet
XLSetDefaultFont(hXL,"Arial Narrow",8)
Dim listfont As Integer = XLAddFont(hXL,"Arial Narrow",8)
XLFontSetColor(hXL,listfont,"&h00FF0000") // red
Dim listformat As Integer = XLAddFormat(hXL)
Dim numberformat As Integer = XLAddFormat(hXL)
// Add font to formats
XLFormatSetFont(hXL,listformat,listfont)
XLFormatSetFont(hXL,numberformat,listfont)
// Set properties of listformat
XLFormatSetBorderBottom(hXL,listformat,"Hair")
// Set properties of numberformat
XLFormatSetAlign(hXL,numberformat,"AlignH","AlignHRight")
XLFormatSetNumFormat(hXL,numberformat,"NumformatNumberD2SepNegbraRed")
XLFormatSetBorderBottom(hXL,numberformat,"Hair")
If hXL > -1 Then
Dim i As integer
For i = 2 To 100
XLWriteString(hXL,i,0,"Zeile " + Str(i),listformat)
XLWriteNumber(hXL,i,1,i,numberformat)
XLWriteBoolean(hXL,i,2,i)
Next
XLSave(hXL)
XL(hXL,"launch")
End If