设为首页收藏本站

PHPIN.NET

 找回密码
 立即注册
查看: 276|回复: 0

[技巧手记] PhpExcel中文帮助手册|PhpExcel使用方法

[复制链接]

395

主题

402

帖子

2987

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
2987
发表于 2017-8-8 15:29:53 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
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. 保护cell
  55. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
  56. $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
  57. 设置格式
  58. // Set cell number formats
  59. echo date('H:i:s') . " Set cell number formats\n";
  60. $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
  61. $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
  62. 设置宽width
  63. // Set column widths
  64. $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
  65. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
  66. 设置font
  67. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
  68. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
  69. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
  70. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
  71. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  72. $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  73. $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
  74. $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
  75. 设置align
  76. $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  77. $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  78. $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  79. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
  80. //垂直居中
  81. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  82. 设置column的border
  83. $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  84. $objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  85. $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  86. $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  87. $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  88. 设置border的color
  89. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
  90. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
  91. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
  92. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
  93. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
  94. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
  95. 设置填充颜色
  96. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  97. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
  98. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  99. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
  100. 加图片
  101. $objDrawing = new PHPExcel_Worksheet_Drawing();
  102. $objDrawing->setName('Logo');
  103. $objDrawing->setDescription('Logo');
  104. $objDrawing->setPath('./images/officelogo.jpg');
  105. $objDrawing->setHeight(36);
  106. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
  107. $objDrawing = new PHPExcel_Worksheet_Drawing();
  108. $objDrawing->setName('Paid');
  109. $objDrawing->setDescription('Paid');
  110. $objDrawing->setPath('./images/paid.png');
  111. $objDrawing->setCoordinates('B15');
  112. $objDrawing->setOffsetX(110);
  113. $objDrawing->setRotation(25);
  114. $objDrawing->getShadow()->setVisible(true);
  115. $objDrawing->getShadow()->setDirection(45);
  116. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
  117. //处理中文输出问题
  118. 需要将字符串转化为UTF-8编码,才能正常输出,否则中文字符将输出为空白,如下处理:
  119. $str  = iconv('gb2312', 'utf-8', $str);
  120. 或者你可以写一个函数专门处理中文字符串:
  121. function convertUTF8($str)
  122. {
  123.    if(empty($str)) return '';
  124.    return  iconv('gb2312', 'utf-8', $str);
  125. }
  126. //从数据库输出数据处理方式
  127. 从数据库读取数据如:
  128. $db = new Mysql($dbconfig);
  129. $sql = "SELECT * FROM  表名";
  130. $row = $db->GetAll($sql);  // $row 为二维数组
  131. $count = count($row);
  132. for ($i = 2; $i <= $count+1; $i++) {
  133. $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));
  134. $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertUTF8($row[$i-2][2]));
  135. $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i-2][3]));
  136. $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));
  137. $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));
  138. $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertUTF8($row[$i-2][6]));
  139. $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i-2][7]));
  140. $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8]));
  141. }

  142. 在默认sheet后,创建一个worksheet
  143. echo date('H:i:s') . " Create new Worksheet object\n";
  144. $objPHPExcel->createSheet();
  145. $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
  146. $objWriter-save('php://output');
