Creating an Excel file

I recently was tasked with creating a report of how many people signed up for the site by date. The query was simple, but creating the file was a bit of a challenge.

/**
 * User sign up report, output as a CSV.
 */
function mymodule_signups_csv() {
  // Prevent Devel from messing us up.
  $GLOBALS['devel_shutdown'] = TRUE;
  
  // Set the headers to indicate this is a CSV file.
  header('Content-type: text/csv; charset=UTF-8');
  header('Content-Disposition: attachment; filename=user_signups.csv');
  header('Pragma: no-cache');
  header('Expires: 0');

  // Create a file.
  $output = fopen('php://output', 'w');

  // Column names.
  fputcsv ($output, array('Date', 'Count'));

  $query = "SELECT DATE(FROM_UNIXTIME(created)) AS joined, COUNT(*) AS number FROM {users} "
    . "WHERE status = 1 "
    . "GROUP BY DATE(FROM_UNIXTIME(created)) "
    . "ORDER BY created DESC ";
  $result = db_query($query);

  // Loop through the rows.
  foreach ($result as $row) {
    fputcsv($output, (array) $row);
  }

  fclose($output);
}

To get to this code was just a matter of creating a MENU_CALLBACK and adding a link to an existing page.

Note the 3^rd line. Devel outputs its stuff after the </html> tag, so Excel saw that as data. That simple statement just turns Devel off for this single page load.

Comments

Explain how to get to this code

leenyx commented 27 June 2013 at 10:17

Hi, this looks very helpful but please can you explain how to get to this code? Can you give us an example of the MENU_CALLBACK and the link to the existing page?

Thanks!

Here you go

NancyDru

she/her/hers

English

Boston

commented 27 June 2013 at 13:32

function mymodule_reporting_permission() {
  return array(
    'access mymodule reports' => array(
      'title' => t('Access reporting section'),
      'description' => t('View reports on mymodule activity.'),
      ),
    );
}

function mymodule_reporting_menu() {
  $items = array();

  $items['mymodule-reporting/signups_csv'] = array(
    'page callback' => 'mymodule_reporting_signups_csv',
    'access arguments' => array('access mymodule reports'),
    'type' => MENU_CALLBACK,
    );

  return $items;
}
/**
 * Downloadable reports.
 */
function mymodule_reporting_downloads() {
  $output = '<div id="mymodule-reporting">';
  $output .= '<div id="mymodule-reporting-downloads"><br />';
  $links = array();

  // Add a link to download sign up stats.
  $links['signup-download'] = array(
    'title' => t('Download registration statistics'),
    'href' => 'mymodule-reporting/signups_csv',
    );

  $output .= theme('links', array('links' => $links));

  $output .= '</div>  <!-- /mymodule-reporting-downloads -->';
  $output .= '</div>  <!-- /mymodule-reporting -->';
  return $output;
}

There is, of course, a lot of other stuff missing from our actual implementation.

NancyDru

2017 Way

Prodigy commented 5 October 2017 at 00:35

The easier way to do this is to use views and download the views data export module. Set your view up and you'll have an option to provide a URL where you or whoever can download that CSV (sorted, filtered, whatever views can output essentially).