I currently have a solution using AngularJS/SpreadJS where we need to update the header section with a formula. When we change a cell value in the header using setValue everythign displays ok, however we need to display a formula using setFormula, in these cases the formula gets calculated and displayed in the rows belonging to the actual sheet where my data is at.
//Does not work and displays in row 2 of the sheet:
sheet.setFormula(2, i, formula, GC.Spread.Sheets.SheetArea.colHeader);
//Displays value in actual header in teh correct location/header cell
sheet.setValue(2, i, 'my formula!', GC.Spread.Sheets.SheetArea.colHeader);
Any help will be appreciated. Thanks!
Implemented below solution to achieve the SUM formula in Column Header of the spread sheet.This function can be called on spread sheet events like cellChanged,clipBoardPasted to achieve functionality.
var spread = new GC.Spread.Sheets.Workbook($("#ss").get(0), { sheetCount: 2 });
var displaySheet = spread.getSheet(0);
displaySheet.setRowCount(2, GC.Spread.Sheets.SheetArea.colHeader);
for (var i = 0; i < 10; i++) {
for (var j = 0; j < 10; j++) {
displaySheet.setValue(i, j, i + j);
}
}
$("#btnSetAutoTotal").click(function () {
setTotal(displaySheet, 0);
});
function setTotal(sheet, columnIndex) {
var formula = "SUM(R[1]C[1]:R[10]C[10]";
value = GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, 'SUM(A:A)', 0,
columnIndex, false);
sheet.setValue(0, columnIndex, value, GC.Spread.Sheets.SheetArea.colHeader);
};