PHP用PHPExcel.php插件上传Excel文件导入数据到MySQL数据库

一、前端代码，及样式

     1、“导入”方式仅支持<span style="color:#ff0000;font-weight:600;">XLS或者XLSX</span> 这种格式文档；<br>

      2、*.xls 或者*.xlsx 要导入的文档必须包含所需字段的信息，不然有可能导入不成功；<br>

      3、单次导入文件不超过5M。<br><br><br>

      <form name="form1" enctype="multipart/form-data" method="post" action="import_excel.php" style="margin-bottom:400px;">
        文档编码：
          <label style="display:inline-block;width:auto;margin-right:10px;">
          <select name="file_encoding" style="margin-bottom:0;height:34px;">
            <option value="gbk">简体中文</option>
            <option value="utf8">UTF-8</option>
          </select>
          </label>
          <label style="display:inline-block;width:auto;margin-right:10px;">
          <input type="file" name="excel" class="btn" style="height:24px;line-height:24px;">
              </label>
            <label style="display:inline-block;width:auto;">
            <input type="submit"  value="确认上传" class="btn btn-success" style="line-height:24px;">
            </label>
    </form>

二、后端代码   import_excel.php

<?php 
include("../include/init.php");
include("check_user.php");
include('./phpexcel/PHPExcel.php');//引入PHPExcel库文件

$user_nichen = isset($_SESSION['user_nichen']) ? $_SESSION['user_nichen'] : "";

 if (!empty($_FILES['excel']['name'])) {
        $fileName = $_FILES['excel']['name'];    //得到文件全名
        $dotArray = explode('.', $fileName);    //把文件名安.区分，拆分成数组
        $type = end($dotArray);
        if ($type != "xls" && $type != "xlsx") {
            $ret['res'] = "0";
            $ret['msg'] = "不是Excel文件，请重新上传!";
            return json_encode($ret);
        }

        //取数组最后一个元素，得到文件类型
        $uploaddir = "../uploads2/" . date("Y-m-d") . '/';//设置文件保存目录 注意包含
        if (!file_exists($uploaddir)) {
            mkdir($uploaddir, 0777, true);
        }
 
        $path = $uploaddir . md5(uniqid(rand())) . '.' . $type; //产生随机文件名
        //$path = "images/".$fileName; //客户端上传的文件名；
        //下面必须是tmp_name 因为是从临时文件夹中移动
        move_uploaded_file($_FILES['excel']['tmp_name'], $path); //从服务器临时文件拷贝到相应的文件夹下
 
        $file_path = $path;
        if (!file_exists($path)) {
            $ret['res'] = "0";
            $ret['msg'] = "上传文件丢失!" . $_FILES['excel']['error'];
            return json_encode($ret);
        }

        //文件的扩展名
        $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
        if ($ext == 'xlsx') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($file_path, 'utf-8');
        } elseif ($ext == 'xls') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($file_path, 'utf-8');
        }
 
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数
        $ar = array();
        $i = 0;
        $importRows = 0;

// 获取一行的数据
for ($row = 1; $row <= 1; $row++){

$rowData = $sheet->rangeToArray('A1:' . $highestColumn . $row, NULL, TRUE, FALSE);

//var_dump($rowData[0]);echo "<br>";

    
    foreach ($rowData[0] as $key => $value) {

           if(trim($value) == "姓名"){
                    $col1 = PHPExcel_Cell::stringFromColumnIndex($key); //获取列号
           }
           if(trim($value) == "性别"){
                    $col2 = PHPExcel_Cell::stringFromColumnIndex($key);//获取列号
           }
           if(trim($value) == "登记号"){
                    $col3 = PHPExcel_Cell::stringFromColumnIndex($key);//获取列号
           }
           if(trim($value) == "制单时间"){
                    $col4 = PHPExcel_Cell::stringFromColumnIndex($key);//获取列号
           }
    }

}


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

            $bianhao =(string)$objPHPExcel->getActiveSheet()->getCell($col3.$j)->getValue(); //需要导入的bianhao
            $name =(string)$objPHPExcel->getActiveSheet()->getCell($col1.$j)->getValue(); //需要导入的name
            $xingbie = (string)$objPHPExcel->getActiveSheet()->getCell($col2.$j)->getValue();//需要导入的xingbie
            $date = (string)$objPHPExcel->getActiveSheet()->getCell($col4.$j)->getFormattedValue();//需要导入的date  即制单时间
            $date =date("Y-m-d H:i:s",strtotime($date));
     
      
            //echo $date; exit;

            //组装的添加数据
                   $data = array(
                       "name" =>$name,
                       "xingbie" =>$xingbie,
                       "bianhao" =>$bianhao,
                       "date" =>$date,

                       "daoshi" =>$user_nichen,
                       "date2" =>date("Y-m-d H:i:s")
                  


                     );

                    $affect_id= insert("{$yy_}hufu",$data);

                    if ($affect_id) {
                       $i++;
                     }
        }



        if ($i > 0) {

                            //写导入日记    /组装的添加数据
                           $data = array(
                               "excel_name" =>$fileName,
                               
                               "daoshi" =>$user_nichen,
                               "date2" =>date("Y-m-d H:i:s")
                             );

                            $affect_id2= insert("{$yy_}log_import",$data);
                            
            show_msg("导入完毕!","form_history_list.php");
        }

} else {
   show_msg("操作失败");
   exit;
   
}



?>