Detailed explanation of the steps to read and write excel files using native PHP

This time I will bring you a detailed explanation of the steps to read and write excel files in native php. What are the precautions for native php to implement excel files? The following is a practical case, let’s take a look.

I recently encountered a requirement at work. I need to export the data in the database to an excel file and download the excel file. Since I haven't done it before, I looked it up on Baidu. Most of the people on the Internet talk about using the PHPExcel class to operate excel files. You have to download this class before you can use it. But I just want to use native PHP and don't want to be so troublesome. Fortunately Some netizens also talked about the method of generating excel files using native PHP. It is actually very simple. Now I will share the code I have practiced based on online information.

Generally, our data import operation is implemented by the user clicking a button on the web page to trigger the corresponding js method, and then requesting the php interface. Therefore, there are two main methods to complete this requirement. .

Method 1: Use window.open() directly in the js code to open the url of the php interface, and you can download the excel file generated by php.

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: In the php interface, first save the generated excel file in the server, and then return the file path to js, ​​js 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';//这里返回文件路径给js

The two methods are very similar. They can export the data in the database to an excel file and download the file. The final file screenshot is as follows:

If necessary, you can also use native PHP to read the contents of the excel file. This is mainly used when the data in the excel file needs to be imported into the database.

The code is as follows: (Here we only show how to read file data into an array)

$path = './country.xls';
$file = fopen($path, 'r');
//标题行读取（第一行）
$row = fgets($file);
$row = explode("\t", $row);
$title = array();
foreach($row as $k => $v) {
  $title[$k] = str_replace("\n", '', $v);
}
//内容读取
$data = array();
$count = 0;
while(!feof($file)) {
  $row = fgets($file);
  $row = explode("\t", $row);
  if(!$row[0]) continue;//去除最后一行
  foreach($title as $k => $v) {
    $data[$count][$title[$k]] = $row[$k];
  }
  $count ++;
}
fclose($file);
echo &#39;<pre class="brush:php;toolbar:false">&#39;;
print_r($data);

However, there is a problem with excel files generated using native php, that is, every time you edit the file When saving a file, the following screenshot problem always occurs:

# I don’t know the reason, maybe there are some problems with the generated file itself. . .

And when using native PHP to read the generated excel file, there will be some weird situations such as Chinese garbled characters. Therefore, it is best to use native PHP to generate excel files only under certain circumstances: simply exporting data from the database to a file for easy viewing, without modifying the file or reading the file. In this case, using native PHP to generate excel is enough to meet the needs, eliminating the trouble of using third-party libraries to operate excel. However, if you still need to modify, save, and read data after generating the file, you should just use third-party libraries such as phpexcel to perform read and write operations, which can avoid many tangled problems.

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the php Chinese website!

Recommended reading:

Detailed explanation of the implementation steps of laravel operation SMS sending verification code function

Detailed explanation of PHP singleton mode use case

The above is the detailed content of Detailed explanation of the steps to read and write excel files using native PHP. For more information, please follow other related articles on the PHP Chinese website!