Renderer Table to CSV

[smittysmee]

If you would, please implement an export option for exporting to CSV from the generated table.

/* Table to CSV - modified from http://stackoverflow.com/questions/16078544/export-to-csv-using-jquery-and-html to work with PivotTable*/
        function exportTableToCSV($table, filename) {
            /* Find all row elements */
            var $rows = $table.find('tr');

            /* CSV Delimiters */
            var colDelim = '","';
            var rowDelim = '"\r\n"';

            /* Setup for the content */
            var csvContent = [];
            var queuedItems = [];

            /* Iterate through the rows */
            for(var i=0; i<$rows.length; i++){
                var row = $rows[i];
                var mappedChildren = [];
                var $row = $(row);
                var $cols = $row.children();

                /* Iterate through the columns */
                for(var j=0; j<$cols.length; j++){
                    var col = $cols[j];
                    var text = $(col).text();
                    var colSpan = col.colSpan;
                    var rowSpan = col.rowSpan;

                    /* Process queued items that match */
                    queuedItems, mappedChildren = blankCheck(queuedItems, i, j, mappedChildren);
                    var baseLength = mappedChildren.length;
                    if (baseLength > 1) {
                        baseLength = baseLength - 1
                    } else {
                        baseLength = 0;
                    }

                    /* Iterate through the row column combo based on the the elements row/col span*/
                    for (var k = 0; k < rowSpan; k++) {
                        for (var l = 0; l < colSpan; l++) {
                            /* Ensure that we have a value... this is the current row especially when it is span 1 for r & c */
                            if (k == 0 & l == 0) {
                                var tempText = text.replace(/"/g, '""');
                                mappedChildren.push(tempText);
                            } else if (k == 0) {
                                /* Spaced column item w/ same row */
                                mappedChildren.push('');
                            } else if (l == 0) {
                                queuedItems.push(createKey(i + k, baseLength + j + l));
                            } else {
                                // These are for multi-span items
                                queuedItems.push(createKey(i + k, j + l));
                            }
                        }
                    }
                }

                /* Add the line to the content array */
                csvContent[i] = mappedChildren.join(colDelim);
            }

            /* Generate the CSV content */
            var csv = '"' + csvContent.join(rowDelim) + '"';

            // Deliberate 'false', see comment below
            if (false && window.navigator.msSaveBlob) {

                var blob = new Blob([decodeURIComponent(csv)], {
                    type: 'text/csv;charset=utf8'
                });

                // Crashes in IE 10, IE 11 and Microsoft Edge
                // See MS Edge Issue #10396033
                // Hence, the deliberate 'false'
                // This is here just for completeness
                // Remove the 'false' at your own risk
                window.navigator.msSaveBlob(blob, filename);
            } else if (window.Blob && window.URL) {
                // HTML5 Blob
                var blob = new Blob([csv], {
                    type: 'text/csv;charset=utf-8'
                });
                var csvUrl = URL.createObjectURL(blob);

                $(this)
                    .attr({
                    'download': filename,
                    'href': csvUrl
                });
            } else {
                // Data URI
                var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);
                $(this)
                    .attr({
                    'download': filename,
                    'href': csvData,
                    'target': '_blank'
                });
            }
        }

        function blankCheck(queuedItems, curRow, curCol, mappedChildren){
            /* Form the key */
            var key = createKey(curRow, curCol);

            /* Check if the index is in the list items*/
            var index = queuedItems.indexOf(key);

            /* Check if we exist */
            if(index != -1){
                /* Get the item */
                var key = queuedItems[index];

                /* Remove the item */
                queuedItems.splice(index, 1);

                /* Process this item */
                mappedChildren.push("");

                /* Call this blankCheck */
                queuedItems, mappedChildren = blankCheck(queuedItems, curRow, curCol+1, mappedChildren);
            }

            return queuedItems, mappedChildren;
        }

        function createKey(curRow, curCol){
            var rtn = curRow+'_'+curCol+'_key';
            return rtn;
        }

[ilyaguy]

"TSV Export" renderer provide tab separated values which could be copy-pasted.

[smittysmee]

This is a functionality that isn't needed to be copy-pasted.

[nicolaskruchten]

Interesting. How does this deal with very large output? My understanding was that data URIs had an upper limit on length?

[nithishanf]

Hi , I tried Export to excel and it is working even for large data.

<script>
    $("#btnExportPivot").on('click', function (e) {
        e.preventDefault();
        var data = $('.pvtRendererArea>table').html();

        $.ajax({
                type: 'POST',
                url: 'yourURL1',
                data: { data: data },
                success: function (result) {
                    window.location = 'yourURL2';
                    e.preventDefault();
                },
                error: function (xhr, textstatus, errorThrown) {
                    alert("An error has occured! Kindly contact administrator.");
                    e.preventDefault();
                }
            })
        })
</script>

I used this js for my MVC application. So the code is: Controller

`[HttpPost]
        [ValidateInput(false)]
        public ActionResult yourURL1(string data)
        {

            TempData["ExportPivotResult"] = data;
            return null;
        }
        public ActionResult yourURL2()
        {
            yourModel objVM = new yourModel();
            objVM.pivotData = Convert.ToString(TempData["ExportPivotResult"]);
            Response.AddHeader("content-disposition", "attachment; filename=PivotSheet.xls");
            Response.ContentType = "application/ms-excel";
            return PartialView(objVM);
        }`

View

`@model yourModel()
<table id="pivot">
    @Html.Raw(Model.pivotData)
</table>`

[RajatUdpr]

@nithishanf Can you please give me more details how you implemented this. I am using battatech excel export which have large data export issues.

[MeriemBH]

Hello, i am working with dot net and angular. can you please explain how to use the above coding?

[smittysmee]

@MeriemBH are you asking me or @nithishanf?

[smittysmee]

Updated to account for multi-spanned columns and rows not beginning at location 0

[Lyn77]

Hello, could you please share with me how to code about accounting for multi-spanned columns and rows not beginning at location 0? @smittysmee