PhpExcel中文帮助手册|PhpExcel使用方法

admin

PhpExcel中文帮助手册|PhpExcel使用方法

1. 下面是总结的几个使用方法
   
2. include 'PHPExcel.php';
   
3. include 'PHPExcel/Writer/Excel2007.php';
   
4. //或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
   
5. 创建一个excel
   
6. $objPHPExcel = new PHPExcel();
   
7. 保存excel—2007格式
   
8. $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
   
9. //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
   
10. $objWriter->save("xxx.xlsx");
    
11. 直接输出到浏览器
    
12. $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    
13. header("Pragma: public");
    
14. header("Expires: 0″);
    
15. header("Cache-Control:must-revalidate, post-check=0, pre-check=0″);
    
16. header("Content-Type:application/force-download");
    
17. header("Content-Type:application/vnd.ms-execl");
    
18. header("Content-Type:application/octet-stream");
    
19. header("Content-Type:application/download");;
    
20. header('Content-Disposition:attachment;filename="resume.xls"');
    
21. header("Content-Transfer-Encoding:binary");
    
22. $objWriter->save('php://output');
    
23. ——————————————————————————————————————–
    
24. 设置excel的属性：
    
25. 创建人
    
26. $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
    
27. 最后修改人
    
28. $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
    
29. 标题
    
30. $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
    
31. 题目
    
32. $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
    
33. 描述
    
34. $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
    
35. 关键字
    
36. $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
    
37. 种类
    
38. $objPHPExcel->getProperties()->setCategory("Test result file");
    
39. ——————————————————————————————————————–
    
40. 设置当前的sheet
    
41. $objPHPExcel->setActiveSheetIndex(0);
    
42. 设置sheet的name
    
43. $objPHPExcel->getActiveSheet()->setTitle('Simple');
    
44. 设置单元格的值
    
45. $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
    
46. $objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
    
47. $objPHPExcel->getActiveSheet()->setCellValue('A3', true);
    
48. $objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
    
49. $objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');
    
50. 合并单元格
    
51. $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
    
52. 分离单元格
    
53. $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
    
54. 
    
55. 保护cell
    
56. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
    
57. $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
    
58. 设置格式
    
59. // Set cell number formats
    
60. echo date('H:i:s') . " Set cell number formats\n";
    
61. $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
    
62. $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
    
63. 设置宽width
    
64. // Set column widths
    
65. $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    
66. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
    
67. 设置font
    
68. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
    
69. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
    
70. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
    
71. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
    
72. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
    
73. $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
    
74. $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
    
75. $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
    
76. 设置align
    
77. $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    
78. $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    
79. $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    
80. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
    
81. //垂直居中
    
82. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    
83. 设置column的border
    
84. $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
85. $objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
86. $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
87. $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
88. $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    
89. 设置border的color
    
90. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
    
91. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
    
92. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
    
93. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
    
94. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
    
95. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
    
96. 设置填充颜色
    
97. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    
98. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
    
99. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    
100. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
     
101. 加图片
     
102. $objDrawing = new PHPExcel_Worksheet_Drawing();
     
103. $objDrawing->setName('Logo');
     
104. $objDrawing->setDescription('Logo');
     
105. $objDrawing->setPath('./images/officelogo.jpg');
     
106. $objDrawing->setHeight(36);
     
107. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
     
108. $objDrawing = new PHPExcel_Worksheet_Drawing();
     
109. $objDrawing->setName('Paid');
     
110. $objDrawing->setDescription('Paid');
     
111. $objDrawing->setPath('./images/paid.png');
     
112. $objDrawing->setCoordinates('B15');
     
113. $objDrawing->setOffsetX(110);
     
114. $objDrawing->setRotation(25);
     
115. $objDrawing->getShadow()->setVisible(true);
     
116. $objDrawing->getShadow()->setDirection(45);
     
117. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
     
118. //处理中文输出问题
     
119. 需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理：
     
120. $str  = iconv('gb2312', 'utf-8', $str);
     
121. 或者你可以写一个函数专门处理中文字符串：
     
122. function convertUTF8($str)
     
123. {
     
124.    if(empty($str)) return '';
     
125.    return  iconv('gb2312', 'utf-8', $str);
     
126. }
     
127. //从数据库输出数据处理方式
     
128. 从数据库读取数据如：
     
129. $db = new Mysql($dbconfig);
     
130. $sql = "SELECT * FROM  表名";
     
131. $row = $db->GetAll($sql);  // $row 为二维数组
     
132. $count = count($row);
     
133. for ($i = 2; $i <= $count+1; $i++) {
     
134. $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));
     
135. $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertUTF8($row[$i-2][2]));
     
