Native implementation method of exporting and downloading excel with PHP

I believe many friends will use the function of exporting data to EXCEL, so how to use PHP to export EXCEL? In addition to class libraries such as PHPExcel, native programming is also possible. This article uses native code to implement EXCEL export.

1. Preface

Sometimes it is necessary to export database table resources to excel to make some report data.

There are several methods for php to export excel:

Use php's class library to export, such as PHPExcel

Use php's native method to export

This article introduces how to export excel through native methods

2, key functions

2.1, ob output

ob_start();
ob_get_contents
ob_end_clean
fopen
fwrite
fclose
iconv

2.2. Header output

header
iconv

3. Use the ob function to export from excel

The ob function is mainly divided into three steps:

进行缓存的设置
读取缓存
写入文件

Paste the exported excel class below

Copy after login

4. Complete page code

query($sql);
    //判断是否有发送get数据
    if(isset($_GET['id'])){
        $name = empty($_GET['id']) ? 'a' . rand(1000,9999) : $_GET['id'] ;
        $filename = $name . '.xls';
        //设置缓存的开始
        Excel::start();
    }
?>
';
    echo '';
    //设置thead输出
    echo '' . Excel::setChar('编号') . '';
    echo '' . Excel::setChar('用户名') . '';
    echo '' . Excel::setChar('密码') . '';
    echo '';
?>
';
    //设置tbody输出
    while($re = $res->fetch(PDO::FETCH_ASSOC)){
        echo '';
        echo '' . Excel::setChar($re['id']) . '';
        echo '' . Excel::setChar($re['user']) . '';
        echo '' . Excel::setChar($re['pwd']) . '';
        echo '';
    }
    echo '';
?>
' .Excel::setChar('完成') .'';
}else{
    echo '' . Excel::setChar('导出') .'';
    
}
?>

5. Use header to download excel

Using the ob function to export excel can only be saved to the server and cannot be downloaded (it is still possible under special processing); the following describes how to use the header() function to export and download excel

The two main header() functions

header("Content-type:application/vnd.ms-excel");//指定文件类型    
header("Content-Disposition:filename=a.xls" );  //指定下载文件名

Other methods are the same as the export of the ob function (complete code below)

query($sql);
?>

';
    echo '';
    //设置thead输出
    echo '' . Excel::setChar('编号') . '';
    echo '' . Excel::setChar('用户名') . '';
    echo '' . Excel::setChar('密码') . '';
    echo '';
?>

';
    //设置tbody输出
    while($re = $res->fetch(PDO::FETCH_ASSOC)){
        echo '';
        echo '' . Excel::setChar($re['id']) . '';
        echo '' . Excel::setChar($re['user']) . '';
        echo '' . Excel::setChar($re['pwd']) . '';
        echo '';
    }
    echo '';
?>

6. Conclusion

Here is a brief introduction to the export and download of excel. One is to use the ob() function, and the other is to use the header() function. , the two methods are similar, but it should be noted that excel requires GBK encoding, and the iconv function needs to be used for transcoding to ensure that no garbled characters appear.

This article only briefly introduces it, more advanced methods need to be explored by yourself.

Related recommendations:

How to use PHPExcel to implement the function of batch uploading tables

[Course] PHP Excel Control Video Tutorial

How to implement the rank function in excel

The above is the detailed content of Native implementation method of exporting and downloading excel with PHP. For more information, please follow other related articles on the PHP Chinese website!