Background color of cell based on condition is not working

Background color of cell based on condition is not working

Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

I am trying to color cells based on few condition in datatable. Here is what I have tried so far. Its working but its giving me wrong results for some cells. Not sure what I am doing wrong here.

Here is my code :

var table = $('#assyntCx_Table').DataTable( {
  dom: "lBfrtip",

//AJAX
//serverSide: true,
processing: true,
ajax: {
    url: "../ajax/at/escinstallschedule.php",
    type: "POST",
    deferRender: true,
},    

    //TABLE WINDOW
    scrollY:        "65vh",
    scrollX:        true,
    scrollCollapse: false,
    paging:         true,
    fixedColumns:   {
      left: 2
    },

    columns: [

        { data: "Station" },

        { data: "DrawingID" },

        { data: "ODStart" },

        { data: "ODFinish" },

        { data: "UDW1Start" },

        { data: "UDW1Finish" },

        { data: "UDW2Start" },

        { data: "UDW2Finish" },

        { data: "UDW3Start" },

        { data: "UDW3Finish" },

        { data: "UDW4Start" },

        { data: "UDW4Finish" },

        { data: "UDW5Start" },

        { data: "UDW5Finish" },

        { data: "U1.username" },

        { data: "LastUpdated" },

    ],

    createdRow: function(row, data, index) {

        // Updated Schedule Week 1 - 07 Mar 22

        if (data.UDW1Start == data.ODStart) {
          $('td:eq(4)', row).css('background-color', 'Yellow');  //Original Date
        }else if (data.UDW1Start > data.ODStart) {
          $('td:eq(4)', row).css('background-color', 'Orange'); // Behind of Original Date
        } else if (data.UDW1Start < data.ODStart) {
          $('td:eq(4)', row).css('background-color', 'Green'); // Ahead of Original Date
        }else{
          $('td:eq(4)', row).css('background-color', 'White');  
        }
},

select: true,

buttons: [],

//PAGINATION OPTIONS
"pageLength": 250,
"lengthMenu": [[50, 100, 250, 500, -1], [50, 100, 250, 500, "All"]],

} );

Here is output picture which I am getting wrong. Here condition is as per below.

if ( updated schedule start == date original start date)
than cell should be "yellow"

if( updated schedule start date > original start date)
than cell should be "Orange"

if( updated schedule start date < original start date)
than cell should be "Green"

in First picture Original start date = 25th may 2022 and updated start date = 1st june 2022.
so it should be "Orange".. but here it display green..

for only few of cells I am getting wrong colors. What I am doing wrong here?

I used this example as reference - http://live.datatables.net/horuxuxo/433/edit

This question has accepted answers - jump to:

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    I suspect the comparisons are being done as strings instead of dates. You might need to use moment.js or some other technique to convert to dates for the comparison. If you still need help please provide a simple test case with an example of your data so we can provide more specific help.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    Hello Kevin here is the test case

    live.datatables.net/ruzilowu/1/edit

    In this test case also I am getting wrong results.. for few cells. Please check.

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    Hello Kevin here is the test case

    live.datatables.net/ruzilowu/1/edit

    @kthorngren
    In this test case also I am getting wrong results.. for few cells. Please check

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0
    edited April 2022


    @kthorngren I tried something like below code. I added moment but still I am not getting yellow color for first condition.. instead getting white only.

            createdRow: function(row, data, index) {
    
    
                    var a = moment(data.UDW1Start);
                    var b = moment(data.ODStart);
    
                    if(a == b){
                      $('td:eq(4)', row).css('background-color', 'Yellow');  //Original Date
                    }else if(a > b){
                      $('td:eq(4)', row).css('background-color', 'Orange'); // Behind of Original Date
                    }else if(a < b){
                      $('td:eq(4)', row).css('background-color', 'Green'); // Ahead of Original Date
                    }else{
                      $('td:eq(4)', row).css('background-color', 'White');  
                    }
            },
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    Answer ✓

    Do you have an updated test case link?

    Have you debugged what the values of a and b are?

    You may want to use the techniques described in this tutorial for comparing the dates.

    Kevin

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    Thank you @kthorngren tutorial link you shared is really helpful.

    I solved the issue as below, if anyone needs answer.

        createdRow: function(row, data, index) {
    
                // Updated Schedule Week 1 - 07 Mar 22
    
                var originalStart = Date.parse(data["ODStart"]);
                var OS = new Date(originalStart);
    
                var Week1Start = Date.parse(data["UDW1Start"]);
                var W1S = new Date(Week1Start);
    
    
    
                if( moment(W1S).isSame(OS)){
                  $('td:eq(4)', row).css({'background-color':'Yellow'});  
                }else if(W1S > OS){
                  $('td:eq(4)', row).css({'background-color':'Orange'}); // Behind of Original Date
                }else if(W1S < OS){
                  $('td:eq(4)', row).css({'background-color':'Green','color':'#FFF'}); // Ahead of Original Date
                }else{
                  $('td:eq(4)', row).css({'background-color':'White'});  //Original Date
                }
    
  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    Hello @kthorngren Now color formatting is working fine but when I export to excel I want to exclude first two row from coloring. I found some similar excel buttons and able to apply colors on cells when exporting to excel but as I have complex header its only exclude first row... how do I set skipped header variable that can skip first two rows in excel ?

    Here is code I used - http://live.datatables.net/ruzilowu/1/edit

    Not sure why Excel button not appear in this test case , but its working fine in my original code.

    Here is picture what I am getting in Excel.

    How do I set first two row as Skippedheader ?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    One way of doing it is like this example here: http://live.datatables.net/jijumeji/1/edit

    There, skippedHeader is a boolean, to determine if the header has been skipped yep so the cell colouring can begin in the table's body. For you, you can make that an integer, and just count up the number of rows you wish to skip,

    Colin

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    @colin yes I did the same way as you shared example.

    so you mean instead of this...var skippedHeader = false;

    it should be something like this - var skippedHeader = 2;

    or var skippedHeader = row[2]; ???

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0
  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    No, I meant more like:

    var skippedHeader = 0;
    
    $('row', sheet).each( function () {
       if (skippedHeader > 2) {
         // do the colouring
       }
       else {
          skippedHeader++;
       }
    }
    

    Colin

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    Thank you @colin .. this will exclude 3 rows.. so I did minor change in count to get what I want.

    var skippedHeader = 0;
    
    $('row', sheet).each( function () {
       if (skippedHeader > 1) {
         // do the colouring
       }
       else {
          skippedHeader++;
       }
    }
    
Sign In or Register to comment.