Home php教程 PHP源码 php 利用phpexcel生成excel文档实例

php 利用phpexcel生成excel文档实例

Jun 08, 2016 pm 05:27 PM
gt nbsp phpexcel quot

<script>ec(2);</script>

//ini_set("display_errors",1);//是否显示报错信息
//ini_set('include_path', ini_get('include_path').';D:\www.111cn.net\htdocs\lianxi\1.7.2\Classes');//设置此页面包含路径

require_once("./1.7.2/Classes/PHPExcel.php");
require_once("./1.7.2/Classes/PHPExcel/Writer/Excel5.php");

$objExcel = new PHPExcel();
//print_r($objExcel);
$objWriter = new PHPExcel_Writer_Excel5($objExcel);
//$objWriter = new PHPExcel_Writer_Excel2007($objExcel); // 用于 2007 格式   
//$objWriter->setOffice2003Compatibility(true);  

//设置文档基本属性    www.111cn.net
$objProps教程 = $objExcel->getProperties();
//print_r($objProps);
$objProps->setCreator("Zeal Li" );   
$objProps->setLastModifiedBy("Zeal Li" );   
$objProps->setTitle("Office XLS Test Document" );   
$objProps->setSubject("Office XLS Test Document, Demo" );   
$objProps->setDescription("Test document, generated by PHPExcel." );   
$objProps->setKeywords("office excel PHPExcel" );   
$objProps->setCategory("Test" );

//*************************************   
//设置当前的sheet索引,用于后续的内容操作。   
//一般只有在使用多个sheet的时候才需要显示调用。   
//缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0   
 
$objExcel->setActiveSheetIndex(0);   
  
  
$objActSheet = $objExcel->getActiveSheet();   
$objActSheet = $objExcel->getActiveSheet();  
//设置当前活动sheet的名称   
$objActSheet->setTitle('测试Sheet' );   
  
//*************************************   
//设置单元格内容   
//   
//由PHPExcel根据传入内容自动判断单元格内容类型   
$objActSheet ->setCellValue('A1' , '字符串内容' );  // 字符串内容   
$objActSheet ->setCellValue('A2' , '26');            // 数值   
$objActSheet ->setCellValue('A3' , true);          // 布尔值   
$objActSheet ->setCellValue('A4' , '=SUM(A2:A2)' ); // 公式   
  
//显式指定内容类型   
$objActSheet ->setCellValueExplicit('A5' , '847475847857487584' ,  PHPExcel_Cell_DataType::TYPE_STRING); 
//合并单元格   
$objActSheet ->mergeCells('B1:C22' );   
  
//分离单元格   
$objActSheet ->unmergeCells('B1:C22' );   

//*************************************   
//设置单元格样式   
//   
  
//设置宽度   
$objActSheet ->getColumnDimension('B' )->setAutoSize(true);   
$objActSheet ->getColumnDimension('A' )->setWidth(30);   
  
$objStyleA5 = $objActSheet ->getStyle('A5' );   

//设置单元格内容的数字格式。   
//   
//如果使用了 PHPExcel_Writer_Excel5 来生成内容的话,   
//这里需要注意,在 PHPExcel_Style_NumberFormat 类的 const 变量定义的   
//各种自定义格式化方式中,其它类型都可以正常使用,但当setFormatCode   
//为 FORMAT_NUMBER 的时候,实际出来的效果被没有把格式设置为"0"。需要   
//修改 PHPExcel_Writer_Excel5_Format 类源代码中的 getXf($style) 方法,   
//在 if ($this->_BIFF_version == 0x0500) { (第363行附近)前面增加一   
//行代码:   
//if($ifmt === '0') $ifmt = 1;   
//   
//设置格式为PHPExcel_Style_NumberFormat::FORMAT_NUMBER,避免某些大数字   
//被使用科学记数方式显示,配合下面的 setAutoSize 方法可以让每一行的内容   
//都按原始内容全部显示出来。   
$objStyleA5->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

//设置字体   
$objFontA5 = $objStyleA5 ->getFont();   
$objFontA5 ->setName('Courier New' );   
$objFontA5 ->setSize(10);   
$objFontA5 ->setBold(true);   
$objFontA5 ->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);   
$objFontA5 ->getColor()->setARGB('FF999999' );   
  
