suchen

Heim >Backend-Entwicklung >PHP-Tutorial >phpExcel中文帮助手册(知识点)

phpExcel中文帮助手册(知识点)

WBOY
WBOYOriginal
2016-07-25 09:04:141072Durchsuche
  2. $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
  3. $objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
  4. $objPHPExcel->getActiveSheet()->setCellValue('A3', true);
  5. $objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
  6. $objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');
复制代码

合并单元格

  2. $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
复制代码

分离单元格

  2. $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
复制代码

保护cell

  2. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
  3. $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
复制代码

设置格式

  2. // Set cell number formats
  3. echo date('H:i:s') . " Set cell number formats\n";
  4. $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
  5. $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
复制代码

设置宽width

  2. // Set column widths
  3. $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
  4. $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
复制代码

设置font

  2. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
  3. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
  4. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
  5. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
  6. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  7. $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  8. $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
  9. $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
复制代码

设置align

  2. $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  3. $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  4. $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
  5. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
复制代码

//垂直居中

  2. $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
复制代码

设置column的border

  2. $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  3. $objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  4. $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  5. $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  6. $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
复制代码

设置border的color

  2. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
  3. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
  4. $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
  5. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
  6. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
  7. $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
复制代码

设置填充颜色

  2. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  3. $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
  4. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  5. $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
复制代码

加图片

  3. $objDrawing = new PHPExcel_Worksheet_Drawing();

  4. $objDrawing->setName('Logo');
  5. $objDrawing->setDescription('Logo');
  6. $objDrawing->setPath('./images/officelogo.jpg');
  7. $objDrawing->setHeight(36);
  8. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

  9. $objDrawing = new PHPExcel_Worksheet_Drawing();

  10. $objDrawing->setName('Paid');
  11. $objDrawing->setDescription('Paid');
  12. $objDrawing->setPath('./images/paid.png');
  13. $objDrawing->setCoordinates('B15');
  14. $objDrawing->setOffsetX(110);
  15. $objDrawing->setRotation(25);
  16. $objDrawing->getShadow()->setVisible(true);
  17. $objDrawing->getShadow()->setDirection(45);
  18. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
复制代码

//处理中文输出问题 需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理： $str = iconv('gb2312', 'utf-8', $str); 或者可以写一个函数专门处理中文字符串：

  2. function convertUTF8($str)
  3. {
  4. if(empty($str)) return '';
  5. return iconv('gb2312', 'utf-8', $str);
  6. }
复制代码

//从数据库输出数据处理方式

从数据库读取数据如：

  3. $db = new Mysql($dbconfig);

  4. $sql = "SELECT * FROM 表名";
  5. $row = $db->GetAll($sql); // $row 为二维数组

  6. $count = count($row);

  7. for ($i = 2; $i $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));
  8. $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertUTF8($row[$i-2][2]));
  9. $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i-2][3]));
  10. $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));
  11. $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));
  12. $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertUTF8($row[$i-2][6]));

  13. $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i-2][7]));

  14. $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8]));
  15. }
复制代码

在默认sheet后，创建一个worksheet 在这里插入一段，之前脚本学堂也为大家介绍过几篇有关phpexcel的文章，有兴趣的朋友可以看看： 使用PHPExcel判别和格式化Excel中的日期格式的例子 phpexcel导出excel的颜色与网页中颜色不一致的解决方法 CI中使用PHPExcel导出数据到Excel

  3. echo date('H:i:s') . " Create new Worksheet object\n";

  4. $objPHPExcel->createSheet();

  5. $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');

  6. $objWriter-save('php://output');
复制代码

附，几个常用的使用方法。

  3. include 'PHPExcel.php';

  4. include 'PHPExcel/Writer/Excel2007.php';
  5. //或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
  6. 创建一个excel
  7. $objPHPExcel = new PHPExcel();

  8. 保存excel—2007格式

  9. $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
  10. //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
  11. $objWriter->save("xxx.xlsx");

  12. 直接输出到浏览器

  13. $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
  14. header("Pragma: public");
  15. header("Expires: 0″);
  16. header("Cache-Control:must-revalidate, post-check=0, pre-check=0″);
  17. header("Content-Type:application/force-download");
  18. header("Content-Type:application/vnd.ms-execl");
  19. header("Content-Type:application/octet-stream");
  20. header("Content-Type:application/download");;
  21. header('Content-Disposition:attachment;filename="resume.xls"');
  22. header("Content-Transfer-Encoding:binary");
  23. $objWriter->save('php://output');
  24. ?>
复制代码


Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：php auto_prepend_file和auto_append_file的用法 Nächster Artikel：php数据备份：单表备份 整表备份 导入数据库

In Verbindung stehende Artikel

Mehr sehen