PHPExcel导出excel_PHP教程-tutorial php-php.cn

Rumah

pembangunan bahagian belakang

tutorial php

PHPExcel导出excel_PHP教程

Jul 13, 2016 am 10:28 AM

phpexcel

如果导出中文时出现乱码，可以尝试将字符串转换成gb2312，例如下面就把<span>$yourStr从utf</span>-8转换成了gb2312:
<span>$yourStr</span> = mb_convert_encoding("gb2312", "UTF-8", <span>$yourStr</span><span>); 

总结 php导出Excel php导入Excel PhpExcel使用说明 PhpExcel使用手册
</span>1：第一推荐无比风骚的PHPExcel，官方网站： http:<span>//</span><span>www.codeplex.com/PHPExcel</span>
<span>导入导出都成，可以导出office2007格式，同时兼容2003。
下载下来的包中有文档和例子，大家可以自行研究。
</span><? 
<span>//</span><span>设置PHPExcel类库的include path </span>
<span>set_include_path</span>('.'. PATH_SEPARATOR .'D:\workspace\biznaligy_eh\dev_src\includes\PHPExcel' . PATH_SEPARATOR . <span>get_include_path</span><span>());     
</span><span>/*</span><span>*
    * 以下是使用示例，对于以 //// 开头的行是不同的可选方式，请根据实际需要
   * 打开对应行的注释。
* 如果使用 Excel5 ，输出的内容应该是GBK编码。
   </span><span>*/</span> 
   <span>require_once</span> 'PHPExcel.php'<span>; 
   </span><span>require_once</span> 'PHPExcel/Writer/Excel5.php';     <span>//</span><span> 用于其他低版本xls </span>
   <span>require_once</span> 'PHPExcel/Writer/Excel2007.php'; <span>//</span><span> 用于 excel-2007 格式 

// 创建一个处理对象实例 </span>
   <span>$objExcel</span> = <span>new</span><span> PHPExcel(); 

</span><span>//</span><span> 创建文件格式写入对象实例, uncomment </span>
   <span>$objWriter</span> = <span>new</span> PHPExcel_Writer_Excel5(<span>$objExcel</span>);     <span>//</span><span> 用于其他版本格式 
   //or
//$objWriter = new PHPExcel_Writer_Excel2007($objExcel); // 用于 2007 格式 
//$objWriter->setOffice2003Compatibility(true); 


//设置文档基本属性 </span>
   <span>$objProps</span> = <span>$objExcel</span>-><span>getProperties(); 
   </span><span>$objProps</span>->setCreator("Zeal Li"<span>); 
   </span><span>$objProps</span>->setLastModifiedBy("Zeal Li"<span>); 
   </span><span>$objProps</span>->setTitle("Office XLS Test Document"<span>); 
    </span><span>$objProps</span>->setSubject("Office XLS Test Document, Demo"<span>); 
   </span><span>$objProps</span>->setDescription("Test document, generated by PHPExcel."<span>); 
   </span><span>$objProps</span>->setKeywords("office excel PHPExcel"<span>); 
    </span><span>$objProps</span>->setCategory("Test"<span>); 
   </span><span>//</span><span>设置当前的sheet索引，用于后续的内容操作。 
//一般只有在使用多个sheet的时候才需要显示调用。 
//缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0 </span>
   <span>$objExcel</span>->setActiveSheetIndex(0<span>); 
    </span><span>$objActSheet</span> = <span>$objExcel</span>-><span>getActiveSheet(); 

</span><span>//</span><span>设置当前活动sheet的名称 </span>
    <span>$objActSheet</span>->setTitle('测试Sheet'<span>); 

</span><span>//</span><span>设置单元格内容 由PHPExcel根据传入内容自动判断单元格内容类型 </span>
    <span>$objActSheet</span>->setCellValue('A1', '字符串内容'); <span>//</span><span> 字符串内容 </span>
    <span>$objActSheet</span>->setCellValue('A2', 26);            <span>//</span><span> 数值 </span>
    <span>$objActSheet</span>->setCellValue('A3', <span>true</span>);          <span>//</span><span> 布尔值 </span>
    <span>$objActSheet</span>->setCellValue('A4', '=SUM(A2:A2)'); <span>//</span><span> 公式 

