摘要: 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?
001
public 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
}