PHPEXCEL的用法与简介_PHP教程

PHPEXCEL是一个用来生成excel的php插件,他可以很方便的对excel数据进行操作,如:生成excel,修改excel数据等等。

一、PHPEXCEL简介

PHPEXCEL提供了一系列的API,能够解析与生成excel,pdf之类的文档。

PHPEXCEL虽然强大,不过使用起来相对有些繁锁,如果需要输出较为复杂格式时,是一个不错的选择。可以到官方下载到源码。

二、PHPEXCEL部分函数

设置当前的工作簿,返回该工作簿对象:
$excelSheet = $excel->setActiveSheetIndex0);

合并单元格,返回该单元格对象,以下示例即合并A列第一行与第二行所在单元格:

代码如下 复制代码

$excelSheet->mergeCells’A1:A2′);

设置单元格的值,参数:单元格名称,值:
$excelSheet->setCellValue’A1′, ‘字符串内容’);
$excelSheet->setCellValue’A2′, 26); //数值
$excelSheet->setCellValue’A3′, true); //布尔值
$excelSheet->setCellValue’A4′, ‘=SUMA2:A2)’); //公式

phpexcel用法介绍

代码如下 复制代码

include ‘PHPExcel.php’;

  include ‘PHPExcel/Writer/Excel2007.php’;

  //或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的

  创建一个excel

  $objPHPExcel = new PHPExcel);

  保存excel—2007格式

  $objWriter = new PHPExcel_Writer_Excel2007$objPHPExcel);

  //或者$objWriter = new PHPExcel_Writer_Excel5$objPHPExcel); 非2007格式

  $objWriter->save”xxx.xlsx”);

  直接输出到浏览器

  $objWriter = new PHPExcel_Writer_Excel5$objPHPExcel);

  header”Pragma: public”);

  header”Expires: 0″);

  header”Cache-Control:must-revalidate, post-check=0, pre-check=0″);

  header”Content-Type:application/force-download”);

  header”Content-Type:application/vnd.ms-execl”);

  header”Content-Type:application/octet-stream”);

  header”Content-Type:application/download”);;

  header’Content-Disposition:attachment;filename=”resume.xls”‘);

  header”Content-Transfer-Encoding:binary”);

  $objWriter->save’php://output’);

  

——————————————————————————————————————–

  设置excel的属性:

  

代码如下 复制代码

创建人

  $objPHPExcel->getProperties)->setCreator”Maarten Balliauw”);

  最后修改人

  $objPHPExcel->getProperties)->setLastModifiedBy”Maarten Balliauw”);

  标题

  $objPHPExcel->getProperties)->setTitle”Office 2007 XLSX Test Document”);

  题目

  $objPHPExcel->getProperties)->setSubject”Office 2007 XLSX Test Document”);

  描述

  $objPHPExcel->getProperties)->setDescription”Test document for Office 2007 XLSX, generated using PHP classes.”);

  关键字

  $objPHPExcel->getProperties)->setKeywords”office 2007 openxml php”);

  种类

  $objPHPExcel->getProperties)->setCategory”Test result file”);

  ——————————————————————————————————————–

  设置当前的sheet

  $objPHPExcel->setActiveSheetIndex0);

  设置sheet的name

  $objPHPExcel->getActiveSheet)->setTitle’Simple’);

  设置单元格的值

  $objPHPExcel->getActiveSheet)->setCellValue’A1′, ‘String’);

  $objPHPExcel->getActiveSheet)->setCellValue’A2′, 12);

  $objPHPExcel->getActiveSheet)->setCellValue’A3′, true);

  $objPHPExcel->getActiveSheet)->setCellValue’C5′, ‘=SUMC2:C4)’);

  $objPHPExcel->getActiveSheet)->setCellValue’B8′, ‘=MINB2:C5)’);

  合并单元格

  $objPHPExcel->getActiveSheet)->mergeCells’A18:E22′);

  分离单元格

  $objPHPExcel->getActiveSheet)->unmergeCells’A28:B28′);

三、PHPEXCEL举例应用

整个代码如下值得注意的是表头用了$orderCellData记录了每个商户编号的顺序,为了在表体把对应的数据取出):

代码如下 复制代码

<<?php

require_once ‘../../../libs/PHPExcel/Classes/PHPExcel.php’;

require_once ‘../../../libs/PHPExcel/Classes/PHPExcel/Writer/Excel5.php’;

include_once ‘../../../libs/PHPExcel/Classes/PHPExcel/IOFactory.php’;

include ‘../common/config.php’;

// 创建一个处理对象实例(此对象对于2003 2007是相同的)

$objExcel = new PHPExcel);

//设置属性这段代码无关紧要,其中的内容可以替换为你需要的)

$objExcel->getProperties)->setCreator”office 2003 excel”);

$objExcel->getProperties)->setLastModifiedBy”office 2003 excel”);

$objExcel->getProperties)->setTitle”Office 2003 XLS Test Document”);

$objExcel->getProperties)->setSubject”Office 2003 XLS Test Document”);

$objExcel->getProperties)->setDescription”Test document for Office 2003 XLS, generated using PHP classes.”);

$objExcel->getProperties)->setKeywords”office 2003 openxml php”);

$objExcel->getProperties)->setCategory”Test result file”);

