获取excel文件并写入数据库

<?php

//引入类库
include "../../jcsrm/lib/phpexcel/PHPExcel.php";
include "../../jcsrm/config.php";
// include "../lib/phpexcel/PHPExcel.php";
// include "../config.php";
//include "../lib/phpexcel/PHPExcel/IOFactory.php"
/*读取excel文件，并进行相应处理*/
ini_set('memory_limit', '1024M');
$date = date("Ymd");
set_time_limit (0);
date_default_timezone_set("PRC");
$stime1=microtime(true); //用了来获取运行开始时间
  // 读取excel文件
  try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
  } catch(Exception $e) {
    die("加载文件发生错误");
  }
 $startTime = time(); //返回当前时间的Unix 时间戳
 
PHPExcel_CachedObjectStorageFactory::cache_to_wincache; //解决内存问题
// echo $objPHPExcel = PHPExcel_IOFactory::load($fileName);//获取sheet表格数目
$sheet = $objPHPExcel->getSheet();//默认选中sheet0表
$rowCount = $sheet->getHighestRow();//获取表格行数
$columnCount = $sheet->getHighestColumn();//获取表格列数

$etime1=microtime(true);//获取程序执行结束的时间
$total1=$etime1-$stime1;   //计算差值

ini_set('execution_time', 2000000);//秒为单位，自己根据需要定义
$stime=microtime(true); 
$date = date("Ymd");
$inputFileName = "../wuhandata/07_orderlist/sz_orderlist_"."$date".".xlsx";
include"../getExcelFunction.php";


 
//  addslashes() 函数返回在预定义字符之前添加反斜杠的字符串。

// 预定义字符是：

// 单引号（'）
// 双引号（"）
// 反斜杠（\）
// NULL

// $search = array(" ","　","\n","\r","\t",";","；");
// $replace = array("","","","","","","");
// str_replace($search, $replace, $str);
$count = 0;
        for($currentRow =2;$currentRow <= $rowCount;$currentRow++)
        {
//if($currentRow>$rowCount)  {break; } else{ continue;}
         $barcode= addslashes($objPHPExcel->getActiveSheet()->getCell("A".$currentRow)->getValue());//获取A列的值


         $order_no= addslashes($objPHPExcel->getActiveSheet()->getCell("B".$currentRow)->getValue());//获取B列的值


         $project_code= addslashes($objPHPExcel->getActiveSheet()->getCell("C".$currentRow)->getValue());//获取C列的值


         $fg_part_no= addslashes($objPHPExcel->getActiveSheet()->getCell("D".$currentRow)->getValue());//获取D列的值


         $serial_number= addslashes($objPHPExcel->getActiveSheet()->getCell("E".$currentRow)->getValue());//获取E列的值


         $material_part_no= addslashes($objPHPExcel->getActiveSheet()->getCell("F".$currentRow)->getValue());//获取F列的值


         $material_name= addslashes($objPHPExcel->getActiveSheet()->getCell("G".$currentRow)->getValue());//获取G列的值


         $mat_parameter= addslashes($objPHPExcel->getActiveSheet()->getCell("H".$currentRow)->getValue());//获取H列的值


         $qty= addslashes($objPHPExcel->getActiveSheet()->getCell("I".$currentRow)->getValue());//获取I列的值


         $unit= addslashes($objPHPExcel->getActiveSheet()->getCell("J".$currentRow)->getValue());//获取J列的值


         $vendor= addslashes($objPHPExcel->getActiveSheet()->getCell("K".$currentRow)->getValue());//获取K列的值

         $upload_time=date("Y-m-d H:i:s");
         //$serial_number= addslashes($objPHPExcel->getActiveSheet()->getCell("K".$currentRow)->getValue());//获取L列的值


         iconv_set_encoding ( "internal_encoding" ,"UTF-8") ;//iconv_set_encoding("internal_encoding", "UTF-8");
        // $material_name=iconv('utf-8','utf-8', $material_name);//消除乱码
         //$vendor=iconv('utf-8','utf-8', $vendor);//消除乱码

         $sql1 = "INSERT INTO  x_orderlist
        (barcode,order_no,project_code,fg_part_no,material_part_no,material_name,mat_parameter,qty,unit,vendor,serial_number,upload_time)
        VALUES
        ('".$barcode."','".$order_no."','".$project_code."','".$fg_part_no."',
        '".$material_part_no."','".$material_name."',
        '".$mat_parameter."','".$qty."','".$unit."','".$vendor."','".$serial_number."','".$upload_time."')";


$sql2 = "SELECT * FROM x_orderlist WHERE barcode = '$barcode'";
$retval2= mysqli_query( $db, $sql2);
$selectBarcode = mysqli_fetch_array($retval2,MYSQLI_ASSOC);


$sql3 = "UPDATE x_orderlist set order_no = '$order_no',project_code = '$project_code',fg_part_no='$fg_part_no',material_part_no='$material_part_no',material_name='$material_name',mat_parameter='$mat_parameter',qty='$qty',unit='$unit',vendor='$vendor',serial_number='$serial_number',upload_time='$upload_time' where barcode='$barcode' ";


if($selectBarcode['order_no'] == null){//判断该订单是否存在，如果不存在就执行插入语句
  $retval1 = mysqli_query( $db, $sql1);
  echo "<br>插入<br>";
}
else{
  $retval3 = mysqli_query( $db, $sql3);//如果存在就执行更新语句
  echo "更新";
} 
        
$count++;

//if($currentRow==$rowCount+1){break;}


}
mysql_close($db);
         


$etime=microtime(true);//获取程序执行结束的时间
$total=$etime-$stime;   //计算差值
echo "<br />读取excel执行时间为：{$total1} 秒";
echo "<br />写入数据库执行时间为：{$total} 秒";
echo "<div>行数： ".$rowCount."　　列数：".$columnCount."</div>";
//循环结束，判断全部数据是否插入

      if($currentRow > $rowCount){

      //echo '文件插入成功！';

      $message='文件上传成功！'; 
      print "{success:true,msg:'".$message."'}";

      }
      else {

      //echo '文件插入失败！';    

      $message='文件上传失败！';  
      print "{success:false,msg:'".$message."'}";

      }      


  
// echo '1';
  ?>