方法一
function upExecel(){//判断是否选择了要上传的表格if (empty($_POST['myfile'])) { echo "alert(您未选择表格);history.go(-1);";}//获取表格的大小，限制上传表格的大小5M$file_size = $_FILES['myfile']['size'];if ($file_size>5*1024*1024) {echo "alert('上传失败，上传的表格不能超过5M的大小');history.go(-1);"; exit();}//限制上传表格类型$file_type = $_FILES['myfile']['type'];//application/vnd.ms-excel 为xls文件类型if ($file_type!='application/vnd.ms-excel') { echo "alert('上传失败，只能上传excel2003的xls格式!');history.go(-1)"; exit();}//判断表格是否上传成功if (is_uploaded_file($_FILES['myfile']['tmp_name'])) { require_once 'PHPExcel-1.8/Classes/PHPExcel.php'; require_once 'PHPExcel-1.8/Classes/PHPExcel/IOFactory.php'; require_once 'PHPExcel-1.8/Classes/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($z=2;$z<=$highestRow;$z++) { // echo "$sql"; $a = $objPHPExcel->getActiveSheet()->getCell("A".$z)->getValue();//获取A(业主名字)列的值 $b = $objPHPExcel->getActiveSheet()->getCell("B".$z)->getValue();//获取B(密码)列的值 $c = $objPHPExcel->getActiveSheet()->getCell("C".$z)->getValue();//获取C(手机号)列的值 $d = $objPHPExcel->getActiveSheet()->getCell("D".$z)->getValue();//获取D(地址)列的值 $e = $objPHPExcel->getActiveSheet()->getCell("E".$z)->getValue(); $f = $objPHPExcel->getActiveSheet()->getCell("F".$z)->getValue(); $g = $objPHPExcel->getActiveSheet()->getCell("G".$z)->getValue(); $h = $objPHPExcel->getActiveSheet()->getCell("G".$z)->getValue(); $i = $objPHPExcel->getActiveSheet()->getCell("I".$z)->getValue(); $j = $objPHPExcel->getActiveSheet()->getCell("J".$z)->getValue(); //null 为主键id，自增可用null表示自动添加 $res= mysql_query("INSERT INTO db2mistake VALUES(null,'$a','$b','$c','$d','$e','$f','$g','$h','$i','$j')"); // exit(); if ($res) { echo "alert('添加成功！');window.location.href='./uploadhtml.html';"; }else{ echo "alert('添加失败！');window.location.href='./uploadhtml.html';"; exit(); } }}}//调用upExecel();
方法二
?php define('BASE_URL', realpath(dirname(__FILE__))); require_once BASE_URL . '/PHPExcel-1.8/Classes/PHPExcel.php';//引入PHPExcel类文件 //excel文件的地址 $excel_fiel_path = './mistakes1.xls'; $$host ='localhost'; $user = "root"; $password = "";$database = "contract resource management"; $server = mysql_connect($host, $user, $password); $conn = mysql_select_db($database, $server); $PHPExcel = new PHPExcel();// 实例化PHPExcel工具类 //分析文件获取后缀判断是2007版本还是2003 $extend = pathinfo("./" . $excel_fiel_path); $extend = strtolower($extend["extension"]); // 判断xlsx版本，如果是xlsx的就是2007版本的，否则就是2003 if ($extend=="xlsx") { $PHPReader = new PHPExcel_Reader_Excel2007(); $PHPExcel = $PHPReader-load("./" . $excel_fiel_path); }else{ $PHPReader = new PHPExcel_Reader_Excel5(); $PHPExcel = $PHPReader->load("./" . $excel_fiel_path); } /* 第二种方法*/ $objWorksheet = $PHPExcel->getActiveSheet(); $highestRow = $objWorksheet->getHighestRow(); echo 'highestRow='.$highestRow; echo "
"; $highestColumn = $objWorksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数 echo 'highestColumnIndex='.$highestColumnIndex; echo "
"; $headtitle=array(); for ($row = 2;$row <= $highestRow;$row++) { $strs=array(); //注意highestColumnIndex的列数索引从0开始 for ($col = 0;$col < $highestColumnIndex;$col++) { $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); } //todo //连接mysql ，一条条写入 $res= mysql_query("INSERT INTO db2mistake VALUES(null,'$strs[0]','$strs[1]','$strs[2]','$strs[3]','$strs[4]','$strs[5]','$strs[6]','$strs[7]','$strs[8]','$strs[9]')"); if ($res) { echo "alert('添加成功！');window.location.href='./test.html';"; }else{ echo "alert('添加失败！');window.location.href='./test.html';"; exit(); }}
