just do it

PHPExcel常用方法小结

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 开启(选填, 如果需要计算准确的列宽需要开启此扩展)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 public function actionGetexcel() {
        $objectPHPExcel = new PHPExcel();
 
        //设置文档基本属性  
        $objProps = $objectPHPExcel->getProperties();
        $objProps->setCreator('ls'); //作者
        $objProps->setLastModifiedBy('ls'); //修订
        $objProps->setTitle("Office XLS Document"); //标题
        $objProps->setSubject("Office XLS Document"); //主题
        $objProps->setDescription(""); //备注
        $objProps->setKeywords("office excel"); //标记
        $objProps->setCategory(""); //类别
        //设置当前的sheet索引,用于后续的内容操作。  
        //一般只有在使用多个sheet的时候才需要显示调用。  
        //缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0  
        $objectPHPExcel->setActiveSheetIndex(0);
 
        //每页条数
        $page_size = 20;
        //数据的取出
        $model = Company::model();
        $model->unsetAttributes();  // clear any default values  
        $model->group_id = 7;
        $dataProvider = $model->search();
 
        $dataProvider->setPagination(false);
        $data = $dataProvider->getData(); //数据
        $count = $dataProvider->getTotalItemCount(); //总条数
        //总页数的算出
        $page_count = (int) ($count / $page_size) + 1;
        $current_page = 0;
 
        $n = 0;
        $spm = 0;
        foreach ($data as $product) {
 
            if ($n % $page_size === 0) {
                if ($n) {
                    $objectPHPExcel->createSheet();
                    $current_page = $current_page + 1;
                    $spm = 0;
                }
                //报表头的输出
                $objectPHPExcel->setActiveSheetIndex($current_page)->mergeCells('B1:G1');
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B1', '平台供应商');
 
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B2', '平台供应商');
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B2', '平台供应商');
                $objectPHPExcel->setActiveSheetIndex($current_page)->getStyle('B1')->getFont()->setSize(24);
                $objectPHPExcel->setActiveSheetIndex($current_page)->getStyle('B1')
                        ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B2', '日期:' . date("Y年m月j日"));
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('G2', '第' . ($current_page + 1) . '/' . $page_count . '页');
                $objectPHPExcel->setActiveSheetIndex($current_page)->getStyle('G2')
                        ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
 
                //表格头的输出
                $objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('B3', '公司id');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6.5);
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('C3', '名称');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(17);
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('D3', '分组');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(22);
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('E3', '电话');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('F3', '创建时间');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex($current_page)->setCellValue('G3', '地区');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
 
                //设置居中
                $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')
                        ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 
                //设置边框
                $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')
                        ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')
                        ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')
                        ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')
                        ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')
                        ->getBorders()->getVertical()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
 
                //设置颜色
                $objectPHPExcel->getActiveSheet()->getStyle('B3:G3')->getFill()
                        ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF66CCCC');
            }
 
            //由PHPExcel根据传入内容自动判断单元格内容类型  setCellValue('A1', '字符串内容');  
            //显式指定内容类型  setCellValueExplicit('A1', '字符串内容', PHPExcel_Cell_DataType::TYPE_STRING)
 
            //明细的输出
            $objectPHPExcel->getActiveSheet()->setCellValueExplicit('B' . ($spm + 4), $product->com_id, PHPExcel_Cell_DataType::TYPE_STRING);
            $objectPHPExcel->getActiveSheet()->setCellValueExplicit('C' . ($spm + 4), $product->com_name, PHPExcel_Cell_DataType::TYPE_STRING);
            $objectPHPExcel->getActiveSheet()->setCellValueExplicit('D' . ($spm + 4), $product->group_id,PHPExcel_Cell_DataType::TYPE_STRING);
            $objectPHPExcel->getActiveSheet()->setCellValueExplicit('E' . ($spm + 4), $product->com_phone,PHPExcel_Cell_DataType::TYPE_STRING);
            $objectPHPExcel->getActiveSheet()->setCellValueExplicit('F' . ($spm + 4), $product->create_time,PHPExcel_Cell_DataType::TYPE_STRING);
            $objectPHPExcel->getActiveSheet()->setCellValueExplicit('G' . ($spm + 4), $product->region_name,PHPExcel_Cell_DataType::TYPE_STRING);
            //设置边框
            $currentRowNum = $spm + 4;
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)
                    ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)
                    ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)
                    ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)
                    ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($spm + 4) . ':G' . $currentRowNum)
                    ->getBorders()->getVertical()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $n = $n + 1;
            $spm +=1;
        }
 
        //设置分页显示
        // $objectPHPExcel->getActiveSheet()->setBreak( 'I55' , PHPExcel_Worksheet::BREAK_ROW );
        //$objectPHPExcel->getActiveSheet()->setBreak( 'I10' , PHPExcel_Worksheet::BREAK_COLUMN );
        $objectPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
        $objectPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(true);
 
 
        ob_end_clean();
        ob_start();
 
        header('Content-Type : application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename="' . '供应商列表-TEST' . date("Y年m月j日") . '.xls"');
        $objWriter = PHPExcel_IOFactory::createWriter($objectPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
点赞