PHPExcel导入导出功能实现

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

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

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

<?php

defined('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  < 1017002145@qq.com>
// +----------------------------------------------------------------------

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：若有不解，可添加评论。