How to import and export Excel files with PHP_PHP tutorial-PHP Tutorial-php.cn

How to import and export Excel files with PHP_PHP tutorial

WBOY

Release： 2016-07-13 10:43:48

Original

778 people have browsed it

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 <= $data->sheets[0]['numRows']; $i++) { for ($j = 1; $j <= $data->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 <= $data->sheets[0]['numRows']; $i++) {

for ($j = 1; $j <= $data->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 <= $data->sheets[0]['numRows']; $i++) {
//以下注释的for循环打印excel表数据
/*
for ($j = 1; $j <= $data->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 <= $data->sheets[0]['numRows']; $i++) { //The following commented for loop prints excel table data /* for ($j = 1; $j <= $data->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");