//开始处理数据索引从0开始)

$objExcel->setActiveSheetIndex0);

$conn = mssql_connect$config[‘mssql’][‘host’],$config[‘mssql’][‘user’],$config[‘mssql’][‘password’]);

mssql_select_db$config[‘mssql’][‘dbname’],$conn);

$tm=$_REQUEST[‘tm’];

$sql = “exec HNow05_getTTSpace ”,'”.$tm.”‘,”,1″;

$sql=mb_convert_encoding$sql,’GBK’,’UTF-8′);

$res=mssql_query$sql);

$i=0;

$k = array’站码’,’站名’,’河系’,’来报时间’,’水位’,’水势’);

$count = count$k);

$arrs = array’A’,’B’,’C’,’D’,’E’,’F’);

//添加表头

for$i=0;$i<$count;$i++){

$objExcel->getActiveSheet)->setCellValue$arrs[$i].”1″, “$k[$i]”);

}

/*——–从数据库读取数据——-*/

$i=0;

while$arr=mssql_fetch_array$res))

{

$stcd = $arr[“STCD”];

$stnm = $arr[“STNM”];

$rvnm = $arr[“RVNM”];

$tm= $arr[“TM”];

$tdz= $arr[“TDZ”];

$tdptn= $arr[“TDPTN”];

if$tdptn==’6′){

$tdptn=’平’;

}else if$tdptn==’5′){

$tdptn=’涨’;

}else if$tdptn==’4′){

$tdptn=’落’;

}

$u1=$i+2;

$stnm=iconv”GBK”,”utf-8″,$stnm);

$rvnm=iconv”GBK”,”utf-8″,$rvnm);

$tm=iconv”GBK”,”utf-8″,$tm);

/*———-写入内容————-*/

$objExcel->getActiveSheet)->setCellValue’a’.$u1, “$stcd”);

$objExcel->getActiveSheet)->setCellValue’b’.$u1, “$stnm”);

$objExcel->getActiveSheet)->setCellValue’c’.$u1, “$rvnm”);

$objExcel->getActiveSheet)->setCellValue’d’.$u1, “$tm”);

$objExcel->getActiveSheet)->setCellValue’e’.$u1, “$tdz”);

$objExcel->getActiveSheet)->setCellValue’f’.$u1, “$tdptn”);

$i++;

}

/*———-设置单元格边框和颜色————-*/

$rows = mssql_num_rows$res);

for$i=0;$i<$rows+1);$i++){

for$j=0;$j<$count;$j++){

$a = $i+1;

$objExcel->getActiveSheet)->getStyle$arrs[$j].$a)->getBorders)->getAllBorders)->setBorderStylePHPExcel_Style_Border::BORDER_THIN);

$objExcel->getActiveSheet)->getStyle$arrs[$j].$a)->getBorders)->getAllBorders)->getColor)->setARGB’FF00BBcc’);

//水平居中

$objExcel->getActiveSheet)->getStyle$arrs[$j].$a)->getAlignment)->setHorizontalPHPExcel_Style_Alignment::HORIZONTAL_CENTER);

}

}

// 高置列的宽度

$objExcel->getActiveSheet)->getColumnDimension’A’)->setWidth10);

$objExcel->getActiveSheet)->getColumnDimension’B’)->setWidth15);

$objExcel->getActiveSheet)->getColumnDimension’C’)->setWidth15);

$objExcel->getActiveSheet)->getColumnDimension’D’)->setWidth20);

$objExcel->getActiveSheet)->getColumnDimension’E’)->setWidth10);

$objExcel->getActiveSheet)->getColumnDimension’F’)->setWidth10);

// 设置页眉和页脚。如果没有不同的标题奇数/即使是使用单头假定.

$objExcel->getActiveSheet)->getHeaderFooter)->setOddHeader’&L&BPersonal cash register&RPrinted on &D’);

$objExcel->getActiveSheet)->getHeaderFooter)->setOddFooter’&L&B’ . $objExcel->getProperties)->getTitle) . ‘&RPage &P of &N’);

// 设置页方向和规模

$objExcel->getActiveSheet)->getPageSetup)->setOrientationPHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);

$objExcel->getActiveSheet)->getPageSetup)->setPaperSizePHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// 重命名表

$objExcel->getActiveSheet)->setTitle’实时潮汐情况’);

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objExcel->setActiveSheetIndex0);

// Redirect output to a client’s web browser Excel5)保存为excel2003格式

//设置Excel的名字

$excelName = ‘实时潮汐情况’.$tm.’)’;

//$excelName = ‘Excel_’.date”YmdHis”);

header’Content-Type: application/vnd.ms-excel’);

header’Cache-Control: max-age=0′);

header ‘Content-Disposition: attachment; filename=’.iconv”utf-8″, “GBK”, $excelName).’.xls’);

$objWriter = PHPExcel_IOFactory::createWriter$objExcel, ‘Excel5’);

$objWriter->save’php://output’);

exit;

?>

http://www.bkjia.com/PHPjc/630735.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/630735.htmlTechArticlePHPEXCEL是一个用来生成excel的php插件,他可以很方便的对excel数据进行操作,如:生成excel,修改excel数据等等。 一、PHPEXCEL简介 PHPEXCEL提供了一…

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注