本帖最后由 yhjsbok 于 2018-11-27 22:42 编辑
function genData() {
var Active = $("#SearchActive").prop("checked");
var TYear = $("#SearchTYear").val();
var Sheet = $("#SearchSheet").val();
var CostCenter = $("#SearchCostCenter").val();
$.ajax({
type: 'Get',
cache: false,
dataType: 'json',
async: true,
success: function (data) {
rowcount = data.recordsTotal;
var colInfos = [
{ name: 'Id', size: 100 },
{ name: 'Status', size: 50 },
{ name: 'Deadline', size: 70 },
{ name: 'Tyear', size: 40 }, //, visible: false
{ name: 'Sheet', size: 100 },
{ name: 'Entity', size: 50 },
{ name: 'R_l_i', size: 50 },
{ name: 'Costcenter', size: 70 },
{ name: 'Description', size: 100 },
{ name: 'Ccowner', size: 70 },
{ name: 'Dept1', size: 70 },
{ name: 'Dept2', size: 70 },
{ name: 'Dept3', size: 70 },
{ name: 'Costcategory', size: 80 },
{ name: 'Employeegrade', size: 100 },
{ name: 'Py_state', size: 50 },
{ name: 'Ytd_state', size: 50 },
{ name: 'D_id', size: 50 },
{ name: 'D_i_e', size: 50 },
{ name: 'Salarygrade', size: 30 },
{ name: 'Month1', size: 45 },
{ name: 'Month2', size: 45 },
{ name: 'Month3', size: 45 },
{ name: 'Month4', size: 45 },
{ name: 'Month5', size: 45 },
{ name: 'Month6', size: 45 },
{ name: 'Month7', size: 45 },
{ name: 'Month8', size: 45 },
{ name: 'Month9', size: 45 },
{ name: 'Month10', size: 45 },
{ name: 'Month11', size: 45 },
{ name: 'Month12', size: 45 },
{ name: 'Yavg', size: 50 },
{ name: 'Targt_state', size: 50 },
{ name: 'Targt_avg', size: 50 },
{ name: 'D_i', size: 30 },
{ name: 'Comment', size: 120 },
{ name: 'Szhstate', size: 50 },
{ name: 'Szhavg', size: 50 },
{ name: 'Wujinstate', size: 50 },
{ name: 'Wujinavg', size: 50 },
{ name: 'Tstate', size: 50 },
{ name: 'Tavg', size: 50 },
{ name: 'Createuser', size: 50},
{ name: 'Month1_read', size: 50 },
{ name: 'Month2_read', size: 50 },
{ name: 'Month3_read', size: 50 },
{ name: 'Month4_read', size: 50 },
{ name: 'Month5_read', size: 50 },
{ name: 'Month6_read', size: 50 },
{ name: 'Month7_read', size: 50 },
{ name: 'Month8_read', size: 50 },
{ name: 'Month9_read', size: 50 },
{ name: 'Month10_read', size: 50 },
{ name: 'Month11_read', size: 50 },
{ name: 'Month12_read', size: 50 },
{ name: 'Ccowner_nt', size: 50 }
];
sheet.autoGenerateColumns = false;
sheet.setDataSource(data.data);
sheet.bindColumns(colInfos);
//停止重绘 important！
spread.suspendPaint();
spread.suspendCalcService();
//数字显示精度设置
var formatRange4 = sheet.getRange(0, 15, rowcount, 2);
formatRange4.formatter(new GC.Spread.Formatter.GeneralFormatter("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"_-;_-@_-"));
var formatRange1 = sheet.getRange(0, 20, rowcount, 12);
formatRange1.formatter(new GC.Spread.Formatter.GeneralFormatter("_-* #,##0_-;-* #,##0_-;_-* \"-\"_-;_-@_-"));
var formatRange2 = sheet.getRange(0, 32, rowcount, 3);
formatRange2.formatter(new GC.Spread.Formatter.GeneralFormatter("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"_-;_-@_-"));
var formatRange3 = sheet.getRange(0, 37, rowcount, 6);
formatRange3.formatter(new GC.Spread.Formatter.GeneralFormatter("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"_-;_-@_-"));
//Month1..Month12 设定是整数
var gcdv = spreadNS.DataValidation;
var ddv = null;
ddv = gcdv.createNumberValidator(parseInt(6), parseInt(0), parseInt(10000), true);
if (ddv != null) {
ddv.errorMessage("");
ddv.errorStyle(parseInt(0));
ddv.errorTitle("");
ddv.inputTitle("");
ddv.inputMessage("");
sheet.setDataValidator(0, 20, rowcount, 12, ddv);
}
var option = {
allowFilter: true,
allowSort: true,
allowResizeRows: true,
allowResizeColumns: true,
allowEditObjects: false,
allowDragInsertRows: false,
allowDragInsertColumns: false,
allowInsertRows: false,
allowInsertColumns: false,
allowDeleteRows: false,
allowDeleteColumns: false
}
//sheet.options.protectionOptions = option;
//sheet.options.isProtected=false ;
var Active = $("#SearchActive").prop("checked");
//查询历史记录数据非管理员
if ((!Active && Role != "Admin")) { //|| Role == "Ctg"
sheet.getRange(0, 20, rowcount, 12).locked(true);
sheet.getRange(0, 15, rowcount, 2).locked(true);
sheet.getRange(0, 37, rowcount, 4).locked(true);
sheet.getRange(0, 33, rowcount, 2).locked(true);
sheet.options.protectionOptions = option;
sheet.options.isProtected = true;
//设置按钮是否可用
$("#Update").prop("disabled", true);
$("#Clear").prop("disabled", true);
$("#ClearAll").prop("disabled", true);
$("#Submit").prop("disabled", true);
$("#History").prop("disabled", true);
}
else {
//栏位是否编辑设置
//sheet.getRange(0, 18, rowcount, 12).locked(false);
//sheet.getRange(0, 32, rowcount, 1).locked(false);
if (Role == "Admin") {
sheet.getRange(0, 15, rowcount, 2).locked(false);
sheet.getRange(0, 37, rowcount, 4).locked(false);
sheet.getRange(0, 33, rowcount, 2).locked(false);
}
//sheet.options.protectionOptions = option;
//sheet.options.isProtected = true;
//用户输入部分红虚线显示
var lineStyle = GC.Spread.Sheets.LineStyle.dotted;
var lineBorder = new GC.Spread.Sheets.LineBorder('red', lineStyle);
var sheetArea = GC.Spread.Sheets.SheetArea.viewport
sheet.getRange(0, 20, rowcount, 12).setBorder(lineBorder, { all: true }, sheetArea);
sheet.getRange(0, 36, rowcount, 1).setBorder(lineBorder, { all: true }, sheetArea);
if (Role == "Admin") {
sheet.getRange(0, 15, rowcount, 2).setBorder(lineBorder, { all: true }, sheetArea);
sheet.getRange(0, 37, rowcount, 4).setBorder(lineBorder, { all: true }, sheetArea);
sheet.getRange(0, 33, rowcount, 2).setBorder(lineBorder, { all: true }, sheetArea);
}
//设置按钮是否可用
if (Role == "Ctg") {
$("#Update").prop("disabled", true);
$("#Clear").prop("disabled", true);
$("#ClearAll").prop("disabled", true);
$("#Submit").prop("disabled", true);
$("#History").prop("disabled", true);
}
else {
$("#Update").prop("disabled", false);
$("#Clear").prop("disabled", false);
$("#ClearAll").prop("disabled", false);
$("#Submit").prop("disabled", false);
$("#History").prop("disabled", false);
}
}
//设置公式 每行自动计算的栏位
for (var i = 0; i < rowcount ; i++) {
sheet.getCell(i, 32).formula('=SUM(U#:AF#)/12'.replace(/#/g, i + 1));
sheet.getCell(i, 41).formula('=AF#-AL#-AN#'.replace(/#/g, i + 1));
sheet.getCell(i, 42).formula('=AG#-AM#-AO#'.replace(/#/g, i + 1));
//处理处理total行
if (i == 0) {
sheet.getRange(i, 0, 1, colcount).locked(true);
sheet.getCell(i, 15).formula('=SUM(P2 #)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 16).formula('=SUM(Q2 #)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 20).formula('=SUM(U2:U#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 21).formula('=SUM(V2:V#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 22).formula('=SUM(W2:W#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 23).formula('=SUM(X2:X#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 24).formula('=SUM(Y2:Y#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 25).formula('=SUM(Z2:Z#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 26).formula('=SUM(AA2:AA#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 27).formula('=SUM(AB2:AB#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 28).formula('=SUM(AC2:AC#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 29).formula('=SUM(AD2:AD#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 30).formula('=SUM(AE2:AE#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 31).formula('=SUM(AF2:AF#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 33).formula('=SUM(AH2:AH#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 34).formula('=SUM(AI2:AI#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 37).formula('=SUM(AL2:AL#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 38).formula('=SUM(AM2:AM#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 39).formula('=SUM(AN2:AN#)'.replace(/#/g, rowcount + 1));
sheet.getCell(i, 40).formula('=SUM(AO2:AO#)'.replace(/#/g, rowcount + 1));
}
else {
//锁定month1...month12
LockMonth(i);
}
}
sheet.options.protectionOptions = option;
sheet.options.isProtected = true;
//恢复重绘 important！
//显示自定义列
UserCustomColumn();
//隐藏列
if (Role == "GeneralUser") {
//sheet.setColumnVisible(8, false);
//sheet.setColumnVisible(10, false);
sheet.setColumnVisible(37, false);
sheet.setColumnVisible(38, false);
sheet.setColumnVisible(39, false);
sheet.setColumnVisible(40, false);
sheet.setColumnVisible(41, false);
sheet.setColumnVisible(42, false);
}
spread.resumeCalcService();
spread.resumePaint();
sheet.clearPendingChanges();
$("#loadingCover").hide();
},
error: function (data) {
$("#loadingCover").hide();
}
});
}
|
|
陕公网安备：61019002000258 |
隐私政策 |
网站地图
