search

Home >Backend Development >PHP Tutorial >PHP Excel class reads excel files and imports into database_PHP tutorial

PHP Excel class reads excel files and imports into database_PHP tutorial

WBOY
WBOYOriginal
2016-07-13 17:06:201148browse

Under normal circumstances, if we want to import the data in the excel database into the mysql database, we have no good way to achieve it, but with PHP, everything becomes simple after Excel loses control.

This code was started in thinkphp

1.Introduce classes

The code is as follows Copy code
 代码如下 复制代码

Vendor('PHPExcel.PHPExcel');//引入扩展类.就是/
        Vendor('PHPExcel.PHPExcel.IOFactory');
        Vendor('PHPExcel.PHPExcel.Reader.Excel5');

 

$excel_file= ROOT_PATH."/public/Uploads/".$publicity_bankdata_mod->where("id=".$data['id'])->getField('excel_file');
        //dump($excel_file);exit;
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($excel_file);//$uploadfile
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$arr_result = array();
$strs=array();
$strs_datas=array();
$succ_result=0;
$error_result=0;
Vendor('PHPExcel.PHPExcel');//Introducing extension classes. That is/

Vendor('PHPExcel.PHPExcel.IOFactory');
Vendor('PHPExcel.PHPExcel.Reader.Excel5');

 代码如下 复制代码
set_time_limit(20000);
ini_set('memory_limit','-1');
require_once './PHPExcel.php';
require_once './PHPExcel/IOFactory.php';
require_once './PHPExcel/Reader/Excel5.php';

//使用pdo连接数据库
$dsn = "mysql:host=localhost;dbname=alumni;";
$user = "root";
$password = "";
try{
$dbh = new PDO($dsn,$user,$password);
$dbh->query('set names utf8;');
}catch(PDOException $e){
 echo "连接失败".$e->getMessage();
}
//pdo绑定参数操作
$stmt = $dbh->prepare("insert into alumni(gid,student_no,name) values (:gid,:student_no,:name) ");
$stmt->bindParam(":gid", $gid,PDO::PARAM_STR);
$stmt->bindParam(":student_no", $student_no,PDO::PARAM_STR);
$stmt->bindParam(":name", $name,PDO::PARAM_STR);
 
$objReader = new PHPExcel_Reader_Excel5(); //use excel2007
$objPHPExcel = $objReader->load('bks.xls'); //指定的文件
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
 
for($j=1;$j<=10;$j++)
{

$student_no = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//第一列学号
$name = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//第二列姓名
$gid = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//第三列gid
}
//将获取的excel内容插入到数据库
$stmt->execute();
?>
$excel_file= ROOT_PATH."/public/Uploads/".$publicity_bankdata_mod->where("id=".$data['id'])->getField('excel_file'); ​​​​ //dump($excel_file);exit; $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format $objPHPExcel = $objReader->load($excel_file);//$uploadfile $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // Get the total number of rows $highestColumn = $sheet->getHighestColumn(); // Get the total number of columns $arr_result = array(); $strs=array(); $strs_datas=array(); $succ_result=0; $error_result=0;
The above looks a bit messy, let me write a complete class
The code is as follows Copy code
set_time_limit(20000);<🎜> ini_set('memory_limit','-1');<🎜> require_once './PHPExcel.php';<🎜> require_once './PHPExcel/IOFactory.php';<🎜> require_once './PHPExcel/Reader/Excel5.php';<🎜> <🎜> //Use pdo to connect to the database<🎜> $dsn = "mysql:host=localhost;dbname=alumni;";<🎜> $user = "root";<🎜> $password = "";<🎜> try{<🎜> $dbh = new PDO($dsn,$user,$password);<🎜> $dbh->query('set names utf8;'); }catch(PDOException $e){ echo "Connection failed".$e->getMessage(); } //pdo binding parameter operation $stmt = $dbh->prepare("insert into alumni(gid,student_no,name) values ​​(:gid,:student_no,:name) "); $stmt->bindParam(":gid", $gid,PDO::PARAM_STR); $stmt->bindParam(":student_no", $student_no,PDO::PARAM_STR); $stmt->bindParam(":name", $name,PDO::PARAM_STR); $objReader = new PHPExcel_Reader_Excel5(); //use excel2007 $objPHPExcel = $objReader->load('bks.xls'); //Specified file $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // Get the total number of rows $highestColumn = $sheet->getHighestColumn(); // Get the total number of columns for($j=1;$j<=10;$j++)<🎜> {<🎜> <🎜> $student_no = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//The first column of student number $name = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//Second column name $gid = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//The third column gid } //Insert the obtained excel content into the database $stmt->execute(); ?>

