CodeIgniter+PHPExcel 导出数据为Excel文件

最新推荐文章于 2024-07-28 08:43:29 发布

weixin_34378922

最新推荐文章于 2024-07-28 08:43:29 发布

阅读量134

点赞数

文章标签： php 数据库

原文链接：http://blog.51cto.com/yerik/1357981

版权

下载安装PHPExcel库

下载PHPExcel：http://phpexcel.codeplex.com

安装PHPExcel非常的简单，只需解压压缩包里的Classes文件夹中的内容到application\libraries\目录下即可，最终的目录结构如下：

– application\libraries\PHPExcel.php

– application\libraries\PHPExcel (文件夹)

但是由于CI的命名规则的约束，需要修改PHPExcel库中的类名以遵从CI中的类命名规则。打开application\libraries\PHPExcel\IOFactory.php 文件，将其类名从PHPExcel_IOFactory改为IOFactory，并将其构造函数改为public类型。

至此，PHPExcel库安装完毕。

使用PHPExcel库生成excel文件

在CI中写一个Controller实现导出功能

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

public function export ( )
{
$this -> load -> model ( "CourseInfo" , "CourseInfo" , TRUE ) ;
$result = $this -> CourseInfo -> countCourseStuNumber ( ) ;

// 清空输出缓冲区
ob_clean ( ) ;
// 载入PHPExcel类库
$this -> load -> library ( 'PHPExcel' ) ;
$this -> load -> library ( 'PHPExcel/IOFactory' ) ;
// 创建PHPExcel对象
$objPHPExcel = new PHPExcel ( ) ;
// 设置excel文件属性描述
$objPHPExcel -> getProperties ( )
-> setTitle ( "选课统计表" )
-> setDescription ( "选课统计表" ) ;
// 设置当前工作表
$objPHPExcel -> setActiveSheetIndex ( 0 ) ;
// 设置表头
$fields = array ( '节次' , '课程名称' , '开课老师' , '选课学生数' ) ;
// 列编号从0开始，行编号从1开始
$col = 0 ;
$row = 1 ;
foreach ( $fields as $field )
{
$objPHPExcel -> getActiveSheet ( ) -> setCellValueByColumnAndRow ( $col , $row , $field ) ;
$col ++;
}
// 从第二行开始输出数据内容
$row = 2 ;
foreach ( $result as $course )
{
$objPHPExcel -> getActiveSheet ( ) -> setCellValueByColumnAndRow ( 0 , $row , $course [ "courseOrder" ] ) ;
$objPHPExcel -> getActiveSheet ( ) -> setCellValueByColumnAndRow ( 1 , $row , $course [ "courseName" ] ) ;
$objPHPExcel -> getActiveSheet ( ) -> setCellValueByColumnAndRow ( 2 , $row , $course [ "teacher" ] ) ;
$objPHPExcel -> getActiveSheet ( ) -> setCellValueByColumnAndRow ( 3 , $row , $course [ "stuCount" ] ) ;
$row ++;
}
//输出excel文件
$objPHPExcel -> setActiveSheetIndex ( 0 ) ;
// 第二个参数可取值：CSV、Excel5(生成97-2003版的excel)、Excel2007(生成2007版excel)
$objWriter = IOFactory :: createWriter ( $objPHPExcel , 'Excel5' ) ;
// 设置HTTP头
header ( 'Content-Type: application/vnd.ms-excel; charset=utf-8' ) ;
header ( 'Content-Disposition: p_w_upload;filename="' . mb_convert_encoding ( "选课统计表" , "GB2312" , "UTF-8" ) . '.xls"' ) ;
header ( 'Cache-Control: max-age=0' ) ;
$objWriter -> save ( 'php://output' ) ;
}

注意事项

1 2	// 清空输出缓冲区 ob_clean ( ) ;

我翻看了网上许多例子，当中都是没有这句话的，但是如果不加这句话，生成的Excel文件很有可能是乱码文件，打开时提示的是文件格式无效。一开始我以为是编码问题，但是即使我将MySQL，CI，HTML页面，HTTP头信息等等所有相关的东西的编码都统一为UTF-8或GBK或GB2312，内容也都用iconv转码，还是一如既往的出现乱码，后来搞得我头都大了，一气之下生成一个空的没有任何内容的Excel文件，打开一看还是乱码，我才知道不是编码的问题。最后经过多方的查找，才知道是PHPExcel输出Excel文件时是将整个缓冲区中的内容写入到Excel文件，而此时如果缓冲区中在Excel文件之前还有其它内容，则会一起写入到Excel文件当中，导致Excel文件头中包含脏数据，故而打开时提示文件格式无效。所以切记，在生成Excel文件前，一定记得先清空一下输出缓冲区！！！

