Formatting features are not yet fully developed. Currently, only some functions for currency and date formatting are supported. Multilingual support is not available yet.
¶ I. Currency Formatting
¶ 1.1 Currency
You can directly use the predefined formatting objects in the system to format cells. The access path is websheet.Model.NumFmts.*. The predefined formats are listed in Section 3.2. The following code uses the predefined format #,##0 to format currency.
// Predefined format for positive numbers
let numFmt3 = websheet.Model.NumFmts.numFmt3;
activeSheet.SetCellNumFmt('C1',numFmt3);
activeSheet.SetCellNumFmt(2,3,numFmt3);
If you have custom formatting requirements, you can declare your own formatting objects. The following code formats numbers with thousand separators and displays four decimal places, with negative numbers shown in red parentheses.
Note: Custom formatting object IDs should not conflict with predefined system IDs or IDs defined in the template.
// Custom format for four decimal places
let numFmt200 = new websheet.Model.NumFmt();
numFmt200.numFmtId=200;
numFmt200.formatCode='#,##0.0000_);[Red]\(#,##0.0000\)';
activeSheet.SetCellNumFmt('C5',numFmt200);
activeSheet.SetCellNumFmt(6,3,numFmt200);
¶ 1.2 Percentage
Similar to currency, you can directly use predefined formatting objects to format cells as percentages. The predefined formats are listed in Section 3.2. The following code uses the predefined format 0% to format percentages.
// Predefined format for percentage
let numFmt9 = websheet.Model.NumFmts.numFmt9;
activeSheet.SetCellNumFmt('C7',numFmt9);
If you have custom formatting requirements, you can declare your own formatting objects. The following code formats percentages with three decimal places.
Note: Custom formatting object IDs should not conflict with predefined system IDs or IDs defined in the template.
// Custom percentage format
let numFmt201 = new websheet.Model.NumFmt();
numFmt201.numFmtId=201;
numFmt201.formatCode='0.000%';
activeSheet.SetCellNumFmt(9,3,numFmt201);
¶ 1.3 Complete Code Example
The complete code for currency and percentage formatting is as follows:
let wsheet = new websheet('myofdID', yourElement);
/**
* Step 1: Get the active sheet
*/
let activeSheet = wsheet.ActiveSheet();
let r=1;
let anumber=123123123.1234
// Currency formatting
activeSheet.SetCellValue(r++,3,anumber);
activeSheet.SetCellValue(r++,3,-anumber);
activeSheet.SetCellValue(r++,3,anumber);
activeSheet.SetCellValue(r++,3,-anumber);
activeSheet.SetCellValue(r++,3,anumber);
activeSheet.SetCellValue(r++,3,-anumber);
// Percentage formatting
activeSheet.SetCellValue(r++,3,1.111);
activeSheet.SetCellValue(r++,3,1.111);
activeSheet.SetCellValue(r++,3,1.111);
/**
* Step 2: Currency formatting
*/
// Predefined format for positive numbers
let numFmt3 = websheet.Model.NumFmts.numFmt3;
activeSheet.SetCellNumFmt('C1',numFmt3);
activeSheet.SetCellNumFmt('C2',numFmt3);
// Predefined format for two decimal places
let numFmt40 = websheet.Model.NumFmts.numFmt40;
activeSheet.SetCellNumFmt('C3',numFmt40);
activeSheet.SetCellNumFmt('C4',numFmt40);
// Custom format for four decimal places
let numFmt200 = new websheet.Model.NumFmt();
numFmt200.numFmtId=200;
numFmt200.formatCode='#,##0.0000_);[Red]\(#,##0.0000\)';
activeSheet.SetCellNumFmt('C5',numFmt200);
activeSheet.SetCellNumFmt('C6',numFmt200);
// Predefined format for percentage
let numFmt9 = websheet.Model.NumFmts.numFmt9;
activeSheet.SetCellNumFmt('C7',numFmt9);
// Predefined format for percentage
let numFmt10 = websheet.Model.NumFmts.numFmt10;
activeSheet.SetCellNumFmt('C8',numFmt10);
// Custom percentage format
let numFmt201 = new websheet.Model.NumFmt();
numFmt201.numFmtId=201;
numFmt201.formatCode='0.000%';
activeSheet.SetCellNumFmt(9,3,numFmt201);
/**
* Step 3: Redraw the table
*/
activeSheet.setColWidth(3,160);
wsheet.BuildSheet();
wsheet.Draw();
The result is as follows:
¶ II. Date and Time Formatting
¶ 2.1 NOW Function
The NOW function in Excel is used to get the current date and time. To format dates and times, I will use this function. Using this function is simple; just enter =NOW() in the cell. In the following example, we set the NOW function for three rows in Column A.
activeSheet.SetCellValue(r++,1,'=NOW()');
activeSheet.SetCellValue(r++,1,'=NOW()');
activeSheet.SetCellValue(r++,1,'=NOW()');
¶ 2.2 Date Formatting
Formatting in the yyyy/M/d format:
// yyyy/M/d format
let numFmt14 = websheet.Model.NumFmts.numFmt14;
activeSheet.SetCellNumFmt('A1',numFmt14);
Formatting in the yyyy年m月d日 format:
// yyyy年m月d日 format
let numFmt31 = websheet.Model.NumFmts.numFmt31;
activeSheet.SetCellNumFmt('A2',numFmt31);
Custom formatting in the yyyy张m三d李 format:
// Custom format - yyyyAmBdC
let numFmt200 = new websheet.Model.NumFmt();
numFmt200.numFmtId=200;
numFmt200.formatCode='yyyy张m三d李 ';
activeSheet.SetCellNumFmt('A3',numFmt200);
¶ 2.3 Time Formatting
In the following examples, time is formatted in three ways: h时mm分ss秒, 上午/下午 h时mm分ss秒, and h:mm:ss AM/PM.
// h时mm分ss秒
let numFmt33 = websheet.Model.NumFmts.numFmt33;
activeSheet.SetCellNumFmt('A4',numFmt33);
// 上午/下午 h时mm分ss秒
let numFmt35 = websheet.Model.NumFmts.numFmt35;
activeSheet.SetCellNumFmt('A5',numFmt35);
// h:mm:ss AM/PM
let numFmt19 = websheet.Model.NumFmts.numFmt19;
activeSheet.SetCellNumFmt('A6',numFmt19);
¶ 2.4 Complete Code Example
let wsheet = new websheet('myofdID', yourElement);
/**
* Step 1: Get the active sheet
*/
let activeSheet = wsheet.ActiveSheet();
let r=1;
// Date formatting
activeSheet.SetCellValue(r++,1,'=NOW()');
activeSheet.SetCellValue(r++,1,'=NOW()');
activeSheet.SetCellValue(r++,1,'=NOW()');
// Time formatting
activeSheet.SetCellValue(r++,1,'=NOW()');
activeSheet.SetCellValue(r++,1,'=NOW()');
activeSheet.SetCellValue(r++,1,'=NOW()');
/**
* Step 2: Date formatting
*/
// yyyy/M/d format
let numFmt14 = websheet.Model.NumFmts.numFmt14;
activeSheet.SetCellNumFmt('A1',numFmt14);
// yyyy年m月d日 format
let numFmt31 = websheet.Model.NumFmts.numFmt31;
activeSheet.SetCellNumFmt('A2',numFmt31);
// Custom format - yyyyAmBdC
let numFmt200 = new websheet.Model.NumFmt();
numFmt200.numFmtId=200;
numFmt200.formatCode='yyyy张m三d李 ';
activeSheet.SetCellNumFmt('A3',numFmt200);
/**
* Step 2: Time formatting
*/
// h时mm分ss秒
let numFmt33 = websheet.Model.NumFmts.numFmt33;
activeSheet.SetCellNumFmt('A4',numFmt33);
// 上午/下午 h时mm分ss秒
let numFmt35 = websheet.Model.NumFmts.numFmt35;
activeSheet.SetCellNumFmt('A5',numFmt35);
// h:mm:ss AM/PM
let numFmt19 = websheet.Model.NumFmts.numFmt19;
activeSheet.SetCellNumFmt('A6',numFmt19);
/**
* Step 3: Redraw the table
*/
activeSheet.setColWidth(1,160);
activeSheet.WorkFormula(); // Rebuild formulas
activeSheet.cacl(); // Calculate formulas
wsheet.BuildSheet();
wsheet.Draw();
The result is as follows:
¶ III. NumFmt Object and Common Formats
Common format IDs are listed below.
| numFmtId | formatCode | Description |
|---|---|---|
| 1 | 0 | |
| 2 | 0.00 | |
| 3 | #,##0 | |
| 4 | #,##0.00 | |
| 9 | 0% | |
| 10 | 0.00% | |
| 11 | 0.00E+00 | |
| 14 | yyyy/mm/dd | |
| 15 | d-mmm-yy | |
| 16 | d-mmm | |
| 17 | mmm-yy | |
| 18 | h:mm AM/PM | |
| 19 | h:mm:ss AM/PM | |
| 20 | h:mm | |
| 21 | h:mm:ss | |
| 22 | m/d/yy h:mm | |
| 37 | h:mm:ss | |
| 38 | #,##0 ;[Red](#,##0) | |
| 39 | #,##0.00;(#,##0.00) | |
| 40 | #,##0.00;[Red](#,##0.00) | |
| 45 | mm:ss | |
| 48 | ##0.0E+0 | |
| 27 | yyyy年m月 | |
| 28 | m月d日 | |
| 29 | m月d日 | |
| 30 | m-d-yy | |
| 31 | yyyy年m月d日 | |
| 32 | h时mm分 | |
| 33 | h时mm分ss秒 | |
| 34 | 上午/下午 h时mm分 | |
| 35 | 上午/下午 h时mm分ss秒 |