jQWidgets Forums
jQuery UI Widgets › Forums › Grid › jqxGrid export to excel – string converted into date
Tagged: jqxGrid export to excel
This topic contains 5 replies, has 2 voices, and was last updated by Peter Stoev 11 years, 10 months ago.
-
Author
-
Hi.
I am trying to export data from a jqxgrid to excel using
$(“#jqxgrid”).jqxGrid(‘exportdata’, ‘xls’, ‘example’).However, the phone numbers (like +91212447622) are converted into date type in the excel file (like 12/13/1971). The jqxGrid column is defined as ‘string’ -{ text: ‘Telefone’, dataField: ‘Telephone’,editable: false, cellsalign: ‘center’, width: 150, type: ‘string’ }.
How can I solve this problem?
Thanks.
Luis Santos
Hi Luis Santos,
Please, provide a sample if you experience an issue and make sure it can be reproduced with the latest version – 3.0.2
Best Regards,
Peter StoevjQWidgets Team
http://www.jqwidgets.com/Yes, the problem remains in the 3.0.2 version.
Description of the example:
Code to format the grid:
$(“#jqxgrid”).jqxGrid({
width: ‘100%’,
height:’100%’,
source: dataAdapter,
theme: ‘classic’,
pageable: true,
autoheight: false,
altrows: true,
enabletooltips: false,
editable: false,
selectionmode: ‘none’,
filterable: true,
sortable: true,
columnsresize: true,
pagesizeoptions: [’20’, ’50’, ‘100’, ‘200’, ‘300’, ‘400’, ‘500’],
autoshowfiltericon: true,
ready: function () {
//addfilter();
var localizationObject = {
filterstringcomparisonoperators: [‘Contains’, ‘Does Not Contain’],
// filter numeric comparison operators.
filternumericcomparisonoperators: [‘Less Than’, ‘Greater Than’],
// filter date comparison operators.
filterdatecomparisonoperators: [‘Less Than’, ‘Greater Than’],
// filter bool comparison operators.
filterbooleancomparisonoperators: [‘Equal’, ‘Not Equal’],
pagergotopagestring: ‘Go to page:’,
pagershowrowsstring: ‘Show rows:’,
pagerrangestring: ‘ of ‘,
pagernextbuttonstring: ‘next’,
pagerpreviousbuttonstring: ‘previous’,
sortascendingstring: ‘Sort Ascending’,
sortdescendingstring: ‘Sort Descending’,
sortremovestring: ‘Remove Sort’,
groupbystring: ‘Group By this column’,
groupremovestring: ‘Remove from groups’,
decimalseparator: ‘.’,
thousandsseparator: ‘,’,
filterclearstring: ‘Clear’,
filterstring: ‘Filter’,
filtershowrowstring: ‘Show rows where:’,
filterorconditionstring: ‘Or’,
filterandconditionstring: ‘And’,
groupsheaderstring: ‘Drag a column and drop it here to group by that column’,
emptydatastring:’No data to display’,
patterns: {
// short date pattern
d: ‘dd/MM/yyyy’
}
}$(“#jqxgrid”).jqxGrid(‘hidecolumn’, ‘DepId’);
$(“#jqxgrid”).jqxGrid(‘localizestrings’, localizationObject);
},
updatefilterconditions: function (type, defaultconditions) {
var stringcomparisonoperators = [‘CONTAINS’, ‘DOES_NOT_CONTAIN’];
var numericcomparisonoperators = [‘LESS_THAN’, ‘GREATER_THAN’];
var datecomparisonoperators = [‘LESS_THAN’, ‘GREATER_THAN’];
var booleancomparisonoperators = [‘EQUAL’, ‘NOT_EQUAL’];
switch (type) {
case ‘stringfilter’:
return stringcomparisonoperators;
case ‘numericfilter’:
return numericcomparisonoperators;
case ‘datefilter’:
return datecomparisonoperators;
case ‘booleanfilter’:
return booleancomparisonoperators;
}
},altrows: true,
columns: [
{ text: ‘Client’, dataField: ‘ClientName’, editable: false,cellsalign: ‘center’ , width: 200, type: ‘string’ },
{ text: ‘Local’, dataField: ‘LocDesig’,editable: false, cellsalign: ‘left’ , type: ‘string’, width:300 },
{ text: ‘Address’, dataField: ‘Address’,editable: false, cellsalign: ‘left’ , width: 200, type: ‘string’ },
{ text: ‘City’, dataField: ‘City’,editable: false, cellsalign: ‘left’, width: 150, type: ‘string’ },
{ text: ‘Telephone’, dataField: ‘Telephone’,editable: false, cellsalign: ‘center’, width: 150, type: ‘string’ },
{ text: ‘Latitude’, dataField: ‘LatitudeString’,editable: false, cellsalign: ‘center’, width: 100, type: ‘string’ },
{ text: ‘Longitude’, dataField: ‘LongitudString’,editable: false, cellsalign: ‘center’, width: 100 , type: ‘string’},{ text: ‘Status’, dataField: ‘StateStr’,editable: false, cellsalign: ‘center’, width: 120, type:’string’ },
{ text: ‘Maintenance Reason’, dataField: ‘MotivoManutencao’,editable: false, cellsalign: ‘left’,width: 150, type:’string’}]
});
Code to load data:
var jsonText = JSON.stringify({ id: id, code: clienteCode, mes: mes, ano:ano, cultura:cultura });
$.ajax({
type: ‘POST’,
dataType: ‘json’,
async: true,
url: ‘/ServiceReports.asmx/’+tipo,
data : jsonText,
cache: false,
contentType: ‘application/json; charset=utf-8’,
success: function (data) {
source.datatype = “json”;
source.localdata = data.d;$(“#jqxgrid”).jqxGrid(‘updatebounddata’);
ResizeGrid();
$(“#jqxgrid”).jqxGrid(‘updatebounddata’);},
error: function (err) {}
});Code to export data to excel:
$(“#jqxgrid”).jqxGrid(‘exportdata’, ‘xls’, exportname);
Phone number in the JSON object:
…
“Telephone”: “+61439109223”,
…
Phone number in the excel file:
…
12/13/1971
…
Note: For “Telephone”: “+61(4)39109223” in the JSON object it works fine in excelLuis Santos
Hi Luis,
I do not see the most important part of this – the initialization of the Source object, the DataFields and the DataAdapter.
Best Regards,
Peter StoevjQWidgets Team
http://www.jqwidgets.com/Hi.
Here is the requested code:
var source = {
datatype: “local”,
ddatafields: [
{ name: ‘ClientName’, type: ‘string’ },
{ name: ‘LocDesig’, type: ‘string’ },
{ name: ‘Address’, type: ‘string’ },
{ name: ‘City’ , type: ‘string’},
{ name: ‘Telephone’ , type: ‘string’},
{ name: ‘LongitudString’, type: ‘string’ },
{ name: ‘LatitudeString’, type: ‘string’ },
{ name: ‘State’, type: ‘string’ },
{ name: ‘StateStr’, type: ‘string’ },
{ name: ‘IMEI’, type: ‘string’ },
{ name: ‘MotivoManutencao’, type: ‘string’ }
],
pagesize: 20,
pager: function (pagenum, pagesize, oldpagenum) {
// callback called when a page or page size is changed.
}
};var dataAdapter = new $.jqx.dataAdapter(source, {
downloadComplete: function (data, status, xhr) { },
loadComplete: function (data) { },
loadError: function (xhr, status, error) { }
});Best regards,
Luis Santos
Hi Luis,
Unfortunately, we cannot reproduce the reported behavior with ver. 3.0.2.
Example:
<!DOCTYPE html><html lang="en"><head> <title id='Description'>With jqxGrid, you can export your data to Excel, XML, CSV, TSV, JSON and HTML.</title> <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" /> <script type="text/javascript" src="../../scripts/jquery-1.10.2.min.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.columnsresize.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdata.export.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.export.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.sort.js"></script> <script type="text/javascript" src="../../scripts/gettheme.js"></script> <script type="text/javascript" src="generatedata.js"></script> <script type="text/javascript"> $(document).ready(function () { var theme = getDemoTheme(); // prepare the data var data = generatedata(100); data[0].firstname = "+91212447622"; data[1].firstname = "+61439109223"; var source = { localdata: data, datatype: "array", datafields: [ { name: 'firstname', type: 'string' }, { name: 'lastname', type: 'string' }, { name: 'productname', type: 'string' }, { name: 'available', type: 'bool' }, { name: 'date', type: 'date' }, { name: 'quantity', type: 'number' }, { name: 'price', type: 'number' } ] }; var dataAdapter = new $.jqx.dataAdapter(source); // initialize jqxGrid $("#jqxgrid").jqxGrid( { width: 670, source: dataAdapter, theme: theme, altrows: true, sortable: true, selectionmode: 'multiplecellsextended', columns: [ { text: 'First Name', datafield: 'firstname', width: 90 }, { text: 'Last Name', datafield: 'lastname', width: 90 }, { text: 'Product', datafield: 'productname', width: 177 }, { text: 'Available', datafield: 'available', columntype: 'checkbox', width: 67, cellsalign: 'center', align: 'center' }, { text: 'Ship Date', datafield: 'date', width: 90, align: 'right', cellsalign: 'right', cellsformat: 'd' }, { text: 'Quantity', datafield: 'quantity', width: 70, align: 'right', cellsalign: 'right' }, { text: 'Price', datafield: 'price', width: 65, cellsalign: 'right', align: 'right', cellsformat: 'c2' } ] }); $("#excelExport").jqxButton({ theme: theme }); $("#xmlExport").jqxButton({ theme: theme }); $("#csvExport").jqxButton({ theme: theme }); $("#tsvExport").jqxButton({ theme: theme }); $("#htmlExport").jqxButton({ theme: theme }); $("#jsonExport").jqxButton({ theme: theme }); $("#excelExport").click(function () { $("#jqxgrid").jqxGrid('exportdata', 'xls', 'jqxGrid'); }); $("#xmlExport").click(function () { $("#jqxgrid").jqxGrid('exportdata', 'xml', 'jqxGrid'); }); $("#csvExport").click(function () { $("#jqxgrid").jqxGrid('exportdata', 'csv', 'jqxGrid'); }); $("#tsvExport").click(function () { $("#jqxgrid").jqxGrid('exportdata', 'tsv', 'jqxGrid'); }); $("#htmlExport").click(function () { $("#jqxgrid").jqxGrid('exportdata', 'html', 'jqxGrid'); }); $("#jsonExport").click(function () { $("#jqxgrid").jqxGrid('exportdata', 'json', 'jqxGrid'); }); }); </script></head><body class='default'> <div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;"> <div id="jqxgrid"></div> <div style='margin-top: 20px;'> <div style='float: left;'> <input type="button" value="Export to Excel" id='excelExport' /> <br /><br /> <input type="button" value="Export to XML" id='xmlExport' /> </div> <div style='margin-left: 10px; float: left;'> <input type="button" value="Export to CSV" id='csvExport' /> <br /><br /> <input type="button" value="Export to TSV" id='tsvExport' /> </div> <div style='margin-left: 10px; float: left;'> <input type="button" value="Export to HTML" id='htmlExport' /> <br /><br /> <input type="button" value="Export to JSON" id='jsonExport' /> </div> </div> </div></body></html>
Best Regards,
Peter StoevjQWidgets Team
http://www.jqwidgets.com/ -
AuthorPosts
You must be logged in to reply to this topic.