Excel export styling -- map from CSS styles

lisarush

For exporting to Excel, I wanted to have the flexibility to map CSS styles to styles applied to the Excel output.
For example, in my data table, I may have certain individual cells highlighted in color to flag erroneous values.
This highlighting is done via a CSS style. What I wanted was the ability to add a configuration option like the
following example:

exportStyles: { cssStyles: ['red_highlight', 'yellow_highlight'],
                excelStyles: [5, 11] }

This would find any cells with the 'red_highlight' CSS style -- and apply Excel style #5.
Then find any cells with 'yellow_highlight' CSS style -- and apply Excel style #11.

I have modified my copy of the source code to implement this. Would you please take a look at it
and think about either a) incorporating this functionality into the source (either this way
or in some better way) -or- b) make any other suggestions.

Thank you for considering. Below is the source I have changed, with appropriate lines marked as 'added' or 'removed'.
Respectfully submitted for your consideration.

---

[On a separate, but similar, vein...
1) I have also added some additional Excel styles to 'excelStrings' to more closely match my needs.
(would be nice to do this in a cleaner way?)
2) Also added another 'else if' to the 'addRow' method to detect strings such as '95%' and encode
in Excel with percentage styling (using one of my new excel styles just mentioned).
]

---

// Get data from the table for export - this is common to a number of plug-in
// buttons so it is included in the Buttons core library
DataTable.Api.register( 'buttons.exportData()', function ( options ) {
   if ( this.context.length ) {
      return _exportData( new DataTable.Api( this.context[0] ), options );
   }
} );


var _exportTextarea = $('<textarea/>')[0];
var _exportData = function ( dt, inOpts )
{
    //   ....

    var body = new Array( rows );    // data of cells
    var bodyDom = new Array( rows ); // added:  DOM (e.g. <td> element) of cells
    var cellCounter = 0;

    for ( var i=0, ien=rows ; i<ien ; i++ ) {
    var row = new Array( columns );
    var rowDom = new Array( columns );  // added

    for ( var j=0 ; j<columns ; j++ ) {
        row[j] = config.format.body( cells[ cellCounter ], i, j, cellNodes[ cellCounter ] );
        rowDom[j] = cellNodes[cellCounter];  // added
        cellCounter++;
    }

    body[i] = row;
    bodyDom[i] = rowDom;  // added
    }

    return {
    header: header,
    footer: footer,
    body:   body,
    bodyDom: bodyDom  // added
    };
}

--------------------------------------------------------
//
// Excel (xlsx) export
//
DataTable.ext.buttons.excelHtml5 = {

  // ...

  action: function ( e, dt, button, config ) {
      // ...

      // var addRow = function ( row ) {   // removed
      var addRow = function ( row, foo, rowDom ) {  // added
         currentRow = rowPos+1;
         rowNode = _createNode( rels, "row", { attr: {r:currentRow} } );
                
         for ( var i=0, ien=row.length ; i<ien ; i++ ) {
        // Concat both the Cell Columns as a letter and the Row of the cell.
        var cellId = createCellPos(i) + '' + currentRow;
        var cell;
        var cellStyle = 0;   // added
                
        // added...
        if ( rowDom !== undefined && config.exportStyles !== undefined ) {
            var domCell = rowDom[i];
            if ( domCell !== undefined ) {
           for ( var idx = 0;  idx < config.exportStyles.cssStyles.length;  idx++ ) {
              var cssStyle   = config.exportStyles.cssStyles[idx];
              var excelStyle = config.exportStyles.excelStyles[idx];
              if ( $(domCell).hasClass(cssStyle) ) {
                 cellStyle = excelStyle;
              }
           }
                }
        }
            // ... end of added section

        if ( row[i] === null || row[i] === undefined ) {
        row[i] = '';
        }
                
        // Detect numbers - don't match numbers with leading zeros or a negative
        // anywhere but the start
        if ( typeof row[i] === 'number' || (
            row[i].match &&
            $.trim(row[i]).match(/^-?\d+(\.\d+)?$/) &&
            ! $.trim(row[i]).match(/^0\d+/) )
        ) {
        cell = _createNode( rels, 'c', {
            attr: {
                    t: 'n',
                r: cellId,
                s: cellStyle  // added
            },
            children: [
                _createNode( rels, 'v', { text: row[i] } )
            ]
        } );
        }
        else {
        // Replace non standard characters for text output
        var text = ! row[i].replace ?
                row[i] :
                row[i].replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, '');
        cell = _createNode( rels, 'c', {
                    attr: {
                        t: 'inlineStr',
                        r: cellId,
                        s: cellStyle  // added
                    },
                    children:{
                        row: _createNode( rels, 'is', {
                            children: {
                                row: _createNode( rels, 't', {
                                    text: text
                                } )
                            }
                        } )
                    }
                } );
            }

            rowNode.appendChild( cell );
        }
        relsGet.appendChild(rowNode);
        rowPos++;
    };

    // ...

    if ( config.header ) {
        addRow( data.header, rowPos );
        $('row c', rels).attr( 's', '2' ); // bold
    }

    for ( var n=0, ie=data.body.length ; n<ie ; n++ ) {
//      addRow( data.body[n], rowPos );  // removed
        addRow( data.body[n], rowPos, data.bodyDom[n] );  // added
    }

    if ( config.footer && data.footer ) {
        addRow( data.footer, rowPos );
//            $('row:last c', rels).attr( 's', '2' ); // bold  // removed; as this un-does our cell styling
    }

        // ...
   },

   // ...
   exportOptions: {},
   exportStyles: { cssStyles: [],  excelStyles [] },  // added
   // ...
};