复制代码
  1. <?php
  2. error_reporting(E_ALL);

  3. require_once '../Classes/PHPExcel.php';


  4. $objPHPExcel = new PHPExcel();

  5. // 设置文件属性
  6. $objPHPExcel->getProperties()->setCreator("C1G")
  7.                              ->setLastModifiedBy("C1G")
  8.                              ->setTitle("phpexcel Test Document")
  9.                              ->setSubject("phpexcel Test Document")
  10.                              ->setDescription("Test document for phpexcel, generated using PHP classes.")
  11.                              ->setKeywords("office 2007 openxml php c1gstudio")
  12.                              ->setCategory("Test");

  13. //设置当前活动的sheet
  14. $objPHPExcel->setActiveSheetIndex(0);

  15. //设置sheet名字
  16. $objPHPExcel->getActiveSheet()->setTitle('phpexcel demo');

  17. //设置默认行高
  18. $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

  19. //由PHPExcel根据传入内容自动判断单元格内容类型
  20. $objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname");
  21. $objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname");
  22. $objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone");
  23. $objPHPExcel->getActiveSheet()->setCellValue('D1', "Fax");
  24. $objPHPExcel->getActiveSheet()->setCellValue('E1', "Address");
  25. $objPHPExcel->getActiveSheet()->setCellValue('F1', "ZIP");
  26. $objPHPExcel->getActiveSheet()->setCellValue('G1', "DATE");


  27. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 8, 'firstname');
  28. $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'lastname');


  29. // utf8格式字符
  30. $objPHPExcel->setActiveSheetIndex(0)
  31.             ->setCellValue('A15', 'Miscellaneous glyphs')
  32.             ->setCellValue('A16', 'éàèùâêîôûëïüÿäöüç')
  33.             ->setCellValue('A17', 'phpexcel演示');

  34. $objPHPExcel->getActiveSheet()->setCellValue('A9', "502");
  35. $objPHPExcel->getActiveSheet()->setCellValue('B9', "99");
  36. $objPHPExcel->getActiveSheet()->setCellValue('C9', "=SUM(A9:B9)");

  37. //设置列宽
  38. $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('20');


  39. // 设置行高
  40. $objPHPExcel->getActiveSheet()->getRowDimension('9')->setRowHeight(20);

  41. // 加粗
  42. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

  43. // 中文
  44. $objPHPExcel->getActiveSheet()->setCellValue('A2', "小风");
  45. $objPHPExcel->getActiveSheet()->setCellValue('B2', "wang");

  46. // 设置单元格格式
  47. $objPHPExcel->getActiveSheet()->getCell('C2')->setValueExplicit('861391327543258', PHPExcel_Cell_DataType::TYPE_NUMERIC);

  48. // 日期
  49. $objPHPExcel->getActiveSheet()->setCellValue('G2', '2008-12-31');
  50. $objPHPExcel->getActiveSheet()->getStyle('G2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

  51. // 时间戳
  52. $time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
  53. $objPHPExcel->getActiveSheet()->setCellValue('G3', PHPExcel_Shared_Date::PHPToExcel($time));
  54. $objPHPExcel->getActiveSheet()->getStyle('G3')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

  55. // url
  56. $objPHPExcel->getActiveSheet()->setCellValue('G11', 'blog.c1gstudio.com');
  57. $objPHPExcel->getActiveSheet()->getCell('G11')->getHyperlink()->setUrl('http://blog.c1gstudio.com');

  58. // 另一个sheet
  59. $objPHPExcel->getActiveSheet()->setCellValue('G12', 'sheetb');
  60. $objPHPExcel->getActiveSheet()->getCell('G12')->getHyperlink()->setUrl("sheet://'sheetb'!A1");

  61. // 水平居上
  62. $objPHPExcel->getActiveSheet()->getStyle('A9:B9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

  63. // 单元格换行
  64. $objPHPExcel->getActiveSheet()->getStyle('G2:G3')->getAlignment()->setWrapText(true);

  65. // 合并
  66. $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');


  67. // 隐藏D列
  68. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);

  69. //
  70. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
  71. $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
  72. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setOutlineLevel(1);
  73. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setVisible(false);
  74. $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setCollapsed(true);

  75. // 固定第一行
  76. $objPHPExcel->getActiveSheet()->freezePane('A2');

  77. // 保护工作表
  78. $objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');
  79. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
  80. $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
  81. $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
  82. $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

  83. //设置边框
  84. $sharedStyle1 = new PHPExcel_Style();
  85. $sharedStyle1->applyFromArray(
  86.     array('borders' => array(
  87.                                 'left'        => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
  88.                             )
  89.          ));
  90. $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "B1:B10");

  91. // 创建一个新的工作表
  92. $objWorksheet1 = $objPHPExcel->createSheet();
  93. $objWorksheet1->setTitle('sheetb');


  94. $objPHPExcel->setActiveSheetIndex(1);


  95. // 创建一个图片
  96. $gdImage = @imagecreatetruecolor(200, 20) or die('Cannot Initialize new GD image stream');
  97. $textColor = imagecolorallocate($gdImage, 255, 255, 255);
  98. imagestring($gdImage, 1, 5, 5,  'Created with PHPExcel (c1gstudio.com)', $textColor);

  99. // 把创建的图片添加到工作表
  100. $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
  101. $objDrawing->setName('Sample image');
  102. $objDrawing->setDescription('Sample image');
  103. $objDrawing->setImageResource($gdImage);
  104. $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
  105. $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
  106. $objDrawing->setHeight(36);
  107. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

  108. $objPHPExcel->setActiveSheetIndex(0);

  109. // 保存
  110. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  111. $objWriter->save('testexcel'.time().'.xls');

  112. ?>
复制代码
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

小黑屋|手机版|Archiver|PHPIN.NET ( 冀ICP备00000001号 )|网站地图  

GMT+8, 2017-11-25 17:32

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表