Bootstrap DataTable Sparkline Get data from comma separated string instead of array

user_11026397 0
Viewed 47

I'm using a Bootstrap DataTable component and I'm able to get data from my db and showing it on my Asp.net Core MVC App. Now in a column I want to add a sparkline,the component requires the sparkline column to be an array of values but I have a comma separated string and I need help to understand how to change the JS in order to use my values type.

To make it more simple I created a new App without DB and using data defined directly in the JS as shown in this example: https://datatables.net/examples/basic_init/stocks.html

This works perfectly.

Now I have to change the value in "last" from:

  var stock_data = [
        {
            "name": "ACME Gadgets",
            "symbol": "AGDTS",
            "last": [2.57, 2.54, 2.54, 2.56, 2.57, 2.58, 2.59]
        },
.....
]

To:

  var stock_data = [
        {
            "name": "ACME Gadgets",
            "symbol": "AGDTS",
            "last": "2.57, 2.54, 2.54, 2.56, 2.57, 2.58, 2.59"
        },
.....
]

This is the js that needs to be changed:

$(document).ready(function () {
    debugger;
    var stock_data = [
        {
            "name": "ACME Gadgets",
            "symbol": "AGDTS",
            "last": [2.57, 2.54, 2.54, 2.56, 2.57, 2.58, 2.59]
        },
        {
            "name": "Spry Media Productions",
            "symbol": "SPMP",
            "last": [1.12, 1.11, 1.08, 1.08, 1.09, 1.11, 1.08]
        },
        {
            "name": "Widget Emporium",
            "symbol": "WDEMP",
            "last": [3.40, 3.39, 3.46, 3.51, 3.50, 3.48, 3.49]
        },
        {
            "name": "Sole Goodman",
            "symbol": "SGMAN",
            "last": [16.20, 16.40, 16.36, 16.35, 16.61, 16.46, 16.19]
        },
        {
            "name": "Stanler Bits and Bobs",
            "symbol": "SBIBO",
            "last": [82.51, 83.47, 83.40, 83.68, 83.81, 83.29, 83.72]
        }
    ];

    let table = $('#example').DataTable({
        ajax: function (dataSent, callback, settings) {
            let data = this.api().ajax.json();
            if (data == undefined) {
                data = stock_data;
            } else {
                data = data.data;
                for (i = 0; i < data.length; i++) {
                    data[i].last.push(data[i].last.shift())
                }
            }

            callback({ data: data });
        },
        paging: false,
        initComplete: function () {
            let api = this.api();
            setInterval(function () {
                api.ajax.reload();
            }, 5000);
        },
        drawCallback: function () {
            $('.sparkline')
                .map(function () {
                    return $('canvas', this).length ? null : this;
                })
                .sparkline('html', {
                    type: 'line',
                    width: '250px'
                })
        },
        columns: [
            {
                data: 'name'
            },
            {
                data: 'symbol'
            },
            {
                data: null,
                render: function (data, type, row, meta) {
                    return row.last[row.last.length - 1].toFixed(2);
                }
            },
            {
                data: null,
                render: function (data, type, row, meta) {
                    var val = (row.last[row.last.length - 1] - row.last[row.last.length - 2]).toFixed(2);
                    var colour = val < 0 ? 'red' : 'green'
                    return type === 'display' ?
                        '<span style="color:' + colour + '">' + val + '</span>' :
                        val;
                }
            },
            {
                data: 'last',
                render: function (data, type, row, meta) {
                    return type === 'display' ?
                        '<span class="sparkline">' + data.toString() + '</span>' :
                        data;
                }
            }
        ]
    });
});

I tried to play around using some .split(", ") in the "data: 'last',render" but with no luck, also it is very difficult for me to debug the js (in site.js), I tried to put some console.log but nothing gets written in the output.

UPDATE Thanks to @andrewJames now my code is working fine and for some reason it doesn't even need any data trasformation, it's like automatically converting the csv string to the array needed.

I added also some other functionalities as grouping by a columns and transform datetime disply format with moment.js

Here is the full code:

