陈建华的博客
专注web开发
SpreadJs常用操作js代码集(打开,字体颜色,对齐方式...)
2014-06-12 15:30:42   阅读11587次

下面列举了一些SpreadJs的常用操作的js代码

1.打开文件

$("#openFile").bind('change', function () {//绑定了一个input文件框的change事件
               var file = $("#openFile").get(0).files[0];
               if (file) {
                   var reader = new FileReader();//创建一个阅读器
                   reader.onprogress = function (evt) {//文件读取进度
                       if (evt.lengthComputable) {
                           var loaded = (evt.loaded / evt.total);
                           if (loaded < 1) {
                           }
                       }
                   };
                   reader.onload = function (evt) {//文件读取完成
                       // Obtain the read file data (string)
                       var fileString = evt.target.result;
                       // Handle UTF-8 file dump
                       var spread = $("#ss").wijspread("spread");
                       spread.isPaintSuspended(true);//打开paint
                       try {
                           if (!spread.id) spread.id = spread.name;
                           var dataField = document.getElementById(spread.id + "_data");
                           if (!dataField) {
                               dataField = document.createElement("input");
                               dataField.id = spread.id + "_data";
                               dataField.type = "hidden";
                               document.body.appendChild(dataField);
                           }
                           dataField.value = fileString;//读取结果传递
                           spread._loadData();//重新下载数据
                           //if (JSON && JSON.parse) {
                           //    var sd = JSON.parse(fileString);
                           //    spread.fromJSON(sd);
                           //}
                       } catch (e) {
                           alert("Load file \"" + $("#openFile").val() + "\" failed, please check opened file is a valid SpreadJS JSON file");
                       } finally {
                           spread.isPaintSuspended(false);//关闭paint
                       }
                   };
                   reader.onerror = function (evt) {//载入文件错误
                   };
                   // Read file into memory as UTF-8
                   reader.readAsText(file, "UTF-8");//读取文件
               }
           });


2.常用操作


