yii excle导出 项目实战

//控制器导出问卷
public function actionexport($uploaddir="./",$hasExp=false)
{


$phpExcelPath = Yii::getPathOfAlias('application.extensions.phpexcel');
$phpExcelPathFile = Yii::getPathOfAlias('application.extensions');
// 关闭YII的自动加载功能，改用手动加载，否则会出错，PHPExcel有自己的自动加载功能
// YII框架对于组件的自动加载，要求类名与文件名一致；
// 而PHPExcel类对应的文件名包含了上级目录名称，如：IOFactory类对应的文件名为PHPExcel_IOFactory.php
spl_autoload_unregister(array('YiiBase','autoload'));
include_once($phpExcelPathFile . DIRECTORY_SEPARATOR . 'PHPExcel.php');
spl_autoload_register(array('YiiBase','autoload'));
set_time_limit(6000);
$model = new ElectronicsrveryModel();
$res=$model->analyse();

$excelStone = new PHPExcel();


$filename =  "调查.xlsx";
//$arr = $designTable->GetMileStone($ids,$i,$hasExp);

$excelStone->setActiveSheetIndex(0);
$sheet = $excelStone->getActiveSheet();
spl_autoload_register(array('YiiBase','autoload'));
$sheet->SetCellValue('A1', '问题');//0
$sheet->SetCellValue('B1', $res[0]['title']);//1
$sheet->SetCellValue('A2', '选项值');//2
$sheet->SetCellValue('B2', '答案数');//2


// Rename sheet
$sheet->setTitle('$Sheet1');
// Save Excel 2007 file

$rowIndex=3;
if(isset($res[2])&&count(isset($res[2])))
foreach ($res[2] as $val)
{
$sheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$rowIndex,  $val['title']);
$sheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$rowIndex, $val['answernum']);



$rowIndex++;
}


/*
//创建一个新的工作空间(sheet)
$excelStone->createSheet();
$sheet =$excelStone->setactivesheetindex(1);
$sheet->SetCellValue('A1', '合同编号');//0
$sheet->SetCellValue('B1', '节点编号');//1
$sheet->SetCellValue('C1', '节点名称');//2
$sheet->SetCellValue('D1', '节点时间');//3
$sheet->SetCellValue('E1', '约定金额');//5
$sheet->SetCellValue('F1', '计划事由');//4

$sheet->SetCellValue('G1', '支付方式');//6
$sheet->SetCellValue('H1', '实际执行时间');//7
$sheet->SetCellValue('I1', '实际发生金额');//8
$sheet->SetCellValue('J1', '系统编号');//9
// Rename sheet
$sheet->setTitle('$Sheet1');
// Save Excel 2007 file
$arr=array('a','b','c');
$rowIndex=2;
foreach ($arr as $val)
{
$sheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$rowIndex,  $val);
$sheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex(3).$rowIndex, $val);



$rowIndex++;
}
*/
$objPHPExcel = PHPExcel_IOFactory::createWriter($excelStone,'Excel2007');
$filename=iconv("utf-8", "utf-8//ignore",$filename);
$userBrowser = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';
if ( preg_match( '/MSIE/i', $userBrowser ) && !empty($userBrowser))
{
//$filename = rawurlencode($filename);


}
$xmlFile = $uploaddir.$filename;
$objPHPExcel->save($xmlFile);
Yii::app()->request->sendFile($filename,file_get_contents($filename),'',false);  
unlink($xmlFile);

 

}

model代码

public function analyse(){
        $id=122;
$sql="select *  from syberos_db.syberos_electronicsurvey_question where surveyid=".intval($id);
$command = Yii::app()->db->createCommand($sql);
$list     = $command->queryRow($sql);
if(count($list))
$arr[]=$list;
$sql="select sum(answernum) as sumans from syberos_db.syberos_electronicsurvery_option  where questionid=".intval($id);

$command = Yii::app()->db->createCommand($sql);
$list2     = $command->queryRow($sql);
        if(count($list2))
$arr[]=$list2;
if($list2["sumans"]!=0)
{
   
$sql="select title,optionflag,answernum,answernum/$list2[sumans] as persent  sumans from syberos_db.syberos_electronicsurvery_option  where questionid=".intval($id)." group by optionflag";
$command = Yii::app()->db->createCommand($sql);
$list3    = $command->queryAll($sql);
}else{

   $sql="select title,optionflag,answernum,0 as persent from syberos_db.syberos_electronicsurvery_option  where questionid=".intval($id)." group by optionflag";
$command = Yii::app()->db->createCommand($sql);
$list3    = $command->queryAll($sql);
}
      
if(isset($list3)&&count($list3))
$arr[]=$list3;


return $arr;


}