jQuery Grid export of grouped rows


95
 
1
2
    $(function () {
3
        var colM = [
4
            { title: "ShipCountry", width: 120, dataIndx: "ShipCountry" },
5
            { title: "Customer Name", width: 130, dataIndx: "ContactName" },
6
            {
7
                title: "Freight", width: 120, format: '$##,###.00',
8
                summary: {
9
                    type: "sum"
10
                },
11
                dataType: "float", dataIndx: "Freight"
12
            },
13
            { title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
14
            { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date", format: 'yy-mm-dd' },
15
            //{ title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
16
            { title: "Shipping City", width: 130, dataIndx: "ShipCity" }
17
        ];
18
        var dataModel = {
19
            location: "remote",
20
            dataType: "JSON",
21
            method: "GET",
22
            url: "/Content/orders.json"
23
            //url: "/pro/orders/get",//for ASP.NET
24
            //url: "orders.php",//for PHP
25
        };
26
        var groupModel = {
27
            on: true,
28
            dataIndx: ['ShipCountry'],
29
            collapsed: [false],
30
            title: [
31
                "{0} ({1})",
32
                "{0} - {1}"
33
            ]
34
        };
35
        var obj = {
36
            height: 500,
37
            toolbar: {
38
                items: [
39
                {
40
                    type: 'select',
41
                    label: 'Format: ',
42
                    cls: 'export-format',
43
                    options: [{ xlsx: 'Excel', csv: 'Csv', htm: 'Html', pdf: 'PDF' }]
44
                },
45
                {
46
                    type: 'button',
47
                    label: "Export",
48
                    icon: 'ui-icon-arrowthickstop-1-s',
49
                    listener: function () {
50
51
                        var format = this.toolbar().find('.export-format').val(),
52
                            data = this.exportData({
53
                                format: format,
54
                                render: true
55
                            });
56
                        if (format == 'pdf') {
57
                            var dd = {
58
                                footer: function (currentPage, pageCount) { return currentPage.toString() + ' of ' + pageCount; },
59
                                pageOrientation: 'landscape',
60
                                content: [
61
                                    {
62
                                        //include document definition of grid.
63
                                        table: data
64
                                    }
65
                                ]
66
                            };
67
                            pq.getScripts(['/pdfmake027/pdfmake.min.js', '/pdfmake027/vfs_fonts.min.js'], function () {
68
                                //finally open the pdf file.
69
                                pdfMake.createPdf(dd).open();
70
                            });
71
                        }
72
                        else {
73
                            pq.saveAs(data, "pqGrid." + format);
74
                        }
75
                    }
76
                }]
77
            },
78
            dataModel: dataModel,
79
            scrollModel: { autoFit: true },
80
            colModel: colM,
81
            numberCell: { show: false },
82
            menuIcon: true,
83
            selectionModel: { type: 'cell' },
84
            groupModel: groupModel,
85
            title: 'Group Rows',
86
            showTitle: false,
87
            resizable: true,
88
            hwrap: false,
89
            wrap: false
90
        };
91
        var grid = pq.grid("#grid_export", obj);
92
93
    });
94
95


35
 
1
2
    [HttpPost, ValidateInput(false)]
3
    public String exportData(String pq_ext, String pq_data, bool pq_decode, String pq_filename)
4
    {
5
    String[] arr = new String[] { "csv", "htm", "json", "xlsx", "zip" };
6
    if (arr.Contains(pq_ext))
7
    {
8
    String filename = pq_filename + "." + pq_ext;
9
    Session["pq_data"] = pq_data;
10
    Session["pq_decode"] = pq_decode;
11
    Session["pq_filename"] = filename;
12
    return filename;
13
    }
14
    else
15
    {
16
    throw new Exception("unsupported format!");
17
    }
18
    }
19
20
    [HttpGet]
21
    public FileContentResult exportData(String pq_filename)
22
    {
23
    if ( Session["pq_filename"].ToString() == pq_filename )
24
    {
25
    String contents = Session["pq_data"].ToString();
26
    byte[] bytes = ((bool)Session["pq_decode"])? Convert.FromBase64String(contents):
27
    new System.Text.UTF8Encoding().GetBytes(contents);
28
    return File(bytes, "application/octet-stream", pq_filename);
29
    }
30
    else
31
    {
32
    throw new Exception("unknown file.");
33
    }
34
    }
35


39
 
1
2
    if (isset($_POST["pq_data"]) && isset($_POST["pq_ext"]))
3
    {
4
    $pq_ext = $_POST["pq_ext"];
5
    $ext = array("csv", "htm", "json", "xlsx", "zip");
6
    if (in_array($pq_ext, $ext))
7
    {
8
    session_start();
9
    $data = $_POST['pq_data'];
10
    $pq_decode = $_POST["pq_decode"];
11
    if( $pq_decode === "true" ){
12
    $data = base64_decode($data);
13
    }
14
    $_SESSION['data'] = $data;
15
    $filename = $_POST['pq_filename']. "." . $pq_ext;
16
    $_SESSION['filename'] = $filename;
17
    echo $filename;
18
    }
19
    }
20
    else if(isset($_GET["pq_filename"]))
21
    {
22
    $filename = $_GET["pq_filename"];
23
    session_start();
24
    if ($filename == $_SESSION["filename"] )
25
    {
26
    if (isset($_SESSION['data'])) {
27
    $data = $_SESSION['data'];
28
    $_SESSION['data']=null;
29
30
    header('Content-Disposition: attachment; filename="'.$filename.'"');
31
    header("Content-Type: application/octet-stream");
32
    header('Content-Length: ' . strlen($data));
33
    header('Connection: close');
34
    echo $data;
35
    exit;
36
    }
37
    }
38
    }
39


43
 
1
2
    @Controller
3
    public class ExportDataController {
4
    @RequestMapping(value = "/exportData", method = RequestMethod.POST)
5
    public @ResponseBody
6
    String exportData(String pq_data, String pq_ext, Boolean pq_decode, String pq_filename, HttpServletRequest request) throws IOException {
7
8
    String[] arr = new String[] {"csv", "xlsx", "htm", "zip", "json"};
9
    String filename = pq_filename + "." + pq_ext;
10
11
    if(Arrays.asList(arr).contains(pq_ext)){
12
    HttpSession ses = request.getSession(true);
13
    ses.setAttribute("pq_data", pq_data);
14
    ses.setAttribute("pq_decode", pq_decode);
15
    ses.setAttribute("pq_filename", filename);
16
    }
17
    return filename;
18
    }
19
20
    @RequestMapping(value = "/exportData", method = RequestMethod.GET)
21
    public void exportData(String pq_filename, HttpServletRequest request, HttpServletResponse response) throws IOException, Base64DecodingException {
22
    HttpSession ses = request.getSession(true);
23
    if ( ((String)ses.getAttribute("pq_filename")).equals(pq_filename) ) {
24
25
    String contents = (String) ses.getAttribute("pq_data");
26
    Boolean pq_decode = (Boolean) ses.getAttribute("pq_decode");
27
28
    byte[] bytes = pq_decode?  Base64.decode(contents): contents.getBytes(Charset.forName("UTF-8"));
29
30
    response.setContentType("application/octet-stream");
31
32
    response.setHeader("Content-Disposition",
33
    "attachment;filename=" + pq_filename);
34
    response.setContentLength(bytes.length);
35
    ServletOutputStream out = response.getOutputStream();
36
    out.write(bytes);
37
38
    out.flush();
39
    out.close();
40
    }
41
    }
42
    }
43

Export of grouped rows to Excel, pdf, html and csv