How to Export Data to Excel in PHP

In this article, you will learn how to export data to Excel in PHP. 

Let’s say you have the following array as data.

$data = array(
    array("NAME" => "Apple", "QUANTITY" => 10),
    array("NAME" => "Bacon", "QUANTITY" => 100),
    array("NAME" => "Cabbage", "QUANTITY" => 23),
    array("NAME" => "Dumpling", "QUANTITY" => 50)
);

Export Data to Excel

In order to export data to Excel, you can use the header() method, implode() method, and array_walk() method.

$data = array(
    array("NAME" => "Apple", "QUANTITY" => 10),
    array("NAME" => "Bacon", "QUANTITY" => 100),
    array("NAME" => "Cabbage", "QUANTITY" => 23),
    array("NAME" => "Dumpling", "QUANTITY" => 50)
);

function filterData(&$str){
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}

// Name for Excel file
$file = "codesource-" . date('dmY') . ".xlsx";

// Headers to force download Excel file
header("Content-Disposition: attachment; filename=\"$file\"");
header("Content-Type: application/vnd.ms-excel");

$flag = false;
foreach($data as $row) {
    if(!$flag) {

        // Set column names as first row
        echo implode("\t", array_keys($row)) . "\n";
        $flag = true;
    }

    // Filter data
    array_walk($row, 'filterData');
    echo implode("\t", array_values($row)) . "\n";
}

exit;