PHP uses PHPExcel to implement batch upload to database

The following editor will bring you an article on how to use PHP to upload batches to the database using PHPExcel. The editor thinks it’s pretty good, so I’ll share it with you now and give it as a reference. Let's follow the editor and take a look.

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

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

  





  
　　　　　

    
上传表格
    
请先下载excel例子模板编辑后上传文件

    
请选择你要上传的文件
    

    
    

  

Run result:

Backend Process.php page

alert(您未选择表格);history.go(-1);";
}

//获取表格的大小，限制上传表格的大小5M
$file_size = $_FILES['myfile']['size'];
if ($file_size>5*1024*1024) {
echo "";
  exit();
}

//限制上传表格类型
$file_type = $_FILES['myfile']['type'];
//application/vnd.ms-excel 为xls文件类型
if ($file_type!='application/vnd.ms-excel') {
  echo "";
 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 "";
      
    }else{
      echo "";
      exit();
    }
  }
}
}

//调用
upExecel();
?>

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

Related recommendations:

php implementationBatch uploadmethod of data to database

thinkPHP5 framework Integrate plupload to implement image Batch upload

Thinkphp5 PHPExcel implementationBatch uploadForm data function

The above is the detailed content of PHP uses PHPExcel to implement batch upload to database. For more information, please follow other related articles on the PHP Chinese website!