pivottable
pivottable copied to clipboard
nicolaskruchten
Renderer Table to CSV
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;
}
"TSV Export" renderer provide tab separated values which could be copy-pasted.
Interesting. How does this deal with very large output? My understanding was that data URIs had an upper limit on length?
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>`
@nithishanf Can you please give me more details how you implemented this. I am using battatech excel export which have large data export issues.
Hello, i am working with dot net and angular. can you please explain how to use the above coding?
Updated to account for multi-spanned columns and rows not beginning at location 0
Hello, could you please share with me how to code about accounting for multi-spanned columns and rows not beginning at location 0? @smittysmee
