Background color of cell based on condition is not working

Shivani Vyas

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

kthorngren

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 Vyas

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 Vyas

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 Vyas

@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');  
                }
        },

kthorngren

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 Vyas

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 Vyas

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 ?

colin

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 Vyas

@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 Vyas

@colin here is the link - live.datatables.net/novohase/1/edit

colin

No, I meant more like:

var skippedHeader = 0;

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

Colin

Shivani Vyas

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++;
   }
}