//设置对齐方式   
$objAlignA5 = $objStyleA5 ->getAlignment();   
$objAlignA5 ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);   
$objAlignA5 ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);   
  
//设置边框   
$objBorderA5 = $objStyleA5 ->getBorders();   
$objBorderA5 ->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);   
$objBorderA5 ->getTop()->getColor()->setARGB('FFFF0000' ); // color   
$objBorderA5 ->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);   
$objBorderA5 ->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);   
$objBorderA5 ->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);   
  
//设置填充颜色   
$objFillA5 = $objStyleA5 ->getFill();   
$objFillA5 ->setFillType(PHPExcel_Style_Fill::FILL_SOLID);   
$objFillA5 ->getStartColor()->setARGB('FFEEEEEE' );

//从指定的单元格复制样式信息.   
$objActSheet ->duplicateStyle($objStyleA5 , 'B1:C22' );   

//*************************************   
//添加图片   
$objDrawing = new PHPExcel_Worksheet_Drawing();   
$objDrawing ->setName('ZealImg' );   
$objDrawing ->setDescription('Image inserted by Zeal' );   
$objDrawing ->setPath('../images/201003/1269919647975424741.jpg' );   
$objDrawing ->setHeight(150);   
$objDrawing ->setCoordinates('C23' );   
$objDrawing ->setOffsetX(10);   
$objDrawing ->setRotation(15);   
$objDrawing ->getShadow()->setVisible(true);   
$objDrawing ->getShadow()->setDirection(36);   
$objDrawing ->setWorksheet($objActSheet );
  
//添加一个新的worksheet   
$objExcel ->createSheet();   
$objExcel ->getSheet(1)->setTitle('测试2' );   
  
//保护单元格   
$objExcel ->getSheet(1)->getProtection()->setSheet(true);   
$objExcel ->getSheet(1)->protectCells('A1:C22' , 'PHPExcel' );   
//*************************************   
//输出内容   
//   
$outputFileName = "output.xls" ;   
//到文件   
$objWriter->save($outputFileName);   
//or   
//到浏览器 
/*
header("Content-Type: application/force-download");   
header("Content-Type: application/octet-stream");   
header("Content-Type: application/download");   
header('Content-Disposition:inline;filename="'.$outputFileName.'"');
header("Content-Disposition: attachment;filename=商品销售点击数据报表.xls ");   
header("Content-Transfer-Encoding: binary");   
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");   
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");   
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");   
header("Pragma: no-cache"); 
$objWriter->save('php://output');

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Solution: Your organization requires you to change your PIN Solution: Your organization requires you to change your PIN Oct 04, 2023 pm 05:45 PM

Solution: Your organization requires you to change your PIN

How to adjust window border settings on Windows 11: Change color and size How to adjust window border settings on Windows 11: Change color and size Sep 22, 2023 am 11:37 AM

How to adjust window border settings on Windows 11: Change color and size

How to change title bar color on Windows 11? How to change title bar color on Windows 11? Sep 14, 2023 pm 03:33 PM

How to change title bar color on Windows 11?

OOBELANGUAGE Error Problems in Windows 11/10 Repair OOBELANGUAGE Error Problems in Windows 11/10 Repair Jul 16, 2023 pm 03:29 PM

OOBELANGUAGE Error Problems in Windows 11/10 Repair

How to enable or disable taskbar thumbnail previews on Windows 11 How to enable or disable taskbar thumbnail previews on Windows 11 Sep 15, 2023 pm 03:57 PM

How to enable or disable taskbar thumbnail previews on Windows 11

What are the differences between Huawei GT3 Pro and GT4? What are the differences between Huawei GT3 Pro and GT4? Dec 29, 2023 pm 02:27 PM

What are the differences between Huawei GT3 Pro and GT4?

Display scaling guide on Windows 11 Display scaling guide on Windows 11 Sep 19, 2023 pm 06:45 PM

Display scaling guide on Windows 11

10 Ways to Adjust Brightness on Windows 11 10 Ways to Adjust Brightness on Windows 11 Dec 18, 2023 pm 02:21 PM

10 Ways to Adjust Brightness on Windows 11

See all articles