A support question we are sometimes asked by other developers is how to export information from web pages to an excel spreadsheet. This tutorial shows the key elements for exporting dynamic information from a database query.

so here  goes…

1. <?php
2. require_once (’lib/config.inc.php’);
3. require_once (MYSQL);
4. $filename =”excelreport.xls”;
5. $q = “select * from student order by studentSurname, studentFirstName asc”;
6. $r = @mysqli_query ($dbc, $q) or trigger_error(”Query: $q\n<br />MySQL Error: ” . mysqli_error($dbc));
7. $contents =”Surname \tFirst Name\tEmail\tDOB\tAddress1\tAddress2\tTown\tPost Code\tSQA\t \n”;
8. while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
9. $contents .=”".$row['studentSurname'].” \t”.$row['studentFirstName'].”\t”.$row['studentEmail'].”\t”.$row['studentDOB'].”\t”.$row['studentAddress1'].”\t”.$row['studentAddress2'].”\t”.$row['studentTown'].”\t”.$row['studentPostCode'].”\t”.$row['studentSQA'].”\t  \n”;
10. } //end of while loop
11. header(’Content-type: application/ms-excel’);
12. header(’Content-Disposition: attachment; filename=’.$filename);
13. echo $contents;
14. ?>

The above code explained -

Line 1 – kicks off the PHP code with the opening PHP tag.

Lines 2 and 3 – makes the database connection. I’ve stored by connection in a separate file called  config.in.php – I’m also using MySQLi (improved).

Line 4 sets up a variable for the file name.

Lines 5 and 6 – query the database for the information I’m looking for and stores the results in an array called $r.

Line 7 – sets up a variable called $contents and inserts the column headings for my excel spreadsheet.

Line 8 – starts are ‘while loop’ to extract information from the $r array from the query on the database.

Line 9 – appends the information for each result to the $contents variable.

Line 10- end the ‘while loop’.

Lines 11 and 12 – set up the file details for an excel spreadsheet.

Line 13 – echos the $contents variable.

Line 14 -closes the php code.

To try this out, replace the query in the code to a database you already have, update the database connection to your details then copy this code and save it as excel.php, then on any other page simply insert a link to it.