Sending data to PHP page to make a spreadsheet out of it

Question

For some reason, when I try to send JSON data to a PHP page (where it gets downloaded as a spreadsheet), it runs without error, but doesn't bring up the prompt to download the spreadsheet. The JSON has generated without problem (I have made the PHP page create the file on the server, before trying to make it download without creating it).

Here is the JavaScript code that sends the JSON data to the server:

function writeToSpreadsheet()
{
    // get the json for #theTable
    var tableJSON = tableToJSON("tr:not(#titleRow)");
    //alert(tableJSON);
    alert("Sending table data to be written to the spreadsheet...");
    $.post('/ResearchProject/tableContent/exportTable.php', {'table': tableJSON}).done(
        function(response) { alert(((response == '') ? response : (tableJSON.title + ' written to file!')));})
        .fail(function (xhr, ajaxOptions, thrownError) { alert("ERROR:" + xhr.responseText+" - "+thrownError); });
}

and here is exportTable.php

<?php
    function cleanData(&$str)
    {
        $str = preg_replace("/\t/", "\\t", $str);   // escaping all of the tabs 
        $str = preg_replace("/\r?\n/", "\\n", $str);    // escaping any and all cases of carriage return
        // if there is a single double-quote in the string, we wrap the string in quotes, replace every single double-quote with double double-quotes, and 
        //  end with a double-quote
        if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';  
    }

    // the data is coming from a JSON object that is being sent here
    if (isset($_POST['table']))
    {
        $tableJSON = $_POST['table'];   # somehow, this is already a PHP array (exactly the one we need)!!
        // get the name of the table from the $tableJSON
        $tableName = $tableJSON['title'];
        // get the title row from $tableJSON
        $titleRow = $tableJSON['titleRow'];
        // fix the titleRow
        foreach ($titleRow as $heading)
        {
            $heading = trim(preg_replace('/\s+/', ' ', $heading));
        }
        // get the rows from $tableJSON
        $rows = $tableJSON['rows'];
        // form the filename from the tableName
        $fileName = $tableName . '.xls';

        // here, we download the file without even creating it
        header("Content-Disposition: attachment; filename=\"$fileName\"");
        header("Content-Type: application/vnd.ms-excel");
        // we echo the titleRow first
        array_walk($titleRow, 'cleanData');
        echo implode(chr(9), $titleRow) . "\r\n";
        ?>
<script>console.log('Title row written to file.');</script>
<?php           
        // now we echo the data
        foreach($rows as $row)
        {
            array_walk($row, 'cleanData');
            echo implode(chr(9), $row) . "\r\n";
?>
<script>console.log('Data row written to file.');</script>
<?php           
        }
    }
    else 
    {
        echo 'You sent me no data :(\n';
    }
?>

OK, MikeWarren, how do I test this??

You can test it by selecting a table from the dropdown menu and clicking the "Export table to spreadsheet" button here: http://dinotator.biokdd.org/ResearchProject/tableViewer.php

I am trying to have it where the table that is on the HTML page gets converted into an JSON object, and then downloaded. Thus, I would need to POST the data to the PHP page, right? (Query strings don't work.)

Answer 1

Query strings won't work because you are using jQuery's $.post call which means that your data is sent in the body of the request, as opposed to a query string which is what a GET uses. For JSON you do indeed want to use a POST.

As for what's going wrong, you need to decode your JSON into a PHP array using json_decode. Unfortunately it can't simply handle JSON how it is.

So most likely you'll want to do:

// now a poorly named variable
$tableJSON = json_decode($_POST['table']);

Also, looking at your Ajax, $.post does accept a .fail() listener, but it doesn't pass any error data as part of the callback. So if you want to be able to handle incoming response errors you'll need to use $.ajax:

$.ajax({
    type: "POST",
    url: "/your/url.php",
    dataType: "json",
    error: errorCallback
});

Finally, looking at how your code is structured, if you're actually trying to save to file, you're going to need some more logic. Right now, you're just rendering that table, and then returning it as a response which will show up in your done function. You're going to add some more logic in order to make it actually download. This question entails your exact problem.

Good luck!

Answer 2

I have found so much bad advice on the internet about how to solve this problem. One of the answers here also didn't work. :(

I have decided to get advice from a friend of mine, and me and him have decided on this approach:

• Have my exportData.php simply write the data to $_SESSION, echo a JSON-encoded "success", and then exit
• On exit, on the client-side of things, if "success" has been received, have the JavaScript open up a new tab to a file that I have created called downloadFile.php which actually does the downloading.

Why didn't sending the data between files work?

Downloading data entails setting the right headers and printing the data. When you send data to the file to do this (via AJAX), the buffer that the data is printed to is the one for response. You can see this by saying something like success: function(response) { alert(response); } and see the data that you "downloaded" not get downloaded, but get printed on-screen.

However, if you go to the file instead of simply passing data to it, your data will download, provided that it has access to the data that you are trying to download. You can see examples of this here: www.the-art-of-web.com/php/dataexport/ . In those examples, the data was "static" (that is, only existing in the scope of that PHP file, until download happened).

We then see that we should let another file handle the downloading. Here is what its contents should look like:

<?php
    if (!isset($_SESSION))
        session_start();
    function cleanData(&$str)
    {
        $str = preg_replace("/\t/", "\\t", $str);   // escaping all of the tabs 
        $str = preg_replace("/\r?\n/", "\\n", $str);    // escaping any and all cases of carriage return
        // if there is a single double-quote in the string, we wrap the string in quotes, replace every single double-quote with double double-quotes, and 
        //  end with a double-quote
        if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';  
    }

    // get the data from $_SESSION
    if (isset($_SESSION))
    {
        $fileName = $_SESSION['fileName'];
        $titleRow = $_SESSION['titleRow'];
        $rows = $_SESSION['rows'];
        // set the excel headers 
        header("Content-Type: application/vnd.ms-excel");
        //header("Content-type: application/octet-stream");
        header("Content-Disposition: attachment; filename=\"$fileName\"");
        header("Pragma: no-cache");
        header("Expires: 0");
        // attempt download
        array_walk($titleRow, 'cleanData');
        echo implode(chr(9), $titleRow) . "\r\n";
        // now we echo the data
        foreach($rows as $row)
        {
            array_walk($row, 'cleanData');
            echo implode(chr(9), $row) . "\r\n";
        }
    }
    else
    {
        die('Problem with session variable. Data could not be sent for download.');
    }
    exit;
?>

Of course, before doing this, make sure that you have 'fileName', 'titleRow', and 'rows' already written to $_SESSION.

This should help anyone having problem downloading HTML table to Excel spreadsheet via PHP, and the best part is that you don't have to bloat your server by downloading an entire library, for potentially the functionality of one button!!