jQWidgets Forums

jQuery UI Widgets Forums Grid jqxGrid export to excel – string converted into date

This topic contains 5 replies, has 2 voices, and was last updated by  Peter Stoev 11 years, 10 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author

  • lsantos
    Participant

    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


    Peter Stoev
    Keymaster

    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 Stoev

    jQWidgets Team
    http://www.jqwidgets.com/


    lsantos
    Participant

    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 excel

    Luis Santos


    Peter Stoev
    Keymaster

    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 Stoev

    jQWidgets Team
    http://www.jqwidgets.com/


    lsantos
    Participant

    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


    Peter Stoev
    Keymaster

    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 Stoev

    jQWidgets Team
    http://www.jqwidgets.com/

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.