About the method of reading and writing excel files in native php

This article mainly introduces the method of reading and writing Excel files in native PHP. It analyzes the related implementation methods and operating precautions of using native PHP to read and write Excel files in the form of examples. Friends in need can refer to the following

The example of this article analyzes the method of reading and writing excel files in native PHP. I would like to share it with you for your reference. The details are as follows:

Recently, I encountered a need 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: php interface first save the generated excel file in In the server, the file path is then returned to js, ​​and js uses window.open() to open the file path and download it.

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, both can export the data in the database to an excel file and Download the file. The final screenshot of the file is as follows:

If necessary, you can also use native php to read the contents of the excel file. This is mainly used to convert the excel file into The data is 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, using excel generated by native php There is a problem with the file, that is, every time I save the file after editing it, the following screenshot will always appear: There may be some problems with the 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.

Method of exporting to Excel or CSV based on PHP

About the usage of Widget in ThinkPHP3.1

The above is the detailed content of About the method of reading and writing excel files in native php. For more information, please follow other related articles on the PHP Chinese website!