Example tutorial on batch importing data into database using phpExcel

This example only uses the .xls document of execel2003. If you are using other versions, you can save it in the format of "Execel 97-2003 workbook (*.xls)", which is the .xls file type!

Function Description: Only Excel2003 type xls files can be uploaded, the size does not exceed 5M. The downloadable example template can be uploaded after adding data!

Front-end test.php page

 上传表格<tr><td>请先<a href="./sample/sample01.xls">下载excel例子模板</a>编辑后上传文件</td></tr>
请选择你要上传的文件

Running results:

Backend Process.php page

<?phpheader("Content-type:text/html;charset=utf-8");//链接数据库$link = @mysql_connect(&#39;localhost&#39;,&#39;root&#39;,&#39;&#39;) or die(&#39;连接数据库失败&#39;);mysql_select_db(&#39;test&#39;,$link);mysql_query(&#39;set names utf8&#39;);function upExecel(){//判断是否选择了要上传的表格if (empty($_POST[&#39;myfile&#39;])) {echo "<script>alert(您未选择表格);history.go(-1);</script>";
}//获取表格的大小，限制上传表格的大小5M$file_size = $_FILES['myfile']['size'];if ($file_size>5*1024*1024) {echo "<script>alert('上传失败，上传的表格不能超过5M的大小');history.go(-1);</script>";exit();
}//限制上传表格类型$file_type = $_FILES['myfile']['type'];//application/vnd.ms-excel  为xls文件类型if ($file_type!='application/vnd.ms-excel') {echo "<script>alert('上传失败，只能上传excel2003的xls格式!');history.go(-1)</script>"; exit();
}//判断表格是否上传成功if (is_uploaded_file($_FILES['myfile']['tmp_name'])) {require_once 'PHPExcel.php';require_once 'PHPExcel/IOFactory.php';require_once 'PHPExcel/Reader/Excel5.php';//以上三步加载phpExcel的类$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format 
    //接收存在缓存中的excel表格$filename = $_FILES['myfile']['tmp_name'];$objPHPExcel = $objReader->load($filename); //$filename可以是上传的表格，或者是指定的表格$sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 
    // $highestColumn = $sheet->getHighestColumn(); // 取得总列数
    
    //循环读取excel表格,读取一条,插入一条
    //j表示从哪一行开始读取  从第二行开始读取，因为第一行是标题不保存
    //$a表示列号for($j=2;$j<=$highestRow;$j++)  
    {$a = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//获取A(业主名字)列的值$b = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//获取B(密码)列的值$c = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//获取C(手机号)列的值$d = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();//获取D(地址)列的值

        //null 为主键id，自增可用null表示自动添加$sql = "INSERT INTO house VALUES(null,'$a','$b','$c','$d')";// echo "$sql";
        // exit();$res = mysql_query($sql);if ($res) {echo "<script>alert('添加成功！');window.location.href='./test.php';</script>";
            
        }else{echo "<script>alert('添加失败！');window.location.href='./test.php';</script>";exit();
        }
    }
}
}//调用upExecel();?>

The effect is: if the file to be uploaded is not selected, "No table selected" will be prompted; if the table file is If it exceeds 5M, a prompt will appear; if the uploaded file type is not xls, a prompt will appear!

This completes the batch import of data into the database using PHPExcel. For information on using PHPExcel to export data, please refer to Using PHPExcel to batch export data into excel tables

The above is the detailed content of Example tutorial on batch importing data into database using phpExcel. For more information, please follow other related articles on the PHP Chinese website!