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

  • 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)
  • Replied 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!
  • Replied 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
  • Replied 9 November 2020, 7:05 pm EST

    Sample code is giving message "License not found". can you check?
  • Replied 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
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels