PHPExcel导入导出功能实现

本篇是讲解常见的导入导出excel表格的问题

首先在网上下载PHPExcel类库，放在你所用框架的相应文件夹里，以备使用。

1：导入功能以微擎框架为例，其中要插入的数据内容自定义

<?phpdefined('IN_IA') or exit('Access Denied');$dir=dirname(__FILE__);require $dir."/PHPExcel/PHPExcel.php";$id = $_GPC['data'];//获取数据库信息$ret=pdo_fetchall("select * from ".tablename('zhwy_information')." where id in ($id)");$objPHPExcel=new PHPExcel();for($i=1;$i<=3;$i++){if($i>1){$objPHPExcel->createSheet();//创建新的内置表}$objPHPExcel->setActiveSheetIndex($i-1);$objSheet=$objPHPExcel->getActiveSheet();//获取当前的sheet$objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objSheet->getDefaultStyle()->getFont()->setName("微软雅黑")->setSize("14");$objSheet->getStyle("A1:Z1")->getFont()->setSize("16")->setBold(True);$objSheet->getStyle("A1:V1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('ffffcc');                $objSheet->setCellValue("A1","id")->setCellValue("B1","公众号cid")->setCellValue("C1","联系人姓名")->setCellValue("D1","联系人电话")                ->setCellValue("E1","联系邮箱")->setCellValue("F1","公司名称")->setCellValue("G1","创建时间")->setCellValue("H1","省份")                ->setCellValue("I1","市")->setCellValue("J1","区/县")->setCellValue("K1","详细地址")->setCellValue("L1","负责人")                ->setCellValue("M1","负责人身份证号")->setCellValue("N1","负责人电话")->setCellValue("O1","客服电话")->setCellValue("P1","结算账户")                ->setCellValue("Q1","结算开户行")->setCellValue("R1","开户支行")->setCellValue("S1","开户行省份")->setCellValue("T1","开户城市")                ->setCellValue("U1","账户类型")->setCellValue("V1","法人/负责人身份证号");     $j=2;      //循环搜索，并导出到excel中       foreach($ret as $key=>$activity) {  $objSheet->setCellValue("A".$j,$activity['id'])->setCellValue("B".$j,$activity['uniacid'])                ->setCellValue("C".$j,$activity['user_name'])->setCellValueExplicit("D".$j,$activity['user_mobile'],                PHPExcel_Cell_DataType::TYPE_STRING)->setCellValueExplicit("E".$j,$activity['user_email'],                PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("F".$j,$activity['company_name'])                ->setCellValueExplicit("G".$j,date("Y-m-d H:i:s",$activity['create_time']),                PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("H".$j,$activity['province'])->setCellValue("I".$j,$activity['city'])                ->setCellValue("J".$j,$activity['country'])->setCellValue("K".$j,$activity['detailed_address'])                ->setCellValue("L".$j,$activity['manager_name'])->setCellValueExplicit("M".$j,$activity['manager_id'],                PHPExcel_Cell_DataType::TYPE_STRING)->setCellValueExplicit("N".$j,$activity['manager_mobile'],PHPExcel_Cell_DataType::TYPE_STRING)                ->setCellValueExplicit("O".$j,$activity['service_tel'],PHPExcel_Cell_DataType::TYPE_STRING)                ->setCellValueExplicit("P".$j,$activity['account'],PHPExcel_Cell_DataType::TYPE_STRING)->setCellValue("Q".$j,$activity['account_bank'])                ->setCellValue("R".$j,$activity['account_son_bank'])->setCellValue("S".$j,$activity['account_province'])                ->setCellValue("T".$j,$activity['account_city'])->setCellValue("U".$j,$activity['account_type'])                 ->setCellValueExplicit("V".$j,$activity['principal_id'],PHPExcel_Cell_DataType::TYPE_STRING); $j++; }}$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');//生成excel文件//$objWriter->save($dir."/export_1.xls");//保存文件browser_export('Excel5','browser_excel03.xls');//输出到浏览器$objWriter->save("php://output");function browser_export($type,$filename){if($type=="Excel5"){header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件}else{header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件}header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称header('Cache-Control: max-age=0');//禁止缓存}

2：导出功能以thinkcmf5为例

<?php// +----------------------------------------------------------------------// | Author: drt9527  < [e-mail]>// +----------------------------------------------------------------------namespace app\credit\controller;header("Content-Type:text/html;charset=utf-8");use cmf\controller\AdminBaseController;use think\Db;//放到simplewind/vendor目录下可直接引用use PHPExcel_IOFactory;use PHPExcel;class ExcelController extends AdminBaseController{    public function  intoDatabase()    {        $dir=dirname(__FILE__);        //获取同级目录下的word文件        $filename=$dir.'/browser_excel03.xls';         $objPHPExcel= PHPExcel_IOFactory::load($filename);//加载文件    // foreach($objPHPExcel->getWorksheetIterator() as $sheet)//循环获取sheet    // {    // foreach ($sheet ->getRowIterator() as  $row)//逐行处理    // {    // if($row->getRowIndex()<2)    // {    // continue;    // }    // foreach ($row->getCellIterator() as $key=> $cell)//逐行读取    // {    // $data=$cell->getValue();    // if (!empty($data))    // {    // $dat[]=$data;    // }    // }    //     dump($dat).' ';        // }     // }       // exit;           //以上注释为原样输出格式，下方为可用于插入数据库操作（逐行插入）       for($i=2;$i<=3;$i++)    {   $data['name']=$objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();        $data['manager']=$objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();        $data['mobile']=$objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();    var_dump($data);exit;    }        }}?>

3：若有不解，可添加评论。