DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Export to XLSX with customized styles

Export to XLSX with customized styles

j e harmsj e harms Posts: 7Questions: 3Answers: 0
in Buttons

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)

This question has an accepted answers - jump to answer

Answers

  • F12MagicF12Magic Posts: 109Questions: 0Answers: 28

    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 harmsj e harms Posts: 7Questions: 3Answers: 0

    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

  • F12MagicF12Magic Posts: 109Questions: 0Answers: 28
    Answer ✓

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

  • bQtbQt Posts: 4Questions: 1Answers: 0

    Thanks, this was very helpful

  • rf1234rf1234 Posts: 2,991Questions: 87Answers: 421

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

  • bQtbQt Posts: 4Questions: 1Answers: 0
    edited August 2017

    @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

  • rf1234rf1234 Posts: 2,991Questions: 87Answers: 421

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

This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.