//显式指定内容类型 </span>
   <span>$objActSheet</span>->setCellValueExplicit('A5','8757584',PHPExcel_Cell_DataType::<span>TYPE_STRING); 

   </span><span>//</span><span>合并单元格 </span>
    <span>$objActSheet</span>->mergeCells('B1:C22'<span>); 

   </span><span>//</span><span>分离单元格 </span>
    <span>$objActSheet</span>->unmergeCells('B1:C22'<span>);
   </span><span>//</span><span>设置宽度 </span>
   <span>$objActSheet</span>->getColumnDimension('B')->setAutoSize(<span>true</span><span>); 
   </span><span>$objActSheet</span>->getColumnDimension('A')->setWidth(30<span>); 
   
</span><span>//</span><span>设置单元格内容的数字格式。 
//如果使用了 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 方法可以让每一行的内容 
   //都按原始内容全部显示出来。</span>
    <span>$objStyleA5</span> = <span>$objActSheet</span> ->getStyle('A5'<span>);
   </span><span>$objStyleA5</span> ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::<span>FORMAT_NUMBER); 

</span><span>//</span><span>设置字体 </span>
    <span>$objFontA5</span> = <span>$objStyleA5</span>-><span>getFont(); 
    </span><span>$objFontA5</span>->setName('Courier New'<span>); 
    </span><span>$objFontA5</span>->setSize(10<span>); 
    </span><span>$objFontA5</span>->setBold(<span>true</span><span>); 
    </span><span>$objFontA5</span>->setUnderline(PHPExcel_Style_Font::<span>UNDERLINE_SINGLE);
    </span><span>$objFontA5</span> ->getColor()->setARGB('FFFF0000'<span>) ;
    </span><span>$objFontA5</span> ->getColor()->setARGB( PHPExcel_Style_Color::<span>COLOR_WHITE);
</span><span>//</span><span> $ objFontA5 ->getFont()->setColor(PHPExcel_Style_Color::COLOR_RED); 

//设置对齐方式 </span>
    <span>$objAlignA5</span> = <span>$objStyleA5</span>-><span>getAlignment(); 
    </span><span>$objAlignA5</span>->setHorizontal(PHPExcel_Style_Alignment::<span>HORIZONTAL_RIGHT); 
    </span><span>$objAlignA5</span>->setVertical(PHPExcel_Style_Alignment::<span>VERTICAL_CENTER); 

</span><span>//</span><span>设置边框 </span>
   <span>$objBorderA5</span> = <span>$objStyleA5</span>-><span>getBorders(); 
   </span><span>$objBorderA5</span>->getTop()->setBorderStyle(PHPExcel_Style_Border::<span>BORDER_THIN); 
   </span><span>$objBorderA5</span>->getTop()->getColor()->setARGB('FFFF0000') ; <span>//</span><span> 边框color </span>
   <span>$objBorderA5</span>->getBottom()->setBorderStyle(PHPExcel_Style_Border::<span>BORDER_THIN); 
   </span><span>$objBorderA5</span>->getLeft()->setBorderStyle(PHPExcel_Style_Border::<span>BORDER_THIN); 
   </span><span>$objBorderA5</span>->getRight()->setBorderStyle(PHPExcel_Style_Border::<span>BORDER_THIN); 

</span><span>//</span><span>设置CELL填充颜色 </span>
   <span>$objFillA5</span> = <span>$objStyleA5</span>-><span>getFill(); 
   </span><span>$objFillA5</span>->setFillType(PHPExcel_Style_Fill::<span>FILL_SOLID); 
   </span><span>$objFillA5</span>->getStartColor()->setARGB('FFEEEEEE'<span>); 

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

