Exporting Database Data to Excel with PHP

This tutorial shows how to export data from a database into an Excel spreadsheet using PHP. It first retrieves records within a specified time range, then prepares the data for export according to custom column headers.

The following code fetches the data and builds an array for export:

$list = Db::table('form')
    ->where('create_time', '>', $stat_time)
    ->select()
    ->where('create_time', '<', $end_time);

if (empty($list)) {
    echo "<script>alert('暂时无数据');window.history.back();</script>";
    exit();
}

foreach ($list as $key => $value) {
    $tuij = Db::table('form')->where('id', $value['id'])->find();
    $arr[$key]['username'] = $tuij['username'];
    $arr[$key]['phone']    = $tuij['phone'];
    $arr[$key]['source']   = $tuij['source'];
    $arr[$key]['text']     = $value['text'];
    $arr[$key]['create_time'] = $value['create_time'];
}

$header = array('姓名', '电话', '来源', '留言', '提交时间');
$index  = array('username', 'phone', 'source', 'text', 'create_time');
$filename = "表单落地页有效推广";
$this->createtable($arr, $filename, $header, $index);

The createtable method is a reusable utility that sets the appropriate HTTP headers for an Excel file, concatenates the header row, iterates over each data row, and outputs the tab‑separated values. The method also converts the output to GB2312 encoding before terminating the script.

/**
 * Export utility method
 * @return \think\Response
 */
function createtable($list, $filename, $header, $index) {
    header("Content-type:application/vnd.ms-excel");
    header("Content-Disposition:filename=" . $filename . ".xls");
    $table_header = implode("\t", $header);
    $strexport = $table_header . "\r";
    foreach ($list as $row) {
        foreach ($index as $val) {
            $strexport .= $row[$val] . "\t";
        }
        $strexport .= "\r";
    }
    $strexport = iconv('UTF-8', "GB2312//IGNORE", $strexport);
    exit($strexport);
}

For the full original article and additional resources, click the "Read Original" link provided at the end of the page.