$("#ribbon").wijribbon({
               click: function (e, cmd) {
                   var spread = $("#ss").wijspread("spread");
                   var sheet = spread.getActiveSheet();
                   sheet.isPaintSuspended(true);
                   switch (cmd.commandName) {
                       case "import"://导入文件
                           var evt = document.createEvent("MouseEvents");
                           evt.initEvent("click", true, false);
                           document.getElementById("openFile").dispatchEvent(evt);//触发上面第一条的那个文件框事件
                           break;
                       case "cut"://调用剪切命令
                           $.wijmo.wijspread.SpreadActions.cut.call(sheet);
                           break;
                       case "copy"://调用复制命令
                           $.wijmo.wijspread.SpreadActions.copy.call(sheet);
                           break;
                       case "paste"://调用粘贴命令
                           $.wijmo.wijspread.SpreadActions.paste.call(sheet);
                           break;
                       case "borders":
                           $("#borderdialog").dialog("open");
                           break;
                       case "highlightcell"://高亮单元格
                           $("#Rule1 option:eq(0)").attr("selected", "selected");
                           $("#Rule1").empty();
                           $("#Rule1").show();
                           $("#Rule1").append("<option value='0'>Cell Value</option>");
                           $("#Rule1").append("<option value='1'>Specific Text</option>");
                           $("#Rule1").append("<option value='2'>Date Occurring</option>");
                           $("#Rule1").append("<option value='5'>Unique</option>");
                           $("#Rule1").append("<option value='6'>Duplicate</option>");
                           var rule = "0";
                           var type = $("#ComparisonOperator1");
                           setEnumTypeOfCF(rule, type);
                           $("#conditionalformatdialog").dialog("open");
                           break;
                       case "topbottom":
                           $("#Rule1 option:eq(1)").attr("selected", "selected");
                           $("#Rule1").empty();
                           $("#Rule1").show();
                           $("#Rule1").append("<option value='4'>Top10</option>");
                           $("#Rule1").append("<option value='7'>Average</option>");
                           var rule = "4";
                           var type = $("#ComparisonOperator1");
                           setEnumTypeOfCF(rule, type);
                           $("#conditionalformatdialog").dialog("open");
                           break;
                       case "colorscale":
                           $("#Rule1 option:eq(2)").attr("selected", "selected");
                           $("#Rule1").empty();
                           $("#Rule1").show();
                           $("#Rule1").append("<option value='8'>2-Color Scale</option>");
                           $("#Rule1").append("<option value='9'>3-Color Scale</option>");
                           var rule = "8";
                           var type = $("#ComparisonOperator1");
                           setEnumTypeOfCF(rule, type);
                           $("#conditionalformatdialog").dialog("open");
                           break;
                       case "otherofconditionalformat":
                           $("#Rule1 option:eq(3)").attr("selected", "selected");
                           $("#Rule1").empty();
                           $("#Rule1").show();
                           $("#Rule1").append("<option value='3'>FormulaRule</option>");
                           var rule = "3";
                           var type = $("#ComparisonOperator1");
                           setEnumTypeOfCF(rule, type);
                           $("#conditionalformatdialog").dialog("open");
                           break;
                       case "databar":
                           $("#databardialog").dialog("open");
                           break;
                       case "iconset":
                           createIconCriteriaDOM();
                           $("#iconsetdialog").dialog("open");
                           break;
                       case "removeconditionalformats":
                           var cfs = sheet.getConditionalFormats();
                           var row = sheet.getActiveRowIndex(), col = sheet.getActiveColumnIndex();
                           var rules = cfs.getRules(row, col);
                           $.each(rules, function (i, v) {
                               cfs.removeRule(v);
                           });
                           sheet.isPaintSuspended(false);
                           break;
                       case "textboxcelltype":
                           $("#comboCellOptions").hide();
                           $("#checkBoxCellOptions").hide();
                           $("#buttonCellOptions").hide();
                           $("#hyperlinkCellOptions").hide();
                           var cellType = new $.wijmo.wijspread.TextCellType();
                           sheet.isPaintSuspended(true);
                           sheet.suspendEvent();
                           var sels = sheet.getSelections();
                           for (var i = 0; i < sels.length; i++) {
                               var sel = sheet._getActualRange(sels[i]);
                               for (var r = 0; r < sel.rowCount; r++) {
                                   for (var c = 0; c < sel.colCount; c++) {
                                       sheet.setCellType(sel.row + r, sel.col + c, cellType, $.wijmo.wijspread.SheetArea.viewport);
                                   }
                               }
                           }
                           sheet.resumeEvent();
                           sheet.isPaintSuspended(false);
                           break;
                       case "comboboxcelltype":
                           $("#comboCellOptions").show();
                           $("#checkBoxCellOptions").hide();
                           $("#buttonCellOptions").hide();
                           $("#hyperlinkCellOptions").hide();
                           $("#celltypedialog").dialog("open");
                           selCellType = "ComboCell";
                           break;
                       case "checkboxcelltype":
                           $("#comboCellOptions").hide();
                           $("#checkBoxCellOptions").show();
                           $("#buttonCellOptions").hide();
                           $("#hyperlinkCellOptions").hide();
                           $("#celltypedialog").dialog("open");
                           selCellType = "CheckBoxCell";
                           break;
                       case "buttoncelltype":
                           $("#comboCellOptions").hide();
                           $("#checkBoxCellOptions").hide();
                           $("#buttonCellOptions").show();
                           $("#hyperlinkCellOptions").hide();
                           $("#celltypedialog").dialog("open");
                           selCellType = "ButtonCell";
                           break;
                       case "hyperlinkcelltype":
                           $("#comboCellOptions").hide();
                           $("#checkBoxCellOptions").hide();
                           $("#buttonCellOptions").hide();
                           $("#hyperlinkCellOptions").show();
                           $("#celltypedialog").dialog("open");
                           selCellType = "HyperLinkCell";
                           break;
                       case "alloweditorreservedlocations":
                           sheet.allowEditorReservedLocations($("#alleditorreserved").attr("checked") === "checked" ? true : false);
                           break;
                       case "ClearCellType":
                           var sels = sheet.getSelections();
                           for (var i = 0; i < sels.length; i++) {
                               var sel = sheet._getActualRange(sels[i]);
                               sheet.clear(sel.row, sel.col, sel.rowCount, sel.colCount, $.wijmo.wijspread.SheetArea.viewport, $.wijmo.wijspread.StorageType.Style);
                           }
                           sheet.isPaintSuspended(false);
                           break;
                       case "datavalidation":
                           $("#datavalidationdialog").dialog("open");
                           break;
                       case "circleinvaliddata":
                           spread.highlightInvalidData(true);
                           break;
                       case "clearvalidationcircles":
                           spread.highlightInvalidData(false);
                           break;
                       case "isr1c1":
                           var result = $("#isR1C1").prop("checked");
                           if (result) {
                               spread.referenceStyle($.wijmo.wijspread.ReferenceStyle.R1C1);
                           }
                           else {
                               spread.referenceStyle($.wijmo.wijspread.ReferenceStyle.A1);
                           }
                           break;
                       case "canuserdragdrop":
                           var result = $("#canUserDragDrop").prop("checked");
                           sheet.canUserDragDrop(result);
                           sheet.isPaintSuspended(false);
                           break;
                       case "canuserdragfill":
                           var result = $("#canUserDragFill").prop("checked");
                           sheet.canUserDragFill(result);
                           sheet.isPaintSuspended(false);
                           break;
                       case "canusereditformula":
                           var result = $("#canUserEditFormula").prop("checked");
                           spread.canUserEditFormula(result);
                           break;
                       case "merge"://合并单元格
                           var sels = sheet.getSelections();
                           var hasSpan = false;
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount());
                               if (sheet.getSpans(sel, $.wijmo.wijspread.SheetArea.viewport).length > 0) {
                                   for (var i = 0; i < sel.rowCount; i++) {
                                       for (var j = 0; j < sel.colCount; j++) {
                                           sheet.removeSpan(i + sel.row, j + sel.col);
                                       }
                                   }
                                   hasSpan = true;
                               }
                           }
                           if (!hasSpan) {
                               for (var n = 0; n < sels.length; n++) {
                                   var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount());
                                   sheet.addSpan(sel.row, sel.col, sel.rowCount, sel.colCount);
                               }
                           }
                           break;
                       case "justifyleft"://文本居左
                       case "justifycenter"://文本居中
                       case "justifyright"://文本居右
                           var align = $.wijmo.wijspread.HorizontalAlign.left;
                           if (cmd.commandName == "justifycenter") align = $.wijmo.wijspread.HorizontalAlign.center;
                           if (cmd.commandName == "justifyright") align = $.wijmo.wijspread.HorizontalAlign.right;
                           var sels = sheet.getSelections();
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount());
                               sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).hAlign(align);
                           }
                           break;
                       case "increaseindent"://增加缩进
                       case "decreaseindent"://删除缩进
                           var sels = sheet.getSelections();
                           var offset = 1;
                           if (cmd.commandName == "decreaseindent") offset = -1;
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount());
                               for (var i = 0; i < sel.rowCount; i++) {
                                   for (var j = 0; j < sel.colCount; j++) {
                                       var indent = sheet.getCell(i + sel.row, j + sel.col, $.wijmo.wijspread.SheetArea.viewport).textIndent();
                                       if (isNaN(indent)) indent = 0;
                                       sheet.getCell(i + sel.row, j + sel.col, $.wijmo.wijspread.SheetArea.viewport).textIndent(indent + offset);
                                   }
                               }
                           }
                           break;
                       case "backcolor"://背景颜色
                       case "fontcolor"://文本颜色
                           $("#colordialog").dialog({ title: cmd.commandName == "backcolor" ? "Back Color" : "Fore Color" });
                           $("#colordialog").dialog("open");
                           break;
                       case "tabStripColor":
                           $("#colordialog").dialog({ title: "Tab Strip Color" });
                           $("#colordialog").dialog("open");
                           break;
                       case "frozenlinecolor"://冻结线的颜色
                           $("#colordialog").dialog({ title: "FrozenLineColor" });
                           $("#colordialog").dialog("open");
                           break;
                       case "bold"://文本加粗
                       case "italic"://文本斜体
                           var styleEle = document.getElementById("colorSample");
                           var font = sheet.getCell(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex(), $.wijmo.wijspread.SheetArea.viewport).font();
                           if (font != undefined) {
                               styleEle.style.font = font;
                           } else {
                               styleEle.style.font = "10pt Arial";
                           }
                           if (cmd.commandName == "bold") {
                               if (styleEle.style.fontWeight == "bold") {
                                   styleEle.style.fontWeight = "";
                               } else {
                                   styleEle.style.fontWeight = "bold";
                               }
                           } else if (cmd.commandName == "italic") {
                               if (styleEle.style.fontStyle == "italic") {
                                   styleEle.style.fontStyle = "";
                               } else {
                                   styleEle.style.fontStyle = "italic";
                               }
                           }
                           var sels = sheet.getSelections();
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount());
                               //sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).font(document.defaultView.getComputedStyle(styleEle,"").font);
                               sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).font(styleEle.style.font);
                           }
                           break;
                       case "tabStop":
                           var sels = sheet.getSelections();
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount()),
                                   isTabStop = sheet.getCell(sel.row, sel.col, $.wijmo.wijspread.SheetArea.viewport).tabStop();
                               if (isTabStop === false) {
                                   isTabStop = true;
                               } else {
                                   isTabStop = false;
                               }
                               sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).tabStop(isTabStop);
                           }
                           break;
                       case "wrapText":
                           var sels = sheet.getSelections();
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount()), wordWrap = sheet.getCell(sel.row, sel.col, $.wijmo.wijspread.SheetArea.viewport).wordWrap();
                               if (wordWrap === true) {
                                   wordWrap = false;
                               } else {
                                   wordWrap = true;
                               }
                               sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).wordWrap(wordWrap);
                           }
                           break;
                       case "Underline"://下划线
                           var sels = sheet.getSelections(),
                               underline = $.wijmo.wijspread.TextDecorationType.Underline;
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount()),
                                   textDecoration = sheet.getCell(sel.row, sel.col, $.wijmo.wijspread.SheetArea.viewport).textDecoration();
                               if ((textDecoration & underline) === underline) {
                                   textDecoration = textDecoration - underline;
                               } else {
                                   textDecoration = textDecoration | underline;
                               }
                               sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).textDecoration(textDecoration);
                           }
                           break;
                       case "Strikethrough"://删除线
                           var sels = sheet.getSelections(),
                               lineThrough = $.wijmo.wijspread.TextDecorationType.LineThrough;
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount()),
                                   textDecoration = sheet.getCell(sel.row, sel.col, $.wijmo.wijspread.SheetArea.viewport).textDecoration();
                               if ((textDecoration & lineThrough) === lineThrough) {
                                   textDecoration = textDecoration - lineThrough;
                               } else {
                                   textDecoration = textDecoration | lineThrough;
                               }
                               sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).textDecoration(textDecoration);
                           }
                           break;
                       case "Overline"://上划线
                           var sels = sheet.getSelections(),
                               overline = $.wijmo.wijspread.TextDecorationType.Overline;
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount()),
                                   textDecoration = sheet.getCell(sel.row, sel.col, $.wijmo.wijspread.SheetArea.viewport).textDecoration();
                               if ((textDecoration & overline) === overline) {
                                   textDecoration = textDecoration - overline;
                               } else {
                                   textDecoration = textDecoration | overline;
                               }
                               sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).textDecoration(textDecoration);
                           }
                           break;
                       case "insertcol"://插入列
                           sheet.addColumns(sheet.getActiveColumnIndex(), 1);
                           break;
                       case "insertrow"://插入行
                           sheet.addRows(sheet.getActiveRowIndex(), 1);
                           break;
                       case "deleterow"://删除行
                           sheet.deleteRows(sheet.getActiveRowIndex(), 1);
                           break;
                       case "deletecol"://删除列
                           sheet.deleteColumns(sheet.getActiveColumnIndex(), 1);
                           break;
                       case "sortaz"://从低到高排序
                       case "sortza"://从高到低排序
                           var sels = sheet.getSelections();
                           for (var n = 0; n < sels.length; n++) {
                               var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount());
                               sheet.sortRange(sel.row, sel.col, sel.rowCount, sel.colCount, true, [
                                   { index: sel.col, ascending: cmd.commandName == "sortaz" }
                               ]);
                           }
                           break;
                       case "filter"://过滤数据
                           if (sheet.rowFilter()) {
                               sheet.rowFilter(null);
                           } else {
                               var sels = sheet.getSelections();
                               if (sels.length > 0) {
                                   var sel = sels[0];
                                   sheet.rowFilter(new $.wijmo.wijspread.HideRowFilter(sel));
                               }
                           }
                           break;
                       case "group"://分组
                           var sels = sheet.getSelections();
                           var sel = sels[0];
                           if (sel.col == -1) // row selection
                           {
                               var groupExtent = new $.wijmo.wijspread.UndoRedo.GroupExtent(sel.row, sel.rowCount);
                               var action = new $.wijmo.wijspread.UndoRedo.RowGroupUndoAction(sheet, groupExtent);
                               spread.doCommand(action);
                               $("#showRowGroup").attr("disabled", false);
                               if ($("#showRowGroup").attr("value") == "true") {
                                   $("#showRowGroupLabel").unbind("mouseup", rowlabelnoactive);
                               }
                               $("#showRowGroup").attr("value", "true");
                               $("#showRowGroupLabel").attr("disabled", false);
                               $("#showRowGroupLabel").removeClass("ui-state-disabled");
                               $("#showRowGroupLabel").unbind("mouseup", rowlabelactive);
                               sheet.showRowRangeGroup(true);
                           }
                           else if (sel.row == -1) // column selection
                           {
                               var groupExtent = new $.wijmo.wijspread.UndoRedo.GroupExtent(sel.col, sel.colCount);
                               var action = new $.wijmo.wijspread.UndoRedo.ColumnGroupUndoAction(sheet, groupExtent);
                               spread.doCommand(action);
                               $("#showColGroup").attr("disabled", false);
                               if ($("#showColGroup").attr("value") == "true") {
                                   $("#showColGroupLabel").unbind("mouseup", collabelnoactive);
                               }
                               $("#showColGroup").attr("value", "true");
                               $("#showColGroupLabel").attr("disabled", false);
                               $("#showColGroupLabel").removeClass("ui-state-disabled");
                               $("#showColGroupLabel").unbind("mouseup", collabelactive);
                               sheet.showColumnRangeGroup(true);
                           }
                           else // cell range selection
                           {
                               alert("please select a range of row or col");
                           }
                           break;
                       case "ungroup"://解除分组
                           var sels = sheet.getSelections();
                           var sel = sels[0];
                           if (sel.col == -1 && sel.row == -1) // sheet selection
                           {
                               sheet.rowRangeGroup.ungroup(0, sheet.getRowCount());
                               sheet.colRangeGroup.ungroup(0, sheet.getColumnCount());
                           }
                           else if (sel.col == -1) // row selection
                           {
                               //sheet.rowRangeGroup.ungroup(sel.row, sel.rowCount);
                               var groupExtent = new $.wijmo.wijspread.UndoRedo.GroupExtent(sel.row, sel.rowCount);
                               var action = new $.wijmo.wijspread.UndoRedo.RowUngroupUndoAction(sheet, groupExtent);
                               spread.doCommand(action);
                               if (sheet.rowRangeGroup.getMaxLevel() < 0) {
                                   $("#showRowGroup").attr("disabled", true);
                                   $("#showRowGroup").attr("value", "true");
                                   $("#showRowGroupLabel").attr("disabled", true);
                                   $("#showRowGroupLabel").addClass("ui-state-disabled");
                                   $("#showRowGroupLabel").bind("mouseup", rowlabelnoactive);
                               }
                           }
                           else if (sel.row == -1) // column selection
                           {
                               //sheet.colRangeGroup.ungroup(sel.col, sel.colCount);
                               var groupExtent = new $.wijmo.wijspread.UndoRedo.GroupExtent(sel.col, sel.colCount);
                               var action = new $.wijmo.wijspread.UndoRedo.ColumnUngroupUndoAction(sheet, groupExtent);
                               spread.doCommand(action);
                               if (sheet.colRangeGroup.getMaxLevel() < 0) {
                                   $("#showColGroup").attr("disabled", true);
                                   $("#showColGroup").attr("value", "true");
                                   $("#showColGroupLabel").attr("disabled", true);
                                   $("#showColGroupLabel").addClass("ui-state-disabled");
                                   $("#showColGroupLabel").bind("mouseup", collabelnoactive);
                               }
                           }
                           else // cell range selection
                           {
                               alert("please select a range of row or col");
                           }
                           break;
                       case "showdetail"://显示明细
                       case "hidedetail"://隐藏明细
                           var sels = sheet.getSelections();
                           var sel = sels[0];
                           if (sel.col == -1 && sel.row == -1) // sheet selection
                           {
                           }
                           else if (sel.col == -1) // row selection
                           {
                               for (var i = 0; i < sel.rowCount; i++) {
                                   var rgi = sheet.rowRangeGroup.find(sel.row + i, 0);
                                   if (rgi) {
                                       sheet.rowRangeGroup.expand(rgi.level, cmd.commandName == "showdetail");
                                   }
                               }
                           }
                           else if (sel.row == -1) // column selection
                           {
                               for (var i = 0; i < sel.colCount; i++) {
                                   var rgi = sheet.colRangeGroup.find(sel.col + i, 0);
                                   if (rgi) {
                                       sheet.colRangeGroup.expand(rgi.level, cmd.commandName == "showdetail");
                                   }
                               }
                           }
                           else // cell range selection
                           {
                           }
                           break;
                       case "showrowrangegroup":
                           if (!$("#showRowGroup").attr("disabled")) {
                               sheet.showRowRangeGroup($("#showRowGroup").prop("checked"));
                           }
                           break;
                       case "showcolrangegroup":
                           if (!$("#showColGroup").attr("disabled")) {
                               sheet.showColumnRangeGroup($("#showColGroup").prop("checked"));
                           }
                           break;
                       case "enableUseWijmoTheme":
                           if ($("#wijmothemecheck").prop("checked")) {
                               $("#setTabStripColor").attr("disabled", false);
                               $("#setTabStripColor").removeClass("ui-state-disabled");
                               spread.useWijmoTheme = false;
                               spread.repaint();
                           } else {
                               $("#setTabStripColor").attr("disabled", true);
                               $("#setTabStripColor").addClass("ui-state-disabled");
                               spread.useWijmoTheme = true;
                               spread.repaint();
                           }
                           break;
                       case "rowheader"://行表头的显示与隐藏
                           //For Support IE7/8
                           //sheet.rowHeaderVisible = !sheet.rowHeaderVisible;
                           sheet.setRowHeaderVisible(!sheet.getRowHeaderVisible());
                           break;
                       case "columnheader"://列表头的显示与隐藏
                           //For Support IE7/8
                           //sheet.colHeaderVisible = !sheet.colHeaderVisible;
                           sheet.setColumnHeaderVisible(!sheet.getColumnHeaderVisible());
                           break;
                       case "vgridline"://表格垂直线
                           //For Support IE7/8
                           //sheet.gridline.showVerticalGridline = !sheet.gridline.showVerticalGridline;
                           var vGridLine = sheet.gridline.showVerticalGridline;
                           var hGridLine = sheet.gridline.showHorizontalGridline;
                           sheet.setGridlineOptions({ showVerticalGridline: !vGridLine, showHorizontalGridline: hGridLine });
                           break;
                       case "hgridline"://表格水平线
                           //For Support IE7/8
                           //sheet.gridline.showHorizontalGridline = !sheet.gridline.showHorizontalGridline;
                           var hGridLine = sheet.gridline.showHorizontalGridline;
                           var vGridLine = sheet.gridline.showVerticalGridline;
                           sheet.setGridlineOptions({ showVerticalGridline: vGridLine, showHorizontalGridline: !hGridLine });
                           break;
                       case "tabstrip":
                           spread.tabStripVisible(!spread.tabStripVisible());
                           break;
                       case "newtab":
                           spread.newTabVisible(!spread.newTabVisible());
                           break;
                       case "freezepane"://冻结行和列
                           sheet.setFrozenRowCount(sheet.getActiveRowIndex());
                           sheet.setFrozenColumnCount(sheet.getActiveColumnIndex());
                           break;
                       case "freezesetting"://冻结设置
                           $("#freezesettingdialog").dialog("open");
                           break;
                       case "unfreeze"://解除冻结
                           sheet.setFrozenRowCount(0);
                           sheet.setFrozenColumnCount(0);
                           sheet.setFrozenTrailingRowCount(0);
                           sheet.setFrozenTrailingColumnCount(0);
                           break;
                       case "showdragdroptip":
                           var dragDrop = $("#showDragDropTip").prop("checked");
                           spread.showDragDropTip(dragDrop);
                           break;
                       case "showdragfilltip":
                           var dragFill = $("#showDragFillTip").prop("checked");
                           spread.showDragFillTip(dragFill);
                           break;
                       case "showhorizontalscrollbar":
                           var hScrollbar = $("#showHorizontalScrollbar").prop("checked");
                           spread.showHorizontalScrollbar(hScrollbar);
                           break;
                       case "showverticalscrollbar":
                           var vScrollbar = $("#showVerticalScrollbar").prop("checked");
                           spread.showVerticalScrollbar(vScrollbar);
                           break;
                       case "autofitwithheadertext":
                           var autoFit = $("#autoFitWithHeaderText").prop("checked") ? $.wijmo.wijspread.AutoFitType.CellWithHeader : $.wijmo.wijspread.AutoFitType.Cell;
                           spread.autoFitType(autoFit);
                           break;
                       case "sheetVisible"://显示或者隐藏一个sheet页
                           var sheetName = $("#sheetList").val();
                           if (sheetName && sheetName !== '') {
                               var visibleSheet = spread.getSheetFromName(sheetName);
                               if (visibleSheet && typeof (visibleSheet.visible) === 'function') {
                                   var visible = visibleSheet.visible();
                                   visibleSheet.visible(!visible);
                               }
                           }
                           break;
                       case "spreadbackcolor":
                           $("#colordialog").dialog({ title: "SpreadBackColor" });
                           $("#colordialog").dialog("open");
                           break;
                       case "grayareabackcolor":
                           $("#colordialog").dialog({ title: "GrayAreaBackColor" });
                           $("#colordialog").dialog("open");
                           break;
                       case "selectionbordercolor":
                           $("#colordialog").dialog({ title: "SelectionBorderColor" });
                           $("#colordialog").dialog("open");
                           break;
                       case "selectionbackcolor":
                           $("#colordialog").dialog({ title: "SelectionBackColor" });
                           $("#colordialog").dialog("open");
                           break;
                       case "table"://插入表格
                           var table = sheet.findTable(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex());
                           $("#ckName").text("");
                           if (!table) {
                               $("#showHeader").attr("checked",true);
                               $("#showFooter").attr("checked",false);
                               $("#highlightFirstColumn").attr("checked",false);
                               $("#highlightLastColumn").attr("checked",false);
                               $("#bandRows").attr("checked",true);
                               $("#bandColumns").attr("checked",false);
                               tableName = getTableName(sheet);
                               $("#tableName").val(tableName);
                               isTableAdd = true;
                               $("#tabledialog").dialog({ title: "Add Table" });
                               $("#tabledialog").next().find("span").get(0).innerHTML = "Add";
                           } else {
                               $("#showHeader").attr("checked",table.showHeader());
                               $("#showFooter").attr("checked",table.showFooter());
                               $("#highlightFirstColumn").attr("checked",table.highlightFirstColumn());
                               $("#highlightLastColumn").attr("checked",table.highlightLastColumn());
                               $("#bandRows").attr("checked",table.bandRows());
                               $("#bandColumns").attr("checked",table.bandColumns());
                               var styleName = table.style().name();
                               $("#tableStyles option[value='"+styleName+"']").attr("selected", true);
                               $("#tableName").val(table.name());
                               isTableAdd = false;
                               $("#tabledialog").dialog({ title: "Design Table" });
                               $("#tabledialog").next().find("span").get(0).innerHTML = "Update";
                           }
                           $("#tabledialog").dialog("open");
                           break;
                       case "picture"://插入图片
                           var pictures = sheet.getPictures(),
                               hasPictureSelected = false,
                               picture;
                           for (var index = 0, len = pictures.length; index < len; index++) {
                               var item = pictures[index];
                               if (item && item.isSelected()) {
                                   hasPictureSelected = true;
                                   picture = item;
                                   break;
                               }
                           }
                           if (!hasPictureSelected) {
                               $("#picturedialog").dialog({ title: "Add Picture" });
                               $("#picturedialog").next().find("span").get(0).innerHTML = "Add";
                               $("#pictureSettingsOptions").hide();
                               $("#pictureAddOptions").show();
                               isPictureAdd = true;
                           } else {
                               $("#picturedialog").dialog({ title: "Design Picture" });
                               $("#picturedialog").next().find("span").get(0).innerHTML = "Update";
                               $("#pictureAddOptions").hide();
                               $("#pictureSettingsOptions").show();
                               $("#dynamicMove").attr("checked",picture.dynamicMove());
                               $("#dynamicSize").attr("checked",picture.dynamicSize());
                               $("#pictureBackColor").css("background", picture.backColor());
                               $("#pictureBorderColor").css("background", picture.borderColor());
                               $("#pictureBorderWidth").val(picture.borderWidth());
                               $("#pictureBorderRadius").val(picture.borderRadius());
                               var borderStyle = picture.borderStyle();
                               $("#pictureBorderStyle option[value='"+borderStyle+"']").attr("selected", true);
                               var pictureStretch = picture.pictureStretch();
                               $("#pictureStretch option[value='"+pictureStretch+"']").attr("selected", true);
                               isPictureAdd = false;
                           }
                           $("#picturedialog").dialog("open");
                           break;
                       default:
                           if (cmd.commandName.substr(0, 1) == "f") {
                               var styleEle = document.getElementById("colorSample");
                               var font = sheet.getCell(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex(), $.wijmo.wijspread.SheetArea.viewport).font();
                               if (font) {
                                   styleEle.style.font = font;
                               }
                               else {
                                   styleEle.style.font = "10pt Arial";
                               }
                               if (cmd.commandName.substr(0, 2) == "fn")
                                   styleEle.style.fontFamily = document.getElementById(cmd.commandName)["title"];
                               if (cmd.commandName.substr(0, 2) == "fs")
                                   styleEle.style.fontSize = document.getElementById(cmd.commandName)["title"];
                               var sels = sheet.getSelections();
                               for (var n = 0; n < sels.length; n++) {
                                   var sel = getActualCellRange(sels[n], sheet.getRowCount(), sheet.getColumnCount());
                                   //sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).font(document.defaultView.getComputedStyle(styleEle, "").font);
                                   sheet.getCells(sel.row, sel.col, sel.row + sel.rowCount - 1, sel.col + sel.colCount - 1, $.wijmo.wijspread.SheetArea.viewport).font(styleEle.style.font);
                               }
                           } else if (cmd.commandName.substr(0, 1) == "s") {
                               if (cmd.commandName.substr(0, 2) == "sp") {
                                   var policy = 2;
                                   if (document.getElementById(cmd.commandName)["title"] == "Single") {
                                       policy = 0;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Range") {
                                       policy = 1;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "MultiRange") {
                                       policy = 2;
                                   }
                                   sheet.selectionPolicy(policy);
                               }
                               if (cmd.commandName.substr(0, 2) == "su") {
                                   var unit = 0;
                                   if (document.getElementById(cmd.commandName)["title"] == "Cell") {
                                       unit = 0;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Row") {
                                       unit = 1;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Column") {
                                       unit = 2;
                                   }
                                   sheet.selectionUnit(unit);
                               }
                           }else if (cmd.commandName.substr(0, 1) == "t") {
                               if (cmd.commandName.substr(0, 2) == "ts") {
                                   var scrollTip = 2;
                                   if (document.getElementById(cmd.commandName)["title"] == "None") {
                                       scrollTip = 0;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Horizontal") {
                                       scrollTip = 1;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Vertical") {
                                       scrollTip = 2;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Both") {
                                       scrollTip = 3;
                                   }
                                   spread.showScrollTip(scrollTip);
                               }
                               if (cmd.commandName.substr(0, 2) == "tr") {
                                   var resizeTip = 0;
                                   if (document.getElementById(cmd.commandName)["title"] == "None") {
                                       resizeTip = 0;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Column") {
                                       resizeTip = 1;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Row") {
                                       resizeTip = 2;
                                   } else if (document.getElementById(cmd.commandName)["title"] == "Both") {
                                       resizeTip = 3;
                                   }
                                   spread.showResizeTip(resizeTip);
                               }
                           } else {
                               alert(cmd.commandName);
                           }
                           break;
                   }
                   sheet.isPaintSuspended(false);
               }
           });