更多应用

PHPExcel是个强大的类库，它所能做的不仅仅只有这些，更多的功能可以查看PHPExcel官方的文档，在这里我列出了一些常用的设置选项。

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

//创建人
$objPHPExcel -> getProperties ( ) -> setCreator (”Maarten Balliauw” ) ;

//最后修改人
$objPHPExcel -> getProperties ( ) -> setLastModifiedBy (”Maarten Balliauw” ) ;

//标题
$objPHPExcel -> getProperties ( ) -> setTitle (”Office 2007 XLSX Test Document” ) ;

//题目
$objPHPExcel -> getProperties ( ) -> setSubject (”Office 2007 XLSX Test Document” ) ;

//描述
$objPHPExcel -> getProperties ( ) -> setDescription (”Test document for Office 2007 XLSX , generated using PHP classes .” ) ;

//关键字
$objPHPExcel -> getProperties ( ) -> setKeywords (”office 2007 openxml php” ) ;

//种类
$objPHPExcel -> getProperties ( ) -> setCategory (”Test result file” ) ;

//设置当前的sheet
$objPHPExcel -> setActiveSheetIndex ( 0 ) ;

//设置sheet的name
$objPHPExcel -> getActiveSheet ( ) -> setTitle ( 'Simple' ) ;

//设置单元格的值
$objPHPExcel -> getActiveSheet ( ) -> setCellValue ( 'A1' , 'String' ) ;
$objPHPExcel -> getActiveSheet ( ) -> setCellValue ( 'A2' , 12 ) ;
$objPHPExcel -> getActiveSheet ( ) -> setCellValue ( 'A3' , true ) ;
$objPHPExcel -> getActiveSheet ( ) -> setCellValue ( 'C5' , '=SUM(C2:C4)' ) ;
$objPHPExcel -> getActiveSheet ( ) -> setCellValue ( 'B8' , '=MIN(B2:C5)' ) ;

//合并单元格
$objPHPExcel -> getActiveSheet ( ) -> mergeCells ( 'A18:E22' ) ;

//分离单元格
$objPHPExcel -> getActiveSheet ( ) -> unmergeCells ( 'A28:B28' ) ;

//保护cell
$objPHPExcel -> getActiveSheet ( ) -> getProtection ( ) -> setSheet ( true ) ; // Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel -> getActiveSheet ( ) -> protectCells ( 'A3:E13' , 'PHPExcel' ) ;

//设置格式
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'E4' ) -> getNumberFormat ( ) -> setFormatCode (PHPExcel_Style_NumberFormat :: FORMAT_CURRENCY_EUR_SIMPLE ) ;
$objPHPExcel -> getActiveSheet ( ) -> duplicateStyle ( $objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'E4' ) , 'E5:E13' ) ;

//设置宽width
// Set column widths
$objPHPExcel -> getActiveSheet ( ) -> getColumnDimension ( 'B' ) -> setAutoSize ( true ) ;
$objPHPExcel -> getActiveSheet ( ) -> getColumnDimension ( 'D' ) -> setWidth ( 12 ) ;

//设置font
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'B1' ) -> getFont ( ) -> setName ( 'Candara' ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'B1' ) -> getFont ( ) -> setSize ( 20 ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'B1' ) -> getFont ( ) -> setBold ( true ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'B1' ) -> getFont ( ) -> setUnderline (PHPExcel_Style_Font :: UNDERLINE_SINGLE ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'B1' ) -> getFont ( ) -> getColor ( ) -> setARGB (PHPExcel_Style_Color :: COLOR_WHITE ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'E1' ) -> getFont ( ) -> getColor ( ) -> setARGB (PHPExcel_Style_Color :: COLOR_WHITE ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'D13' ) -> getFont ( ) -> setBold ( true ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'E13' ) -> getFont ( ) -> setBold ( true ) ;

