jQuery if excel cell value is negative, color text red

Question

I'm using the Datatables library to render HTML table and on button click output an excel document. Here is the code I currently have:

    $(document).ready( function () {
      $('#mainTable').DataTable({
        fixedHeader: false,
        dom: 'Bfrtip',
        buttons: [
          {
            extend: 'copy', 
            exportOptions: {
              columns: ':not(:first-child)',
              rows: ':visible'
            }
          },
          {
            extend: 'excelHtml5',
            title: 'Profit and Loss Report', 
            messageTop: `Ran on ${(new Date()).toLocaleString()} for period <xsl:value-of select="P_PERIOD_NUM"/> - FY<xsl:value-of select="P_PERIOD_YEAR"/>`,
            messageBottom: `Companies: <xsl:value-of select="P_COMP_CHILD"/> Cost Centers: <xsl:value-of select="P_CC_CHILD_1"/><xsl:value-of select="P_CC_CHILD_2"/>`,
            // Function iterates over each row and applies styling if conditions are met
            customize: function (xlsx) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                var rows = $('row:gt(2)', sheet);
                rows.each(function () {
                  // bold all rows where first cell ends with : (totals, % or revs)
                  if ($('c:first-of-type is t', this).text().endsWith(':')) {$('c', this).attr('s', '2');}
                  // highlight red all rows that start with - (negative numbers)
                  $('c', this).each(function() {
                    if ($('v', this).text().startsWith('-')) {
                        $(this).attr('s', '11');
                    }
                  });
                });
            }
          },
          {
            extend: 'pdfHtml5', 
            title: 'Profit and Loss Report', 
            orientation: 'landscape', 
            pageSize: 'LEGAL',
            messageTop: `Ran on ${(new Date()).toLocaleString()} for period <xsl:value-of select="P_PERIOD_NUM"/> - FY<xsl:value-of select="P_PERIOD_YEAR"/>`,
            messageBottom: `Companies: <xsl:value-of select="P_COMP_CHILD"/> Cost Centers: <xsl:value-of select="P_CC_CHILD_1"/><xsl:value-of select="P_CC_CHILD_2"/>`
          } 
        ],
        "ordering": false,
        paging: false
      });

As you can see I have a function that iterates over every row of the excel file. The first if statement looks for ':' at the end of the first cell's string. Summation rows use this character, so they are bolded.

However, the issue I'm having is with this piece of code:

if ($('v', this).text().startsWith('-')) {
  $(this).attr('s', '11');
}

The if statement works as intended; it selects every cell that starts with - (negative numbers). the if statement's body is where the issue is. I want to use red font to show negative numbers. attribute "s" of value "11" denotes white text and red background. That is as close as I've got. I haven't been able to find anything that actually just sets the text as red.

Edit: I found a list of values for excel's s attribute here in case it is useful: https://datatables.net/reference/button/excelHtml5

Answer 1

Edit: if you only need to have one the red font value, then you can replace one of the preset fonts:

var styles = xlsx.xl['styles.xml'];
// change white font to red for negative numbers
$('fonts font:nth-child(2) color', styles).attr('rgb', 'FFFF0000');
// reference this font with $('c').attr('s', '1');

Okay so I actually managed to make this work. You have to use custom font/fill/borders values. These values are available in the "styles.xml" file. Please see below code:

            customize: function (xlsx) {
                // add in custom font for negative numbers
                var styles = xlsx.xl['styles.xml']
                $('fonts', styles).empty();
                $('fonts', styles).attr('count', '3');
                $('fonts', styles).append(`
                  <font>
                    <sz val="11"/>
                    <color rgb="00000000"/>
                    <name val="Calibri"/>
                    <family val="2"/>
                    <scheme val="minor"/>
                  </font>
                  <font>
                    <b/>
                    <sz val="11"/>
                    <color rgb="00000000"/>
                    <name val="Calibri"/>
                    <family val="2"/>
                    <scheme val="minor"/>
                  </font>
                  <font>
                    <sz val="11"/>
                    <color rgb="FFFF0000"/>
                    <name val="Calibri"/>
                    <family val="2"/>
                    <scheme val="minor"/>
                  </font>
                `);
                $('fills', styles).empty();
                $('fills', styles).attr('count', '2');
                $('fills', styles).append(`
                <fill>
                  <patternFill patternType="none"/>
                </fill>
                <fill>
                  <patternFill patternType="gray125"/>
                </fill>
                `);
                $('borders', styles).empty();
                $('borders', styles).attr('count', '1');
                $('borders', styles).append(`
                <border>
                  <left/>
                  <right/>
                  <top/>
                  <bottom/>
                  <diagonal/>
                </border>
                `);
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                var rows = $('row:gt(2)', sheet);
                rows.each(function () {
                  // bold all rows where first cell ends with : (totals, % or revs)
                  if ($('c:first-of-type is t', this).text().endsWith(':')) {$('c', this).attr('s', '1');}
                  // highlight red all rows that start with - (negative numbers)
                  $('c', this).each(function() {
                    if ($('v', this).text().startsWith('-')) {
                        console.log($('v', this).text());
                        $(this).attr('s', '2');
                    }
                  });
                });
            }

Here I am stripping out the default fonts, fills and borders. I'm adding in three fonts, a standard black font, a bolded black font, and a red font.

I also added in standard fills and borders.

I can then reference these using the "s" attributes. The underlying values are just different.