Exporting a web report to Excel in PHP

Asked By 460 points N/A Posted on -
qa-featured

I am writing a PHP website that enables users to track time. I have a page that i want to download to Microsoft Excel. The PHP page has a date range to select the start date and the end date. The user then can select a particular work or all workers and get the details. My hosting platform is Linux.

Is it possible to create a report that could be opened in Microsoft Excel?

SHARE
Best Answer by Ed_johnson
Best Answer
Best Answer
Answered By 0 points N/A #98518

Exporting a web report to Excel in PHP

qa-featured

Hi Serena,

The easiest way to accomplish this is to use the OUTFILE parameter within mysql. The basic premise is that mysql will output the formatted data into a temporary file, which afterwards will be read and outputted to the stream.

Below is sample code which was tested working on a linux box running ubuntu server

<?php

    $user="userhere";

    $pass="passwordhere";

    $database="databasehere";

    mysql_connect ("localhost",$user, $pass) or die("Unable to connect");

    mysql_select_db($database);

    $outfile = "/tmp/out" . rand() . ".csv";

    $select = "SELECT * FROM user";

    $query = $select . " INTO OUTFILE '" . $outfile . "' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n'";

    $success = mysql_query($query);

    if ($success) {

        header('Content-type: application/vnd.ms-excel');

        header('Content-Disposition: attachment; filename="myfile.csv"');

        readfile($outfile);

    } else {

        echo "Unable to run query";

    }

    
?>

Answered By 0 points N/A #98519

Exporting a web report to Excel in PHP

qa-featured

It is never hard to create a report in excel in php just try it resolve your problem but make sure there is no html or anything else being sent to the browser. techyv

$file="test.xls";
if (file_exists($file)):
  header("Content-Type: application/vnd.ms-excel");
  header("Content-Disposition: attachment;filename=".$file );
  header('Pragma: no-cache');
  header('Expires: 0');
  readfile($file);
endif;

Login/Register to Answer

Related Questions