PHP 匯出 Excell

標籤：title   資料   time   att   樣式   use   money   pex   llb   

Vendor(‘PHPExcel179.PHPExcel‘);
$objPHPExcel = new PHPExcel(); //建立PHPExcel對象
//設定屬性
$objPHPExcel->getProperties()->setCreator("StudyIM")->setLastModifiedBy("StudyIM")->setTitle("StudyIM.com")->setDescription("StudyIM.com")->setKeywords("StudyIM");
//設定寬度
$objPHPExcel->getActiveSheet()->getColumnDimension(‘A‘)->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘B‘)->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘C‘)->setWidth(35);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘D‘)->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘E‘)->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘F‘)->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘G‘)->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘H‘)->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘I‘)->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘J‘)->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘K‘)->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘L‘)->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘M‘)->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘N‘)->setWidth(20);

//設定行高
$objPHPExcel->getActiveSheet()->getRowDimension(‘1‘)->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getRowDimension(‘2‘)->setRowHeight(20);
//設定字型樣式
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); //預設字型大小
$objPHPExcel->getActiveSheet()->getStyle(‘A1‘)->getFont()->setSize(16)->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle(‘A2:N2‘)->getFont()->setBold(true); //粗體

//合并excel
$objPHPExcel->getActiveSheet()->mergeCells(‘A1:N1‘);

//設定垂直、水平置中
$objPHPExcel->getActiveSheet()->getStyle(‘A1‘)->getAlignment()
   ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
   ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(‘A2:N2‘)->getAlignment()
   ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
   ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   
//設定邊框
$objPHPExcel->getActiveSheet()->getStyle(‘A2:N2‘)->getBorders()->getAllBorders()
   ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
   
//前兩行儲存格內容
$objPHPExcel->setActiveSheetIndex(0)
   ->setCellValue(‘A1‘,I(‘module_name‘).‘表查詢記錄匯總表‘)
   ->setCellValue(‘A2‘,‘ID‘)
   ->setCellValue(‘B2‘,‘使用者名稱‘)
   ->setCellValue(‘C2‘,‘密碼‘)
   ->setCellValue(‘D2‘,‘郵箱‘)
   ->setCellValue(‘E2‘,‘性別‘)
   ->setCellValue(‘F2‘,‘使用者資金‘)
   ->setCellValue(‘G2‘,‘註冊時間‘)
   ->setCellValue(‘H2‘,‘註冊IP‘)
   ->setCellValue(‘I2‘,‘最後登入時間‘)
   ->setCellValue(‘J2‘,‘最後登入IP‘)
   ->setCellValue(‘K2‘,‘QQ‘)
   ->setCellValue(‘L2‘,‘手機號‘)
   ->setCellValue(‘M2‘,‘狀態‘)
   ->setCellValue(‘N2‘,‘備忘‘);

//資料行設定
for($i = 0;$i < count($modellist);$i++)
{
   $objPHPExcel->getActiveSheet()->setCellValue(‘A‘ . ($i+3), $modellist[$i][‘id‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘B‘ . ($i+3), $modellist[$i][‘username‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘C‘ . ($i+3), $modellist[$i][‘password‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘D‘ . ($i+3), $modellist[$i][‘email‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘E‘ . ($i+3), $modellist[$i][‘sex‘]=1?‘男‘:‘女‘);
   $objPHPExcel->getActiveSheet()->setCellValue(‘F‘ . ($i+3), $modellist[$i][‘user_money‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘G‘ . ($i+3), $modellist[$i][‘reg_time‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘H‘ . ($i+3), $modellist[$i][‘reg_ip‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘I‘ . ($i+3), $modellist[$i][‘last_login‘]?date(‘Y-m-d H:i:s‘,$modellist[$i][‘last_login‘]):‘‘);
   $objPHPExcel->getActiveSheet()->setCellValue(‘J‘ . ($i+3), $modellist[$i][‘last_ip‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘K‘ . ($i+3), $modellist[$i][‘qq‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘L‘ . ($i+3), $modellist[$i][‘mobile‘]);
   $objPHPExcel->getActiveSheet()->setCellValue(‘M‘ . ($i+3), $modellist[$i][‘status‘]=1?‘正常‘:‘禁止‘);
   $objPHPExcel->getActiveSheet()->setCellValue(‘N‘ . ($i+3), $modellist[$i][‘remark‘]);
   
   //設定垂直、水平置中
   $objPHPExcel->getActiveSheet()->getStyle(‘A‘ . ($i+3).‘:N‘.($i+3))->getAlignment()
      ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
      ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      
   $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);//行高
      
   //設定邊框
   $objPHPExcel->getActiveSheet()->getStyle(‘A‘ . ($i+3).‘:N‘.($i+3))->getBorders()->getAllBorders()
      ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
}

//sheet命名
$objPHPExcel->getActiveSheet()->setTitle(I(‘module_name‘).‘表‘);
//預設開啟的sheet
$objPHPExcel->setActiveSheetIndex(0);

//excel頭參數
header("Content-Type:application/vnd.ms-execl");
header(‘Content-Disposition:attachment;filename=‘.I(‘module_name‘).‘表查詢結果(‘.date(‘YmdHis‘).‘).xls‘);//日期檔案名稱尾碼
header(‘Cache-Control:max-age=0‘);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘); //excel2007為xlsx
$objWriter->save(‘php://output‘);

PHP 匯出 Excell