php-导入导出excel

原生代码导出
$DB_Server = "127.0.0.1";
$DB_Username = "";//数据库用户名
$DB_Password = "";//数据库密码
$DB_DBName = "";//数据库名
$DB_TBLName = "";//要下载的数据表
$savename = date("Y-m-j H:i:s");
// 数据库连接
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect.");
//注意mysql 导入cxcel中的时候
mysql_query("Set Names 'utf8'");
//定义文件导出的格式
$file_type = "vnd.ms-excel";
//定义文件后缀名称
$file_ending = "xls";
header("Content-Type: application/$file_type;charset=utf8");
header("Content-Disposition: attachment; filename=".$savename.".$file_ending");
//header("Pragma: no-cache");
$now_date = date("Y-m-j H:i:s");
//定义要输出的数据表标题
$title = "数据表名:$DB_TBLName,|日期:$now_date";
$sql = "";
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database");
// $result = @mysql_query($sql,$Connect) or die(mysql_error());

echo "ID\t";echo "称呼\t";echo "公司\t";echo "手机号\t";echo "状态\t";echo "会员类型\t";echo "注册时间\t\n";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
    

    echo $row['id']."\t";echo $row['nicname']."\t";echo $row['company']."\t";echo $row['mobilephone']."\t";echo $ischecka."\t";echo $isdealera."\t";echo $date."\t\n";
}



phpexcel导入
$file = $_FILES['excelfile'];//file上传文件
if (!empty($file['name'])) {
    $config = array(
        'maxSize' => 2097152,// 设置附件上传大小  字节为单位B
        'rootPath' => './Uploads/',// 设置附件上传根目录
        'savePath' => 'excel/', //附件保存目录
        'exts' => array('xlsx', 'xls'),// 设置附件上传类型
        'autoSub' => false,// 是否使用子目录保存上传文件
        'replace' => true,  // 存在同名文件是否是覆盖
        'subName' => array('date', 'Y-m-d'), //子目录创建方式，[0]-函数名，[1]-参数，多个参数使用数组
    );
    $upload = new \Think\Upload($config);
    if (!$info = $upload->uploadOne($file)) {
        $this->error($upload->getError());
    }
    require_once('Public/Admin/phpExcel/PHPExcel.php');
    $file_name = './Uploads/'. $info['savepath'] . $info['savename'];//要写./相对路径
    $file_path = pathinfo($file_name);
    if($file_path['extension'] =='xlsx' )
    {
        $objReader = new \PHPExcel_Reader_Excel2007();
        $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
    }
    else
    {
        $objReader = new \PHPExcel_Reader_Excel5();
        $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
    }
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow(); // 取得总行数
    $highestColumn = $sheet->getHighestColumn(); // 取得总列数
    //i=2  从第二行开始
    for ($i = 2; $i <= $highestRow; $i++) {
        $data['orderid'] = $objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
        $data['type'] = $objPHPExcel->getActiveSheet()->getCell("B" . $i)->getValue();
        $data['cname'] = $objPHPExcel->getActiveSheet()->getCell("C" . $i)->getValue();
        $data['name'] = $objPHPExcel->getActiveSheet()->getCell("D" . $i)->getValue();
        $data['company'] = $objPHPExcel->getActiveSheet()->getCell("E" . $i)->getValue();
        $data['duty'] = $objPHPExcel->getActiveSheet()->getCell("F" . $i)->getValue();
        $data['headpic'] = $objPHPExcel->getActiveSheet()->getCell("G" . $i)->getValue();

        $data['time'] = time();
        if (!empty($data['cname']) && !empty($data['orderid'])) {
            $member->add($data);
        }
        else{
            $this->error("表中编号和ID为空，上传中断", U('Admin/Member/memberlist'), 1);
        }
    }
}  

phpexcel导出
error_reporting(E_ALL);
date_default_timezone_set('Etc/GMT-8');
require_once('Public/phpExcel/PHPExcel.php');
// echo file_exists("./Public/phpexcel/Classes/PHPExcel.php");
$obpe = new \PHPExcel();
    
