The example in this article describes how to import excel files into mysql database in php. Share it with everyone for your reference. The specific analysis is as follows:
When php imports excel files into mysql database, we need to use a phpexcel class file. With this class file, we can quickly and easily import excel into mysql database. Here is an example to explain the details. Usage.
We need to prepare a database before importing. The sql statement code is as follows:
The code is as follows:
/*
Navicat MySQL Data Transfer
Source Server: localhost
Source Server Version: 50133
Source Host: localhost:3306
Source Database: test
Target Server Type: MYSQL
Target Server Version: 50133
File Encoding: 65001
Date: 2011-10-11 14:11:38
*/
SET FOREIGN_KEY_CHECKS=0;
-------------------------------
-- Table structure for `execl`
-------------------------------
DROP TABLE IF EXISTS `execl`;
CREATE TABLE `execl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
----------------------------
-- Records of execl
-------------------------------
INSERT INTO `execl` VALUES ('14', 'jim');
INSERT INTO `execl` VALUES ('15', 'taurus');
PHP processing program, here we need to use a phpexcel class file. This can be downloaded by Baidu search. The code is as follows:
Navicat MySQL Data Transfer
Source Server: localhost
Source Server Version: 50133
Source Host: localhost:3306
Source Database: test
Target Server Type: MYSQL
Target Server Version: 50133
File Encoding: 65001
Date: 2011-10-11 14:11:38
*/
SET FOREIGN_KEY_CHECKS=0;
-------------------------------
-- Table structure for `execl`
-------------------------------
DROP TABLE IF EXISTS `execl`;
CREATE TABLE `execl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
----------------------------
-- Records of execl
-------------------------------
INSERT INTO `execl` VALUES ('14', 'jim');
INSERT INTO `execl` VALUES ('15', 'taurus');
PHP processing program, here we need to use a phpexcel class file. This can be downloaded by Baidu search. The code is as follows:
The code is as follows:
if($_FILES['execl']['name']){
$db = mysql_connect('localhost','root','');
mysql_select_db('test');
mysql_query('set names gbk');
require_once 'reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP936');
$data->read($_FILES['execl']['name']);
error_reporting(E_ALL ^ E_NOTICE);
$sql = "";
for($i=1;$i<=$data->sheets[0]['numRows'];$i )
{
if($data->sheets[0]['cells'][$i][1]!=""){
$sql = "INSERT INTO `execl`(`name`)values('".$data->sheets[0]['cells'][$i][2]."');";
if(mysql_query($sql)){
echo 'success';
}else{
die('failed');
}
}
}
}
?>
$db = mysql_connect('localhost','root','');
mysql_select_db('test');
mysql_query('set names gbk');
require_once 'reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP936');
$data->read($_FILES['execl']['name']);
error_reporting(E_ALL ^ E_NOTICE);
$sql = "";
for($i=1;$i<=$data->sheets[0]['numRows'];$i )
{
if($data->sheets[0]['cells'][$i][1]!=""){
$sql = "INSERT INTO `execl`(`name`)values('".$data->sheets[0]['cells'][$i][2]."');";
if(mysql_query($sql)){
echo 'success';
}else{
die('failed');
}
}
}
}
?>
I hope this article will be helpful to everyone’s PHP programming design.