136. $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i-2][3]));
     
137. $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));
     
138. $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));
     
139. $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertUTF8($row[$i-2][6]));
     
140. $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i-2][7]));
     
141. $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8]));
     
142. }
     
143. 
     
144. 在默认sheet后，创建一个worksheet
     
145. echo date('H:i:s') . " Create new Worksheet object\n";
     
146. $objPHPExcel->createSheet();
     
147. $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
     
148. $objWriter-save('php://output');

复制代码

1. <?php
   
2. error_reporting(E_ALL);
   
3. 
   
4. require_once '../Classes/PHPExcel.php';
   
5. 
   
6. 
   
7. $objPHPExcel = new PHPExcel();
   
8. 
   
9. // 设置文件属性
   
10. $objPHPExcel->getProperties()->setCreator("C1G")
    
11.                              ->setLastModifiedBy("C1G")
    
12.                              ->setTitle("phpexcel Test Document")
    
13.                              ->setSubject("phpexcel Test Document")
    
14.                              ->setDescription("Test document for phpexcel, generated using PHP classes.")
    
15.                              ->setKeywords("office 2007 openxml php c1gstudio")
    
16.                              ->setCategory("Test");
    
17. 
    
18. //设置当前活动的sheet
    
19. $objPHPExcel->setActiveSheetIndex(0);
    
20. 
    
21. //设置sheet名字
    
22. $objPHPExcel->getActiveSheet()->setTitle('phpexcel demo');
    
23. 
    
24. //设置默认行高
    
25. $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
    
26. 
    
27. //由PHPExcel根据传入内容自动判断单元格内容类型
    
28. $objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname");
    
29. $objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname");
    
30. $objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone");
    
31. $objPHPExcel->getActiveSheet()->setCellValue('D1', "Fax");
    
32. $objPHPExcel->getActiveSheet()->setCellValue('E1', "Address");
    
33. $objPHPExcel->getActiveSheet()->setCellValue('F1', "ZIP");
    
34. $objPHPExcel->getActiveSheet()->setCellValue('G1', "DATE");
    
35. 
    
36. 
    
37. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 8, 'firstname');
    
38. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'lastname');
    
39. 
    
40. 
    
41. // utf8格式字符
    
42. $objPHPExcel->setActiveSheetIndex(0)
    
43.             ->setCellValue('A15', 'Miscellaneous glyphs')
    
44.             ->setCellValue('A16', 'éàèùâêîôûëïüÿäöüç')
    
45.             ->setCellValue('A17', 'phpexcel演示');
    
46. 
    
47. $objPHPExcel->getActiveSheet()->setCellValue('A9', "502");
    
48. $objPHPExcel->getActiveSheet()->setCellValue('B9', "99");
    
49. $objPHPExcel->getActiveSheet()->setCellValue('C9', "=SUM(A9:B9)");
    
50. 
    
51. //设置列宽
    
52. $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('20');
    
53. 
    
54. 
    
55. // 设置行高
    
56. $objPHPExcel->getActiveSheet()->getRowDimension('9')->setRowHeight(20);
    
57. 
    
58. // 加粗
    
59. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    
60. 
    
61. // 中文
    
