Achieve same % formatting behavior as excel in spreadjs 11

Achieve same % formatting behavior as excel in spreadjs 11

Posted by: wmeng on 9 April 2018, 10:56 am EST

• 

  wmeng

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 9 April 2018, 10:56 am EST

  In SpreadJs 9, We have request the feature to handle % format same as excel, when the user type 5 in % format cell, it will turn into 5% instead of 500%. The spreadJs support has provided us the following code to achieve the behavior. Now we upgrade to spreadJs 11, the code doesn’t work any more. Could you let me know how to fix them, or there is any other way in spreadJs 11 to achieve the same excel behavior for percentage cell. Thanks!

  //attach % sign in the cell when we editing a cell with percentage format
  
  function gridControl_onEditStarted(sheet, row, column) {
  
  var format = sheet.getFormatter(row, column);
  
  //if the last character in the format is %
  
  if (typeof format !== “string” && format != null) {
  
  format = format.formatCached;
  
  }
  
  if (format != null && format.indexOf(“%”) >=0) {
  
  //This is a percentage format, and SJS have entered edit mode, find the editor from DOM tree.
  
  var $textArea = $(sheet.getParent().getHost()).find(‘textarea[gcuielement=gcEditingInput]’);
  
  var text = $textArea.val();
  
  if (!isNaN(text)) {
  
  //If value is number, adjust text from the textArea, then combine the % to the text.
  
  text = (text === “” ? “” : (+text) * 100) + “%”;
  
  $textArea.val(text);
  
  //Adjust the caret position
  
  $textArea[0].selectionStart = $textArea[0].selectionEnd = text.length - 1;
  
  }
  
  }

      }
  
      //remove the % sign if the input not a number even the cell format as percentage 
      function gridControl_onEditEnded(sheet, row, column, editingText) {
  		var format = sheet.getFormatter(row, column);
          if (typeof format !== "string" && format != null) {
              format = format.formatCached;
          }
          var result = null;
          if (format != null && format.indexOf("%") >= 0) {
              //This is a percentage format, and SJS have entered edit mode, find the editor from DOM tree.
              var $textArea = $(sheet.getParent().getHost()).find('textarea[gcuielement=gcEditingInput]');
             
              var text = editingText;
              if (text != null && text.indexOf("%") === text.length - 1) {
                  // If the value is not a number, just set the text without the "%"
                  text = text.substring(0, text.length - 1);
                  var convertNumber = formatter.convertToNumber(text);
                  if (isNaN(convertNumber)) {
                      result = text;
                      $textArea.val(text);
                  } 
              }
          }
          //if the input is number , return null, if the input is text, return the text without %
          return result;
  
      }
  

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 12 April 2018, 8:02 am EST

  Hello,

  With SpreadJS v11.0 you can create a custom celltype for PercentCellType and achieve the behavior where it does not convert the whole number multiplied by 100.
  
  Please have a look at the attached sample.

  Thanks,
  
  Deepak Sharma

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 20 April 2018, 4:44 am EST

  Here is the attachment:
  
  SpreadJSPercentCellType.zip