Export database data to an Excel file using native PHP

Recently encountered a requirement in your work, you need to export the data in the database to an Excel file, and download the Excel file. Because have not done before, so Baidu a bit,

Most of the online is to use the Phpexcel class to manipulate Excel files, which also to download the class to use, and I just want to use the native PHP, not so troublesome, fortunately

Also have the netizen to say about the native PHP to generate Excel file method, actually is very simple, below put me to combine the code of the net the practice of oneself to share a bit.

In general, the operation of this kind of data is through the user on the page page click on a button to trigger the corresponding JS method, and then request the PHP interface to achieve, so there are two main

method to accomplish this requirement.

Method 1: Directly in the JS code using window.open () to open the URL of the PHP interface, you can download the PHP generated Excel file.

The PHP interface code is as follows:

$mysqli = Mysqli_connect (' localhost ', ' root ', ' 123456 ', ' test '); $sql = ' select * from country '; $res = Mysqli_query ($mysqli , $sql); Header ("Content-type:application/vnd.ms-excel"); Header ("Content-disposition:filename=country.xls"); echo "code\t"; echo "name\t"; echo "population\t\n"; if (Mysqli_num_rows ($res) > 0) {while    ($row = Mysqli_fetch_ Array ($res)) {        echo $row [' Code ']. " \ t ";        echo $row [' name ']. " \ t ";        echo $row [' population ']. " \t\n ";    }}

Method 2:php the generated Excel file in the server first, then return the file path to Js,js and then use window.open () to open the file path to download.

The PHP interface code is as follows:

$mysqli = Mysqli_connect (' localhost ', ' root ', ' 123456 ', ' test '); $sql = ' select * from country '; $res = Mysqli_query ($mysqli , $sql); $file = fopen ('./country.xls ', ' W '); Fwrite ($file, "code\tname\tpopulation\t\n"); if (Mysqli_num_rows ($res) > 0) {    while ($row = Mysqli_fetch_array ($res)) {        fwrite ($file, $row [' Code ']. \ t ". $row [' name ']." \ t ". $row [' population ']." \t\n ");}    } Fclose ($file); Echo ' Http://www.jtw.com/....../country.xls ';//return file path to JS here

The two methods are similar, can be implemented to export the data in the database into an Excel file and download the file, the final file is as follows:

Export database data to an Excel file using native PHP