How to export an SQL table to an excel format with PHP

Question

So I need my SQL table to be downloadable. Right now my php file creates an excel file and forces a download but the excel file just has a large array with the table's data inside it.

Here is my code

$sql = mysql_query("SELECT * FROM table");

while ($row_user = mysql_fetch_assoc($sql))
{   $userinfo[] = $row_user;

print_r($row_user);

header("Content-Disposition: attachment; filename=\"papi.xls\"");
header("Content-Type: application/vnd.ms-excel;");
header("Pragma: no-cache");
header("Expires: 0");
$file = fopen("php://output","w");

foreach($userinfo as $data)
   {
  fputcsv($file,explode(',',$data),"\t");
 }

  fclose($file);
 }

Any help would be greatly appreciated, thank you in advance.

Answer 1

Something like this should work. I am using PDO instead of the deprecated mysql. I have also included the prepare statement that you may want to use in most cases along with bindParam to prevent SQL injection...even though it is not needed in this example. You will notice that I also changed your headers to indicate a CSV file rather than an Excel file.

<?php

$sql = "select * from table";

$dbh = new PDO("mysql:host=localhost; dbname=MYDB", "DBUSER", "DBPASSWORD");
$stmt = $dbh->prepare($sql);
$stmt->execute();

header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename=example.csv');
header('Pragma: no-cache');

$out = fopen('php://output', 'w');

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    fputcsv($out, $row);
}

fclose($out);

I think the main thing throwing you off may have been your explode statement, which is typically not needed with fputcsv. However, I am not familiar with your data.

This follows your example of writing a CSV file. If you'd rather have an Excel .xls or .xlsx file you can use PHPExcel. It does take a bit longer to set up, and the documentation isn't the greatest, but it does a good job.

Answer 2

So, This is a class that I wrote to export to CSV and keep everything lined up perfectly.

<?php
class exporter{
    private $map = array();
    private $data = array();
    private $nr = "\n";
    private $dl = ",";
    private $containerChar = "\"";
    private $exportData = "";


    function extractFullMapData($data){
        $map = array();
        foreach($data as $row){
            foreach($row as $colName => $col){
                if(!in_array($colName, $map)){
                    $map[] = $colName;
                }
            }
        }
        $this->setMap($map);
    }

    function addData($row, $multipleRows){
        if($multipleRows){
            foreach($row as $curRow){
                $this->addData($row);
            }
        }else{
            if(empty($this->map)){
                $this->extractMapData($row);
            }
            $newRow = array();
            foreach($this->map as $varName){
                if(isset($row[$varName])){
                    $newRow[$varName] = str_replace(, "\\".$this->containerChar, $row[$varName]);
                }else{
                    $newRow[$varName] = "";
                }
            }
            $this->data[] = $newRow;
        }
    }

    function export(){
        header('Content-Type: application/csv');
        header('Content-Disposition: attachment; filename="export.csv"');
        header('Pragma: no-cache');
        $this->buildExport();
        echo $this->exportData;
    }

    private function extractMapData($row){
        $this->setMap(array_keys($row));
    }

    private function setMap($map){
        $this->map = $map;
    }

    private function buildExport(){
        $exportData = "";
        foreach($this->map as $varName){
            $exportData .= ($exportData == "") ? "" : $this->dl;
            $exportData .= $this->containerChar.$varName.$this->containerChar;
        }
        foreach($this->data as $data){
            $row = "";
            $looped = false;
            foreach($data as $item){
                $row .= (!$looped) ? "" : $this->dl;
                $row .= $this->containerChar.$item.$this->containerChar;
                $looped = true;
            }
            $exportData .= $this->nr.$row;
        }
        $this->exportData = $exportData;
    }
}
?>

This code is derived from a class I wrote within my framework that handles exports for a ton of things. I've also written a class that can read back this data. The way this class is to be used is like this.

<?php
    $exporter = new exporter();
    $exporter->extractFullMapData($fullDataArray);
    $exporter->addData($fullDataArray, true);
    $exporter->export();
?>

Answer 3

There are several PHP libraries which could help you format your output excel file before forcing the download. I've had good experiences using PHPExcel.

Using PHPExcel would not change accessing the data from your table, but it would change how you process the MySQL result:

//Collect result set
$data = array();
while ($row = mysql_fetch_assoc($sql)){ $data[] = $row; }

//initialize PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("AuthorName")->setTitle("DocumentTitle");

//Write Column Titles into first Row
$col = 'A';
foreach($data[0] as $key => $val){ 
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.1, $key);
    $col++;
}

//Write Data
for($i = 0; $i < count($data); $i++){
    $col = 'A';
    $row = 2 + $i;
    foreach($data[$i] as $val){
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col.$row, $val);
        $col++;
    }
}

//Set Header
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="DataExport.xlsx"');
header('Cache-Control: max-age=0');

//Output Results        
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
$objWriter->save('php://output');

This is by no means the only option, however, I have found this library useful to export data into an XLSX file and have additional options to format it or add equations as needed for generating reports.