//设置align
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'D11' ) -> getAlignment ( ) -> setHorizontal (PHPExcel_Style_Alignment :: HORIZONTAL_RIGHT ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'D12' ) -> getAlignment ( ) -> setHorizontal (PHPExcel_Style_Alignment :: HORIZONTAL_RIGHT ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'D13' ) -> getAlignment ( ) -> setHorizontal (PHPExcel_Style_Alignment :: HORIZONTAL_RIGHT ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'A18' ) -> getAlignment ( ) -> setHorizontal (PHPExcel_Style_Alignment :: HORIZONTAL_JUSTIFY ) ;
//垂直居中
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'A18' ) -> getAlignment ( ) -> setVertical (PHPExcel_Style_Alignment :: VERTICAL_CENTER ) ;

//设置单元格边框
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'A4' ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle (PHPExcel_Style_Border :: BORDER_THIN ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'B4' ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle (PHPExcel_Style_Border :: BORDER_THIN ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'C4' ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle (PHPExcel_Style_Border :: BORDER_THIN ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'D4' ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle (PHPExcel_Style_Border :: BORDER_THIN ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'E4' ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle (PHPExcel_Style_Border :: BORDER_THIN ) ;

//设置边框颜色
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'D13' ) -> getBorders ( ) -> getLeft ( ) -> getColor ( ) -> setARGB ( 'FF993300' ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'D13' ) -> getBorders ( ) -> getTop ( ) -> getColor ( ) -> setARGB ( 'FF993300' ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'D13' ) -> getBorders ( ) -> getBottom ( ) -> getColor ( ) -> setARGB ( 'FF993300' ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'E13' ) -> getBorders ( ) -> getTop ( ) -> getColor ( ) -> setARGB ( 'FF993300' ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'E13' ) -> getBorders ( ) -> getBottom ( ) -> getColor ( ) -> setARGB ( 'FF993300' ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'E13' ) -> getBorders ( ) -> getRight ( ) -> getColor ( ) -> setARGB ( 'FF993300' ) ;

//设置填充颜色
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'A1' ) -> getFill ( ) -> setFillType (PHPExcel_Style_Fill :: FILL_SOLID ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'A1' ) -> getFill ( ) -> getStartColor ( ) -> setARGB ( 'FF808080' ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'B1' ) -> getFill ( ) -> setFillType (PHPExcel_Style_Fill :: FILL_SOLID ) ;
$objPHPExcel -> getActiveSheet ( ) -> getStyle ( 'B1' ) -> getFill ( ) -> getStartColor ( ) -> setARGB ( 'FF808080' ) ;

//加图片
$objDrawing = new PHPExcel_Worksheet_Drawing ( ) ;
$objDrawing -> setName ( 'Logo' ) ;
$objDrawing -> setDescription ( 'Logo' ) ;
$objDrawing -> setPath ( './p_w_picpaths/officelogo.jpg' ) ;
$objDrawing -> setHeight ( 36 ) ;
$objDrawing -> setWorksheet ( $objPHPExcel -> getActiveSheet ( ) ) ;

$objDrawing = new PHPExcel_Worksheet_Drawing ( ) ;
$objDrawing -> setName ( 'Paid' ) ;
$objDrawing -> setDescription ( 'Paid' ) ;
$objDrawing -> setPath ( './p_w_picpaths/paid.png' ) ;
$objDrawing -> setCoordinates ( 'B15' ) ;
$objDrawing -> setOffsetX ( 110 ) ;
$objDrawing -> setRotation ( 25 ) ;
$objDrawing -> getShadow ( ) -> setVisible ( true ) ;
$objDrawing -> getShadow ( ) -> setDirection ( 45 ) ;
$objDrawing -> setWorksheet ( $objPHPExcel -> getActiveSheet ( ) ) ;

//在默认sheet后，创建一个worksheet
$objPHPExcel -> createSheet ( ) ;

http://www.freebluer.net/export-data-in-codeigniter-to-excel-with-phpexcel.html希望对大家有所帮助。

以上！

转载于:https://blog.51cto.com/yerik/1357981

weixin_34378922

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
CodeIgniter+PHPExcel 导出数据为Excel文件

下载安装PHPExcel库下载PHPExcel：http://phpexcel.codeplex.com安装PHPExcel非常的简单，只需解压压缩包里的Classes文件夹中的内容到application\libraries\目录下即可，最终的目录结构如下：–application\libraries\PHPExcel.php–application\libraries...
复制链接

扫一扫