allan

Hi,

Thanks very much for the suggestion - I like this!

Let me address these two points first, in reverse order :

2) Also added another 'else if' to the 'addRow' method to detect strings such as '95%' and encode in Excel with percentage styling (using one of my new excel styles just mentioned).

In the nightly version of Buttons I've actually added this ability already. I've used an array of objects that contain a regex for matching, style index and an optional formatter method (for example percentage values need to be /100 in the output XML).

The fact that the variable is an array means it is extensible and you'll be able to see I've got a little bit of currency detection as well.

I've been considering making that available via an API, but I'm concerned that it might introduce too much confusion / complexity.

1) I have also added some additional Excel styles to 'excelStrings' to more closely match my needs. (would be nice to do this in a cleaner way?)

The way I've done this myself is to do it in the customize callback and add it directly to the XML documents. Its ugly and a bit of a pain (if you are adding multiple styles you need to keep track of their indexes), but it at least means you don't need to modify the library and it is forwards compatible.

More generally I don't really want the Excel export offered by Buttons to be a comprehensive Excel generation API. For that kind of thing there are other libraries available and often a server-side approach might be even better suited.

The low level XML is available for customisation, but I don't really want to abstract that away into an easy to use API as that would massively increase the size of the library and would be a project in its own right!

Regards,
Allan

lisarush

On #2 (percentage '%' format)... If you've added this, this is plenty sufficient for me. I understand your hesitation to add this to the API.

On #1 (adding excel styles), you said you've done this via the 'customize' callback. Would you give me an example? Totally understand not wanting to start supporting too much different Excel stuff.

Does this mean you're considering adding the CSS-to-Excel style mappings to the code somehow?

allan

There is a very basic example available here. It uses the styles which are built into the excelHtml5 button type.

Expanding upon that what would need to happen is that the styles.xml file be modified with the addition styling options required, in the function. That's the horrible part because you need to read the style information from the DOM (which is slow and cumbersome) and then create the XML for them (which is just plain cumbersome).

Rather than trying to dynamically map them, it would probably be easier to have a set of specific styles that you want for your project and add them into the styling, and assign their indexes, like int he example above to the required cells.

Does this mean you're considering adding the CSS-to-Excel style mappings to the code somehow?

The converse actually I'm afraid. That is beyond the scope of what I think the Buttons library should focus on. What would be approriate here is for there to be a plug-in button type that depends upon another library that will create the Excel file using a nicely defined API rather than needing to mess around with the XML directly.

Regards,
Allan

lisarush

In the 'customize' example you gave (had seen this one before), it uses the text of the cell to determine whether to add the styling. In my case, I'm trying to use the CSS class from the original HTML table. If I'm not mistaken, this information is no longer available to be used in the 'customize' method.

allan

You are correct - that is an interesting dilemma. The CSS class could be determined by the position of each cell, but I wonder if it might be better to pass in some kind of meta information along with the XML file structure to the customize method. I don't want to have it already read classes from the DOM for performance reasons, but perhaps passing in index information would be an option.

Regards,
Allan

lisarush

I added the code where I did, because it was already looping through & should not really affect performance.

hli12017

Hi,
I have the same problem that I need to export the styling in any way.
@allan: could you please provide an example how to

to pass in some kind of meta information along with the XML file structure to the customize method ?

If this cannot be done with Excelbuttons I need to use some serverside solution, because the styling of cells contains important information for the user.
Since my application consists mainly of tables Excelbuttons could save me a lot of time.

allan

The examples here and in the excelHtml5 documentation show how you can use the customize method to modify the XML to suit your needs. You would need to have knowledge of how styling information works in the Open Spreadsheet format, which you would find in the Open Spreadsheet specification. Buttons does not attempt to provide an easy to use abstraction layer for the Excel XML output - that would be a significant project in its own rights!

Allan