Answers

• rf1234 Posts: 2,994Questions: 87Answers: 421

  January 2018 edited January 2018

  Hi Edwin,

  I took a look at file "DataTables/Buttons .../js/buttons.html5.js"

  According to this: https://datatables.net/reference/button/excelHtml5
  Dollar Currency Values are built-in style 57.

  This is built-in style 57:

  '<xf numFmtId="164" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
  

  As you can see it points to numFmtId 164.
  This is that format id:

  '<numFmt numFmtId="164" formatCode="#,##0.00_-\ [$$-45C]"/>'
  

  I copied this into Excel: #,##0.00_-\ [$$-45C] as a custom cell format.
  and entered 88 this is the result: 88.00 $

  Then I tried num format 166 ([$€-2]\ #,##0.00) which is Euro currency values and entered 88 and got this:
  € 88.00.

  So works as designed I would say ... Question is does it make sense to have such a different rendering. The dollar rendering would be German style with the trailing dollar sign while the Euro rendering would be US style with the leading Euro sign. Opposite rendering would at least make more sense to me ...

  Of course you can edit the buttons.html5.js file accordingly but then it gets overwritten in the next release. I used the customize function to add my own styles that won't get overwritten with a new release like this:

  //custom button for cashflow excel generation
      $.fn.dataTable.ext.buttons.excelCashFlow = {
          extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1', title: cashFlowTitle,
          customize: function( xlsx ) {
  // see built in styles here: https://datatables.net/reference/button/excelHtml5
  // take a look at "buttons.html5.js", search for "xl/styles.xml"
  //styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
  //styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
  //styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
  //styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
  //styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
  //styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
  //on the last line we have the 67 currently built in styles (0 - 66), see link above
              
              var sSh = xlsx.xl['styles.xml'];
              var lastXfIndex = $('cellXfs xf', sSh).length - 1;
      //n1, n2 ... are number formats; s1, s2, ... are styles
              var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
              var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
              var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
              '<alignment horizontal="center"/></xf>';
      //s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
      //just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
              var s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
              var s4 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
              '<alignment horizontal="center" wrapText="1"/></xf>'
              sSh.childNodes[0].childNodes[0].innerHTML += n1;
              sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4;
              
              var fourDecPlaces    = lastXfIndex + 1;
              var greyBoldCentered = lastXfIndex + 2;
              var twoDecPlacesBold = lastXfIndex + 3;
              var greyBoldWrapText = lastXfIndex + 4;
  

  applying some of the styles in the customize function as well:

  //two decimal places columns          
              var twoDecPlacesCols = ['D', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O'];            
              for ( i=0; i < twoDecPlacesCols.length; i++ ) {
                  $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
              }
              $('row c[r^="E"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
  //                $('row c', sheet).attr( 's', '25' ); //for all rows
              $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
              $('row:eq(1) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold, text wrapped
              $('row:last c', sheet).attr( 's', '2' );  //bold
  

  image.png 713B
• wittich Posts: 3Questions: 1Answers: 0

  June 2018

  @rf1234 thx for the post, it helped me a lot to understand the whole thing.
• scottsauber Posts: 1Questions: 0Answers: 0

  July 2018

  This might be super hacky.... but it works... for now!

  "buttons": [
              {
                  extend: 'excelHtml5',
                  customize: function( xlsx ) {
                      $(xlsx.xl["styles.xml"]).find('numFmt[numFmtId="164"]').attr('formatCode', '[$$-45C] #,##0.00_-');
              }}
  ]
  

  To piggy back on what @rf1234 said. The format is originally

  '<numFmt numFmtId="164" formatCode="#,##0.00_-\ [$$-45C]"/>'
  

  So what this is doing is parsing through the styles, finding 164, and prepending the currency on it.