1.下载phpexcel文件(之前提到过!)
2.下载后将文件放到:
3.在控制器中引用:
4.部分实现的代码:
public function OutputExcel($data) //导入订单 { vendor("PHPExcel.PHPExcel"); // Create new PHPExcel object $objPHPExcel = new \PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("ctos") ->setLastModifiedBy("ctos") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); //set width $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(13); $objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(13); //设置行高度 // $objPHPExcel->getActiveSheet()->getRowDimension()->setRowHeight(25); $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(45.75); //$objPHPExcel->getActiveSheet()->mergeCells('A1:M1'); //set font size bold //$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); $objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->getFont()->setSize(11); //$objPHPExcel->getActiveSheet()->getStyle('A2:M2')->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->getFont()->setName('等线'); $objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->getFont()->setBold(true); //$objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); /*$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('宋体'); $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('宋体'); $objPHPExcel->getActiveSheet()->getStyle('A2:M2')->getFont()->getColor()->setARGB('FFffffff'); $objPHPExcel->getActiveSheet()->getStyle('A2:M2')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2:M2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2:M2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); //$objAlignA5 = $objStyleA5->getAlignment(); $objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //$objAlignA5->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置水平居中 $objPHPExcel->getActiveSheet()->getDefaultStyle('')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2:M2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A2:M2')->getFill()->getStartColor()->setARGB("165,48,15"); */ //$objPHPExcel->getActiveSheet()->getStyle('A1:H5')->getFill()->getStartColor()->setARGB('00ff99cc'); // 将背景设置为浅粉色 // set table header content $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', '收到订单 ') ->setCellValue('B1', '签单日期') ->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', '保单号码') ->setCellValue('W1', '生效日期') ->setCellValue('X1', '首期保费日') ->setCellValue('Y1', '预计冷静期截止日') ->setCellValue('Z1', '保单状态') ; // Miscellaneous glyphs, UTF-8 for ($i = 0; $i < count($data); $i++) { switch($data[$i]['status']){ case 0: $s = "批核中"; break; case 1: $s = "保单生效"; break; case 2: $s = "拒保/退保"; break; case 3: $s = "Pending-处理中"; break; case 4: $s = "Pending-需补资料"; break; default: $s = "Pending-资料需审核"; } $splwhere['id'] = array('eq',$data[$i]['sales_id']); $sale = \Model\SalesModel::get($splwhere); $where['id'] = array('eq',$sale['admin_id']); $admin = \Model\AdminModel::get($where); $where1['admin_id'] = array('eq',$admin['id']); $company = \Model\CompanyModel::get($where1); $customer = M('Customer')->where(array('id'=>$data[$i]['customer_id']))->find(); $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 2), $data[$i]['deliver_date']); $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 2), date('Y-m-d',strtotime($data[$i]['sold_date']))); $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 2), $data[$i]['suoshu']); $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($i + 2), $data[$i]['receiver']); $objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($i + 2), $data[$i]['sale_jingli']); $objPHPExcel->getActiveSheet(0)->setCellValue('F' . ($i + 2), $company['name']); $objPHPExcel->getActiveSheet(0)->setCellValue('G' . ($i + 2), $admin['username']); $objPHPExcel->getActiveSheet(0)->setCellValue('H' . ($i + 2), $data[$i]['chipai']); $objPHPExcel->getActiveSheet(0)->setCellValue('I' . ($i + 2), $data[$i]['sales']['company']['name']); $objPHPExcel->getActiveSheet(0)->setCellValue('J' . ($i + 2), $data[$i]['sales']['name']); $objPHPExcel->getActiveSheet(0)->setCellValue('K' . ($i + 2), $data[$i]['subproduct']['product']['insurance_company']['name']); $objPHPExcel->getActiveSheet(0)->setCellValue('L' . ($i + 2), $data[$i]['subproduct']['product']['name']); $objPHPExcel->getActiveSheet(0)->setCellValue('M' . ($i + 2), $data[$i]['subproduct']['name']); $objPHPExcel->getActiveSheet(0)->setCellValue('N' . ($i + 2), $data[$i]['payment_method']); $objPHPExcel->getActiveSheet(0)->setCellValue('O' . ($i + 2), '美元'); $objPHPExcel->getActiveSheet(0)->setCellValue('P'.($i+2), $data[$i]['premium_usd']); $objPHPExcel->getActiveSheet(0)->setCellValue('Q'.($i+2), $data[$i]['limit_usd']); $objPHPExcel->getActiveSheet(0)->setCellValue('R'.($i+2), $data[$i]['customer_name']?$data[$i]['customer_name']:$customer['name']); $objPHPExcel->getActiveSheet(0)->setCellValue('S'.($i+2), $data[$i]['customer_name_en']); $objPHPExcel->getActiveSheet(0)->setCellValue('T'.($i+2), $data[$i]['policy_name_cn']); $objPHPExcel->getActiveSheet(0)->setCellValue('U'.($i+2), $data[$i]['policy_name_en']); $objPHPExcel->getActiveSheet(0)->setCellValue('V'.($i+2), $data[$i]['contract_code']); $objPHPExcel->getActiveSheet(0)->setCellValue('W'.($i+2), $data[$i]['shengxiao']); $objPHPExcel->getActiveSheet(0)->setCellValue('X'.($i+2), $data[$i]['begin_date']); $objPHPExcel->getActiveSheet(0)->setCellValue('Y'.($i+2), $data[$i]['lengjing']); $objPHPExcel->getActiveSheet(0)->setCellValue('Z'.($i+2), $s); //$objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 2) . ':M' . ($i + 3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //$objPHPExcel->getActiveSheet()->getStyle('A' . ($i + 2) . ':M' . ($i + 3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getRowDimension($i + 2)->setRowHeight(20); } // sheet命名 $objPHPExcel->getActiveSheet()->setTitle('销售记录表'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // excel头参数 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="销售记录表(' . date('Ymd-His') . ').xls"'); //日期为文件名后缀 header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式,excel2007为xlsx格式 $objWriter->save('php://output'); }
5.测试。应用。