Set Excel cell style based on DataTable cell class

uomopalese

Im wondering if there's a way to style an excel cell based on DataTables corrisponding cell class.
Example:

 $('row:eq(1) c[r^="B1"]', sheet).hasClass('custom').attr( 's', '5' );

or

$('row c[r^="B"]', sheet).each( function (){
  if ( $('is t', this).hasClass( 'custom ) ) {
    $(this).attr('s', '1');
  }
});

Basically I'm working on a row of cells (more than 30, so I have AA, AB, AC and so on) and i need a method to discriminate some of them to add a different style, let's say the header has 31 cells with calendars day/name as header and i want the colums with Saturday and Sunday to be with a gray background.

This is the datatable:

And this is the excel file so far, i need the Sab and Dom columns to be gray

thank you

allan

If you know that Sab / Dom are always going to be columns I / J / P / Q / etc, then you could base the styling on that. That would mean that you always start the week on the same day.

If that isn't an option, then I fear this is going to be somewhat more tricky, as by the time the customize function runs, you don't have a reference to the cells any more. I think you'd probably need to do a reverse lookup to go from the cell location to the DataTable cell...

Allan

uomopalese

Hi Allan, the table is dinamically builded with php foreach() loop

// for those who may be interested... I get $anno and $mese from $_POST['']
$start = new DateTime($anno.'-'.$mese.'-01');
$end   = new DateTime($anno.'-'.$mese.'-01 + 1 month');
$period = new DatePeriod($start, new DateInterval('P1D'), $end);
foreach($period as $day){
   $giorno = strftime("%a",strtotime($day->format('Y-m-d')));
      echo "<th class='".$giorno."'>".$giorno."</th>";
    }
} );

so the position of the Sab/Dom may vary (in this case you're looking at June monthly table with 30 days, July first occurrence of Sab/Dom will be on colums G/H.

Currently I'm triyng to use this answer from Stackoverflow where I posted the same question.

var cellIndexes = tabellaOre.cells(".Sab, .Dom").indexes();

for (var i = 0; i < cellIndexes.length; i++) {
  var cellIndex = cellIndexes[i];
  var tblRow = cellIndex['row']+4; //changed to my needs
  var tblCol = cellIndex['column']+1;
  var xlCol = String.fromCharCode(64 + tblCol);

  // .... previous styles here....

  $('row c[r^="'+xlCol+tblRow+'"]', sheet).attr('s','11');
}

and this is what i get:

It works, but the loop picks only the first occurrence of each rowspan (merged cells) in the excel file i guess because it fails wit AA, AB, AC
Any idea? Thanks

uomopalese

The solution above was already working, the cells in the Sab,Dom cols in the excel file doesn't have the grey background beacause they are missing the class .Sab, .Dom in the datatable cells (I gave the class only to the first row before to write the code for the whole table and than i forgot that!). The problem still remain for the cells with double letters: AA, AB, AC, AD, AE ...
I found this solution on Stackoverflow .
Mixed together they work as i need, here is my solution:

// solve the problem of AA, AB, AC...

function colName(n) {
  var ordA = 'A'.charCodeAt(0);
  var ordZ = 'Z'.charCodeAt(0);
  var len = ordZ - ordA + 1;
  var s = "";
  while(n >= 0) {
    s = String.fromCharCode(n % len + ordA) + s;
    n = Math.floor(n / len) - 1;
  }
  return s;
}

var cellIndexes = tabellaOre.cells(".Sab, .Dom").indexes();

for (var i = 0; i < cellIndexes.length; i++) {
  var cellIndex = cellIndexes[i];
  var tblRow = cellIndex['row']+4; //changed to my needs
  var tblCol = cellIndex['column']; //removed +1
  // var xlCol = String.fromCharCode(64 + tblCol); changed with follow
  var xlCol = colName(tblCol);

  // .... previous style here...

  $('row c[r='+xlCol+tblRow+']', sheet).attr('s','12');
}

and here is the result:

one last effort would be to style the cells with the thick border with their own style, but for now it's ok, thanks.