// declare constants: var KDCALC_FORM = "KDCALC_FORM_"; // FORMAT CONSTANTS var FMT_NONE = 0; var FMT_GENERAL = 1; var FMT_NUMERIC = 2; var FMT_CURRENCY = 3; var FMT_PERCENT = 4; var FMT_FRACTION = 5; var FMT_SCIENTIFIC = 6; var FMT_DATE = 7; var FMT_TIME = 8; var FMT_DATETIME = 9; var FMT_TEXT = 10; var FMT_SPECIAL = 11; var FMT_CUSTOM = 12; //var FIRST_ELEMENT = 3; // declare local variables: var dirty; // boolean // declare functions: /************************************************************************/ /** TOOLBAR METHODS */ /************************************************************************/ function doSave(bookName) { if(dirty) { alert("Click the 'Recalculate' button first to save the latest updates."); } window.open("saveDialog.aspx?bookName=" + bookName, "loadSave", "status=no,toolbar=no,scrollbars=no,menubar=no,resizable=no,width=435,height=420"); } function doLoad(bookName) { window.open("loadDialog.aspx", "loadSave", "status=no,toolbar=no,scrollbars=no,menubar=no,resizable=no,width=435,height=420"); } function doPrintPreview() { var OLECMDID = 7; /* OLECMDID values: * 6 - print * 7 - print preview * 8 - page setup (for printing) * 1 - open window * 4 - Save As * 10 - properties */ try { var PROMPT = 1; // 1 PROMPT & 2 DONT PROMPT USER var oWebBrowser = document.getElementById("WebBrowser1"); if(oWebBrowser == null) { var sWebBrowser = '

