excel上传–phpExcel读取xls、xlsx
// 不同的后缀文件需要不同的加载方式
$extension = strtolower( pathinfo($filename, PATHINFO_EXTENSION) );
if ($extension =='xlsx') {
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader ->load($filename);
}else{
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($filename); //$filename可以是上传的文件,或者是指定的文件
}
实例:(需要根据自身情况做调整)
<?php
// 接收前台页面传过来的文件信息
public function mdUpload() {
// 搜索标记
if (!empty($_FILES['file_stu']['name'])){
$tmp_file = $_FILES['file_stu']['tmp_name'];
$file_types = explode( ".",$_FILES ['file_stu']['name']);
$file_type = $file_types[count($file_types) - 1];//文件类型
/*判别是不是.xls文件,判别是不是excel文件*/
if (strtolower($file_type) != "xls" && strtolower($file_type) != "xlsx")
{
$this->Session->setFlash("不是Excel文件,重新上传");
$this->redirect("orderList");
}
/*设置上传路径*/
$riqi = date("Ymd");
$savePath = "./upload/excel/ryupload/".$riqi;
if (!file_exists($savePath)){
mkdir ($savePath,0777,true);
}
/*以时间来命名上传的文件*/
$str = date('Ymdhis');
$file_name = $str.".".$file_type;
/*是否上传成功*/
if (!copy($tmp_file,$savePath.'/'.$file_name))
{
$this->Session->setFlash("上传失败");
$this->redirect("orderList");
}
$res = $this->writeMessage($savePath.'/'.$file_name);
if ($res == '') {
$this->Session->setFlash("上传成功");
$this->redirect("orderList");
}
}
}
// 获取文件数据 插入数据库
public function writeMessage($filename) {
require_once '../Vendor/Excel/PHPExcel.php';
require_once '../Vendor/Excel/PHPExcel/IOFactory.php';
require_once '../Vendor/Excel/PHPExcel/Reader/Excel5.php';
// 不同的后缀文件需要不同的加载方式
$extension = strtolower( pathinfo($filename, PATHINFO_EXTENSION) );
if ($extension =='xlsx') {
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader ->load($filename);
}else{
$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
$objPHPExcel = $objReader->load($filename); //$filename可以是上传的文件,或者是指定的文件
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$k = 0;
//循环读取excel文件,读取一条,插入一条
$product_name = $objPHPExcel->getActiveSheet()->getCell("A2")->getValue();
$policy_code = $objPHPExcel->getActiveSheet()->getCell("B2")->getValue();
$cus_name = $objPHPExcel->getActiveSheet()->getCell("C2")->getValue();
$insured_person = $objPHPExcel->getActiveSheet()->getCell("D2")->getValue();
$accept_date = $objPHPExcel->getActiveSheet()->getCell("E2")->getValue();
$valid_date = $objPHPExcel->getActiveSheet()->getCell("F2")->getValue();
$end_date = $objPHPExcel->getActiveSheet()->getCell("G2")->getValue();
if( $product_name == "产品名称" &&
$policy_code == "单号" &&
$cus_name == "名称" &&
$accept_date == "签发日"
){
$cfName = '';
for($j=3;$j<=$highestRow;$j++){
// 搜索标记
$data['product_name'] = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();
$data['policy_code'] = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
$data['cus_name'] = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
$data['insured_person'] = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();
$data['accept_date'] = $objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();
$data['valid_date'] = $objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();
$data['end_date'] = $objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();
$this->Bguser->useTable='orders';
$this->Bguser->id = null;
if( !empty($data['policy_code']) ){
$rel = $this->Bguser->save($data);
}
}
}else{
$this->Session->setFlash("上传模板错误,请检查模板!");
$this->redirect("orderList");
}
return $cfName;
}
?>