phpExcel180数据导入、导出

1、导入

/*
 * 导入Excel数据
 * $filename //文件路径 $exts :文件后缀名
 * */
public function handleExcelData($filename,$exts){
    import('@.ORG.PHPExcel180.Classes.PHPExcel', '', '.php');
    import('@.ORG.PHPExcel180.Classes.PHPExcel.IOFactory', '', '.php');
    $exts = substr(strrchr($exts, '.'), 1);
    //            var_dump($exts);exit;
    //创建PHPExcel对象，注意，不能少了\
    $PHPExcel=new \PHPExcel();
    $shared = new \PHPExcel_Shared_Date();
    //如果excel文件后缀名为.xls，导入这个类
    if($exts == 'xls'){
        import('@.ORG.PHPExcel180.Classes.PHPExcel.Reader.Excel5', '', '.php');
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
    }else if($exts == 'xlsx'){
        import('@.ORG.PHPExcel180.Classes.PHPExcel.Reader.Excel2007', '', '.php');
        $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    }
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($filename);
    $objWorksheet = $objPHPExcel->getActiveSheet();
    $highestRow = $objWorksheet->getHighestRow();
    $highestColumn = $objWorksheet->getHighestColumn();
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $excelData = array();
    for ($row = 1; $row <= $highestRow; $row++) {
        for ($col = 0; $col < $highestColumnIndex; $col++) {
            $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
        }
    }
    return $excelData;
}

public function import_exam_time(){
       if($_FILES){
           $filename = $_FILES['file']['tmp_name'];
           $exts = $_FILES['file']['name'];
           $res = $this->handleExcelData($filename,$exts);
           $res = array_slice($res,1);
          
        
      //数据处理 
           foreach ($res as $k => $v ){
              
           }
           
           echo json_encode(array("status"=>1,"msg"=>"导入成功"));die;
       }
   }

2、导出

/*
 * 导出
 * $fileName 文件名 $headArr 标题 $data 数组
 * */
public  function getExcel($fileName="",$headArr="",$data=""){
    import('@.ORG.PHPExcel180.Classes.PHPExcel', '', '.php');
    import('@.ORG.PHPExcel180.Classes.PHPExcel.IOFactory', '', '.php');

    //对数据进行检验
    if(empty($data) || !is_array($data)){
        die("data must be a array");
    }
    //检查文件名
    if(empty($fileName)){
        exit;
    }
    $date = date("Y_m_d",time());
    $fileName .= "_{$date}.xls";
    //创建PHPExcel对象，注意，不能少了
    $objPHPExcel = new \PHPExcel();
    $objProps = $objPHPExcel->getProperties();
    //设置表头
    $key = ord("A");
    foreach($headArr as $v){
        $colum = chr($key);
        //表头
        $objPHPExcel->getActiveSheet(0) ->setCellValue($colum.'1', $v);
        $key += 1;
    }
    $column = 2;

    $objActSheet = $objPHPExcel->getActiveSheet();
    foreach($data as $key => $rows){ //行写入
        $span = ord("A");
        foreach($rows as $keyName=>$value){// 列写入
            $j = chr($span);
            $objActSheet->setCellValue($j.$column, $value);
            $span++;
        }
        $column++;
    }
    $fileName = iconv("utf-8", "gb2312", $fileName);
    //重命名表
    // $objPHPExcel->getActiveSheet()->setTitle('test');
    //设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
    ob_end_clean();
    ob_start();
    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;filename=\"$fileName\"");
    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output'); //文件通过浏览器下载
    exit;
}

public function export_exam_place(){
  
    $datas = $model->where($map)->order("create_time DESC")->select();


    $row = array();
    $headArr = array('考试类型','名称','座位数','行','列','教室','位置');
    $i=1;
    foreach($datas as $v){
        $row[$i]['group_name'] = $v['exam_group_name'];
        $row[$i]['name'] = $v['name'];
        $row[$i]['seat_num'] = $v['seat_num'];
        $row[$i]['line_num'] = $v['line_num'];
        $row[$i]['column_num'] = $v['column_num'];
        $row[$i]['room_name'] = $v['room_name'];
        $row[$i]['room_position'] = $v['room_position'];
        $i++;
    }

    $filename = "考场信息";
    $this->getExcel($filename,$headArr,$row);
}