php-excel-reader operates two important methods in excel:

1.dump(), which can output excel content in html format:

echo $data->dump(true,true);

2. Store the excel data into an array, use $data->sheets, and print it as follows:

The code is as follows Copy code

Array
(
[0] => Array
(
[maxrow] => 0
[maxcol] => 0
[numRows] => 5
[numCols] => 4
[cells] => Array
(
[1] => Array
(
[1] => Number
[2] => Name
[3] => Age
[4] => Student ID
)
[2] => Array
(
[1] => 1
[2] => Xiaohong
[3] => 22
[4] => a1000
)
[3] => Array
(
[1] => 2
[2] => Xiao Wang
[3] => 33
[4] => a1001
)
[4] => Array
(
[1] => 3
[2] => Xiaohei
[3] => 44
[4] => a1002
)
[5] => Array
(
[2] => by
[3] => www.phpddt.com
)
)
[cellsInfo] => Array
(
[1] => Array
(
[1] => Array
(
[xfIndex] => 15
)
[2] => Array
(
[xfIndex] => 15
)
[3] => Array
(
[xfIndex] => 15
)
[4] => Array
(
[xfIndex] => 15
)
)
[2] => Array
(
[1] => Array
(
[string] => 1
[raw] => 1
[rectype] => unknown
[format] => %s
[formatIndex] => 0
[fontIndex] => 0
[formatColor] =>
[xfIndex] => 15
)
[2] => Array
(
[xfIndex] => 15
)
[3] => Array
(
[string] => 22
[raw] => 22
[rectype] => unknown
[format] => %s
[formatIndex] => 0
[fontIndex] => 0
[formatColor] =>
[xfIndex] => 15
)
[4] => Array
(
[xfIndex] => 15
)
)
[3] => Array
(
[1] => Array
(
[string] => 2
[raw] => 2
[rectype] => unknown
[format] => %s
[formatIndex] => 0
[fontIndex] => 6
[formatColor] =>
[xfIndex] => 23
)
[2] => Array
(
[xfIndex] => 23
)
[3] => Array
(
[string] => 33
[raw] => 33
[rectype] => unknown
[format] => %s
[formatIndex] => 0
[fontIndex] => 6
[formatColor] =>
[xfIndex] => 23
)
[4] => Array
(
[xfIndex] => 23
)
)
[4] => Array
(
[1] => Array
(
[string] => 3
[raw] => 3
[rectype] => unknown
[format] => %s
[formatIndex] => 0
[fontIndex] => 0
[formatColor] =>
[xfIndex] => 15
)
[2] => Array
(
[xfIndex] => 15
)
[3] => Array
(
[string] => 44
[raw] => 44
[rectype] => unknown
[format] => %s
[formatIndex] => 0
[fontIndex] => 0
[formatColor] =>
[xfIndex] => 15
)
[4] => Array
(
[xfIndex] => 15
)
)
[5] => Array
(
[2] => Array
(
[xfIndex] => 15
)
[3] => Array
(
[xfIndex] => 24
[hyperlink] => Array
(
[flags] => 23
[desc] => www.bKjia.c0m

[link] => http://www.phpddt.co
)
)
)
)
)
[1] => Array
(
[maxrow] => 0
[maxcol] => 0
[numRows] => 0
[numCols] => 0
)
[2] => Array
(
[maxrow] => 0
[maxcol] => 0
[numRows] => 0
[numCols] => 0
)
)

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630697.htmlTechArticleUnder normal circumstances, if we want to import the data in the excel database into the mysql database, we have no good way to achieve it. But with PHP Excel out of control, everything becomes easier. This generation...
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
Previous article：PHP ajax real input automatic search matching program code_PHP tutorialNext article：PHP ajax real input automatic search matching program code_PHP tutorial

Related articles

See more