Managing Sheet with APIs

SheetAPI.loadMoreData



SheetAPI.loadMoreData method is used to load more Json data into sheet. This method should be combined with SheetAPI.loadData method during load big data into system in order to improve performance. This method only can load cell data.

loadMoreData( hd, json )

Parameters The handle information of the Sheet application your created.

The json data to be loaded into the sheet. loadData json object includes only one name: cells.

NameProperty ExampleDescription
Cell objectcells cells: [{},{}] Set defined cell information for each tab.

For load more data into file, the following lists the general structure

    One SheetAPI.loadData:
        -> fileName
        -> floatings 
            -> rowgroup
            -> merge
            ...
        -> sheets
        -> cells
    
    Many SheetAPI.loadMoreData:
        -> cells
    

Example code


var loadingMask = new Ext.LoadMask(Ext.getBody(), {msg: SLANG['waiting']});
loadingMask.show();

var callback = function(){
	loadingMask.hide();	
};

var xrow = 5000;

var cells = [], count = 0, addFlag = false;
for(var i = 1; i <= xrow; i++){
	for(var j = 1; j <= 10; j++){
		var data = i*j;	
		cells.push({sheet:1, row:i, col:j, json:{data: data}});			    
		count++;
		
		if (count > 3000) {
			if (!addFlag) {
				SHEET_API.loadData(SHEET_API_HD, {
					fileName: "Example Sheet",
					sheets: [{ name: "50000", id: 1}],
					cells: cells
				}, callback, this);
				addFlag = true;
			} else {
				SHEET_API.loadMoreData(SHEET_API_HD, {
					cells: cells
				}, callback, this);
			}
			cells = [];
			count = 0;
		}
	}
}

if (count > 0) {
	if (!addFlag) {
		SHEET_API.loadData(SHEET_API_HD, {
			fileName: titleTxt,
			sheets: [{ name: tabName, id: 1}],
			cells: cells
		}, callback, this);
	} else {
		SHEET_API.loadMoreData(SHEET_API_HD, {
			cells: cells
		}, callback, this);
	}
	cells = [];
}
    

Above method will create a sheet file with file name as "Example Sheet", this sheet includes one tab: 50000.

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.