¶ 1. Cell Content
¶ 1.1 Setting Content
Use the activeSheet.SetCellValue method to set content in the active sheet as shown below.
let yourElement = document.getElementById("yourElement");
let wsheet = new websheet('HTML', yourElement);
/**
* Step 1: Get active sheet
*/
let activeSheet = wsheet.ActiveSheet();
/**
* Step 2: Set cell values
*/
activeSheet.SetCellValue('C2','hello websheet!');
activeSheet.SetCellValue(3,3,'www.websheet.cn');
/**
* Step 3: Redraw sheet
*/
wsheet.BuildSheet();
wsheet.Draw();
Result will appear as:
For bulk updates, it's recommended to set batch update flag first. This will pause formula calculations.
activeSheet.bBathUpate=true;
activeSheet.SetCellValue('C2','hello websheet!');
activeSheet.SetCellValue(3,3,'www.websheet.cn');
.......//More value settings
activeSheet.bBathUpate=false;
activeSheet.WorkFormula(); //If new formulas were added
activeSheet.cacl(); //Calculate formulas
wsheet.BuildSheet();
wsheet.Draw();
¶ 1.2 Getting Content
Use activeSheet.GetCellValue method to get specified cell content. The method returns a cell object where cell.v contains the cell value.
let yourElement = document.getElementById("yourElement");
let wsheet = new websheet('HTML', yourElement);
/**
* Step 1: Get active sheet
*/
let activeSheet = wsheet.ActiveSheet();
/**
* Step 2: Set and get cell values
*/
activeSheet.SetCellValue('C2','hello websheet!');
let c2Cellvalue= activeSheet.GetCellValue('C2'); //Prints hello websheet!
console.log(c2Cellvalue.v);
activeSheet.SetCellValue(1,1,'Matchbox');
let c1Cellvalue= activeSheet.GetCellValue(1,1);
console.log(c1Cellvalue.v); //Prints "Matchbox"
/**
* Step 3: Redraw sheet
*/
wsheet.BuildSheet();
wsheet.Draw();
¶ 1.3 Active Cell Address
Get via activeSheet.ActiveRange()
let range=activeSheet.ActiveRange();
¶ 2. Fonts
Use websheet.Model.Font() to get font object for setting font-related properties, then use activeSheet.SetCellFont() to apply to cells.
Note 1: Default font is SimSun (宋体) at 14px.
Note 2: For other fonts, ensure they are properly installed in the browser.
¶ 2.1 Setting Font
Set font via Font's name property:
let newFontlsc = new websheet.Model.Font();
newFontlsc.name='LiSu';
activeSheet.SetCellFont('A1', newFontlsc);
¶ 2.2 Font Color
Set font color via Font's color property:
let newFontlsc = new websheet.Model.Font();
newFontlsc.color.rgb='red';
//Or
newFontlsc.color.rgb='#ff00ff';
activeSheet.SetCellFont('B2', newFontlsc);
¶ 2.3 Font Size
Set size in pixels via Font's sz property:
let newFontlsc = new websheet.Model.Font();
newFontls.sz=25;
activeSheet.SetCellFont(3,2, newFontlsc);
¶ 2.4 Default Font and Size
Set default font and size via Font static variables.
Must be set before using websheet.
websheet.Model.Font.defaultFontSize=20;
websheet.Model.Font.defaultFontName='SimHei';
¶ 2.5 Code Example
Complete implementation for the following result:
let yourElement = document.getElementById("yourElement");
let wsheet = new websheet('HTML', yourElement);
/**
* Step 1: Get active sheet and set values
*/
let activeSheet = wsheet.ActiveSheet();
activeSheet.SetCellValue('A1','Default Font');
activeSheet.SetCellValue('A2','Display Font');
activeSheet.SetCellValue('A3','Reference Font');
activeSheet.SetCellValue('A4','Display Font');
activeSheet.SetCellValue('A5','Reference Font');
activeSheet.SetCellValue('B1','SimSun');
activeSheet.SetCellValue('B2','YaHei');
activeSheet.SetCellValue('B3','YaHei');
activeSheet.SetCellValue('B4','LiSu');
activeSheet.SetCellValue('B5','LiSu');
activeSheet.SetCellValue('C1','SimSun');
activeSheet.SetCellValue('C2','YaHei');
activeSheet.SetCellValue('C3','YaHei');
activeSheet.SetCellValue('C4','LiSu');
activeSheet.SetCellValue('C5','LiSu');
activeSheet.SetCellValue('D5',123123);
activeSheet.SetCellValue(4,4,'Row4 Col4');
/**
* Step 2: Create and apply font objects
*/
// YaHei in pink
let newFontYH = new websheet.Model.Font();
newFontYH.name='YaHei';
newFontYH.sz=25;
activeSheet.SetCellFont('B2', newFontYH);
let newFontYHc = new websheet.Model.Font();
newFontYHc.name='YaHei';
newFontYHc.sz=25;
newFontYHc.color.rgb='#ff00ff';
activeSheet.SetCellFont(2,3, newFontYHc);
// LiSu
let newFontls = new websheet.Model.Font();
newFontls.name='LiSu';
newFontls.sz=25;
activeSheet.SetCellFont('B4', newFontls);
let newFontlsc = new websheet.Model.Font();
newFontlsc.name='LiSu';
newFontlsc.sz=25;
newFontlsc.color.rgb='red';
activeSheet.SetCellFont('C4', newFontlsc);
// Default 25px size
let default25Size = new websheet.Model.Font();
default25Size.sz=25;
activeSheet.SetCellFont('B1', default25Size);
activeSheet.SetCellFont('C1', default25Size);
activeSheet.SetCellFont('B3', default25Size);
activeSheet.SetCellFont('C3', default25Size);
activeSheet.SetCellFont('B5', default25Size);
activeSheet.SetCellFont('C5', default25Size);
// Set row/column dimensions
activeSheet.setColWidth(1,160)
activeSheet.setColWidth(2,160)
activeSheet.setColWidth(3,160)
activeSheet.setColWidth(4,160)
activeSheet.setRowHeight(1,30);
activeSheet.setRowHeight(2,30);
activeSheet.setRowHeight(3,30);
activeSheet.setRowHeight(4,30);
activeSheet.setRowHeight(5,30);
/**
* Step 3: Redraw sheet
*/
wsheet.BuildSheet();
wsheet.Draw();
¶ 3. Cell Background
Note: Currently only solid colors are supported.
¶ 3.1 Fill Object
Use websheet.Model.Fill to set fill color, then apply with activeSheet.SetCellFill().
let redFill = new websheet.Model.Fill(); //Create fill object
redFill.setColor('#FF0000')
activeSheet.SetCellFill('C2',redFill); //Set C2 fill color
// Or
activeSheet.SetCellFill(2,3,redFill); //Set row 2 column 3 fill color
¶ 3.2 Code Example
Example setting C2 to red and row 2 column 5 to blue:
let yourElement = document.getElementById("yourElement");
let wsheet = new websheet('HTML', yourElement);
let activeSheet = wsheet.ActiveSheet();
activeSheet.SetCellValue('B2','Red background');
activeSheet.SetCellValue('D2','Blue color');
/**
* Step 1: Create fill objects
*/
let redFill = new websheet.Model.Fill();
redFill.setColor('#FF0000')
activeSheet.SetCellFill('C2',redFill);
let blueFill = new websheet.Model.Fill();
blueFill.setColor('blue')
activeSheet.SetCellFill(2,5,blueFill);
// Set column widths
activeSheet.setColWidth(2,160);
activeSheet.setColWidth(3,160);
activeSheet.setColWidth(4,160);
activeSheet.setColWidth(5,160);
/**
* Step 2: Redraw sheet
*/
wsheet.BuildSheet();
wsheet.Draw();
Result:
¶ 4. Cell Borders
Use websheet.Model.Border and BorderPr objects to configure borders, then apply them to cells using activeSheet.SetCellBorder().
¶ 4.1 Border Color
Set border color via BorderPr's color property:
let redBorderPr = new websheet.Model.BorderPr();
redBorderPr.color = new websheet.Model.Color('red');
// Or
redBorderPr.color = new websheet.Model.Color('#FF00FF');
¶ 4.2 Border Style
Set border style via BorderPr's borderStyle property:
let redBorderPr = new websheet.Model.BorderPr();
redBorderPr.borderStyle = 'thin';
Available border styles:
BorderStyle =
| 'none'
| 'thin'
| 'medium'
| 'dashed'
| 'dotted'
| 'thick'
| 'double'
| 'hair'
| 'mediumDashed'
| 'dashDot'
| 'mediumDashDot'
| 'dashDotDot'
| 'mediumDashDotDot'
| 'slantDashDot';
¶ 4.3 Cell Borders
Border has four main properties corresponding to cell edges:
start: BorderPr; // Left border
end: BorderPr; // Right border
top: BorderPr; // Top border
bottom: BorderPr; // Bottom border
Example setting red 'thin' left border for cell C2:
let redBorderPr = new websheet.Model.BorderPr();
redBorderPr.borderStyle = 'thin';
redBorderPr.color = new Color('red');
let C2Border = new websheet.Model.Border();
C2Border.start = redBorderPr;
activeSheet.SetCellBorder('C2', C2Border);
Alternatively, set all borders at once:
b2Border.SetAllBorder(redBorderPr);
activeSheet.SetCellBorder('C2', b2Border);
¶ 4.4 Code Example
let wsheet = new websheet('myofdID', yourElement);
/**
* Step 1: Get active sheet
*/
let activeSheet = wsheet.ActiveSheet();
/**
* Step 2: Configure border styles and colors
*/
let b1Border = new websheet.Model.Border();
activeSheet.SetCellValue('A1','thin');
let redthinBorderPr = new websheet.Model.BorderPr();
redthinBorderPr.borderStyle = 'thin';
redthinBorderPr.color = new websheet.Model.Color('red');
b1Border.SetAllBorder(redthinBorderPr);
activeSheet.SetCellBorder('B1',b1Border);
let d1Border = new websheet.Model.Border();
activeSheet.SetCellValue('C1','medium');
let redmediumBorderPr = new websheet.Model.BorderPr();
redmediumBorderPr.borderStyle = 'medium';
redmediumBorderPr.color = new websheet.Model.Color('blue');
d1Border.SetAllBorder(redmediumBorderPr);
activeSheet.SetCellBorder('D1',d1Border);
let e1Border = new websheet.Model.Border();
activeSheet.SetCellValue('E1','dashed');
let reddashedBorderPr = new websheet.Model.BorderPr();
reddashedBorderPr.borderStyle = 'dashed';
reddashedBorderPr.color = new websheet.Model.Color('green');
e1Border.SetAllBorder(reddashedBorderPr);
activeSheet.SetCellBorder('F1',e1Border);
// Set column widths
activeSheet.setColWidth(1,160);
activeSheet.setColWidth(2,160);
activeSheet.setColWidth(3,160);
activeSheet.setColWidth(4,160);
activeSheet.setColWidth(5,160);
activeSheet.setColWidth(6,160);
/**
* Step 3: Redraw sheet
*/
wsheet.BuildSheet();
wsheet.Draw();
Result:
¶ 5. Alignment
¶ 5.1 Alignment Object
CellAlignment class configures alignment using horizontal and vertical properties.
let wsheet = new websheet('myofdID', yourElement);
let activeSheet = wsheet.ActiveSheet();
let cellAlignment = new websheet.Model.CellAlignment();
¶ 5.2 Horizontal Alignment
Set horizontal alignment via vertical property:
| Value | Description |
|---|---|
| left | Left-aligned |
| center | Center-aligned |
| right | Right-aligned |
let leftTop = new websheet.Model.CellAlignment();
leftTop.vertical = 'top'; // Horizontal alignment
activeSheet.SetCellAlignment('B2', leftTop);
¶ 5.3 Vertical Alignment
Set vertical alignment via horizontal property:
| Value | Description |
|---|---|
| top | Top-aligned |
| center | Center-aligned |
| bottom | Bottom-aligned |
let leftTop = new websheet.Model.CellAlignment();
leftTop.horizontal = 'left'; // Vertical alignment
activeSheet.SetCellAlignment('B2', leftTop);
¶ 5.4 Code Example
let wsheet = new websheet('myofdID', yourElement);
/**
* Step 1: Get active sheet and set values
*/
let activeSheet = wsheet.ActiveSheet();
activeSheet.SetCellValue('B2','leftTop');
activeSheet.SetCellValue('C2','leftCenter');
activeSheet.SetCellValue('D2','leftBottom');
activeSheet.SetCellValue('B3','CenterTop');
activeSheet.SetCellValue('C3','CenterCenter');
activeSheet.SetCellValue('D3','CenterBottom');
activeSheet.SetCellValue('B4','rightTop');
activeSheet.SetCellValue('C4','rightCenter');
activeSheet.SetCellValue('D4','rightBottom');
/**
* Step 2: Configure alignments
*/
let leftTop = new websheet.Model.CellAlignment();
leftTop.horizontal = 'left'; // Vertical
leftTop.vertical = 'top'; // Horizontal
activeSheet.SetCellAlignment('B2', leftTop);
let leftCenter = new websheet.Model.CellAlignment();
leftCenter.horizontal = 'left';
leftCenter.vertical = 'center';
activeSheet.SetCellAlignment('C2', leftCenter);
let leftbottom = new websheet.Model.CellAlignment();
leftbottom.horizontal = 'left';
leftbottom.vertical = 'bottom';
activeSheet.SetCellAlignment('D2', leftbottom);
let centerTop = new websheet.Model.CellAlignment();
centerTop.horizontal = 'center';
centerTop.vertical = 'top';
activeSheet.SetCellAlignment('B3', centerTop);
let centeCenter = new websheet.Model.CellAlignment();
centeCenter.horizontal = 'center';
centeCenter.vertical = 'center';
activeSheet.SetCellAlignment('C3', centeCenter);
let centerbottom = new websheet.Model.CellAlignment();
centerbottom.horizontal = 'center';
centerbottom.vertical = 'bottom';
activeSheet.SetCellAlignment('D3', centerbottom);
let rightTop = new websheet.Model.CellAlignment();
rightTop.horizontal = 'right';
rightTop.vertical = 'top';
activeSheet.SetCellAlignment('B4', rightTop);
let rightCenter = new websheet.Model.CellAlignment();
rightCenter.horizontal = 'right';
rightCenter.vertical = 'center';
activeSheet.SetCellAlignment('C4', rightCenter);
let rightbottom = new websheet.Model.CellAlignment();
rightbottom.horizontal = 'right';
rightbottom.vertical = 'bottom';
activeSheet.SetCellAlignment('D4', rightbottom);
// Set row heights and column widths
activeSheet.setRowHeight(4,50);
activeSheet.setRowHeight(2,50);
activeSheet.setRowHeight(3,50);
activeSheet.setColWidth(1,160);
activeSheet.setColWidth(2,160);
activeSheet.setColWidth(3,160);
activeSheet.setColWidth(4,160);
activeSheet.setColWidth(5,160);
activeSheet.setColWidth(6,160);
/**
* Step 3: Redraw sheet
*/
wsheet.BuildSheet();
wsheet.Draw();
Result:
¶ 5.5 Additional References
¶ 6. Cell Navigation
This feature directly navigates to a specific cell, scrolling if necessary. For formula cells, it activates the formula for viewing:
let activeSheet = wsheet.ActiveSheet();
activeSheet.SetCellValue(1, 3, '=SUM(C2:E20)');
// activeSheet.setActiveRange('C1');
activeSheet.SetCellValue('S51', '=SUM(S40:W50)');
activeSheet.SetMoveToActiveRange('S51');
¶ 7. Merge Cells
The code for merging cells is as follows:
let activeSheet = wsheet.ActiveSheet();
activeSheet.SetCellValue('A1', '9999');
activeSheet.AddoneMergeCell('A1:E1');