摘要: PHPExcel 是用来操作Office Excel 文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格，如 Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOf ...PHPExcel 是用来操作Office Excel 文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格，如 Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML等等。要求：PHP 5.2.0 版本及以上PHP extension php_zip 开启 (如果你需要使用 PHPExcel 来操作 .xlsx .ods or .gnumeric 文件)PHP extension php_xml 开启PHP extension php_gd2 开启(选填, 如果需要计算准确的列宽需要开启此扩展)[代码]php代码：view sourceprint?001public function actionGetexcel() {002 $objectPHPExcel = new PHPExcel();003 004 //设置文档基本属性 005 $objProps = $objectPHPExcel->getProperties();006 $objProps->setCreator('ls'); //作者007 $objProps->setLastModifiedBy('ls'); //修订008 $objProps->setTitle("Office XLS Document"); //标题009 $objProps->setSubject("Office XLS Document"); //主题010 $objProps->setDescription(""); //备注011 $objProps->setKeywords("office excel"); //标记012 $objProps->setCategory(""); //类别013 //设置当前的sheet索引，用于后续的内容操作。 014 //一般只有在使用多个sheet的时候才需要显示调用。 015 //缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0 016 $objectPHPExcel->setActiveSheetIndex(0);017 018 //每页条数019 $page_size = 20;020 //数据的取出021 $model = Company::model();022 $model->unsetAttributes(); // clear any default values 023 $model->group_id = 7;024 $dataProvider = $model->search();025 026 $dataProvider->setPagination(false);027 $data = $dataProvider->getData(); //数据028 $count = $dataProvider->getTotalItemCount(); //总条数029 //总页数的算出030 $page_count = (int) ($count / $page_size) + 1;031 $current_page = 0;032 033 $n = 0;034 $spm = 0;035 foreach ($data as $product) {036 037 if ($n % $page_size === 0) {038 if ($n) {039 $objectPHPExcel->createSheet();040 $current_page = $current_page + 1;041 $spm = 0;042 }043 //报表头的输出044 $objectPHPExcel->getActiveSheet()->mergeCells('B1:G1');045 $objectPHPExcel->getActiveSheet()->setCellValue('B1', '平台供应商');046 047 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B2', '平台供应商');048 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B2', '平台供应商');049 $objectPHPExcel->setActiveSheetIndex($current_page)->getStyle('B1')->getFont()->setSize(24);050 $objectPHPExcel->setActiveSheetIndex($current_page)->getStyle('B1')051 ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);052 053 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B2', '日期：' . date("Y年m月j日"));054 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('G2', '第' . ($current_page + 1) . '/' . $page_count . '页');055 $objectPHPExcel->setActiveSheetIndex($current_page)->getStyle('G2')056 ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);057 058 //表格头的输出059 $objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);060 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B3', '公司id');061 $objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6.5);062 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('C3', '名称');063 $objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(17);064 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('D3', '分组');065 $objectPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(22);066 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('E3', '电话');067 $objectPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);068 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('F3', '创建时间');069 $objectPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);070 $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('G3', '地区');071 $objectPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);072 073 //设置居中074 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')075 ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);076 077 //设置边框078 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')079 ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);080 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')081 ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);082 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')083 ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);084 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')085 ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);086 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')087 ->getBorders()->getVertical()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);088 089 //设置颜色090 $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')->getFill()091 ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF66CCCC');092 }093 094 //由PHPExcel根据传入内容自动判断单元格内容类型 setCellValue('A1', '字符串内容'); 095 //显式指定内容类型 setCellValueExplicit('A1', '字符串内容', PHPExcel_Cell_DataType::TYPE_STRING)096 097 //明细的输出098 $objectPHPExcel->getActiveSheet()->setCellValueExplicit('B' . ($spm + 4), $product->com_id, PHPExcel_Cell_DataType::TYPE_STRING);099 $objectPHPExcel->getActiveSheet()->setCellValueExplicit('C' . ($spm + 4), $product->com_name, PHPExcel_Cell_DataType::TYPE_STRING);100 $objectPHPExcel->getActiveSheet()->setCellValueExplicit('D' . ($spm + 4), $product->group_id,PHPExcel_Cell_DataType::TYPE_STRING);101 $objectPHPExcel->getActiveSheet()->setCellValueExplicit('E' . ($spm + 4), $product->com_phone,PHPExcel_Cell_DataType::TYPE_STRING);102 $objectPHPExcel->getActiveSheet()->setCellValueExplicit('F' . ($spm + 4), $product->create_time,PHPExcel_Cell_DataType::TYPE_STRING);103 $objectPHPExcel->getActiveSheet()->setCellValueExplicit('G' . ($spm + 4), $product->region_name,PHPExcel_Cell_DataType::TYPE_STRING);104 //设置边框105 $currentRowNum = $spm + 4;106 $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)107 ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);108 $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)109 ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);110 $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)111 ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);112 $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)113 ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);114 $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)115 ->getBorders()->getVertical()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);116 $n = $n + 1;117 $spm +=1;118 }119 120 //设置分页显示121 // $objectPHPExcel->getActiveSheet()->setBreak( 'I55' , PHPExcel_Worksheet::BREAK_ROW );122 //$objectPHPExcel->getActiveSheet()->setBreak( 'I10' , PHPExcel_Worksheet::BREAK_COLUMN );123 $objectPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);124 $objectPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(true);125 126 127 ob_end_clean();128 ob_start();129 130 header('Content-Type : application/vnd.ms-excel');131 header('Content-Disposition:attachment;filename="' . '供应商列表-TEST' . date("Y年m月j日") . '.xls"');132 $objWriter = PHPExcel_IOFactory::createWriter($objectPHPExcel, 'Excel5');133 $objWriter->save('php://output');134 }