62. $objPHPExcel->getActiveSheet()->setCellValue('A2', "小风");
    
63. $objPHPExcel->getActiveSheet()->setCellValue('B2', "wang");
    
64. 
    
65. // 设置单元格格式
    
66. $objPHPExcel->getActiveSheet()->getCell('C2')->setValueExplicit('861391327543258', PHPExcel_Cell_DataType::TYPE_NUMERIC);
    
67. 
    
68. // 日期
    
69. $objPHPExcel->getActiveSheet()->setCellValue('G2', '2008-12-31');
    
70. $objPHPExcel->getActiveSheet()->getStyle('G2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
    
71. 
    
72. // 时间戳
    
73. $time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
    
74. $objPHPExcel->getActiveSheet()->setCellValue('G3', PHPExcel_Shared_Date::PHPToExcel($time));
    
75. $objPHPExcel->getActiveSheet()->getStyle('G3')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
    
76. 
    
77. // url
    
78. $objPHPExcel->getActiveSheet()->setCellValue('G11', 'blog.c1gstudio.com');
    
79. $objPHPExcel->getActiveSheet()->getCell('G11')->getHyperlink()->setUrl('http://blog.c1gstudio.com');
    
80. 
    
81. // 另一个sheet
    
82. $objPHPExcel->getActiveSheet()->setCellValue('G12', 'sheetb');
    
83. $objPHPExcel->getActiveSheet()->getCell('G12')->getHyperlink()->setUrl("sheet://'sheetb'!A1");
    
84. 
    
85. // 水平居上
    
86. $objPHPExcel->getActiveSheet()->getStyle('A9:B9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    
87. 
    
88. // 单元格换行
    
89. $objPHPExcel->getActiveSheet()->getStyle('G2:G3')->getAlignment()->setWrapText(true);
    
90. 
    
91. // 合并
    
92. $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
    
93. 
    
94. 
    
95. // 隐藏D列
    
96. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
    
97. 
    
98. //
    
99. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
    
100. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
     
101. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setOutlineLevel(1);
     
102. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setVisible(false);
     
103. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setCollapsed(true);
     
104. 
     
105. // 固定第一行
     
106. $objPHPExcel->getActiveSheet()->freezePane('A2');
     
107. 
     
108. // 保护工作表
     
109. $objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
     
110. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
     
111. $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
     
112. $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
     
113. $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
     
114. 
     
115. //设置边框
     
116. $sharedStyle1 = new PHPExcel_Style();
     
117. $sharedStyle1->applyFromArray(
     
118.     array('borders' => array(
     
119.                                 'left'        => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
     
120.                             )
     
121.          ));
     
122. $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B1:B10");
     
123. 
     
124. // 创建一个新的工作表
     
125. $objWorksheet1 = $objPHPExcel->createSheet();
     
126. $objWorksheet1->setTitle('sheetb');
     
127. 
     
128. 
     
129. $objPHPExcel->setActiveSheetIndex(1);
     
130. 
     
131. 
     
132. // 创建一个图片
     
133. $gdImage = @imagecreatetruecolor(200, 20) or die('Cannot Initialize new GD image stream');
     
134. $textColor = imagecolorallocate($gdImage, 255, 255, 255);
     
135. imagestring($gdImage, 1, 5, 5,  'Created with PHPExcel (c1gstudio.com)', $textColor);
     
136. 
     
137. // 把创建的图片添加到工作表
     
138. $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
     
139. $objDrawing->setName('Sample image');
     
140. $objDrawing->setDescription('Sample image');
     
141. $objDrawing->setImageResource($gdImage);
     
142. $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
     
143. $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
     
144. $objDrawing->setHeight(36);
     
145. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
     
146. 
     
147. $objPHPExcel->setActiveSheetIndex(0);
     
148. 
     
149. // 保存
     
150. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     
151. $objWriter->save('testexcel'.time().'.xls');
     
152. 
     
153. ?>

复制代码