spreadJsの使用例
20939 ワード
spreadJs-demo
var param=window.location.search;
var spreadNS = GC.Spread.Sheets;
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'));
var activeSheet = spread.getSheet(0);
// sheet
spread.options.newTabVisible=false;
var columnNums=0;
window.onload = function() {
$.ajax({
type: "POST",
url: "http://localhost:8080/getData.do"+param+"&r="+ new Date().getTime(),
async: false,
success: function(data){
var obj=data.Data;
var header=data.Header
columnNums=data.columnNum;
activeSheet.suspendPaint();
//
activeSheet.setRowCount(2, GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 0, " ", GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.addSpan(0,1,1,2,GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 1, header[0], GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 3, " ", GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 4, header[1], GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 5, " ", GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 6, header[2], GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 7, " ", GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 8, header[3], GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 9, " ", GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setValue(0, 10, header[4], GC.Spread.Sheets.SheetArea.colHeader);
// activeSheet.setValue(0, 11, " ", GC.Spread.Sheets.SheetArea.colHeader);
var cellType = new GC.Spread.Sheets.CellTypes.Button();
cellType.buttonBackColor("#FF0000");
cellType.text(" ");
activeSheet.getCell(0, 11,GC.Spread.Sheets.SheetArea.colHeader).cellType(cellType);
activeSheet.addSpan(0, 12, 1, 65, GC.Spread.Sheets.SheetArea.colHeader);
var defaultStyle = new GC.Spread.Sheets.Style();
defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
activeSheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
//Change the column header height.
activeSheet.setRowHeight(0, 60.0,GC.Spread.Sheets.SheetArea.colHeader);
activeSheet.setRowHeight(1, 30.0,GC.Spread.Sheets.SheetArea.colHeader);
//Change the row header width.
// activeSheet.setColumnWidth(1, 150.0,GC.Spread.Sheets.SheetArea.colHeader);
// activeSheet.addSpan(0,);
// activeSheet.setRowHeight(1, 50);
// activeSheet.setColumnWidth(0, 150);
activeSheet.autoGenerateColumns = true;
activeSheet.options.allowCellOverflow = true;
activeSheet.setDataSource(obj);
activeSheet.defaults.rowHeight = 50;
// activeSheet.defaults.columnWidth = 100;
activeSheet.getRange(-1, 1, -1, columnNums-1, GC.Spread.Sheets.SheetArea.viewport).width(120);
// activeSheet.getRange(1, 1, 1, 3).backColor("rgb(20, 140, 1218)");
var row = activeSheet.getRange(1, -1, 1, -1, spreadNS.SheetArea.colHeader);
row.backColor("yellow");
activeSheet.autoFitColumn;
// activeSheet.autoFitRow
// yanse
for(var i=0;i<obj.length;i++){
if(obj[i]. ==' '){
activeSheet.getRange(i, -1, 1, -1, spreadNS.SheetArea.viewport).backColor("Pink");
}
}
//
var instance = new GC.Spread.Sheets.CellRange(activeSheet, -1, columnNums, -1, 3, spreadNS.SheetArea.viewport);
instance.visible(false);
//
activeSheet.autoFitColumn(0);
// activeSheet.autoFitColumn(1);
activeSheet.autoFitColumn(4);
activeSheet.setColumnWidth(5, 240.0,GC.Spread.Sheets.SheetArea.colHeader);
// activeSheet.getRange(0, -1, obj.length, -1, GC.Spread.Sheets.SheetArea.viewport).height(150);
/* var instance = new GC.Spread.Sheets.CellRange(activeSheet, 0, -1, obj.length, -1, GC.Spread.Sheets.SheetArea.viewport);
instance.height(100); */
for(var i=0;i<obj.length;i++){
var text=activeSheet.getCell(i,5).value();
if(!(typeof text == "undefined" || text == null || text == "" || text.length<30)){
activeSheet.getCell(i,5).wordWrap(true);
activeSheet.autoFitRow(i);
}
}
// activeSheet.setColumnWidth(5, 240.0,GC.Spread.Sheets.SheetArea.colHeader);
//
var filter = new spreadNS.Filter.HideRowFilter(new spreadNS.Range(-1, columnNums-4, -1, 4));
activeSheet.rowFilter(filter);
filter.filterButtonVisible(true);
//
var style = new GC.Spread.Sheets.Style();
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
style.wordWrap = true;
activeSheet.setStyle(-1,-1,style,GC.Spread.Sheets.SheetArea.viewport);
//
activeSheet.getRange(-1, -1, -1, -1).setBorder(new spreadNS.LineBorder('black', spreadNS.LineStyle.thin), { all: true });
spread.sheets[0].name(header[0]);
//
spread.bind(spreadNS.Events.ValueChanged, function (e, args) {
console.log(args);
if(args.col==5){
// alert(args.newValue);
if(confirm(" ？")){
alert(activeSheet.getValue(1,columnNums+1));
var viewId=activeSheet.getValue(args.row,columnNums);
var userId=activeSheet.getValue(args.row,columnNums+1);
var msg=args.newValue;
submitMessage_bgg(args.newValue,userId,viewId)
$.ajax({
type: "POST",
url: "url",
dataType: "json",
data: "userId=" + userId+"&remark="+msg+"&viewId="+viewId,
async: false,
success: function(data) {
var result = data.result;
if(result == 1){
alert(" ");
activeSheet.getCell(args.row,args.col).wordWrap(true);
activeSheet.autoFitRow(args.row);
}else{
alert(" ")
}
}
});
}
}
});
activeSheet.resumePaint();
}
});
}
$(function(){
//
function CustomFilter(){
GC.Spread.Sheets.ConditionalFormatting.Condition.apply(this, arguments);
};
CustomFilter.prototype = new GC.Spread.Sheets.ConditionalFormatting.Condition();
CustomFilter.prototype.evaluate = function (evaluator, row, col) {
var value = evaluator.getValue(row, col);
if (value !== '' && isNumber(value.trim())) {
return true;
} else {
return false;
}
};
document.getElementById('searchBtn').onclick = function() {
var sheet = spread.getActiveSheet();
var searchCondition = getSearchCondition();
var searchResult = null;
var sels = sheet.getSelections();
if (sels.length > 1) {
searchCondition.searchFlags |= spreadNS.Search.SearchFlags.blockRange;
} else if (sels.length == 1) {
var spanInfo = getSpanInfo(sheet, sels[0].row, sels[0].col);
if (sels[0].rowCount != spanInfo.rowSpan && sels[0].colCount != spanInfo.colSpan) {
searchCondition.searchFlags |= spreadNS.Search.SearchFlags.blockRange;
}
}
searchResult = getResultSearchinSheetEnd(searchCondition);
if (searchResult == null || searchResult.searchFoundFlag == spreadNS.Search.SearchFoundFlags.none) {
searchResult = getResultSearchinSheetBefore(searchCondition);
}
if (searchResult != null && searchResult.searchFoundFlag != spreadNS.Search.SearchFoundFlags.none) {
spread.setActiveSheetIndex(searchResult.foundSheetIndex);
var sheet = spread.getActiveSheet();
sheet.setActiveCell(searchResult.foundRowIndex, searchResult.foundColumnIndex);
if ((searchCondition.searchFlags & spreadNS.Search.SearchFlags.blockRange) == 0) {
sheet.setActiveCell(searchResult.foundRowIndex, searchResult.foundColumnIndex, 1, 1);
}
//scrolling
if (searchResult.foundRowIndex < sheet.getViewportTopRow(1) ||
searchResult.foundRowIndex > sheet.getViewportBottomRow(1) ||
searchResult.foundColumnIndex < sheet.getViewportLeftColumn(1) ||
searchResult.foundColumnIndex > sheet.getViewportRightColumn(1)
) {
sheet.showCell(searchResult.foundRowIndex,
searchResult.foundColumnIndex,
spreadNS.VerticalPosition.center,
spreadNS.HorizontalPosition.center);
} else {
sheet.repaint();
}
} else {
//Not Found
alert('Not Found');
}
};
//
$("#viewType").change(function(){
var sheet = spread.getActiveSheet(),
filter = sheet.rowFilter(),
value = $("#viewType").val();
col = columnNums-1;
if (filter) {
if(''==value){
value="*";
}
filter.removeFilterItems(col);
var condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: spreadNS.ConditionalFormatting.TextCompareType.contains,
expected: value
});
filter.addFilterItem(col, condition);
filter.filter();
sheet.invalidateLayout();
sheet.repaint();
}
});
//
$("#monthCE").change(function(){
var sheet = spread.getActiveSheet(),
filter = sheet.rowFilter(),
value = $("#monthCE").val();
col = columnNums-4;
if (filter) {
if(''==value){
value="*";
}else if("Y"==value){
value=' ';
}
filter.removeFilterItems(col);
var condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: spreadNS.ConditionalFormatting.TextCompareType.contains,
expected: value
});
if('N'==value){
var condition1 = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: spreadNS.ConditionalFormatting.TextCompareType.doesNotContain,
expected: ' '
});
var condition2 = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: spreadNS.ConditionalFormatting.TextCompareType.notEqualsTo,
expected: ''
});
var conditions=getComplexCondition(condition1,condition2);
filter.addFilterItem(col, conditions);
}else{
filter.addFilterItem(col, condition);
}
filter.filter();
sheet.invalidateLayout();
sheet.repaint();
}
});
//
$("#hisCompletedMonth").change(function(){
var sheet = spread.getActiveSheet(),
filter = sheet.rowFilter(),
value = $("#hisCompletedMonth").val();
col = columnNums-3;
if (filter) {
if(''==value){
value="*";
}else if('1 '==value){
value='/1 ';
}
filter.removeFilterItems(col);
var condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: spreadNS.ConditionalFormatting.TextCompareType.contains,
expected: value
});
if('x '==value){
var condition1 = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: spreadNS.ConditionalFormatting.TextCompareType.doesNotContain,
expected: '/1 '
});
var condition2 = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: spreadNS.ConditionalFormatting.TextCompareType.notEqualsTo,
expected: ''//null
});
/* var condition2 = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.formulaCondition,{
compareType: GC.Spread.Sheets.ConditionalFormatting.CustomValueType.nonEmpty
});*/
var conditions=getComplexCondition(condition1,condition2);
filter.addFilterItem(col, conditions);
}else{
filter.addFilterItem(col, condition);
}
filter.filter();
sheet.invalidateLayout();
sheet.repaint();
}
});
//
$("#hasCompletedMonthPlan").change(function(){
var sheet = spread.getActiveSheet(),
filter = sheet.rowFilter(),
value = $("#hasCompletedMonthPlan").val();
col = columnNums-2;
if (filter) {
if(''==value){
value="*";
}
filter.removeFilterItems(col);
var condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: spreadNS.ConditionalFormatting.TextCompareType.contains,
expected: value
});
filter.addFilterItem(col, condition);
filter.filter();
sheet.invalidateLayout();
sheet.repaint();
}
});
});
function getComplexCondition(condition1,condition2){
var conditions = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.relationCondition, {
compareType: spreadNS.ConditionalFormatting.LogicalOperators.and,
item1: condition1,
item2: condition2
});
return conditions;
}
function getSpanInfo(sheet, row, col) {
var span = sheet.getSpans(new spreadNS.Range(row, col, 1, 1));
if (span.length > 0) {
return {
rowSpan : span[0].rowCount,
colSpan : span[0].colCount
};
} else {
return {
rowSpan : 1,
colSpan : 1
};
}
}
function getResultSearchinSheetEnd(searchCondition) {
var sheet = spread.getActiveSheet();
searchCondition.startSheetIndex = spread.getActiveSheetIndex();
searchCondition.endSheetIndex = spread.getActiveSheetIndex();
if (searchCondition.searchOrder == spreadNS.Search.SearchOrder.zOrder) {
searchCondition.findBeginRow = sheet.getActiveRowIndex();
searchCondition.findBeginColumn = sheet.getActiveColumnIndex() + 1;
} else if (searchCondition.searchOrder == spreadNS.Search.SearchOrder.nOrder) {
searchCondition.findBeginRow = sheet.getActiveRowIndex() + 1;
searchCondition.findBeginColumn = sheet.getActiveColumnIndex();
}
if ((searchCondition.searchFlags & spreadNS.Search.SearchFlags.blockRange) > 0) {
var sel = sheet.getSelections()[0];
searchCondition.rowStart = sel.row;
searchCondition.columnStart = sel.col;
searchCondition.rowEnd = sel.row + sel.rowCount - 1;
searchCondition.columnEnd = sel.col + sel.colCount - 1;
}
var searchResult = spread.search(searchCondition);
return searchResult;
}
function getResultSearchinSheetBefore(searchCondition) {
var sheet = spread.getActiveSheet();
searchCondition.startSheetIndex = spread.getActiveSheetIndex();
searchCondition.endSheetIndex = spread.getActiveSheetIndex();
if ((searchCondition.searchFlags & spreadNS.Search.SearchFlags.blockRange) > 0) {
var sel = sheet.getSelections()[0];
searchCondition.rowStart = sel.row;
searchCondition.columnStart = sel.col;
searchCondition.findBeginRow = sel.row;
searchCondition.findBeginColumn = sel.col;
searchCondition.rowEnd = sel.row + sel.rowCount - 1;
searchCondition.columnEnd = sel.col + sel.colCount - 1;
} else {
searchCondition.rowStart = -1;
searchCondition.columnStart = -1;
searchCondition.findBeginRow = -1;
searchCondition.findBeginColumn = -1;
searchCondition.rowEnd = sheet.getActiveRowIndex();
searchCondition.columnEnd = sheet.getActiveColumnIndex();
}
var searchResult = spread.search(searchCondition);
return searchResult;
}
function getResultSearchinWorkbookEnd(searchCondition) {
searchCondition.rowStart = -1;
searchCondition.columnStart = -1;
searchCondition.findBeginRow = -1;
searchCondition.findBeginColumn = -1;
searchCondition.rowEnd = -1;
searchCondition.columnEnd = -1;
searchCondition.startSheetIndex = spread.getActiveSheetIndex() + 1;
searchCondition.endSheetIndex = -1;
var searchResult = spread.search(searchCondition);
return searchResult;
}
function getResultSearchinWorkbookBefore(searchCondition) {
searchCondition.rowStart = -1;
searchCondition.columnStart = -1;
searchCondition.findBeginRow = -1;
searchCondition.findBeginColumn = -1;
searchCondition.rowEnd = -1;
searchCondition.columnEnd = -1;
searchCondition.startSheetIndex = -1
searchCondition.endSheetIndex = spread.getActiveSheetIndex() - 1;
var searchResult = spread.search(searchCondition);
return searchResult;
}
function getSearchCondition(searchString) {
var searchCondition = new spreadNS.Search.SearchCondition();
var searchString=$("#inputValue").val();
searchCondition.searchString = searchString;
searchCondition.startSheetIndex = spread.getActiveSheetIndex();
searchCondition.endSheetIndex = spread.getActiveSheetIndex();
searchCondition.searchOrder = spreadNS.Search.SearchOrder.zOrder;
searchCondition.searchTarget = spreadNS.Search.SearchFoundFlags.cellText;
searchCondition.searchFlags |= spreadNS.Search.SearchFlags.ignoreCase;
return searchCondition;
}