通过phpexcel读取excel文件到数据库实例

最新推荐文章于 2025-06-19 10:29:52 发布

puddingpp

最新推荐文章于 2025-06-19 10:29:52 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/puddingpp/article/details/79030615

这篇博客展示了如何使用PHPExcel库从上传的Excel文件中读取数据，并将其插入到MySQL数据库中。首先，JavaScript部分处理文件上传，然后PHP代码连接到数据库并根据Excel文件的格式读取数据，最后将数据批量插入到指定的表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

js:

$('input[id=lefile]').change(function() {
               $('#photoCover').val($(this).val());
           });

           function doUpload(){
               var formData = new FormData($("#uploadForm")[0]);
               $.ajax({
                   url:'http://localhost/IQCInspect/resources/upload.php',
                   type:'POST',
                   data:formData,
                   async:false,
                   contentType:false,
                   processData:false,
                   success:function(returndata){
                       alert(returndata);
                   },
                   error:function(returndata){
                       alert(returndata);
                   }
               });
           }

php:

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
header("Access-Control-Allow-origin:*");
header("Content-type:text/html;charset=utf-8");

$link=@mysql_connect('localhost','root','pudding') or die('连接数据库失败');
mysql_select_db('upload_iccid',$link);
mysql_query('set names utf8');

function upExecel(){

   if (empty($_FILES['file']['name'])){
       echo "您未选择表格";
       exit();
   }

   $ext = strtolower(substr(strrchr($_FILES['file']['name'],"."),1));

   if(is_uploaded_file($_FILES['file']['tmp_name'])){
       require_once '../../phpexcel/PHPExcel.php';
       require_once '../../phpexcel/PHPExcel/IOFactory.php';
       require_once '../../phpexcel/PHPExcel/Reader/Excel2007.php';
       require_once '../../phpexcel/PHPExcel/Reader/Excel5.php';


       if($ext == 'xlsx'){
           $objReader = PHPExcel_IOFactory::createReader('Excel2007');
       }else if($ext == 'xls'){
           $objReader = PHPExcel_IOFactory::createReader('Excel5');
       }else{
           echo "上传失败,只能上传excel的格式！";
           exit();
       }


       $filename = $_FILES['file']['tmp_name'];
       $objPHPExcel = $objReader->load($filename);
       $sheet = $objPHPExcel->getSheet(0);
       $highestRow = $sheet->getHighestRow();
//       $highestColumn = $sheet->getHighestColumn();

       for($i=2;$i<$highestRow;$i++){
           $val1 = $sheet->getCellByColumnAndRow(0, $i)->getValue();
           $val2 = $sheet->getCellByColumnAndRow(0, $i+1)->getValue();

           if(strlen($val1)!=strlen($val2)){
              echo "上传失败,iccid长度不一样！";
              exit();
           }

       }



       for($j=2;$j<=$highestRow;$j++){

           $a = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();
           $b = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
           $c = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
           $d = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();

           $sql = "INSERT ignore INTO iccid(iccid,partno,lotno,po,TIME) VALUES('$a','$b','$c','$d',curdate())";

//           echo "$sql";
//           exit();
           $res = mysql_query($sql) or die(mysql_error());

           if(!$res){

               echo "添加失败！";
               exit();
           }

       }
       echo '添加成功！';


   }


}

upExecel();

?>

通过phpexcel读取excel文件到数据库 实例

通过phpexcel读取excel文件到数据库实例