PHPExcel导出

-- Yii

前台：

html:

<input type="button" onclick="fnExport()" value="导出到表格">

js:

//导出到表格
function fnExport() {
   var txt=  '<h2>确定将所有数据导出到Excel表格吗？<h2>';

    //xcConfirm插件

    var option = {
       title: "导出到表格",
       btn: parseInt("0011",2),
       onOk: function(){
           window.location.href = "<?php echo url('sysadmin/goods/excelout')?>";            
        }
   }
   window.wxc.xcConfirm(txt, "custom", option);
}

后台：

/**
* 导出贵州省第六届中小学生艺术展演Excel表格
*/
public function actionExcelout(){
   $sql = "sql查询";
   $this->exp2Xls($sql);

}

public function exp2Xls($sql){

   require_once 'PHPExcel.php';
   require_once 'PHPExcel/IOFactory.php';
   error_reporting(0);
   $objPHPExcel = new PHPExcel();
   $title = '展演数据导出-'.gmdate("YmdHis");

   /*以下是一些设置 ，什么作者  标题啊之类的*/
   $objPHPExcel->getProperties()->setCreator("展演数据导出")
       ->setLastModifiedBy("贵州省第六届中小学生艺术展演")
       ->setTitle("展演数据导出")
       ->setSubject("展演数据导出")
       ->setDescription("展演数据导出")
       ->setKeywords("excel")
       ->setCategory("result file");

   $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
   $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
   $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
   $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
   $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
   $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
   $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
   $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
   $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
   $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
   $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
   $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(50);
   $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(50);

   // 设置行高度
   $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);

   $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);

   // 字体和样式
   $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);
   $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->getFont()->setBold(true);
   $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

   $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   $objPHPExcel->getActiveSheet()->getStyle('A2:K2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

   // 设置水平居中
   $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

   //  合并表头
   $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');

   // 表头
   $objPHPExcel->setActiveSheetIndex(0)
       ->setCellValue('A1', $title)
       ->setCellValue('A2', '编号')
       ->setCellValue('B2', '报名编号')
       ->setCellValue('C2', '参赛地区')
       ->setCellValue('D2', '参赛学校')
       ->setCellValue('E2', '辅导教师')
       ->setCellValue('F2', '联系电话')
       ->setCellValue('G2', '电子邮箱')
       ->setCellValue('H2', '项目分类')
       ->setCellValue('I2', '作品分类')
       ->setCellValue('J2', '作品名称')
       ->setCellValue('K2', '作者姓名')
       ->setCellValue('L2', '作品文件')
       ->setCellValue('M2', '作品视频');
       //->setCellValue('M2', '作品类型');

   $objlist_exp= Yii::app()->db->createCommand($sql)->queryAll();
   if($objlist_exp){
       $numIndex = 0;
       foreach ($objlist_exp as $s) {
           $rowIndex=$numIndex + 3;
           $objPHPExcel->getActiveSheet(0)->setCellValue('A' . $rowIndex, ($numIndex + 1));

           $objPHPExcel->getActiveSheet(0)->setCellValue('B' . $rowIndex, $s['Id']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('C' . $rowIndex, $s['MatchAddr']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('D' . $rowIndex, $s['SchoolName']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('E' . $rowIndex, $s['FdTeacherName']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('F' . $rowIndex, $s['FdTeacherTel']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('G' . $rowIndex, $s['TeamIds']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('H' . $rowIndex, $s['pro_cate']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('I' . $rowIndex, $s['works_cate']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('J' . $rowIndex, $s['Title']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('K' . $rowIndex, $s['RealName']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('L' . $rowIndex, $s['HeadPic']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('M' . $rowIndex, $s['WorksUrl']);
           $numIndex++;
       }
   }
   $objPHPExcel->getActiveSheet()->setTitle($title);
   $objPHPExcel->setActiveSheetIndex(0);
   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
   header("Content-Disposition:attachment;filename=".$title.".xls");
   header("Content-Type:application/octet-stream");
   header("Content-Transfer-Encoding:binary");
   header("Pragma:no-cache");
    $objWriter->save('php://output');
    exit;
}