How to import and export Excel files with PHP_PHP tutorial

Let me introduce to you two examples of operating excel in PHP. One is to use PHP-ExcelReader to import excel and output it, and the other is to directly input excel and export it. Let's see the examples below.

With the help of the PHP-ExcelReader open source class, we can easily import Excel file data. The sample code is as follows:

PHP-ExcelReader download address: http://sourceforge.net/projects/phpexcelreader/

Example. Import Excel file

The code is as follows

Copy code

代码如下	复制代码
require_once 'Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('gbk'); $data->read('test.xls'); for ($i = 1; $i sheets[0]['numRows']; $i++) {     for ($j = 1; $j sheets[0]['numCols']; $j++) {         echo """.$data->sheets[0]['cells'][$i][$j]."",";     }     echo "n"; } ?>

require_once 'Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('gbk'); $data->read('test.xls');

for ($i = 1; $i sheets[0]['numRows']; $i++) {

for ($j = 1; $j sheets[0]['numCols']; $j++) {

代码如下

复制代码

require_once 'reader.php'; // ExcelFile($filename, $encoding); $data = new Spreadsheet_Excel_Reader(); // Set output Encoding. $data->setOutputEncoding('gbk'); //”data.xls”是指要导入到mysql中的excel文件 $data->read('data.xls'); @ $db = mysql_connect('localhost', 'root', '123456') or        die("Could not connect to database.");//连接数据库 mysql_query("set names 'gbk'");//输出中文 mysql_select_db('mydb');       //选择数据库 error_reporting(E_ALL ^ E_NOTICE); for ($i = 1; $i sheets[0]['numRows']; $i++) { //以下注释的for循环打印excel表数据 /* for ($j = 1; $j sheets[0]['numCols']; $j++) {             echo """.$data->sheets[0]['cells'][$i][$j]."",";            }            echo "n"; //PHP开源代码 */ //以下代码是将excel表数据【3个字段】插入到mysql中，根据你的excel表字段的多少，改写以下代码吧！     $sql = "INSERT INTO test VALUES('".                $data->sheets[0]['cells'][$i][1]."','".                  $data->sheets[0]['cells'][$i][2]."','".                  $data->sheets[0]['cells'][$i][3]."')";     echo $sql.' ';        $res = mysql_query($sql); } ?>

            echo """.$data->sheets[0]['cells'][$i][$j]."","; } echo "n"; } ?>

Example.phpexcel to excel 1. test.php

The code is as follows

Copy code

require_once 'reader.php'; // ExcelFile($filename, $encoding); $data = new Spreadsheet_Excel_Reader(); // Set output Encoding. $data->setOutputEncoding('gbk'); //"data.xls" refers to the excel file to be imported into mysql $data->read('data.xls'); @ $db = mysql_connect('localhost', 'root', '123456') or          die("Could not connect to database.");//Connect to the database mysql_query("set names 'gbk'");//Output Chinese mysql_select_db('mydb'); //Select database error_reporting(E_ALL ^ ​​E_NOTICE); for ($i = 1; $i sheets[0]['numRows']; $i++) { //The following commented for loop prints excel table data /* for ($j = 1; $j sheets[0]['numCols']; $j++) {                   echo """.$data->sheets[0]['cells'][$i][$j]."",";            } echo "n"; //PHP open source code */ //The following code inserts excel table data [3 fields] into mysql. Rewrite the following code according to the number of fields in your excel table! $sql = "INSERT INTO test VALUES('".                    $data->sheets[0]['cells'][$i][1]."','".                        $data->sheets[0]['cells'][$i][2]."','". $data->sheets[0]['cells'][$i][3]."')"; echo $sql.' ';          $res = mysql_query($sql); } ?>

Example. Export excel file

For example, if I need a program to export Excel from PHP, I only need to export the relevant data to the Excel table. Such a simple operation does not require the use of those class libraries. Just use the header method directly: header("Content-type:application/vnd.ms-excel");

Look at the code:

$head="Number".$tab."Remarks".$br; //The output content is as follows:

The code is as follows

代码如下

复制代码

header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=Export_test.xls"); $tab="t"; $br="n"; $head="编号".$tab."备注".$br; //输出内容如下：  echo $head.$br; echo  "test321318312".$tab;  echo  "string1"; echo  $br; echo  "330181199006061234".$tab;  //直接输出会被Excel识别为数字类型 echo  "number"; echo  $br; echo  "="330181199006061234"".$tab;  //原样输出需要处理 echo  "string2"; echo  $br; ?>

Copy code

header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=Export_test.xls");

代码如下	复制代码
header("Content-Type:   application/msword");        header("Content-Disposition:   attachment;   filename=doc.doc");

$tab="t"; $br="n";

echo $head.$br; echo "test321318312".$tab;

echo "string1";

echo $br; echo "330181199006061234".$tab; //Direct output will be recognized by Excel as a numeric type echo "number"; echo $br; echo "="330181199006061234"".$tab; //The original output needs to be processed echo "string2"; echo $br; ?>

A problem will be found after exporting. If the data is numeric, some unexpected situations will occur. For example, "012345" will become "12345" in Excel; if you enter a long number such as an ID number, it will be expressed in scientific notation in Excel, and the last four digits will be deviated and changed to 0000 etc. This requires setting the cell to text format by echo "="330181199006061234""If the program is encoded in UTF-8, you need to use the iconv function to transcode it, otherwise it will be garbled and garbled.

The other word format import is similar, just specify the header:

The code is as follows

Copy code

header("Content-Type: application/msword"); header("Content-Disposition: attachment; filename=doc.doc"); http://www.bkjia.com/PHPjc/633143.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/633143.htmlTechArticleLet me introduce to you two examples of operating excel in php. One is to use PHP-ExcelReader to import excel and Output, the other is to directly input into excel and export, see the example below. With...