3.冻结列

var spread = $("#ss").wijspread("spread");
var sheet = spread.getActiveSheet();
sheet.isPaintSuspended(true);
sheet.suspendEvent();
var frozenRowCount = parseInt($("#frozenRowCount").val(), 10);
sheet.setFrozenRowCount(frozenRowCount);//设置的冻结的行数
var frozenColumnCount = parseInt($("#frozenColumnCount").val(), 10);
sheet.setFrozenColumnCount(frozenColumnCount);//设置冻结的列数
var frozenTrailingRowCount = parseInt($("#frozenTrailingRowCount").val(), 10);
sheet.setFrozenTrailingRowCount(frozenTrailingRowCount);//冻结拖动的行数
var frozenTrailingColumnCount = parseInt($("#frozenTrailingColumnCount").val(), 10);
sheet.setFrozenTrailingColumnCount(frozenTrailingColumnCount);//冻结拖动的列数
sheet.isPaintSuspended(false);


4.常用接口

sheet.isPaintSuspended(true);//先打开,设置之后,在关闭,提高效率
sheet.addSpan(0, 1, 1, 10);//合并单元格
sheet.setRowHeight(0, 40);//设置行高
sheet.setValue(0, 1, "Sale Data Analysis");//设置单元格的文本
sheet.getCell(0, 1).font("bold 30px arial");//设置单元格的字体
sheet.getCell(0, 1).vAlign($.wijmo.wijspread.VerticalAlign.center);//设置单元格的垂直对齐方式
sheet.setFormula(10, 4, "=SUM(E5:E10)");//设置单元格的公式,这里是范围,用冒号
sheet.setFormula(18, 10, "=K11+K18");//设置单元格的公式,这里是没有用函数,直接用的公式
sheet.getCells(4, 1, 18, 3).backColor("#D9FFD9");//设置单元格的背景颜色
sheet.getCells(1, 1, 3, 10).hAlign($.wijmo.wijspread.HorizontalAlign.center);//设置单元格的水平对齐方式
sheet.setBorder(new $.wijmo.wijspread.Range(1, 1, 18, 10), new $.wijmo.wijspread.LineBorder("Black", $.wijmo.wijspread.LineStyle.thin), { all: true });//设置单元格的边框
sheet.setBorder(new $.wijmo.wijspread.Range(4, 4, 3, 6), new $.wijmo.wijspread.LineBorder("Green", $.wijmo.wijspread.LineStyle.dotted), { innerHorizontal: true });//设置单元格的边框
sheet.setBorder(new $.wijmo.wijspread.Range(7, 4, 3, 6), new $.wijmo.wijspread.LineBorder("Green", $.wijmo.wijspread.LineStyle.dotted), { innerHorizontal: true });//设置单元格的边框
sheet.isPaintSuspended(false);











演示地址:http://wijmo.com/demo/spreadjs/samples/spreadsheet.html



-----------------------------------------------------
转载请注明来源此处
原地址:#

-----网友评论----
1楼:fanxi 发表于 2016-07-21 12:20:49
也可以去spreadjs官网看看 http://www.gcpowertools.com.cn/products/spreadjs/
-----发表评论----
微网聚博客乐园 ©2014 blog.mn886.net 鲁ICP备14012923号   网站导航