jQuery UI Widgets Forums Grid Grid export to excel with filename dialog

This topic contains 6 replies, has 2 voices, and was last updated by  paulcobben 3 years ago.

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

  • paulcobben
    Participant

    Hi,

    Is it possible to export to excel from grid with an extra save as dialog box.
    So you can type in a filename to save to.

    Kind regards,
    Paul Cobben


    Yavor Dashev
    Participant

    Hi Paul,

    I have created a code example which demonstrates you how to achieve this kind of functionality:

     <script type="text/javascript">
            $(document).ready(function () {
                // prepare the data
                var data = generatedata(100);
                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
                $("#grid").jqxGrid(
                {
                    width: getWidth('Grid'),
                    source: dataAdapter,                
                    altrows: true,
                    sortable: true,
                    selectionmode: 'multiplecellsextended',
                    columns: [
                      { text: 'First Name', datafield: 'firstname', width: 130 },
                      { text: 'Last Name', datafield: 'lastname', width: 130 },
                      { text: 'Product', datafield: 'productname', width: 200 },
                      { text: 'Available', datafield: 'available', columntype: 'checkbox', width: 67, cellsalign: 'center', align: 'center' },
                      { text: 'Ship Date', datafield: 'date', width: 120, align: 'right', cellsalign: 'right', cellsformat: 'd' },
                      { text: 'Quantity', datafield: 'quantity', width: 70, align: 'right', cellsalign: 'right' },
                      { text: 'Price', datafield: 'price', cellsalign: 'right', align: 'right', cellsformat: 'c2' }
                    ]
                });
                $("#openDial").jqxButton()
                $("#excelExport").jqxButton();
    
                $("#excelExport").click(function () {
                    var value = $("#input").val();
                   $("#grid").jqxGrid('exportdata', 'xlsx', value);           
                });
    
                $('#openDial').click(function () {
                    $('#jqxwindow').jqxWindow('open');
                });
              
                $("#jqxwindow ").jqxWindow({ height:250, width: 250, okButton: $('#excelExport')});
                $('#jqxwindow').jqxWindow('close');
                $("#input").jqxInput({ width: '250px', height: '25px', placeHolder: 'Choose file name'});
            });
        </script>
    </head>
    <body class='default'>
        <div id='jqxwindow'>
            <div>Header</div>
           <div>  
             
            <input type="text" id="input"/>
            <input type="button" value="Export to Excel" id='excelExport' />       
            </div>
        </div>
        <input type="button" value="Open Dialog" id='openDial' />
        </div>
            <div id="grid"></div>
    </body>
    </html>

    Quick explanantion of the code snippet- we use jqxWindow for creating the dialog box and after the user inputs a value in the jqxInput we use that value for the name of the file.

    Let me know if that works for you!

    Please, do not hesitate to contact us if you have any additional questions.

    Best Regards,
    Yavor Dashev
    jQWidgets team
    https://www.jqwidgets.com


    paulcobben
    Participant

    Hi Yavor,

    I’ve tried your script, but can not get a working page.
    jqwidgetssample

    <html>
    <head>
        <meta charset="UTF-8">
        <title>Dashboard Project Forecast</title>
        <meta name="description" content="Dashboard Project Forecast" />             
        <link rel="stylesheet" href="css/style.css">
        <link rel="stylesheet" href="jqwidgets/styles/jqx.base.css" type="text/css" />
        <link rel="stylesheet" href="jqwidgets/styles/jqx.metro.css" type="text/css" />
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.5.0/css/font-awesome.min.css">
        <script type="text/javascript" src="scripts/jquery-1.11.1.min.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxdata.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/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/jqxgrid.sort.js"></script> 
        <script type="text/javascript" src="jqwidgets/jqxlistbox.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxdropdownlist.js"></script>
        <script type="text/javascript" src="scripts/demos.js"></script>
        <script type="text/javascript">
        $(document).ready(function () {
            // prepare the data
            var data = generatedata(100);
            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
            $("#grid").jqxGrid(
            {
                width: getWidth('Grid'),
                source: dataAdapter,                
                altrows: true,
                sortable: true,
                selectionmode: 'multiplecellsextended',
                columns: [
                  { text: 'First Name', datafield: 'firstname', width: 130 },
                  { text: 'Last Name', datafield: 'lastname', width: 130 },
                  { text: 'Product', datafield: 'productname', width: 200 },
                  { text: 'Available', datafield: 'available', columntype: 'checkbox', width: 67, cellsalign: 'center', align: 'center' },
                  { text: 'Ship Date', datafield: 'date', width: 120, align: 'right', cellsalign: 'right', cellsformat: 'd' },
                  { text: 'Quantity', datafield: 'quantity', width: 70, align: 'right', cellsalign: 'right' },
                  { text: 'Price', datafield: 'price', cellsalign: 'right', align: 'right', cellsformat: 'c2' }
                ]
            });
            $("#openDial").jqxButton()
            $("#excelExport").jqxButton();
    
            $("#excelExport").click(function () {
                var value = $("#input").val();
               $("#grid").jqxGrid('exportdata', 'xlsx', value);           
            });
    
            $('#openDial').click(function () {
                $('#jqxwindow').jqxWindow('open');
            });
          
            $("#jqxwindow ").jqxWindow({ height:250, width: 250, okButton: $('#excelExport')});
            $('#jqxwindow').jqxWindow('close');
            $("#input").jqxInput({ width: '250px', height: '25px', placeHolder: 'Choose file name'});
        });
    </script>
    </head>
    <body class='default'>
    <div id='jqxwindow'>
        <div>Header</div>
       <div>  
         
        <input type="text" id="input"/>
        <input type="button" value="Export to Excel" id='excelExport' />       
        </div>
    </div>
    <input type="button" value="Open Dialog" id='openDial' />
    </div>
        <div id="grid"></div>
    </body>
    </html>

    Yavor Dashev
    Participant

    Hi paulcobben,

    The reason for that your code is not working is because you need to include all the necessary scripts for the window and exporting etc. like so:

    
    <head>
        <title id='Description'>With jqxGrid, you can export your data to Excel, XML, CSV, TSV, JSON, PDF and HTML.</title>
    	<meta name="description" content="jQuery Grid Data Export to Excel, PDF, XML, CSV, TSV, JSON and HTML" /> 	
        <link rel="stylesheet" href="jqwidgets/styles/jqx.base.css" type="text/css" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1 maximum-scale=1 minimum-scale=1" />
        <script type="text/javascript" src="scripts/jquery-1.12.4.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.sort.js"></script> 
        <script type="text/javascript" src="jqwidgets/jqxwindow.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxgrid.storage.js"></script> 
        <script type="text/javascript" src="jqwidgets/jqxexport.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxinput.js"></script>
        <script type="text/javascript" src="scripts/jszip.min.js"></script>
        <script type="text/javascript" src="scripts/demos.js"></script>
        <script type="text/javascript" src="generatedata.js"></script>
     	

    Please, do not hesitate to contact us if you have any additional questions.

    Best Regards,
    Yavor Dashev
    jQWidgets team
    https://www.jqwidgets.com


    paulcobben
    Participant

    Hi I’ve tried your solution in my own code and added it for only the Export to Excel function.
    When I load the page in my browser, the placeholder text is not shown and there is a open dialog button that does nothing.
    When I type text in the input box, and click “Export to Excel” it saves with the filename that was given.
    But the Open Dialog does not work. I think we are close to a solution. Thanks in Advance.

    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <title>Licentie Overzicht</title>
        <meta name="description" content="Licentie Overzicht" />             
        <link rel="stylesheet" href="jqwidgets/styles/jqx.base.css" type="text/css" />
        <link rel="stylesheet" href="jqwidgets/styles/sparkleflowdash.css" type="text/css">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.5.0/css/font-awesome.min.css">
        <script type="text/javascript" src="scripts/jquery-1.11.1.min.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxcore.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/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/jqxgrid.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxgrid.export.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/jqxgrid.filter.js"></script> 
        <script type="text/javascript" src="jqwidgets/jqxgrid.sort.js"></script> 
        <script type="text/javascript" src="jqwidgets/jqxlistbox.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxdropdownlist.js"></script>
        <script type="text/javascript" src="jqwidgets/jqxexport.js"></script> 
        <!-- JSZip -->
        <script type="text/javascript" src="scripts/jszip.min.js"></script>
        <!-- pdfmake -->
        <script type="text/javascript" src="scripts/pdfmake.min.js"></script>
        <script type="text/javascript" src="scripts/vfs_fonts.js"></script>
        <script type="text/javascript" src="scripts/demos.js"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                var cellclass = function (row, columnfield, value) {
                    if (value < 0) {
                        return 'red';
                    }
                    if (value == 0) {
                        return 'yellow';
                    }
                    if (value > 0) {
                        return 'green';
                    }
                }
     
                var source =
                {
                    datatype: "csv",
                    datafields: [
                        { name: 'applicatie',   type: 'string' },
                        { name: 'licentie',     type: 'float'},
                        { name: 'usersad',      type: 'float'},
                        { name: 'verschil1',    type: 'float'},
                        { name: 'installaties', type: 'float'},
                        { name: 'verschil2',    type: 'float'},
                        { name: 'gebruik',      type: 'float'},
                        { name: 'verschil3',     type: 'float'}
                        
                    ],
                    sortcolumn: 'verschil1',
                    sortdirection: 'asc',
                    url: "sparkleflow-data/licentieall.csv",
                    columnDelimiter: ";"
                    
                };
                var dataAdapter = new $.jqx.dataAdapter(source);
                $("#jqxgrid").jqxGrid(
                {
                    width: 380+100+100+100+100+100+100+100+16,
                    height: 750,
                    source: dataAdapter,
                    showfilterrow: true,
                    filterable: true,
                    sortable: true,
                    selectionmode: 'multiplecellsextended',
                    theme: 'sparkleflowdash',
                    columnsresize: true,
                    columns: [
                      { text: 'Applicatie',   datafield: 'applicatie',   width: 380 },
                      { text: 'Licentie',     datafield: 'licentie',     width: 100 },
                      { text: 'Users in AD',  datafield: 'usersad',      width: 100 },
                      { text: 'Verschil',     datafield: 'verschil1', cellclassname: cellclass, width:  100 },
                      { text: 'Installaties', datafield: 'installaties', width: 100 },
                      { text: 'Verschil',     datafield: 'verschil2', cellclassname: cellclass, width:  100 },
                      { text: 'Gebruik',      datafield: 'gebruik',      width:  100 },
                      { text: 'Verschil',     datafield: 'verschil3', cellclassname: cellclass, width:  100 }
                    ]
                });
                $("#excelExport").jqxButton();
                $("#pdfExport").jqxButton();
    
                // $("#excelExport").click(function () {
                //     $("#jqxgrid").jqxGrid('exportview', 'xlsx', 'jqxGrid');           
                // });
    
                $("#excelExport").click(function () {
                    var value = $("#input").val();
                    $("#jqxgrid").jqxGrid('exportdata', 'xlsx', value);           
                });
    
                $('#openDial').click(function () {
                    $('#jqxwindow').jqxWindow('open');
                });
                
                $("#jqxwindow ").jqxWindow({ height:250, width: 250, okButton: $('#excelExport')});
                $('#jqxwindow').jqxWindow('close');
                $("#input").jqxInput({placeHolder: "Kies bestandsnaam", width: '250px', height: '25px'});
    
                $("#pdfExport").click(function () {
                    $("#jqxgrid").jqxGrid('exportview', 'pdf', 'jqxGrid');
                });
    
            });
    
        </script>
    
    </head>
    <body class="default">
           <style>     
            .green {
                color: black;
                background-color: #c6efce;
            }
            .yellow {
                color: black;
                background-color: #ffeb9c;
            }
            .red {
                color: black;
                background-color: #ffc7ce;
            }
    
            .green:not(.jqx-grid-cell-hover):not(.jqx-grid-cell-selected), .jqx-widget .green:not(.jqx-grid-cell-hover):not(.jqx-grid-cell-selected) {
                color: black;
                background-color: #c6efce;
            }
            .yellow:not(.jqx-grid-cell-hover):not(.jqx-grid-cell-selected), .jqx-widget .yellow:not(.jqx-grid-cell-hover):not(.jqx-grid-cell-selected) {
                color: black;
                background-color: #ffeb9c;
            }
            .red:not(.jqx-grid-cell-hover):not(.jqx-grid-cell-selected), .jqx-widget .red:not(.jqx-grid-cell-hover):not(.jqx-grid-cell-selected) {
                color: black;
                background-color: #ffc7ce;
            }
        </style>
    <p style="font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; font-weight: bold; color: rgb(143, 143, 143)">Licentie Overzicht</p>
            <div id="jqxgrid"></div>
            <div id= "jqxwindow" style="margin-top: 20px;">
                <div style="float: left;">
                    <input type="text" id="input" />
                    <input type="button" value="Export naar Excel" id="excelExport" />
                </div>
                <input type="button" value="Open Dialog" id="openDial" />
                <div style="margin-left: 10px; float: left;">
                    <input type="button" value="Export naar PDF" id="pdfExport" />
                </div>
            </div>
    </body>
    </html>

    Yavor Dashev
    Participant

    Hi Paul,

    You still have a script that is missing and you need it for the jqxWindow component.
    The script is: <script type="text/javascript" src="jqwidgets/jqxwindow.js"></script>

    You can also can take a look at the documentation about how to set up the jqxWindow component here:
    https://www.jqwidgets.com/jquery-widgets-documentation/documentation/jqxwindow/jquery-window-getting-started.htm?search=window

    Please, do not hesitate to contact us if you have any additional questions.

    Best Regards,
    Yavor Dashev
    jQWidgets team
    https://www.jqwidgets.com


    paulcobben
    Participant

    It works. Thanks.

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

You must be logged in to reply to this topic.