Messages

1

Help for ParamQuery Grid (free version) / Re: exportExcel - PHP - What am I missing?

« on: November 28, 2015, 07:15:34 pm »

Update:

I noticed that the file will download, but it takes a long, long time.  Currently, there are just over 5K rows that I'm trying to export.  If I limit my query to 50 rows, it works quickly.  Is there any way to speed it up for a higher record count?

Thanks,

David

2

Help for ParamQuery Grid (free version) / exportExcel - PHP - What am I missing?

« on: November 28, 2015, 06:53:06 pm »

Hello.  I'm working with the sort_paging demo, trying to add in an export to Excel.  The sorting and paging works fine, but clicking the export button does nothing.  I'm sure I must be missing something simple.  Can anyone help me get this working?

Here's the index.php file.

Code: [Select]

<?php
require_once '../include.php';
?>

<script class="ppjs">
    $(function () {
        var colM = [
            { title: "Last Name", width: 125, dataIndx: "client_last_name" },
            { title: "First Name", width: 125, dataIndx: "client_first_name" },
            { title: "Username", width: 100, dataIndx: "username" },
            { title: "Company", width: 150, dataIndx: "company_name" },
            { title: "Address", width: 150, dataIndx: "homeaddress", },
            { title: "City", width: 100, dataIndx: "homecity"},
            { title: "County", width: 100, dataIndx: "homecounty"},
            { title: "Phone", width: 100, dataIndx: "homephone1"},
            { title: "Email", width: 200, dataIndx: "email"},
            { title: "Terms Of Use", width: 100, dataIndx: "termsofusetimestamp", align: "center"},
            { title: "Paper/Online", width: 100, dataIndx: "ipaddress", align: "center"}
        ];
        var dataModel = {
            location: "remote",
            sorting: "local",
            dataType: "JSON",
            method: "GET",
            getUrl : function () {
                return { url: 'remote.php'};
            },
            getData: function ( response ) {
                return { data: response };
            }
        }

        var grid1 = $("div#grid_paging").pqGrid({
            width: 1500,
            height: 625,
            collapsible: false,
            pageModel: {type: "local", rPP:25, strRpp:"{0}", strDisplay:"{0} to {1} of {2}"},
            dataModel: dataModel,
            colModel: colM,
            toolbar: {
                cls: 'pq-toolbar-export',
                items: [{
                    type: 'button',
                    label: "Export to Excel",
                    icon: 'ui-icon-document',
                    excel: 1,
                    extension: 'xml',
                    listeners: [{
                        "click": function (evt) {
                            $("#grid_paging").pqGrid("exportExcel", { url: "export.php", sheetName: "pqGrid" });
                        }
                    }]
                }]
            },
            wrap:false,
            hwrap:false,
            freezeCols: 0,
            numberCell: { resizable: true, title: "#" },
            title: "Shipping Orders",
            resizable: false
        });
    });

</script>

<div id="grid_paging" style="margin:5px auto;"></div>

</body>
</html>

Here's remote.php.

Code: [Select]

<?php
require_once '../conf.php';
$dbh = getDatabaseHandle();

$sql = "select client_last_name, client_first_name, username, company_name, homeaddress,
        homecity, homecounty, homephone1, email, DATE_FORMAT(termsofusetimestamp,'%m-%d-%Y') as termsofusetimestamp,
        case when ipaddress = 'PAPER' then 'PAPER' else 'ONLINE' end as ipaddress
        from users order by username";

$stmt = $dbh->query($sql);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($products);
?>

and here is export.php

Code: [Select]

<?
if (isset($_POST["excel"]) && isset($_POST["extension"])) {
    $extension = $_POST["extension"];
    if ($extension == "csv" || $extension == "xml") {
        $excel = $_POST["excel"];
        $filename = "pqGrid." . $extension;
        $file = dirname($_SERVER["SCRIPT_FILENAME"]) . "\\" . $filename;
        file_put_contents($file, $excel);
        echo $filename;
    }
} else if (isset($_GET["filename"])) {
    $filename = $_GET["filename"];
    if ($filename == "pqGrid.csv" || $filename == "pqGrid.xml") {
        $file = dirname($_SERVER["SCRIPT_FILENAME"]) . "\\" . $filename;

        if (file_exists($file)) {
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename=' . basename($file));
            header('Content-Transfer-Encoding: binary');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($file));
            ob_clean();
            flush();
            readfile($file);
            exit;
        }
    }
}

?>

Any help is appreciated.

Thanks,

David