导出导入Excel

 getActiveSheet()->setCellValue(chr($asiStart + $i) . 1, $column[$i]);
	    }
	    
	    if (!empty($result)) {
	        $startIndex = 2;
	        foreach ($result as $row) {
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$startIndex, $row['mobile']);
	            $startIndex++;
	        }
	    }
	    $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);//设置excel宽度
        //$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);//设置某列的宽度
	    // Redirect output to a client’s web browser (Excel2007)
	    $fileName = date("Y-m-d H:i:s", REQUEST_TIME) . ".xlsx";
	    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	    header('Content-Disposition: attachment;filename="' . $fileName . '"');
	    header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed
	    header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed
	    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
	    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	    header('Pragma: public'); // HTTP/1.0
	    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	    $objWriter->save('php://output');
	    exit;

?>

namespace Admin\Controller;

class OtherController extends AdminController {

    /**
     * onethink中Excel导出导入
     * 下载PHPexcel第三方库放入ThinkPHP\Library\Vendor中
     */
    
    //导入
    public function importExcel(){
        header('Content-Type:text/html;charset=utf-8');
        if ( isset($_FILES['files']) && !empty($_FILES['files']) ) {
            if ( !$_FILES['files']['name'] || empty($_FILES['files']['name']) ){
                $this->error("请选择上传的文件");
            }
            $extensions = array('xlsx','xls','csv');
            $arr = explode(".", $_FILES['files']['name']);
            $extend = end($arr);
            if ( !in_array($extend, $extensions) ) {
                $this->error("文件格式不对，请上传xls,xlsx格式的文件");
            }

            $files = Request::instance()->file('files');
            $file_dir = ROOT_PATH . 'public' . DS . 'data/voucherTemplate/';
            $file_name = $_FILES['files']['name']??"";
            $info = $files->move($file_dir, $file_name);
            $file_path = "";
            if( $info ){
                $file_name = str_replace("\\", "/", $info->getSaveName());
                $file_path = $file_dir . $file_name;
            }else{
                echo $files->getError();
                exit();
            }
        
            vendor ( 'PHPExcel' );
            vendor ( 'PHPExcel.PHPExcel_IOFactory' );
            switch (strtolower ( $extend )) {
                case 'csv' :
                    vendor ( 'PHPExcel.Reader.CSV' );
                    $format = 'CSV';
                    $objReader = \PHPExcel_IOFactory::createReader ( $format )->setDelimiter ( ',' )->setInputEncoding ( 'GBK' )->setEnclosure ( '"' )->setLineEnding ( "\r\n" )->setSheetIndex ( 0 );
                    break;
                case 'xls' :
                    vendor ( 'PHPExcel.Reader.Excel5' );
                    $format = 'Excel5';
                    $objReader = \PHPExcel_IOFactory::createReader ( $format );
                    break;
                default :
                    vendor ( 'PHPExcel.Reader.Excel2007' );
                    $format = 'excel2007';
                    $objReader = \PHPExcel_IOFactory::createReader ( $format );
            }            

            $objReader->setReadDataOnly(true);
            $objPHPExcel = $objReader->load($file_path);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $objWorksheet->getHighestRow();
            $highestColumn = $objWorksheet->getHighestColumn();
            $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
            
            //事物
            $flag = false;
            Db::startTrans();
            //第1行A列起
            for($i=3; $i<=$highestRow; $i++){
                $data = array();
                for($j= 'A'; $j< $highestColumn; $j++){
                    $data[] = $objPHPExcel->getActiveSheet()->getCell("$j$i")->getValue();
                }
                $result = $this->_model->insertData($data);
                if ( $result && $result['code'] == 100 ){
                    $false = true;
                }else {
                    $flag = false;
                    Db::rollback();
                    $this->error( $result['msg'] );
                }
            }
            if ( !$false ){
                Db::rollback();
                $this->error( $result['msg'] );
            }
            Db::commit();
            $this->success('导入成功！');
        }else{
            $this->error("请选择上传的文件");
        }
    }
    
    public function exportExcel(){
        $expCellName = array('场地名','优惠券名称','优惠券类型','天数','折扣','开始时间', '结束时间', '优惠内容');
        //导出的数据格式
        $expTableData = array(
            array('浦东', '5天优惠券', '天数券', '5', '', '2017-11-20 09:00:00', '2018-02-18 22:00:00', '5天优惠券的优惠内容'),
            array('虹桥', '8折优惠券', '折扣券', '', '0.8', '2017-11-20 09:00:00', '2018-02-18 22:00:00', '8折优惠券的优惠内容'),
        );
        $expTitle = "优惠券模板";
        
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = 'voucher_template';//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);        //多少列
        $dataNum = count($expTableData);        //多少行
        Vendor("PHPExcel");
         
        $objPHPExcel = new \PHPExcel();            //初始化
        $cellName = array('A','B','C','D','E','F','G','H');
        
        //标题
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle);//标题
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);//设置标题行高
        $objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFont()->setName('微软雅黑');
        $objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFont()->setSize(16);
        $objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//标题水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//标题垂直居中
       
        //底部注意事项
        $attention = "注意事项：";
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A5', $attention);
        $objPHPExcel->getActiveSheet(0)->mergeCells('A5:'.$cellName[$cellNum-1].'5');//合并单元格
        $objPHPExcel->getActiveSheet()->getRowDimension(5)->setRowHeight(20);//设置标题行高
        $objPHPExcel->getActiveSheet()->getStyle('A5:I5')->getFont()->setSize(14);
        $objPHPExcel->getActiveSheet()->getStyle('A5:I5')->getFont()->setColor(\PHPExcel_Style_Color::indexedColor(3));//设置颜色，颜色值可查看对应的indexedColor方法

        //列表水平、垂直居中
        $objPHPExcel->setActiveSheetIndex()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//所有水平居中
        $objPHPExcel->setActiveSheetIndex()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//所有垂直居中
        
        //设置宽度
        foreach ( $cellName as $key=>$value){
            $objPHPExcel->getActiveSheet()->getRowDimension($key+1)->setRowHeight(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth(20);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($value.'2', $expCellName[$key]);     //表头(列名)
        }
        
        //设置行高
        foreach ( $expTableData as $key=>$value ){
            $objPHPExcel->getActiveSheet()->getRowDimension($key+2)->setRowHeight(30);
        }
        //输出数据
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$j]);            //内容
            }
        }
        
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xlsx"');
        header("Content-Disposition:attachment;filename=$fileName.xlsx");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;        
    }
}