A method of native PHP to read and write Excel files

This article mainly introduces the native PHP implementation of Excel file reading and writing methods, combined with the example of the use of native PHP for Excel read and write operation of the relevant implementation methods and operational considerations, the need for friends can refer to the following

This paper analyzes the method of native PHP to read and write Excel files. Share to everyone for your reference, as follows:

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 on Baidu a bit, online said mostly use Phpexcel class to operate Excel file, this also to download this class to use, and I just want to use the native PHP, do not want to be so troublesome, fortunately there are netizens talking about native PHP to generate Excel file method, In fact, it is very simple, the following I combined with the online information of their own practice code to share.

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 methods to complete this requirement.

Method 1: directly in the JS code using window.open() the URL to open 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:the PHP interface first save the generated Excel file in the server, and then return the file path to Js,js and then use the window.open() Open 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 ");//not well written here, you should assemble all the file contents into a string and write the file once.  }} 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:

If necessary, you can also use native PHP to read the contents of an Excel file, primarily for situations where you need to import data from an Excel file into a database.

The code is as follows: (This shows only reading the file data into an array)

$path = './country.xls '; $file = fopen ($path, ' r ');//header row read (first row) $row = Fgets ($file); $row = explode ("\ t", $row); $title = Arra Y (), foreach ($row as $k = + $v) {  $title [$k] = str_replace ("\ n", "', $v);} Content Read $data = Array (), $count = 0;while (!feof ($file)) {  $row = fgets ($file);  $row = explode ("\ t", $row);  if (! $row [0]) continue;//removes the last line of  foreach ($title as $k = = $v) {$data [$count] [    $title [$k]] = $row [$k];  }  $count + +;} Fclose ($file); Echo ' <pre> ';p rint_r ($data);

However, using native PHP-generated Excel files, there is a problem, that is, each time you edit a file after saving the file will always appear the following problems:

Also do not know what is the reason, probably the generated file itself has some problems it ...

And the use of native PHP to generate the Excel file to read when there will be some wonderful things such as Chinese garbled. Therefore, using native PHP to generate Excel files is best used only in some cases: simply export the data from the database to a file for easy viewing, no need to modify the file, and no need to read the file. This scenario uses native PHP to generate Excel enough to meet your needs, eliminating the hassle of using a third-party class library to manipulate Excel. However, if the file after the creation of changes to save, read data requirements, it is still honest use of third-party libraries such as Phpexcel to read and write operations, you can avoid a lot of tangled problems.

The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!