csvHtml5 Export adding ‌
csvHtml5 Export adding ‌
hemalait
Posts: 12Questions: 3Answers: 0
I am populating data for columns using mData and I need to provide export options CSV and Excel. I had an issue where leading zeros were getting deleted for both. I found a solution for this which works great for Excel but for CSV it is adding ‌ for all the columns which are defined in ColumnDefs. Here is the code snippet of how I have defined datatables and rendering data. I am using DataTables 1.10.9 version.
$('#datatable_inbond')
.dataTable(
{
"sDom" : "<'dt-toolbar'<'col-xs-12 col-sm-6'l><'col-sm-6 col-xs-6 hidden-xs'B>r>"
+ "t"
+ "<'dt-toolbar-footer'<'col-sm-6 col-xs-12 hidden-xs'i><'col-sm-6 col-xs-12'p>>",//dom settings: l=length changing input control(Records per page); T=to enable TableTools; B=Button for Datatable 1.10.9
buttons: [{
extend : 'colvis',
text : '<fmt:message bundle="${msg}" key="DT_oColVisShowHide"/>'
},
{
extend : 'csvHtml5',
text : '<fmt:message bundle="${msg}" key="csv_txt"/>',
exportOptions : { orthogonal : 'sort'}
},{
extend : 'excelHtml5',
text : '<fmt:message bundle="${msg}" key="excel_txt"/>',
exportOptions : { orthogonal : 'sort'}
}],
"paging": true, // Table pagination
"ordering": true, // Column ordering
"info": true, // Bottom left status text
"destroy": true,
sAjaxSource : "/aceinbond/aceInbondPage/getDataTable", //url for ajax; used as sSource in the ajax call below
"fnServerData" : function(sSource, aoData, fnCallback) { //sSource:ajax url; aoData: parameters to POST; fnCallback: function to execute on success
if (btnClick == true) {
//execute ajax call only if btnClick flag is set to true
aoData.push({"name" : "userId","value" : '${context.userId}'});
aoData.push({"name" : "authToken","value" : '${context.token}'});
aoData.push({"name" : "LocationType","value" : LocationType});
aoData.push({"name" : "Site", "value" : site});
aoData.push({"name" : "BeginDate","value" : beginDate});
aoData.push({"name" : "EndDate","value" : endDate});
aoData.push({"name" : "MovementType","value" : movementType});
aoData.push({"name" : "ErrorCode","value" : errorCode});
$.ajax({
"dataType" : 'json',
"type" : "POST",
"url" : sSource,
"data" : aoData,
"cache" : true,
"success" : function(tableData) {
if (tableData.statusCode != "undefined" && tableData.statusCode != "0000") {
$("#errorBlock").text("<fmt:message bundle="${msg}" key="InbondError.DataRetvError" />"+ tableData.statusCode);
$("#errorBlock").show();
$.unblockUI();
} else {
fnCallback(tableData); // draw the table on success
$("headerData").show();
}
recCount = tableData.aaData.length; //get number of records in the table
if (recCount < 1) { //Error handling for Empty table
$("#errorBlock").text("<fmt:message bundle="${msg}" key="InbondError.NoData" />");
$("#errorBlock").show();
}
},
"error" : function(jqXHR,textStatus,errorThrown) {
// Will enter here whenever the server does not respond with a http status in 200 range.
// example 404, 500, .......
// Will also enter here when datatables fails to create the request
if (textStatus == "parsererror") {
if ($('#errorBlock').show() == true) {
} else {
$("#errorBlock").text("<fmt:message bundle="${msg}" key="InbondError.SystemError" />");
}
} else {
if ($('#errorBlock').show() == true) {
} else {
$("#errorBlock").text("<fmt:message bundle="${msg}" key="InbondError.HTTPError" />"+ jqXHR.status+ ": "+ textStatus);
}
}
$("#errorBlock").show();
$.unblockUI();
}
});
btnClick = false;
}else{
var initialData=null;
initialData = {aaData:[], iTotalDisplayRecords:0, iTotalRecords:0, sColumns:null, sEcho:null, statusCode:"0000"};
fnCallback(initialData); // draw the table
}
},
aoColumns : [ //DataTable column definitions in order of display
{"mData" : "carrierCode"}, //0
{"mData" : "flight"}, //1
{"mData" : "arrivalDate"}, //2
{"mData" : "mawb"}, //3
{"mData" : "shipment"}, //4
{"mData" : "packageID"}, //5
{"mData" : "fpoaSite"}, //6
{"mData" : "bondNumber"}, //7
{"mData" : "bdsAirport"},//8
{"mData" : "ogzNumber"}, //9
{"mData" : "errorCode"}//10
],columnDefs: [{
targets:[1,3,4,6,7,9,10],
render: function(data, type, row, meta){
if(type === 'sort'){
return "\u200C" + data ; //force to keep leading zeros.
}
return data ;
}
}],
"preDrawCallback" : function() {
if (btnClick == true) {
$("#wid-id-1").hide();
$.blockUI(); // START BLOCK UI
}
// INITIALIZE THE RESPONSIVE DATATABLES HELPER ONCE.
if (!responsiveHelper_datatable_col_reorder) {
responsiveHelper_datatable_col_reorder = new ResponsiveDatatablesHelper(
$('#datatable_inbond'),
breakpointDefinition);
}
},
"rowCallback" : function(nRow,aData) {
responsiveHelper_datatable_col_reorder.createExpandIcon(nRow);
return nRow;
},
"fnDrawCallback": function( oSettings ) {
// END BLOCK UI
$.unblockUI();
// SHOW WIDGET GRID
$("#wid-id-1").show();
},
"fnInitComplete" : function(oSettings, json) {
// END BLOCK UI
$.unblockUI();
// SHOW WIDGET GRID
$("#wid-id-1").show();
},
"oLanguage" : { //Language used in the DataTable
"sEmptyTable" : "<fmt:message bundle="${msg}" key="DT_emptytable" />",
"sInfo" : "<fmt:message bundle="${msg}" key="DT_sInfo" />",
"sInfoEmpty" : "<fmt:message bundle="${msg}" key="DT_sInfoEmpty" />",
"sLoadingRecords" : "<fmt:message bundle="${msg}" key="DT_sLoadingRecords" />",
"sZeroRecords" : "<fmt:message bundle="${msg}" key="DT_sZeroRecords" />",
"sProcessing" : "<fmt:message bundle="${msg}" key="DT_sProcessing" />",
"oPaginate" : {
"sNext" : "<fmt:message bundle="${msg}" key="DT_sNext" />",
"sPrevious" : "<fmt:message bundle="${msg}" key="DT_sPrevious"/>"
}
},
"oColVis" : {
"buttonText" : "<fmt:message bundle="${msg}" key="DT_oColVisShowHide" />"
}
}); //DataTable Code ends
This discussion has been closed.
Replies
Thanks for your question - however, per the forum rules can you link to a test case showing the issue please. This will allow the issue to be debugged.
Information on how to create a test page, if you can't provide a link to your own page can be found here.
Thanks,
Allan
Hi Allan,
I'm sorry about that, here
http://jsfiddle.net/hemalait/m6zhwsxy/67/
Thanks! You are using a really old version of Buttons (1.0.1). If you use the latest release (1.2.2) it works without issue.
Allan
I think you updated external resources (datatables.buttons.min.js and buttons.html5.min.js) to use 1.2.2 version. When I click CSV I am still getting ‌ in the data columns where I am forcing to retain zeros. http://jsfiddle.net/m6zhwsxy/68/
This is what the CSV file form your example looks like for me.
Allan
Which browser are you using Allan and how are you opening the CSV file?
I was trying on FireFox (version 47.0.1), it still comes up with those characters, I tried Chrome and it is giving me same results like Firefor. When I tried in IE11 nothing is happening when I click on CSV, Excel is opening fine in all three browsers.
Chrome Mac. However, I was opening the file in a text editor, it looks like you are using Excel or similar. I've just tried that and see the extra characters there. I'm not sure why Excel adds that from the CSV file.
I'm not sure what Buttons can do here - you want to retain the leading zeros, but Excel is parsing the file and finding an integer. Perhaps the best you can do is add a regular space at the front.
Allan
Thanks for your question. At firsrt you should set <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> in your page. After that set 'encoding' => 'utf8' in database configure file.