$(document).ready(function () {
    var groupColumn = 0;

    let table = $('#example').DataTable({
        responsive: true,
        autoWidth: true,
        processing: true,
        ordering: true,
        scrollY: '50vh',
        scrollCollapse: true,
        paging: false,
        searching: true,

        "ajax": {
            "url": "api/ApartmentsAvailables",
            "type": "GET",
            "contentType": "application/json; charset=utf-8",
            "dataType": "json",
        },
        columnDefs: [{ visible: false, targets: groupColumn },

        {
            targets: 7,
            render: DataTable.render.datetime('YYYY-MM-DDT00:00:00', 'MMMM D, YYYY', 'en'),
        },
        { responsivePriority: 1, targets: 0 },

        ],
        order: [[groupColumn, 'asc']],
        
        drawCallback: function (settings) {
            $('.sparkline')
                .map(function () {
                    return $('canvas', this).length ? null : this;
                })
                .sparkline('html', {
                    type: 'line',
                    width: '250px'
                })

            var api = this.api();
            var rows = api.rows({ page: 'current' }).nodes();
            var last = null;

            api
                .column(groupColumn, { page: 'current' })
                .data()
                .each(function (group, i) {
                    if (last !== group) {
                        $(rows)
                            .eq(i)
                            .before('<tr class="group" style="background-color:DarkGray; text-align:center;font-weight: bold; color:white;"><td  colspan="8">' + group + '</td></tr>');

                        last = group;
                    }
                })
        },
        columns: [

            { "data": "building" },
            { "data": "floor_Plan" },
            { "data": "apt_Number" },
            { "data": "rent" },
            { "data": "bedrooms" },
            { "data": "bathrooms" },
            { "data": "sqft" },
            { "data": "available_Date" },
            {
                data: 'prices',
                render: function (data, type, row, meta) {

                    return type === 'display' ?
                        '<span class="sparkline">' + data.toString() + '</span>' :
                        data;
                }
            },
        ]
    });
    new $.fn.dataTable.FixedHeader(table);
    // Order by the grouping
    $('#example tbody').on('click', 'tr.group', function () {
        var currentOrder = table.order()[0];
        if (currentOrder[0] === groupColumn && currentOrder[1] === 'asc') {
            table.order([groupColumn, 'desc']).draw();
        } else {
            table.order([groupColumn, 'asc']).draw();
        }
    });
});

The only issue I'm facing now is that once I enable responsive: true, the sparkline column becomes hidden and when I click to expand the row to show the hidden columns it shows the whole array of Value and not the sparkline.

I guess that the

