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:
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"; |
$head="Number".$tab."Remarks".$br;
//The output content is as follows:
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...
|
Statement：The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
