Hi Please help in loading data to handsontable sreadsheet I am trying to add like this getting "#bad Value#" in column .
$("#benefitDT").handsontable({
data: benefitData(),
// colWidths: [55, 127, 127, 147, 157, 147, 147, 147],
rowHeaders: true,
colHeaders: true,
colHeaders: ["Name", "Yes/No", "Add/Nil", "Nil/Multiple", "Field Name", "Value", "Information"],
stretchH: 'all',
startRows: 5,
fillHandle: true,
columns: [
{
readOnly:true
},
{
type: 'checkbox',
checkedTemplate: 'yes',
uncheckedTemplate: 'no'
// source: ["Nil", "Add"]
},
{
type: 'dropdown',
source: ["Nil", "Add"]
},
{
type: 'dropdown',
source: ["Nil", "Multiple"]
},
{
type: 'dropdown',
source: ["Select", "No of Passenger", "No Of Cylinders", "No Of Doors"]
},
{
type: 'numeric',
format: '$ 0,0[.]00',
language: 'en' //this is the default locale, set up for USD
},
{
}
],
minSpareRows: 1
});
I am trying to get the data from web service and creating a array object and returning the same
function benefitData() {
var obj = new Array();
var ncbData = [];
XXXXXXXX_apps.GetBenefits(function (arg) {
if (arg.length > 0) {
for (var i=1; i < arg.length - 1; i++) {
var temp = [arg[i].split("|")[2], "yes", "Nil", "Nil", "Select", 12, 13];
}
}
}
return obj;
}
Get the data in json format
Public Function GetRates(ByVal ratesheetID As String) As String
Dim alObj As New ArrayList()
Dim dt As DataSet = xxxxxxxxx.GetAllRates(ratesheetID)
Dim ratelist As New List(Of Rates)
For Each dr As DataRow In dt.Tables(0).Rows
Dim rate As New Rates()
rate.YEAR = dr("YEAR")
rate.AGENCYRATE = dr("AGENCYRATE")
rate.AGENCYLOADING = dr("AGENCYLOADING")
rate.AGENCYMINIPREMIUM = dr("AGENCYMINIPREMIUM")
rate.NONAGENCYRATE = dr("NONAGENCYRATE")
rate.NONAGENCYLOADING = dr("NONAGENCYLOADING")
rate.NONAGENCYMINPREMIUM = dr("NONAGENCYMINPREMIUM")
rate.EXCESS = dr("EXCESS")
ratelist.Add(rate)
Next
Dim jss As New JavaScriptSerializer()
Dim _JsonString = jss.Serialize(ratelist)
Return _JsonString
End Function
Using jquery page on load get the data into json format setup your column style s per your data types and controls in spreadsheet. After setting the spreadsheet setting according to your data load the data.
function GenerateRateSheet() {
var obj = new Array();
var ncbData = [];
loading("start");
xxxxxxxxxxxxxx.GetRates($("#hdRID").val(), function (obj1) {
debugger;
var arg = $.parseJSON(obj1);
if (arg.length > 0) {
debugger;
$("#rateDT").handsontable({
// data: obj,
colHeaders: true,
colHeaders: ["YEAR", "Agency Rate (%)", "Agency Loading", "Agency MP", "Non Agency Rate (%)", "Non Agency Loading", "Non Agency MP", "Excess/Deductible"],
currentRowClassName: 'currentRow',
currentColClassName: 'currentCol',
colWidths: [100, 150, 110, 100, 175, 160, 130, 180],
startRows: 6,
startCols: 8,
contextMenu: { items: { 'row_above': {}, 'row_below': {}, 'remove_row': {}, 'undo': {}, 'redo': {}} },
columnSorting: {
column: 1,
sortOrder: true
},
persistentState: true,
columns: [
{
data: "YEAR",
allowInvalid: false,
type: 'numeric'
//readOnly: true
},
{ data: "AGENCYRATE",
type: 'numeric',
format: '0.00%',
allowInvalid: false,
language: 'en' //this is the default locale, set up for USD
}
,
{ data: "AGENCYLOADING",
type: 'numeric',
format: '0,0[.]00',
allowInvalid: false,
language: 'en' //i18n: use this for EUR (German)
//more locales available on numeraljs.com
},
{ data: "AGENCYMINIPREMIUM",
// data: "Agency MP",
type: 'numeric',
format: '0,0[.]00',
allowInvalid: false,
language: 'en' //this is the default locale, set up for USD
},
{ data: "NONAGENCYRATE",
// data: "Non Agency Rate (%)",
type: 'numeric',
format: '0.00%',
allowInvalid: false,
language: 'en' //i18n: use this for EUR (German)
//more locales available on numeraljs.com
},
{ data: "NONAGENCYLOADING",
// data: "Non Agency Loading",
type: 'numeric',
format: '0,0[.]00',
allowInvalid: false,
language: 'en' //this is the default locale, set up for USD
},
{ data: "NONAGENCYMINPREMIUM",
// data: "Non Agency MP",
type: 'numeric',
format: '0,0[.]00',
allowInvalid: false,
language: 'en' //i18n: use this for EUR (German)
//more locales available on numeraljs.com
},
{
data: "EXCESS",
type: 'numeric',
format: '0,0[.]00',
allowInvalid: false,
language: 'en' //this is the default locale, set up for USD
}
]
});
$("#rateDT").handsontable("loadData", arg);
loading("end");
}
},
function (arg) {
$("#divResult").html('<div class="alert"><button class="close" data-dismiss="alert">×</button><strong>Warning!</strong> Internal Server Error.</div>');
});
}
//Hope it will help if not then try to break the solution i small piece and then try.because of lack of time i putted every thing.