drawCallback: function (settings) {
            $('.sparkline')
                .map(function () {
                    return $('canvas', this).length ? null : this;
                })
                .sparkline('html', {
                    type: 'line',
                    width: '250px'
                })

is not able to be applied to column that is hidden.

Without the responsive option the html generated for the td is:

<td>
    <span class="sparkline">
        <canvas style="display: inline-block; width: 250px; height: 21px; vertical-align: top;"
                width="250"
                height="21"/>
    </span>
</td>

With the responsive set to true:

<td style="display: none;"
    class="dtr-hidden">
    <span class="sparkline">3446,3446,3416,3416,3416,3546,3546,3546,3546,3546,3546,3561,3556,3551,3396,3396,3396,3346,3306,3306,3306</span>
</td>

I presume that I should somehow capture the mouse click on the expand icon and then re-inject the canvas but I don't know how to do that.

bootstrap-5 asp.net-core-3.1 datatable javascript
2 Answers
user_198318530

You can convert each string containing comma-separated numbers back to an array of numbers using this:

for(i = 0; i < data.length; i++) {
    data[i].last = data[i].last.split(",").map(element => {
        return Number(element);
    });
}

This performs a standard split() - but that gives you string values in the array. So we also use the map() function to convert each mumber-as-string back to a number.

Where to place this?

In your code in the question, it goes after this line:

data = stock_data;

Here is a self-contained demo:

$(document).ready(function() {
    var stock_data = [
        {
            "name": "ACME Gadgets",
            "symbol": "AGDTS",
            "last": "2.57, 2.54, 2.54, 2.56, 2.57, 2.58, 2.59"
        },
        {
            "name": "Spry Media Productions",
            "symbol": "SPMP",
            "last": "1.12, 1.11, 1.08, 1.08, 1.09, 1.11, 1.08"
        },
        {
            "name": "Widget Emporium",
            "symbol": "WDEMP",
            "last": "3.40, 3.39, 3.46, 3.51, 3.50, 3.48, 3.49"
        },
        {
            "name": "Sole Goodman",
            "symbol": "SGMAN",
            "last": "16.20, 16.40, 16.36, 16.35, 16.61, 16.46, 16.19"
        },
        {
            "name": "Stanler Bits and Bobs",
            "symbol": "SBIBO",
            "last": "82.51, 83.47, 83.40, 83.68, 83.81, 83.29, 83.72"
        }
    ];
 
    let table = $('#example').DataTable({
        ajax: function(dataSent, callback, settings) {
            let data = this.api().ajax.json();
            if(data == undefined) {
                data = stock_data;
                for(i = 0; i < data.length; i++) {
                    data[i].last = data[i].last.split(",").map(element => {
                        return Number(element);
                    });
                }
            } else {
                data = data.data;
                for(i = 0; i < data.length; i++) {
                    data[i].last.push(data[i].last.shift())
                }
            }

            callback({data: data});
        },
        paging: false,
        initComplete: function() {
            let api = this.api();
            setInterval(function() {
                api.ajax.reload();
            }, 5000);
        },
        drawCallback: function() {
            $('.sparkline')
                .map(function() {
                    return $('canvas', this).length ? null : this;
                })
                .sparkline('html', {
                    type: 'line',
                    width: '250px'
                })
        },
        columns: [
            {
                data: 'name'
            },
            {
                data: 'symbol'
            },
            {
                data: null,
                render: function(data, type, row, meta) {
                    return row.last[row.last.length - 1].toFixed(2);
                }
            },
            {
                data: null,
                render: function(data, type, row, meta) {
                    var val = (row.last[row.last.length - 1] - row.last[row.last.length - 2]).toFixed(2);
                    var colour = val < 0 ? 'red' : 'green'
                    return type === 'display' ?
                        '<span style="color:' + colour + '">' + val + '</span>' :
                        val;
                }
            },
            {
                data: 'last',
                render: function(data, type, row, meta) {
                    return type === 'display' ?
                        '<span class="sparkline">' + data.toString() + '</span>' :
                        data;
                }
            }
        ]
    });
});
<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>  
  
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-sparklines/2.1.2/jquery.sparkline.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display nowrap" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Symbol</th>
                <th>Price</th>
                <th>Difference</th>
                <th>Last</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Symbol</th>
                <th>Price</th>
                <th>Difference</th>
                <th>Last</th>
            </tr>
        </tfoot>
    </table>

</div>

</body>
</html>

Update

Regarding the comment: If you are actually using the following as your real data source...

ajax: "api/ApartmentsAvailables"

...then you can adapt the same logic as above.

To do this you will need to expand your DataTables ajax option to use the DataTables dataSrc option. Using this option allows you to pre-process the JSON you receive from the Ajax URL - and convert those comma-separated strings back to arrays of numbers, using basically the same logic as I used above.

"ajax": {
    "url": "api/ApartmentsAvailables",
    "dataSrc": function ( json ) { // the JSON containing strings

      // transform the received JSON here...
  
      return json;  // the updated JSON containing arrays
    }
}

You can console.log( json ) if you need to double-check the exact structure of the JSON before and after you have transformed it.

I don't know the structure of the JSON you are receiving from your URL, so you may need to adjust my original code to fit your actual JSON. Give it a try and if you get stuck you can ask a new question - but there are a lot of questions about dataSrc on Stack Overflow.

Here is the official documentation: ajax.dataSrc. As you can see from my example above, you need to look at the syntax and examples for the function syntax.

user_125673650
Related