Export to XLSX with customized styles

j e harms

DataTables has some 67 built-in styles for customizing cells in an exported XLSX document.
Is there a way to edit or add to these built-in styles, or to create a customized style that gets added to the stylesheet?

For example, when I export a table and add cells at the bottom to for Subtotal, GST (goods and services tax) and Total, I'd like the amounts in bold and formatted as currency. Using the built-in styles, I can apply bold or currency formatting, but not both.

Also, if I use built-in style 57 (dollar currency values), the '$' appears after the amount (rather than before). When I checked the number formatting in Excel, it appears that the built-in style's currency format uses "$ Cherokee (Cherokee)". I'd like it change it (or add a new style) so that it uses "$ English (Canada)".

Jake (Canada)

F12Magic

It's even possible to replace the complete internal stylesheet provided by the buttons extension.
Working on an example on codepen but it's not finished yet.
If you need to have your own styles, create a excel file with all the styles you need and save it.
Rename the file with a .zip extension. Unpack it. Open the styles.xml, each style xf tag is a style, so the number for it goes from 1 to ... Copy the source to the javascript. That's all there's to it.

j e harms

Thanks for your prompt reply.
Actually, after posting my question, I managed to add to the styles.xml file using the same technique you demonstrated for adding rows to sheet1.xml.

var styleSheet = xlsx.xl['styles.xml'];
var n1 = '<numFmt formatCode="$ #,##0.00" numFmtId="170"/>';
var s1 = '<xf numFmtId="170" borderId="1" fillId="4" fontId="2" applyBorder="1" applyFill="1" applyFont="1" />';
styleSheet.childNodes[0].childNodes[0].innerHTML = styleSheet.childNodes[0].childNodes[0].innerHTML + n1;
styleSheet.childNodes[0].childNodes[5].innerHTML = styleSheet.childNodes[0].childNodes[5].innerHTML + s1;

The variable n1 is the xml code for the number format ($ before the amount). I gave it the ID of 170 because the last ID used in the built-in style sheet is 169.

The variable s1 is the xml code for the modified style that uses the new number format (170), bold text and a light green fill.

Jake

F12Magic

Great. Thx for posting back with this piece of code. It'll be useful to others too.

bQt

Thanks, this was very helpful

rf1234

@bQt : I tried to get the above code from @j e harms working but failed. Did you make it? And if so can you post something please! Many thanks in advance.

bQt

@rf1234

I'm pretty sure I used the code without any editing at first and it was working. The added style is the 68th so using

let sheet = xlsx.xl.worksheets['sheet1.xml'];
$('c[r=B4]', sheet).attr('s', '67')

should do the trick. You can also use custom 'fonts' using styleSheet.childNodes[0].childNodes[1].innerHTML

rf1234

Thanks a lot got it working! Posted a detailed example here: https://datatables.net/forums/discussion/43973/excel-customization-with-4-decimal-places#latest