Exporting a web report to Excel in PHP

Asked By Serena 460 points N/A Posted on - 05/17/2011

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?

• Status: Open
• Question Views: 1097
• Answer Count: 2
• Vote Up 22 Vote Down

• Answer Accepted: Yes
• Question Category: PHP

Best Answer by Ed_johnson

Go To Solution

Best Answer

Best Answer

Answered By Ed_johnson 0 points N/A #98518

Exporting a web report to Excel in PHP

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";

    }

    
?>

About Ed_johnson

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 06/17/2011
• Question Category: PHP

Answered By tomrid 0 points N/A #98519

Exporting a web report to Excel in PHP

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;

About tomrid

Questions
0

Answers
4

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 12/19/2011
• Question Category: PHP