Managing Sheet with APIs

SheetAPI.loadData

SheetAPI.loadData method is used to load Json data into sheet. A new file can be created use this way. If you need load more than 5000 cells into file one time, please combine this method with loadMoreData method to improve performance.

loadData( hd, json, callback, scope )

Parameters The handle information of the Sheet application your created.

The json data to be loaded into the sheet. loadData json object includes at least 3 names: fileName, sheets and cells.

NameProperty ExampleDescription
File namefileName fileName: 'Example Sheet' Define the file name for this sheet file.
Sheet objectsheets sheets: [{},{}] Set defined sheet tabs information for this file.
Cell objectcells cells: [{},{}] Set defined cell information for each tab.
The callback function which will be called after the file is loaded. The scope for the callback function.

Example code


	SHEET_API.loadData(SHEET_API_HD, {
		fileName: 'Example Sheet',
		sheets: [{
			name: 'First tab',
			id: 1
		}, {
			name: 'Second tab',
			id: 2
		}],
		cells: [{
			sheet: 1,
			row: 1,
			col: 1,
			json: {data: 110}
		}]
	});

Above method will create a sheet file with file name as "Example Sheet", this sheet includes two tab: First tab and Second tab. And cell A1 data in the tab "First tab" is popped in number 110.

Json Format

The following table shows the detail json format which allows you to create your style sheet.

MethodExampleDescription
Create Sheet sheets: [{
 name: 'First tab',
 color: 'orange',
 id: 1
}] 		This code will create one tab with name 'First tab' and Orange color. Sheets is a Json arrays. Each object in the Json array represents one sheet tab. Sheet id need to be defined in each object which will be referred by cells and it needs to be unique.
Define column width cells: [{
 sheet: 1,
 row: 0,
 col: 1,
 json: {width: 152}
}] 		This json data defines the width of first column in sheet tab 1 as 152 pixels. In here, sheet value is referred by the sheets object and it needs to be one of id value defined in the sheet tab objects. If row value is set as 0, this means that the whole column defined by the col value will be format as json value.
Define whole row style cells: [{
 sheet: 1,
 row: 1,
 col: 0,
 json: {bgc: '#F79646', height: 28, color: '#EEECE1'}
}] 		This json data defines row 1 in sheet tab 1 background color as '#F79646', font color '#EEECE1' as and row height as 28px.

bgc means background color.
color means font color.
Define cell data style cells: [{
 sheet: 1,
 row: 1,
 col: 1,
 json: {data: "my data", bgc: '#F79646', color: '#EEECE1', fw: "bold", fs: "italic", u: "underline"}
}] 		This json data defines the content of cell A1 (row:1, col:1) in sheet tab 1 as "my data" and style as bold, italic, underline, the cell background color will be '#F79646' and font color is '#EEECE1'.

fw means font weight.
fs means font style.
u means underline.
Define cell data link cells: [{
 sheet: 1,
 row: 1,
 col: 1,
 json: {data: "my data", link: "www.abc.com"}
}] 		This json data defines cell A1 in sheet tab 1 content as "my data" and create a hyperlink "www.abc.com" to this cell".

link means hyperlink.
Define a formula with format in cell cells: [{
 sheet: 1,
 row: 1,
 col: 1,
 json: {data: '=today()', cal: true, fm: 'date', dfm: 'Y-m-d'}
}] 		This json data defines cell A1 in sheet tab 1 as date formula "=today()". And the result will be formatted as yyyy-mm-dd (Y-m-d). "cal: true" need to be added in the json array when this cell is a formula format and need to be calculated.

fm means format.
dfm means detail format.
Define cell data money format cells: [{
 sheet: 1,
 row: 1,
 col: 1,
 json: {data: "123.45", fm: "money|$|2|none"}
}] 		This json data defines cell A1 in sheet tab 1 as money format. It will add $ in the front and will be round to 2 decimals.

fm means format.
Define checkbox / radio cells: [{
 sheet: 1,
 row: 1,
 col: 1,
 json: {data: "Banana", it: "checkbox", itn: "fruit", itchk: false}
},{
 sheet: 1,
 row: 2,
 col: 1,
 json: {data: "Apple", it: "checkbox", itn: "fruit", itchk: true}
}] 		This json data defines 2 checkbox in A1 and A2 cells. And A2 content is "Apple" and it is checked.

Change the it value as "radio" will create radio buttons for those 2 cells.
Define drop down list cells: [{
 sheet: 1,
 row: 1,
 col: 1,
 json: {drop: Ext.encode({data: "Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"}), data: 'Monday'}
}] 		This json data defines cell A1 in tab 1 as a combobox and pre-filled data is "Monday".
Define template table cells: [{
 sheet: 1,
 row: 1,
 col: 1,
 json: {data: "450",tpl: '{id: "tpl_27", span: [1,1,1,1,2]}' }
},{
 sheet: 1,
 row: 2,
 col: 1,
 json: {data: "500", tpl: '{id: "tpl_27", span: [1,1,1,1,2]}' }
}] 		This json data will create a template table to the A1 and B1 cells.

You can select one of table templates from tpl_0 to tpl_59. Span parameters is:

tabId, x1, y1, x2, y2
Disable row, cell cells: [{
 sheet: 1,
 row: 0,
 col: 1,
 json: {dsd: "ed"}
},{
 sheet: 1,
 row: 3,
 col: 3,
 json: {data: "500", dsd: "ed" }
}] 		This json data will disable whole column A and C3 cell.

To see detail definition for the cell properties, please go to page: Cell Properties Definition

 

 

Copyright © FeyaSoft Inc. All rights reserved.