'; document.body.insertAdjacentHTML('beforeEnd', sWebBrowser); oWebBrowser = document.getElementById("WebBrowser1"); } oWebBrowser.ExecWB(OLECMDID,PROMPT); } catch(e) { //e.message; alert("Error with Print Preview, opening Print dialog."); print(); } } function doRecalc() { markClean(); var theForm = getCalcForm(); //alert("doRecalc:" + theForm.name); if(validateForm(theForm)) { theForm.submit(); } } function doReset(model) { var theForm = getCalcForm(); // use a hidden form field to handle a command that is sent to the server? theForm.elements["commandField"].value = "reset"; theForm.submit(); } function doHelp() { window.open("help.htm", "helpDialog", "status=no,toolbar=no,scrollbars=yes,menubar=no,resizable=no,width=435,height=420"); } function changeZoom(val) { var divColl = document.all.tags("DIV"); var len = divColl.length; for(var i=0; i < len; i++) { var name = divColl[i].id; if(name.lastIndexOf("Tab") != name.length -3) { divColl[i].style.zoom = val + "%"; } } //sheetDiv.style.zoom = val; } /************************************************************************/ /** CORE METHODS */ /************************************************************************/ /** * init() should be called in the HTML's body "onLoad" event * @see */ function init(zoomVal) { if(zoomVal) { var i = 0; var zoomLevel; zoomLevel = zoomCtrl.options[i].value; while(zoomLevel < zoomVal) { zoomLevel = zoomCtrl.options[i].value; i++; } //alert(zoomVal + " " + zoomLevel +" "+ i); zoomCtrl.selectedIndex = i-1; changeZoom(zoomLevel); } // set the focus to the first element //var comp = getCalcForm().elements[FIRST_ELEMENT]; //if(comp != null) //comp.focus(); justifyInterface(); } /** * Function called to switch worksheet tabs. */ function switchTabs(tabName) { var theForm = getCalcForm(); theForm.elements["commandField"].value = "switchTabs"; theForm.elements["parm1"].value = tabName; //theForm.action = theForm.elements["parm1"].value + ".aspx"; doRecalc(); } /** * Method returns a reference to the generated KDCalc form. */ function getCalcForm() { // find the form starting with "KDCALC_FORM" var len = document.forms.length; for(j=0; j< len; j++) { var theForm = document.forms[j]; if(theForm.name.indexOf(KDCALC_FORM) != -1) { return theForm; } } } /** * Method called when focus is given to a form element. */ function doFocus(component, event) { unformat(component); } /** * Method called when a change happens to an input cell. */ function doChange(component, event) { // handle linked controls: if(component.linkCtrl != null) { var controlRef = getCalcForm().elements[component.linkCtrl]; //alert(controlRef.name + " -> " + controlRef.type); if(controlRef.type == "checkbox") { controlRef.value = component.value; // set the hidden field value: var hdnCtrlStr = "hdn" + component.name.substr(3); var hdnCtrlRef = getCalcForm().elements[hdnCtrlStr]; hdnCtrlRef.value = component.value; var val = component.value.toUpperCase(); if(val == "FALSE" || val == '0' || val == "") controlRef.checked = false; else controlRef.checked = true; // like Excel uppercase TRUE or FALSE if(val == "FALSE" || val == "TRUE") component.value = val; } else if(controlRef.type == "radio") { controlRef.value = component.value; // set the hidden field value: var hdnCtrlStr = "hdn" + component.name.substr(3); var hdnCtrlRef = getCalcForm().elements[hdnCtrlStr]; hdnCtrlRef.value = component.value; var val = component.value.toUpperCase(); if(val == "FALSE" || val == '0' || val == "") controlRef.checked = false; else controlRef.checked = true; // like Excel uppercase TRUE or FALSE if(val == "FALSE" || val == "TRUE") component.value = val; // change the rest of the radio buttons: var rbtnGroup = getCalcForm().elements[controlRef.name]; for(var i = 0; i < rbtnGroup.length; i++) { var tmpBtn = rbtnGroup[i]; // the controlRef was changed above, only change the other rbts if(tmpBtn != controlRef) { if (tmpBtn.checked) val = "TRUE"; else val = "FALSE"; var hdnStr = "hdn" + tmpBtn.id.substr(3); var hdnRef = getCalcForm().elements[hdnStr]; if(hdnRef != null) hdnRef.value = val; var linkStr = "lnk" + tmpBtn.id.substr(3); var linkRef = getCalcForm().elements[linkStr]; if(linkRef != null) linkRef.value = val; } } } //else if(controlRef.name.substr(3) == "ddl") // select-one or select-multiple else if(controlRef.type.indexOf("select") == 0) { // loop through and look for the value, if it's there then set it, otherwise don't var len = controlRef.length; //alert("in select: " + len); for (var i=0; i < len ; i++) { if(controlRef.options[i].value == component.value) { controlRef.value = component.value; break; } } } //else if(controlRef.name.substr(3) == "opt") else if(controlRef.type == "hidden") // for form option buttons { // controlRef = "hdn" + controlRef.name.substr(3); controlRef.value = component.value; } else if(controlRef.type == "text" || controlRef.type == "textarea") { controlRef.value = component.value; } else if(controlRef.type == "toggle") { // toggle button uses methods controlRef.setValue(component.value); } else if(controlRef.type == "scroll" || controlRef.type == "spinner" ) { // toggle button uses methods controlRef.setValue(component.value); } } if(autoSubmit) doRecalc(); else markDirty(); } /** * Method called when a form element looses focus. */ function doBlur(component, event) { reformat(component); } /** * Method called when a key down happens on a form element. */ function doKeyDown(component, event) { checkDel(component, event); } /************************************************************************/ /** FORM ELEMENT METHODS */ /************************************************************************/ /** * Called when a checkBox is clicked. */ function checkBoxClick(control) { var val = "FALSE"; if (control.checked) val = "TRUE"; // set the value of the hidden form field var hdnStr = "hdn" + control.name.substr(3); var hdnRef = getCalcForm().elements[hdnStr]; //if(hdnRef != null) hdnRef.value = val; // set the value of the linked cell var controlStr = "lnk" + control.name.substr(3); var controlRef = getCalcForm().elements[controlStr]; if(controlRef != null) controlRef.value = val; if(autoSubmit) doRecalc(); else markDirty(); } /** * Handle an OLE radio button click. */ function radioBtnClick(control) { var rbtnGroup = getCalcForm().elements[control.name]; var val; for(var i = 0; i < rbtnGroup.length; i++) { var tmpBtn = rbtnGroup[i]; if (tmpBtn.checked) val = "TRUE"; else val = "FALSE"; var hdnStr = "hdn" + tmpBtn.id.substr(3); var hdnRef = getCalcForm().elements[hdnStr]; if(hdnRef != null) hdnRef.value = val; var controlStr = "lnk" + tmpBtn.id.substr(3); var controlRef = getCalcForm().elements[controlStr]; if(controlRef != null) controlRef.value = val; } if(autoSubmit) doRecalc(); else markDirty(); } /** * Handle a forms option button click. */ function optionBtnClick(control) { var rbtnGroup = getCalcForm().elements[control.name]; var val; for(var i = 0; i < rbtnGroup.length; i++) { var tmpBtn = rbtnGroup[i]; if (tmpBtn.checked) val = tmpBtn.value; } var hdnStr = "hdn" + tmpBtn.id.substr(3); var hdnRef = getCalcForm().elements[hdnStr]; if(hdnRef != null) hdnRef.value = val; var controlStr = "lnk" + control.id.substr(3); var controlRef = getCalcForm().elements[controlStr]; if (controlRef != null) controlRef.value = val; if(autoSubmit) doRecalc(); else markDirty(); } /** * Handle changing a textbox */ function textBoxChanged(control) { // set the value of the linked cell var controlStr = "lnk" + control.name.substr(3); var controlRef = getCalcForm().elements[controlStr]; if(controlRef != null) controlRef.value = control.value; if(autoSubmit) doRecalc(); else markDirty(); } /** * Handle a ddl or listbox changing. */ function lboxChanged(control) { // set the value of the linked cell var controlStr = "lnk" + control.name.substr(3); var controlRef = getCalcForm().elements[controlStr]; if(controlRef != null) controlRef.value = control.value; if(autoSubmit) doRecalc(); else markDirty(); } function buttonClick(control) { // insert custom macro code here alert("buttonClick event: " + control.name); if(autoSubmit) doRecalc(); else markDirty(); } function toggleClick(control) { var val = control.getValue(); // set the value of the linked cell var linkedStr = control.getLinkedCell(); var linkedCell = getCalcForm().elements[linkedStr]; if(linkedCell != null) linkedCell.value = val; if(autoSubmit) doRecalc(); else markDirty(); } function scrollClick(control) { var val = control.getValue(); //alert("scrollClick: " + control); var linkedStr = control.getLinkedCell(); var linkedCell = getCalcForm().elements[linkedStr]; if(linkedCell != null) linkedCell.value = val; if(autoSubmit) doRecalc(); else markDirty(); } /** * If the delete key is pressed when selecting a validation drop down, set the value to blank. */ function checkDel(control, evt) { if(control.type.indexOf("select") == 0) { if(evt.keyCode == 46 || evt.keyCode == 8) //46 - Delete, 8 - backspace { control.value = ""; if(autoSubmit) doRecalc(); else markDirty(); } } } /************************************************************************/ /** UTILITY METHODS */ /************************************************************************/ /** * loop through controls and right-align numbers and left-align text */ function justifyInterface() { // loop through all controls // if it starts with ctl // parse out sheet, row, col // get the text from that sheet, row, and col // iterate through all forms and find ones which start with "KDCALC_FORM_" var theForm = getCalcForm(); var len = theForm.elements.length; for(var i = 0; i < len; i++) { var field = theForm.elements[i]; if(field.name.indexOf("ctl") == 0) { if(field.format > 0) { //alert("field:" + field.name + ", format:" + field.format + field.value); if(validateNumeric(field.value) || validateCurrency(field.value) || validatePercent(field.value)) field.style.textAlign='right'; else field.style.textAlign='left'; } } } } function validateForm(aForm) { var len = aForm.elements.length; for(var i= 0; i < len; i++) { var component = aForm.elements[i]; if(component.name.indexOf("ctl") == 0 && component.readOnly == false) { component.value = getUnformattedValue(component); } } return true; } /** * parses out the sheet, row, col from a control's name ctl0100004003 is sheet 1, row 4, col 3 */ function parseSheetRowCol(sName, args) { var tmpStr; // validate if(sName.length != 16) { args = null; return; } // remove the first 3 letters: tmpStr = sName.substr(2); // parse out the sheet, row, col: args[0] = Number(sName.substr(sName.indexOf("S")+1, 2)); args[1] = Number(sName.substr(sName.indexOf("R")+1, 5)); args[2] = Number(sName.substr(sName.indexOf("C")+1, 3)); } /** * Called to mark the sheet ready for recalc. */ function markDirty() { if(!dirty) { document.title = document.title +"*"; var btn = document.getElementById("recalcBtn"); btn.style.color='#FF0000'; btn.style.fontWeight="bold"; dirty = true; } } /** * Called to mark the sheet clean - just been recalculated. */ function markClean() { if(dirty) { document.title = document.title.substring(0, document.title.indexOf("*")); var btn = document.getElementById("recalcBtn"); btn.style.color='#FFFFFF'; btn.style.fontWeight="normal"; dirty = false; } } /** * onBlur of a text field - reformat the field */ function reformat(component) { //if (component != getCalcForm().elements[FIRST_ELEMENT]) markDirty(); //perform simplistic formatting var tmpStr = component.value; var format = Math.abs(component.format); switch(format) { case FMT_NUMERIC: break; case FMT_CURRENCY: tmpStr = formatCurrency(tmpStr); break; case FMT_PERCENT: tmpStr = formatPercent(tmpStr); break; case FMT_DATE: break; case FMT_TIME: break; } component.value = tmpStr; } /** * When a user clicks on an input box, unformat the input so the user changes the underlying value. */ function unformat(component) { component.value = getUnformattedValue(component); component.select(); } function getUnformattedValue(component) { var tmpStr = component.value; var format = Math.abs(component.format); switch(format) { case FMT_NUMERIC: tmpStr = removeCommas(tmpStr); break; case FMT_CURRENCY: tmpStr = unformatCurrency(tmpStr); break; case FMT_PERCENT: tmpStr = unformatPercent(tmpStr); break; case FMT_DATE: break; case FMT_TIME: break; } return tmpStr; } /** * Validates that a string contains only valid numbers. */ function validateNumeric(sVal) { // -9. or 99. | -9 or 99 | -.9 or .99 | w/ commas... var objRegExp = /(^-?\d\d*\.\d*$)|(^-?\d\d*$)|(^-?\.\d\d*$)|^-?\d{1,3}(,\d{3})*\.?\d*$/; return objRegExp.test(sVal); } /** * Validates that a string contains only valid currency. */ function validateCurrency(sVal) { //negative sign, decimals, or () negative markers are optional //var objRegExp = /^-?\$\(?\d{1,3}(,\d{3})*\.?\d*\)?$/; // internationalization for $,Ł,€,Ą var objRegExp = /^-?\$|\Ł|\€|\Ą\(?\d{1,3}(,\d{3})*\.?\d*\)?$/; return objRegExp.test(sVal); } /** * Validates that a string is a percentage */ function validatePercent(sVal) { // -9. or 99. | -9 or 99 | -.9 or .99 | w/ commas... var objRegExp = /^-?\d{1,3}(,\d{3})*\.?\d*%$/; return objRegExp.test(sVal); } /** * Removes currency formatting from a string. */ function unformatCurrency(sVal) { var objRegExp = /\(/; var sMinus = ''; //check if negative if(objRegExp.test(sVal)) sMinus = '-'; //objRegExp = /\$|\)|\(|[,]/g; // internationalization for $,Ł,€,Ą objRegExp = /\$|\Ł|\€|\Ą|\)|\(|[,]/g; sVal = sVal.replace(objRegExp,''); return sMinus + sVal; } /** * Removes commas from a string. */ function removeCommas(sVal) { var objRegExp = /,/g; // global search for commas //replace all matches with empty strings return sVal.replace(objRegExp,''); } /** * Removes percent formatting from a string */ function unformatPercent(sVal) { // remove the % sign and divide by 100 if(sVal.indexOf("%") != -1) { sVal = sVal.replace("%",""); sVal = sVal/100; } return sVal; } /** * Adds percent formatting to a string while rounding to a specified # of decimal places. */ function formatPercent(sVal) { return sVal*100 + "%"; } /** * Adds currency formatting to a string. */ function formatCurrency(sVal) { var objRegExp = /-?\d+\.\d{2}$/; // no decimals: var objRegExp2 = /-?\d$/; if(objRegExp.test(sVal) || objRegExp2.test(sVal)) { objRegExp.compile('^-'); sVal = addCommas(sVal); if (objRegExp.test(sVal)) { sVal = '(' + sVal.replace(objRegExp,'') + ')'; } // internationalization: // if(sVal.indexOf('$') < 0) // sVal = '$' + sVal; return sVal; } else return sVal; } /** * Add commas to a number */ function addCommas(sVal) { var parts = sVal.split('.'); sVal = parts[0]; var objRegExp = /(-?\d+)(\d{3})/; // loop backward through string & check for RegExp matches while(objRegExp.test(sVal)) { //replace original string with first group match, //a comma, then second group match sVal = sVal.replace(objRegExp, '$1,$2'); } if(parts[1] != undefined) sVal = sVal + "." + parts[1]; return sVal; } function addCommasX(sVal) { var number = '' + sVal; if(number.length > 3) { var mod = number.length % 3; var output = (mod > 0 ? (number.substring(0, mod)) : ''); var len = Math.floor(number.length / 3); for(i=0 ; i < len; i++) { if ((mod == 0) && (i == 0)) output += number.substring(mod+ 3 * i, mod + 3 * i + 3); else output+= ',' + number.substring(mod + 3 * i, mod + 3 * i + 3); } return(output); } else return number; } /************************************************************************/ /** SPECIAL METHODS */ /************************************************************************/ function KD_HideSheet(sheet, cond) { //alert("hide sheet: " + sheet + ": " + cond); var adiv = document.getElementById(sheet); if (cond == true) adiv.style.display = "none"; else adiv.style.display = ""; }