Set entire column data type and set background color if cell data type mismatch

Set entire column data type and set background color if cell data type mismatch

Posted by: jekin.desai on 8 November 2020, 7:19 pm EST

• 

  jekin.desai

  • Post Options:
  • Link
    Copy

  Posted 8 November 2020, 7:19 pm EST

  I have 2 scenario:

  1. During loading data in excel: I want to apply/set data type of an entire column. Type can be Date with format, Time with format, String or Number. If the cell value data type doesn’t match then that cell backcolor should be red else default color. (Empty value should be default color)

  2. During editing: Assuming column data type(mentioned above)l is already set and if value is entered in the cell(s) which doesn’t match with the data type then backcolor red should be shown for that cell else default color.(Empty value should be default color)

• 

  jekin.desai

  • Post Options:
  • Link
    Copy

  Posted 8 November 2020, 9:34 pm EST

  data type: boolean type can also be apply/set to column.

  Also, i am using Angular v10, if you can provide sample/demo that would be great!

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 9 November 2020, 6:55 pm EST

  Hi Jekin,

  We are sorry, but this feature is currently not supported by SJS but you could add Date Validator to the sheet and for the Boolean, you may use check box cell type. Please refer to the following code snippet in the attached sample that demonstrated the same.

  
  spread.options.highlightInvalidData = true;
    var dv = GC.Spread.Sheets.DataValidation.createDateValidator(
      GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between,
      new Date(2012, 11, 31),
      new Date(2013, 11, 31)
    );
    dv.showInputMessage(true);
    dv.inputMessage("Enter a date between 12/31/2012 and 12/31/2013.");
    dv.inputTitle("Tip");
    sheet.getCell(-1, 1).validator(dv);
  
    //CheckBox
    var c = new GC.Spread.Sheets.CellTypes.CheckBox();
    sheet.getCell(-1, 2).cellType(c).width(120);
    c.isThreeState(true);
    var state = c.isThreeState();
  
    c.isThreeState(false);
    c.textTrue("Check state");
    c.textFalse("UnCheck state");
  
  

  Sample: https://codesandbox.io/s/serene-driscoll-7cl8c?file=/src/app/app.component.ts

  API References:
  
  createDateValidator:https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.DataValidation~createDateValidator.html

  checkBoxDemo: https://www.grapecity.com/spreadjs/demos/features/cells/cell-types/checkbox#demo_source_name

  Regards
  
  Avinash

• 

  jekin.desai

  • Post Options:
  • Link
    Copy

  Posted 9 November 2020, 7:05 pm EST

  Sample code is giving message “License not found”. can you check?

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 9 November 2020, 9:01 pm EST

  Hi Jekin,

  Looks like the Key link is not working. Please refer to the updated sample and let us know if you face any issues.
  
  sample: https://codesandbox.io/s/serene-driscoll-7cl8c?file=/src/app/app.component.html

  Regards
  
  Avinash