/* @func 设置文档基本属性 */
$obpe_pro = $obpe->getProperties();
$obpe_pro->setCreator('midoks')//设置创建者
  // ->setLastModifiedBy('2013/2/16 15:00')//设置时间
 ->setTitle('data')//设置标题
 ->setSubject('beizhu')//设置备注
 ->setDescription('miaoshu')//设置描述
  ->setKeywords('keyword')//设置关键字 | 标记
  ->setCategory('catagory');//设置类别
 /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
 $i =0;
 $obpe->setactivesheetindex($i);
 //写入多行数据
 $obpe->getActiveSheet()->getColumnDimension('B')->setWidth(25);
 $obpe->getActiveSheet()->getColumnDimension('E')->setWidth(40);
 $obpe->getactivesheet()->setcellvalue('A'."1", "姓名");
 $obpe->getactivesheet()->setcellvalue('B'."1", "电话");
 $obpe->getactivesheet()->setcellvalue('C'."1","公司" );
 $obpe->getactivesheet()->setcellvalue('D'."1","职位" );
 $obpe->getactivesheet()->setcellvalue('E'."1", "身份证");
foreach($data as $k=>$v){
    $k = $k+2;
    /* @func 设置列 */
    $obpe->getactivesheet()->setcellvalue('A'.$k, $v['p_name']);
    $obpe->getactivesheet()->setcellvalue('B'.$k, $v['p_phone']);
    $obpe->getactivesheet()->setcellvalue('C'.$k, $v['p_company']);
    $obpe->getactivesheet()->setcellvalue('D'.$k, $v['p_position']);
    $obpe->getactivesheet()->setcellvalue('E'.$k, ' '.$v['p_idcard']);

}
$obpe->getActiveSheet()->setTitle('个人信息表');
     
if (strstr($type,"1")) {    
    $i++;                              
    // // 创建一个新的工作空间(sheet)
    $obpe->createSheet();
    $obpe->setactivesheetindex($i);
    //写入多行数据
    $obpe->getActiveSheet()->getColumnDimension('A')->setWidth(25);
    $obpe->getactivesheet()->setcellvalue('A'."1", "制卡时间");
    $obpe->getactivesheet()->setcellvalue('B'."1","类型" );
    $obpe->getactivesheet()->setcellvalue('C'."1", "卡模板");
    foreach($data['cardmakinglog'] as $k=>$v){
        $k = $k+2;
        /* @func 设置列 */
        $v['cm_date'] =date("Y.m.d H:i:s",$v['cm_date']);
        $obpe->getactivesheet()->setcellvalue('A'.$k, $v['cm_date']);
        if($v['cm_type']==1){
        $v['cm_type'] ="批量制卡";
    }
    $obpe->getactivesheet()->setcellvalue('B'.$k, $v['cm_type']);
    $v['cm_modal'] =M("model")->where("mod_id =".$v['cm_modal'])->getField("mod_name");
    $obpe->getactivesheet()->setcellvalue('C'.$k, $v['cm_modal']);
    
    }
    $obpe->getActiveSheet()->setTitle('制卡日志表');
        
}

if (strstr($type,"2")) {    
     // // 创建一个新的工作空间(sheet)
    $i++;
    $obpe->createSheet();
    $obpe->setactivesheetindex($i);
    //写入多行数据
    $obpe->getActiveSheet()->getColumnDimension('A')->setWidth(25);
    $obpe->getactivesheet()->setcellvalue('A'."1", "写卡时间");
    $obpe->getactivesheet()->setcellvalue('B'."1", "类型");
    $obpe->getactivesheet()->setcellvalue('C'."1","卡类型" );
    foreach($data['cardwritinglog'] as $k=>$v){
        $k = $k+2;
        /* @func 设置列 */
        $v['cw_date'] =date("Y.m.d H:i:s",$v['cw_date']);
        $obpe->getactivesheet()->setcellvalue('A'.$k, $v['cw_date']);
        
        $obpe->getactivesheet()->setcellvalue('B'.$k, $v['cw_type']);
        $obpe->getactivesheet()->setcellvalue('C'.$k, $v['cw_card']);
    }
    $obpe->getActiveSheet()->setTitle('写卡日志表');
}     
       
//写入类容
$obwrite = \PHPExcel_IOFactory::createWriter($obpe, 'Excel5');
//ob_end_clean();
//保存文件
// $obwrite->save('mulit_sheet.xls');
                
//or 以下方式
/*******************************************
                 直接在浏览器输出
*******************************************/

header('Pragma: public');
header('Expires: 0');
header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
header('Content-Type:application/force-download');
header('Content-Type:application/vnd.ms-execl');
header('Content-Type:application/octet-stream');
header('Content-Type:application/download');
header("Content-Disposition:attachment;filename='".$name.".xls'");
header('Content-Transfer-Encoding:binary');
$obwrite->save('php://output');
            
-------------------------------------------------------------------------------------------------