phpExcel应用

1.首先下载PHPexcel包

解压后他的目录结构是这样的

2.将EXCEL表导入MySQL数据库：

<?php
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
require_once 'Classes/PHPExcel/Reader/Excel2007.php';

$objReader = PHPExcel_IOFactory::createReader('Excel2007');//use excel2007 for 2007 format

$objPHPExcel = $objReader->load('excels/test.xlsx'); //$filename可以是上传的文件，或者是指定的文件
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$arr = array(1=>'A',2=>'B',3=>'C',4=>'D',5=>'E',6=>'F',7=>'G',8=>'H',9=>'I',10=>'J',11=>'K',12=>'L',13=>'M', 14=>'N',15=>'O',16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',26=>'Z');
//echo $highestRow.$highestColumn;
// 一次读取一列
$link=new mysqli('localhost','root','root','excel',3306);
$link->query('set names utf8');
for ($row = 2; $row <= $highestRow; $row++) {
    $bb=array();
    for ($column = 1; $arr[$column] != 'F'; $column++) {

        $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
        $bb[]=$val;

    }
    $sql="insert INTO `import` values ('$bb[0]','$bb[1]','$bb[2]','$bb[3]','$bb[4]')";
    $link->query($sql);
    print_r($bb)."<br/>";
}
$link->close();
echo "success";die;

3.将数据库导出Mysql：

public function actionEx(){
        require_once '../PHPexcel/Classes/PHPExcel.php';

        $objPHPExcel = new \PHPExcel();

        new \PHPExcel();

        $letter = array('A','B','C','D','E','F','F','G');
        $tableHeader = array('党支部账号','党支部名字','id','parentId','党员人数','活动数');

        for($i = 0;$i < count($tableHeader);$i++) {
            $objPHPExcel->getActiveSheet()->setCellValue("$letter[$i]1","$tableHeader[$i]");
        }

        $query=User::find()->where(['like','id','001.001.033.001.025.007'])->all();
        $data=array();
        foreach ($query as $res){
            $all=$res->getAttributes(['username','name','id','parentId']);
            $all['username']=$all['username']?$all['username']:" ";
            $all['parentId']=$all['parentId']?$all['parentId']:" ";
            $all['name']=$all['name']?$all['name']:" ";

            $all['personCnt'] = User::find()->where(['parentId' => $res->id, 'type' => User::TYPE_PERSON])->count();
            $all['activityCnt'] = Activity::find()->where(['creatorId' => $res->id])->count();

            $data[]=$all;


        }


        //表格数组
        /*$data = array(
            array('1','小王','男','20','100'),
            array('2','小李','男','20','101'),
            array('3','小张','女','20','102'),
            array('4','小赵','女','20','103')
        );*/

//填充表格信息
        for ($i = 2;$i <= count($data) + 1;$i++) {
            $j = 0;
            foreach ($data[$i - 2] as $key=>$value) {
                $objPHPExcel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");
                $j++;
            }
        }

//创建Excel输入对象
        $write = new \PHPExcel_Writer_Excel5($objPHPExcel);
        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="testdata.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('export2.xls');

    }