</span><span>//</span><span>添加图片 </span>
   <span>$objDrawing</span> = <span>new</span><span> PHPExcel_Worksheet_Drawing(); 
   </span><span>$objDrawing</span>->setName('ZealImg'<span>); 
   </span><span>$objDrawing</span>->setDescription('Image inserted by Zeal'<span>); 
   </span><span>$objDrawing</span>->setPath('./zeali.net.logo.gif'<span>); 
   </span><span>$objDrawing</span>->setHeight(36<span>); 
   </span><span>$objDrawing</span>->setCoordinates('C23'<span>); 
   </span><span>$objDrawing</span>->setOffsetX(10<span>); 
   </span><span>$objDrawing</span>->setRotation(15<span>); 
   </span><span>$objDrawing</span>->getShadow()->setVisible(<span>true</span><span>); 
   </span><span>$objDrawing</span>->getShadow()->setDirection(36<span>); 
   </span><span>$objDrawing</span>->setWorksheet(<span>$objActSheet</span><span>); 

</span><span>//</span><span>添加一个新的worksheet </span>
   <span>$objExcel</span>-><span>createSheet(); 
   </span><span>$objExcel</span>->getSheet(1)->setTitle('测试2'<span>); 

</span><span>//</span><span>保护单元格 </span>
   <span>$objExcel</span>->getSheet(1)->getProtection()->setSheet(<span>true</span><span>); 
   </span><span>$objExcel</span>->getSheet(1)->protectCells('A1:C22', 'PHPExcel'<span>);

</span><span>//</span><span>显示网格线: </span>
<span>$objPHPExcel</span>->getActiveSheet()->setShowGridlines(<span>true</span><span>);

</span><span>//</span><span>显示隐藏列</span>
<span>$objPHPExcel</span>->getActiveSheet()->getColumnDimension('C')->setVisible(<span>true</span><span>);
</span><span>$objPHPExcel</span>->getActiveSheet()->getColumnDimension('D')->setVisible(<span>false</span><span>);

</span><span>//</span><span>显示隐藏行</span>
<span>$objPHPExcel</span>->getActiveSheet()->getRowDimension('10')->setVisible(<span>false</span><span>);
</span><span>//</span><span>默认列宽</span>
<span>$objPHPExcel</span>->getActiveSheet()->getDefaultColumnDimension()->setWidth(12<span>);
</span><span>//</span><span>默认行宽</span>
<span>$objPHPExcel</span>->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15<span>);
</span><span>//</span><span>worksheet 默认style 设置 (和默认不同的需单独设置)</span>
<span>$objPHPExcel</span>->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial'<span>);
</span><span>$objPHPExcel</span>->getActiveSheet()->getDefaultStyle()->getFont()->setSize(8<span>);
</span><span>$objPHPExcel</span>->getActiveSheet()->getDefaultStyle()-><span>getAlignment(); 
</span><span>$objPHPExcel</span>->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::<span>HORIZONTAL_CENTER); 
</span><span>$objPHPExcel</span>->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::<span>VERTICAL_CENTER);
</span><span>//</span><span>输出内容 </span>
   <span>$outputFileName</span> = "output.xls"<span>; 
</span><span>//</span><span>到文件 </span><span>
//</span><span>//$objWriter->save($outputFileName); 
//or 
//到浏览器 </span>
   <span>header</span>("Content-Type: application/force-download"<span>); 
   </span><span>header</span>("Content-Type: application/octet-stream"<span>); 
   </span><span>header</span>("Content-Type: application/download"<span>); 
   </span><span>header</span>('Content-Disposition:inline;filename="'.<span>$outputFileName</span>.'"'<span>); 
   </span><span>header</span>("Content-Transfer-Encoding: binary"<span>); 
   </span><span>header</span>("Last-Modified: " . <span>gmdate</span>("D, d M Y H:i:s") . " GMT"<span>); 
   </span><span>header</span>("Cache-Control: must-revalidate, post-check=0, pre-check=0"<span>); 
   </span><span>header</span>("Pragma: no-cache"<span>); 
   </span><span>$objWriter</span>->save('php://output'<span>); 
</span>?>

Salin selepas log masuk

Kenyataan Laman Web ini

Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn