How to create a custom script to force the download of a .csv file that is generated in real time?

Question

So I've been handed a custom administrator module which simply displays database table data in an HTML table. My task is to create a clickable button on the module that will generate the csv data on the fly and force the download of the same content as an excel document.

Andrew Eddie recommends these solutions, but I don't know how/where to implement them so that:

1. I can authenticate that the downloading user is, in fact, the same logged in user with permission (I don't want someone to be able to learn the url and be able to directly access this private data. I assume a token is the best tool for this)
2. I can enjoy the Joomla query builder methods to gather the necessary data
3. I can avoid displaying any other administrator (menu dom) content with my generated data

It could divert to a different static method like this:

if (JFactory::getApplication()->input->get->get('export', 0, 'int'))
{
    mod[modname]Helper::exportToExcel();
}
else
{
    mod[modname]Helper::displayAsHTML();
}

to access:

public static function exportToExcel()
{
    header("Content-type: text/csv");
    header("Content-Disposition: attachment; filename=Some_Report_" . JFactory::getDate()->format('Y_m_d_H_i_s') . ".csv");  // @ https://docs.joomla.org/How_to_use_JDate it looks like someone got a bit spastic with the percent signs  (bookmarked for upcoming Pizza, Bugs, & Fun)
    header("Pragma: no-cache");
    header("Expires: 0");

    // get my db data and echo it as csv data

    // Close the application gracefully.
    JFactory::getApplication()->close();
}

But in doing so, my csv data is appended to the earlier loaded content -- no good. I wasn't able to ob_ the other content out; and I don't even know if this is a good approach to begin with.

The custom administrator module that I am looking at is as basic as it gets with files like:

• mod_[modname].php
• helper.php (previous dev does all processing and displaying from here)
• tmpl/default.php (previous dev didn't output anything from here)

My plan is to use javascript to append a button after the module's title:

<h3 class="module-title nav-header">[the module's title] [add my button here]</h3>

What I don't know is: Where do I park my new code and how should I craft the destination url to access it?

I'd like to learn how to write this custom code with the correct architecture and best practices in mind for my own education. I'm hoping someone is going to blow my mind with some elegant Joomla magic.

Answer 1

If this needs to be done by the module (there's no accompanying component or the component is 3rd party and you can't touch it), use com_ajax.

Point the form to com_ajax:

<form method="post">
    <input type="hidden" name="option" value="com_ajax">
    <?php // Module name without mod_ prefix ?>
    <input type="hidden" name="module" value="custom">
    <?php // Output format. CSV is not supported, use raw ?>
    <input type="hidden" name="format" value="raw">
    <?php // Add session token ?>
    <?php echo HTMLHelper::_('form.token'); ?>
    <button type="submit">Download</button>
</form>

Create getAjax() method in your module's helper:

public static function getAjax()
{
    $app = Factory::getApplication();

    // Check for request forgeries
    if (!$app->getSession()->checkToken())
    {
        $app->enqueueMessage(Text::_('JINVALID_TOKEN'));
        $app->redirect('index.php');
    }

    // Check for user permissions (only example)
    if (!Factory::getUser()->authorise('core.admin'))
    {
        $app->enqueueMessage(Text::_('JERROR_ALERTNOAUTHOR'));
        $app->redirect('index.php?option=com_users&view=login');
    }

    self::exportToExcel();
}

Answer 2

Regarding clearing the output buffer, some time ago I had a similar issue and this solved the issue, at least for me:

    // Close output buffers till the level reaches 0
    while (ob_get_level() > 0)
    {
        ob_end_clean();
    }

    // Start capturing output into a buffer
    ob_start();

    // Content to export
    echo $toExport;

    // Get the buffer and clear it
    $fileContent = ob_get_contents();
    ob_end_clean();

    // Echo will start the download
    echo $fileContent; 

Answer 3

It turned out that using @n.h.'s nifty output buffer cleansing workaround was not necessary after implementing @Sharky's advice regarding com_ajax and getAjax().

So that researchers will be able to access a complete solution for their own needs, I'll post a "boiled down" version of my complete working solution which can be easily amended to suit other scenarios. This is condensed into the mod_[modname].php** and helper.php files.

*Note that tmpl/default.php was not used, though I typically use it for displaying html content.

**I will use the placeholder [modname] for all places where the actual/unique module name should be written.

Using fopen("php://output", "w") and fputcsv() is an integral piece of best practice inside of the exportToExcel() method to provide a "stable" document.

I deliberately used JHtml::date('now', 'Y_m_d_H_i_s', 'Australia/Brisbane') for the generated report's timestamp so that it will be consistent regardless of the user's profile timezone and regardless of the server's timezone.

The default behavior of this script will be to display an html table with the query data. The new excel document will be generated and forced to download after the "Download" button has been clicked.

mod_[modname].php

defined('_JEXEC') or die;
require_once dirname(__FILE__) . '/helper.php';
mod[modname]Helper::showAsHTML();

helper.php

defined('_JEXEC') or die;

use Joomla\CMS\HTML\HTMLHelper;

class mod[modname]Helper
{
    public static function getAjax()
    {
        $app = JFactory::getApplication();

        // Check for request forgeries
        if (!$app->getSession()->checkToken())
        {
            $app->enqueueMessage(Text::_('JINVALID_TOKEN'));
            $app->redirect('index.php');
        }

        // Check for user permissions (only example)
        if (!JFactory::getUser()->authorise('core.admin'))
        {
            $app->enqueueMessage(Text::_('JERROR_ALERTNOAUTHOR'));
            $app->redirect('index.php?option=com_users&view=login');
        }

        self::exportToExcel();
    }

    public static function getResultSet()
    {           
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);

        // build your query; yatta-yatta

        $db->setQuery($query);

        // load the results
        try
        {
            if (!$resultset = $db->loadAssocList()) {
                return [['Status' => 'No Rows Found']];
            }
            return $resultset;
        }
        catch (Exception $e)
        {
            return [['Error' => 'Syntax Failure']]; //, ['Message' => $e->getMessage()], ['Query' => $query->dump()]];
        }
    }
    
    public static function exportToExcel()
    {
        // define output settings
        header("Content-type: text/csv");
        header("Content-Disposition: attachment; filename=Report_" . JHtml::date('now', 'Y_m_d_h_i_s', 'Australia/Brisbane') . ".csv");  // create unique document name
        header("Pragma: no-cache");
        header("Expires: 0");

        // get db data
        $resultset = self::getResultSet();
        $column_heads = array_map('ucwords', str_replace('_', ' ', array_keys($resultset[0])));  // pretty up the column headings

        // write output
        $fp = fopen("php://output", "w");
        fputcsv ($fp, $column_heads);
        foreach ($resultset as $row)
        {
            fputcsv($fp, $row);
        }
        fclose($fp);

        // Close the application gracefully.
        JFactory::getApplication()->close();
    }

    public static function showAsHTML()
    {
        $resultset    = self::getResultSet();
        $column_heads = array_map('ucwords', str_replace('_', ' ', array_keys($resultset[0])));  // pretty up the column headings
        
        ?>
        <h3>Report</h3>
        <form method="post" style="display:inline; margin-left:12px;">
            <input type="hidden" name="option" value="com_ajax">
            <input type="hidden" name="module" value="[mod_name]">
            <input type="hidden" name="format" value="raw">
            <?=HTMLHelper::_('form.token')?>
            <button type="submit">Download</button>
        </form> 
        <?php
        echo "<table>";
            echo "<tr><th>" , implode("</th><th>", $column_heads) , "</th></tr>";
            foreach ($resultset as $row) {
                echo "<tr><td>" , implode("</td><td>", array_map("htmlspecialchars", $row)) , "</td></tr>";
            }
        echo "</table>";
    }
}