PHPExcel Function Reference developer documentation

合集下载

php利用phpexcel插件实现数据的导入和导出(支持csvxlsxlsx格式和超过26个字段列)解析

.title h4{ border-bottom:2px solid #01AFD4; padding-bottom:8px;}
.title a{margin-top:-50ey是从1开始
$res = $this->excelToArray($savePath.$file_name,end($file_types));
//echo 12321321;exit;
//如果有表头，则过滤掉第一行
if($table_head)
unset($res[1]);
<link rel="apple-touch-icon-precomposed" href="" />
<meta name="-site-verification" content="5fNm7bQabR" />
<meta http-equiv="X-UA-Compatible" content="IE=8" />
}
if($tableid=="1"){
$rawdata_obj = $this->rawdata_pctmodel;
}elseif($tableid=="2"){
$rawdata_obj = $this->rawdata_applymodel;
}elseif($tableid=="3"){
$rawdata_obj = $this->rawdata_authmodel;
}
//该字段比较特殊，必须导入表中都有该字段
$data['month_number'] = $month_number;

PHP中创建和编辑Excel表格的方法

PHP中创建和编辑Excel表格的⽅法要使⽤纯PHP创建或编辑Excel电⼦表格，我们将使⽤PHPExcel库，它可以读写许多电⼦表格格式，包括xls，xlsx，ods和csv。

在我们继续之前，仔细检查您的服务器上是否有PHP 5.2或更⾼版本以及安装了以下PHP扩展：php_zip，php_xml和php_gd2。

创建电⼦表格创建电⼦表格是PHP应⽤程序中最常见的⽤例之⼀，⽤于将数据导出到Excel电⼦表格。

查看以下代码，了解如何使⽤PHPExcel创建⽰例Excel电⼦表格：// Include PHPExcel library and create its objectrequire('PHPExcel.php');$phpExcel = new PHPExcel;// Set default font to Arial$phpExcel->getDefaultStyle()->getFont()->setName('Arial');// Set default font size to 12$phpExcel->getDefaultStyle()->getFont()->setSize(12);// Set spreadsheet properties – title, creator and description$phpExcel ->getProperties()->setTitle("Product list");$phpExcel ->getProperties()->setCreator("Voja Janjic");$phpExcel ->getProperties()->setDescription("PHP Excel spreadsheet testing.");// Create the PHPExcel spreadsheet writer object// We will create xlsx file (Excel 2007 and above)$writer = PHPExcel_IOFactory::createWriter($phpExcel, "Excel2007");// When creating the writer object, the first sheet is also created// We will get the already created sheet$sheet = $phpExcel ->getActiveSheet();// Set sheet title$sheet->setTitle('My product list');// Create spreadsheet header$sheet ->getCell('A1')->setValue('Product');$sheet ->getCell('B1')->setValue('Quanity');$sheet ->getCell('C1')->setValue('Price');// Make the header text bold and larger$sheet->getStyle('A1:D1')->getFont()->setBold(true)->setSize(14);// Insert product data// Autosize the columns$sheet->getColumnDimension('A')->setAutoSize(true);$sheet->getColumnDimension('B')->setAutoSize(true);$sheet->getColumnDimension('C')->setAutoSize(true);// Save the spreadsheet$writer->save('products.xlsx');如果要下载电⼦表格⽽不是将其保存到服务器，请执⾏以下操作：header('Content-Type: application/vnd.ms-excel');header('Content-Disposition: attachment;filename="file.xlsx"');header('Cache-Control: max-age=0');$writer->save('php://output');编辑现有电⼦表格在PHP中编辑电⼦表格与创建电⼦表格类似：// Include PHPExcel library and create its objectrequire('PHPExcel.php');// Load an existing spreadsheet$phpExcel = PHPExcel_IOFactory::load('products.xlsx');// Get the first sheet$sheet = $phpExcel ->getActiveSheet();// Remove 2 rows starting from the row 2$sheet ->removeRow(2,2);// Insert one new row before row 2$sheet->insertNewRowBefore(2, 1);// Create the PHPExcel spreadsheet writer object// We will create xlsx file (Excel 2007 and above)$writer = PHPExcel_IOFactory::createWriter($phpExcel, "Excel2007");// Save the spreadsheet$writer->save('products.xlsx');准备电⼦表格进⾏打印要准备电⼦表格进⾏打印，我们将设置纸张⽅向，尺⼨和边距：$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);$sheet -> getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);$sheet->getPageMargins()->setTop(1);$sheet ->getPageMargins()->setRight(0.75);$sheet ->getPageMargins()->setLeft(0.75);$sheet ->getPageMargins()->setBottom(1);将PHPExcel与Laravel⼀起使⽤PHPExcel库也可以在Laravel框架中使⽤。

PHP输出ExcelPHPExcel的方法

PHP输出ExcelPHPExcel的⽅法本⽂实例为⼤家分享了PHP输出Excel PHPExcel的具体代码，供⼤家参考，具体内容如下⽅法1：/*** 创建(导出)Excel数据表格* @param array $list 要导出的数组格式的数据* @param string $filename 导出的Excel表格数据表的⽂件名* @param array $header Excel表格的表头* @param array $index $list数组中与Excel表格表头$header中每个项⽬对应的字段的名字(key值)* ⽐如: $header = array('编号','姓名','性别','年龄');* $index = array('id','username','sex','age');* $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24));* @return [array] [数组]*/function createtable($list,$filename,$header=array(),$index = array()){header("Content-type:application/vnd.ms-excel");header("Content-Disposition:filename=".$filename.".xls");$teble_header = implode("\t",$header);$strexport = $teble_header."\r";foreach ($list as $row){foreach($index as $val){$strexport.=$row[$val]."\t";}$strexport.="\r";}$strexport=iconv('UTF-8',"GB2312//IGNORE",$strexport);exit($strexport);}此⽅法代码量少，可以放在thinkPHP5中的公共函数common.php中，⽅便调⽤，但是对输出的Excel表格⽆法设置属性⽅法2：⾸先需要到下载SDK，，下载后解压，我们只需要⾥边的Classes⽂件夹，将其改名为PHPExcel。

Php函数完整参考手册

Php函数完整参考⼿册序号分类描述1Array 函数　2Calendar 函数⽇历扩展包含了简化不同⽇历格式间的转换的函数。

3Date/Time 函数Date/Time 函数⽤于从 PHP 脚本运⾏的服务器上获取⽇期和时间并进⾏格式化。

4Directory 函数Directory 函数⽤于获得关于⽬录及其内容的信息。

5Error/Logging函数Error/Logging 函数⽤于对错误进⾏处理和记录。

6Filesystem 函数Filesystem 函数⽤于访问和操作⽂件系统。

7Filter 函数PHP 过滤器⽤于对来⾃⾮安全来源的数据（⽐如⽤户输⼊）进⾏验证和过滤。

8FTP 函数FTP 函数通过⽂件传输协议 (FTP) 提供对⽂件服务器的客户端访问。

9HTTP 函数HTTP 函数⽤于对由 Web 服务器发送到浏览器的信息进⾏操作。

10Libxml 函数Libxml 函数和常量与 SimpleXML、XSLT 以及 DOM 函数⼀起使⽤。

11Mail 函数mail() 函数⽤于从脚本中直接发送电⼦邮件。

12Math 函数Math 函数能处理 integer 和 float 范围内的值。

13杂项函数我们把不属于其他类别的函数归纳到杂项函数类别。

14MySQLi 函数MySQLi 函数⽤于访问 MySQL 数据库服务器。

15SimpleXML 函数SimpleXML 扩展提供了⼀种获取 XML 元素的名称和⽂本的简单⽅式。

16String 函数　17XML Parser 函数XML Parser 函数允许您创建 XML 解析器，并为 XML 事件定义句柄。

18Zip File 函数Zip File 函数允许您读取压缩⽂件。

PhpExcel中文帮助手册

PhpExcel中文帮助手册|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->setActiveSheetIndex(0);设置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', '=SUM(C2:C4)');$objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');合并单元格$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');分离单元格$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');保护cell$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');设置格式// Set cell number formatsecho date('H:i:s') . " Set cell number formats\n";$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );设置宽width// Set column widths$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);设置font$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara'); $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20); $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);设置align$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);//垂直居中$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);设置column的border$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);设置border的color$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');设置填充颜色$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');加图片$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Logo');$objDrawing->setDescription('Logo');$objDrawing->setPath('./images/officelogo.jpg');$objDrawing->setHeight(36);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Paid');$objDrawing->setDescription('Paid');$objDrawing->setPath('./images/paid.png');$objDrawing->setCoordinates('B15');$objDrawing->setOffsetX(110);$objDrawing->setRotation(25);$objDrawing->getShadow()->setVisible(true);$objDrawing->getShadow()->setDirection(45);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());//处理中文输出问题需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理： $str = iconv('gb2312', 'utf-8', $str);或者你可以写一个函数专门处理中文字符串：function convertUTF8($str){if(empty($str)) return '';return iconv('gb2312', 'utf-8', $str);}//从数据库输出数据处理方式从数据库读取数据如：$db = new Mysql($dbconfig);$sql = "SELECT * FROM 表名";$row = $db->GetAll($sql); // $row 为二维数组$count = count($row);for ($i = 2; $i <= $count+1; $i++) {$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertUTF8($row[$i-2][2]));$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i-2][3]));$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertUTF8($row[$i-2][6]));$objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i-2][7]));$objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8]));}在默认sheet后，创建一个worksheetecho date('H:i:s') . " Create new Worksheet object\n";$objPHPExcel->createSheet();$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');$objWriter-save('php://output');。

在ThinkPHP中调用PHPExcel的问题解决方案

在ThinkPHP中调用PHPExcel的问题解决方案在ThinkPHP中调用PHPExcel时，数据可以完全读出来，但是下一步D,M或调用模板的时候会出错。

(不知道是我一个人遇到这个问题吗？)经过研究，终于找到了解决方法。

和大家分享一下。

呵呵！1，首先下载PHPExcel的包，放在ThinkPHP/Vendor/(也就是Think的第三方类库目录)下。

2，调用函数。

protected function Import_Execl($file){if(!file_exists($file)){return array("error"=>1);}Vendor("PHPExcel.PHPExcel");$PHPExcel = new PHPExcel();$PHPReader = new PHPExcel_Reader_Excel2007();if(!$PHPReader->canRead($file)){$PHPReader = new PHPExcel_Reader_Excel5();if(!$PHPReader->canRead($file)){return array("error"=>2);}}$PHPExcel = $PHPReader->load($file);$SheetCount = $PHPExcel->getSheetCount();for($i=0;$i<$SheetCount;$i++){$currentSheet = $PHPExcel->getSheet($i);$allColumn = $this->ExcelChange($currentSheet->getHighestColumn());$allRow = $currentSheet->getHighestRow();$array[$i]["Title"] = $currentSheet->getTitle();$array[$i]["Cols"] = $allColumn;$array[$i]["Rows"] = $allRow;$arr = array();for($currentRow = 1 ;$currentRow<=$allRow;$currentRow++){$row = array();for($currentColumn=0;$currentColumn<$allColumn;$currentColumn++){$row[$currentColumn] = $currentSheet->getCellByColumnAndRow($currentColumn,$currentRow)->getValue();}$arr[$currentRow] = $row;}$array[$i]["Content"] = $arr;}spl_autoload_register(array('Think','autoload'));//必须的，不然ThinkPHP和PHPExcel会冲突unset($currentSheet);unset($PHPReader);unset($PHPExcel);unlink($file);return array("error"=>0,"data"=>$array);}protected function ExcelChange($str){//配合Execl批量导入的函数$len = strlen($str)-1;$num = 0;for($i=$len;$i>=0;$i--){$num += (ord($str[$i]) - 64)*pow(26,$len-$i);}return $num;}复制代码3，调用。

PHPExcel开发手册

PHPExcel Developer Documentation
Authorrten Balliauw 1.7.4 26 August 2010
1. Contents
PHPExcel Developer Documentation ............................................................................. 1 1. Contents ......................................................................................................... 2 2. Prerequisites.................................................................................................... 4
2.1. Software requirements ................................................................................. 4 2.2. Installation instructions ................................................................................ 4 2.3. Getting started .......................................................................................... 4 2.4. Useful links and tools ................................................................................... 4

tp5使用PHPexcel扩展导出excel表

tp5使⽤PHPexcel扩展导出excel表1，使⽤composer安装phpexcel包:composer require phpoffice/phpexcel2，在控制器中创建⽅法：（1）使⽤PHPexcel扩展。

代码如下/*** 导出excel表格（默认格式）** @param array $columName 第⼀⾏的列名称* @param array $list ⼆维数组* @param string $setTitle sheet名称* @return* @author Tggui <tggui@>*/private function exportExcel1($columName, $list, $fileName='demo', $setTitle='Sheet1'){vendor('phpoffice.phpexcel.Classes.PHPexcel');vendor('phpoffice.phpexcel.Classes.PHPexcel.IOFactory');if ( empty($columName) || empty($list) ) {return '列名或者内容不能为空';}if ( count($list[0]) != count($columName) ) {return '列名跟数据的列不⼀致';}$fileName = iconv("utf-8", "gb2312", $fileName);//实例化PHPExcel类$PHPExcel = new \PHPExcel();//获得当前sheet对象$PHPSheet = $PHPExcel -> getActiveSheet();//定义sheet名称$PHPSheet -> setTitle($setTitle);//excel的列这么多够⽤了吧？不够⾃个加 AA AB AC ……$letter = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];//把列名写⼊第1⾏ A1 B1 C1 ...for ($i=0; $i < count($list[0]); $i++) {//$letter[$i]1 = A1 B1 C1 $letter[$i] = 列1 列2 列3$PHPSheet->setCellValue("$letter[$i]1","$columName[$i]");}//内容第2⾏开始foreach ($list as$key => $val) {//array_values 把⼀维数组的键转为0 1 2 3 ..foreach (array_values($val) as$key2 => $val2) {//$letter[$key2].($key+2) = A2 B2 C2 ……$PHPSheet->setCellValue($letter[$key2].($key+2),$val2);}}//⽣成2007版本的xlsx$PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename='.$fileName.'.xlsx');header('Cache-Control: max-age=0');$PHPWriter->save("php://output");exit;}2），⾃定义函数，不使⽤PHPexcel扩展。

php读取excel内容

php读取excel内容PHP读取Excel内容。

在Web开发中，经常会遇到需要读取Excel文件并处理其中数据的情况。

而PHP作为一种广泛应用于Web开发的编程语言，具有强大的文件处理能力，因此也可以很方便地实现对Excel文件的读取操作。

本文将介绍如何使用PHP读取Excel文件中的内容，并对读取到的数据进行处理。

首先，我们需要使用PHPExcel这个PHP库来实现对Excel文件的读取操作。

PHPExcel是一个功能强大的PHP类库，可以用来读取、写入和操作Excel文件。

我们可以通过Composer来安装PHPExcel，或者直接下载PHPExcel的源代码并引入到项目中。

接下来，我们需要创建一个PHP文件，并在其中引入PHPExcel 库。

然后，我们可以使用PHPExcel提供的类和方法来读取Excel文件中的内容。

首先，我们需要实例化一个PHPExcel对象，并加载要读取的Excel文件：```php。

require 'PHPExcel/Classes/PHPExcel.php';$excelFile = 'example.xlsx';$excelReader =PHPExcel_IOFactory::createReaderForFile($excelFile);$excelObj = $excelReader->load($excelFile);```。

在上面的代码中，我们首先引入了PHPExcel库，并指定了要读取的Excel文件example.xlsx。

然后，我们使用PHPExcel_IOFactory类的createReaderForFile方法来创建一个Excel文件读取器，并使用load方法加载Excel文件，得到一个PHPExcel对象$excelObj。

接下来，我们可以通过PHPExcel对象来获取Excel文件中的数据。

例如，我们可以获取Excel文件中的第一个工作表，并遍历其中的每一行数据：```php。

【php】phpExcel使用教程，如何导出excel表格

【php】phpExcel使⽤教程，如何导出excel表格【1】下载phpExcel类⽂件可在官⽅去下载我们只需要classes中的⽂件，把Classes⽂件复制到项⽬中只需要2个⽂件就可以了⼀个就是phpExcel（刚才我们复制过来的⽂件 Classse改成的phpExcel），再⾃⼰创建⼀个⽂件index.php,代码内容如下【1】最基本⽤法--直接可以保存到当前⽂件夹下1 <?php2$dir=dirname('__FILE__'); //找到当前脚本所在路径3require$dir."/phpExcel/PHPExcel.php"; //引⼊⽂件4$objPHPExcel=new PHPExcel(); //实例化PHPExcel类，等同于在桌⾯上创建⼀个ecxel表格5$objSheet=$objPHPExcel->getActiveSheet();//获取当前活动sheet的操作对象6$objSheet->setTitle('dome'); //给当前的活动sheet设置名称7 //填充数据8$objSheet->setCellValue("A1",'姓名')->setCellValue("B1",'年龄'); //给当前活动sheet填充数据9$objSheet->setCellValue("A2",'程镜')->setCellValue("B2",'25');10$objWrite=PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");//按照指定格式⽣成excel⽂件11$objWrite->save($dir."/demo.xlsx");//保存到当前⽂件夹下12131415 ?>【2】稍微升级了⼀下使⽤⽅法--可以直接再浏览器中下载保存1 <?php2$dir=dirname('__FILE__'); //找到当前脚本所在路径3require$dir."/phpExcel/PHPExcel.php"; //引⼊⽂件4$objPHPExcel=new PHPExcel(); //实例化PHPExcel类，等同于在桌⾯上创建⼀个ecxel表格5 //$objPHPExcel->createSheet(); //创建新的内置表执⾏⼀次创建⼀个新的⼀页6 //$objPHPExcel->setActiveSheetIndex(1);//把新创建的的sheet设定微当前活动sheet7$objSheet=$objPHPExcel->getActiveSheet();//获取当前活动sheet的操作对象8$objSheet->setTitle('dome2'); //给当前的活动sheet设置名称910$arr=array(1112array(),13array('姓名','年龄','职业','⼯资'),14array('程镜','24','程序猿','50000'),15array('⼤神','27','⾼级程序猿','20K'),1617 );18$objSheet->fromArray($arr);//直接加载数据块来实现填充数据1920$objWrite=PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");//按照指定格式⽣成excel⽂件21 //$objWrite->save($dir."/demo_3.xlsx");//保存到当前⽂件夹下2223 browser_export("Excel2007",'excel.xlsx'); //不保存在当前⽂件夹下，直接输出⾄浏览器24$objWrite->save('php://output'); //保存2526function browser_export($type,$filename){ //声明⼀个⽅法判断保存保存格式27if($type=='Excel5'){28header('Content-Type: application/vnd.ms-excel');29 }else{30header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');31 }32header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器输出的⽂件名称33header('Cache-Control: max-age=0');//禁⽌缓存34 }353637 ?>。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Author: Mark Baker Version: 1.8.0 Date: 28 April 2016 PHPExcel Formula Function ReferenceDeveloper Documentation1. ContentsPHPExcel Formula Function Reference Developer Documentation (1)1.Contents (2)2.Frequently asked questions (9)3.Function Reference (10)3.1.Function that are not Supported in Excel5 (10)3.2.Date and Time Values (10)3.2.1.Excel functions that return a Date and Time value (10)3.2.2.Excel functions that accept Date and Time values as parameters (12)3.2.3.Helper Methods (12)3.3.Cube Functions (14)3.3.1.CUBEKPIMEMBER (14)3.3.2.CUBEMEMBER (14)3.3.3.CUBEMEMBERPROPERTY (14)3.3.4.CUBERANKEDMEMBER (14)3.3.5.CUBESET (14)3.3.6.CUBESETCOUNT (14)3.3.7.CUBEVALUE (14)3.4.Database Functions (15)3.4.1.DAVERAGE (15)3.4.2.DCOUNT (16)3.4.3.DCOUNTA (16)3.4.4.DGET (17)3.4.5.DMAX (18)3.4.6.DMIN (19)3.4.7.DPRODUCT (20)3.4.8.DSTDEV (21)3.4.9.DSTDEVP (22)3.4.10.DSUM (23)3.4.11.DVAR (24)3.4.12.DVARP (24)3.5.Date and Time Functions (25)3.5.1.DATE (25)3.5.2.DATEDIF (26)3.5.3.DATEVALUE (28)3.5.4.DAY (29)3.5.5.DAYS360 (29)3.5.6.EDATE (31)3.5.7.EOMONTH (32)3.5.8.HOUR (33)3.5.9.MINUTE (33)3.5.10.MONTH (34)WORKDAYS (35)3.5.12.NOW (36)3.5.13.SECOND (37)3.5.14.TIME (37)3.5.15.TIMEVALUE (37)3.5.16.TODAY (38)3.5.17.WEEKDAY (38)3.5.18.WEEKNUM (39)3.5.19.WORKDAY (39)3.5.20.YEAR (39)3.5.21.YEARFRAC (39)3.6.Engineering Functions (40)3.6.1.BESSELI (40)3.6.2.BESSELJ (41)3.6.3.BESSELK (41)3.6.4.BESSELY (42)3.6.7.BIN2OCT (43)PLEX (43)3.6.9.CONVERT (43)3.6.10.DEC2BIN (43)3.6.11.DEC2HEX (43)3.6.12.DEC2OCT (43)3.6.13.DELTA (43)3.6.14.ERF (43)3.6.15.ERFC (43)3.6.16.GESTEP (43)3.6.17.HEX2BIN (43)3.6.18.HEX2DEC (43)3.6.19.HEX2OCT (43)3.6.20.IMABS (44)3.6.21.IMAGINARY (44)3.6.22.IMARGUMENT (44)3.6.23.IMCONJUGATE (44)3.6.24.IMCOS (44)3.6.25.IMDIV (44)3.6.26.IMEXP (44)3.6.27.IMLN (44)3.6.28.IMLOG10 (44)3.6.29.IMLOG2 (44)3.6.30.IMPOWER (44)3.6.31.IMPRODUCT (44)3.6.32.IMREAL (44)3.6.33.IMSIN (44)3.6.34.IMSQRT (44)3.6.35.IMSUB (44)3.6.36.IMSUM (44)3.6.37.OCT2BIN (45)3.6.38.OCT2DEC (45)3.6.39.OCT2HEX (45)3.7.Financial Functions (46)3.7.1.ACCRINT (46)3.7.2.ACCRINTM (46)3.7.3.AMORDEGRC (46)3.7.4.AMORLINC (46)3.7.5.COUPDAYBS (46)3.7.6.COUPDAYSNC (46)3.7.7.COUPNCD (46)3.7.8.COUPNUM (46)3.7.9.COUPPCD (46)3.7.10.CUMIPMT (46)3.7.11.CUMPRINC (46)3.7.12.DB (46)3.7.13.DDB (47)3.7.14.DISC (47)3.7.15.DOLLARDE (47)3.7.16.DOLLARFR (47)3.7.17.DURATION (48)3.7.18.EFFECT (48)3.7.19.FV (48)3.7.20.FVSCHEDULE (48)3.7.21.INTRATE (48)3.7.22.IPMT (48)3.7.23.IRR (48)3.7.24.MDURATION (48)3.7.27.NPER (48)3.7.28.NPV (48)3.7.29.ODDFPRICE (48)3.7.30.ODDFYIELD (48)3.7.31.ODDLPRICE (48)3.7.32.ODDLYIELD (48)3.7.33.ORICEDISC (48)3.7.34.PMT (49)3.7.35.PPMT (49)3.7.36.PRICE (49)3.7.37.PRICEMAT (49)3.7.38.PV (49)3.7.39.RATE (49)3.7.40.RECEIVED (49)3.7.41.SLN (49)3.7.42.SYD (49)3.7.43.TBILLEQ (49)3.7.44.TBILLPRICE (49)3.7.45.TBILLYIELD (49)DOLLAR (49)3.7.47.VDB (49)3.7.48.XIRR (49)3.7.49.XNPV (49)3.7.50.YIELD (49)3.7.51.YIELDDISC (50)3.7.52.YIELDMAT (50)rmation Functions (51)3.8.1.CELL (51)3.8.2.ERROR.TYPE (51) (51)3.8.4.ISBLANK (51)3.8.5.ISERR (51)3.8.6.ISERROR (51)3.8.7.ISEVEN (51)3.8.8.ISLOGICAL (51)3.8.9.ISNA (51)3.8.10.ISNONTEXT (51)3.8.11.ISNUMBER (51)3.8.12.ISODD (51)3.8.13.ISPMT (51)3.8.14.ISREF (51)3.8.15.ISTEXT (51)3.8.16.N (52)3.8.17.NA (52)3.8.18.TYPE (52)3.8.19.VERSION (52)3.9.Logical Functions (53)3.9.1.AND (53)3.9.2.FALSE (53)3.9.3.IF (53)3.9.4.IFERROR (53)3.9.5.NOT (53)3.9.6.OR (53)3.9.7.TRUE (53)3.10.Lookup and Reference Functions (54)3.10.1.ADDRESS (54)3.10.2.AREAS (54)3.10.3.CHOOSE (54)3.10.6.GETPIVOTDATA (54)3.10.7.HLOOKUP (54)3.10.8.HYPERLINK (54)3.10.9.INDEX (54)3.10.10.INDIRECT (54)3.10.11.LOOKUP (54)3.10.12.MATCH (54)3.10.13.OFFSET (54)3.10.14.ROW (54)3.10.15.ROWS (54)3.10.16.RTD (55)3.10.17.TRANSPOSE (55)3.10.18.VLOOKUP (55)3.11.Mathematical and Trigonometric Functions (56)3.11.1.ABS (56)3.11.2.ACOS (56)3.11.3.ACOSH (57)3.11.4.ASIN (58)3.11.5.ASINH (59)3.11.6.ATAN (59)3.11.7.ATAN2 (59)3.11.8.ATANH (59)3.11.9.CEILING (59)BIN (59)3.11.11.COS (60)3.11.12.COSH (60)3.11.13.DEGREES (60)3.11.14.EVEN (60)3.11.15.EXP (60)3.11.16.FACT (60)3.11.17.FACTDOUBLE (60)3.11.18.FLOOR (60)3.11.19.GCD (60)3.11.20.INT (60)3.11.21.LCM (60)3.11.22.LN (60)3.11.23.LOG (60)3.11.24.LOG10 (60)3.11.25.MDETERM (60)3.11.26.MINVERSE (60)3.11.27.MMULT (60)3.11.28.MOD (61)3.11.29.MROUND (61)3.11.30.MULTINOMIAL (61)3.11.31.ODD (61)3.11.32.PI (61)3.11.33.POWER (61)3.11.34.PRODUCT (61)3.11.35.QUOTIENT (61)3.11.36.RADIANS (61)3.11.37.RAND (61)3.11.38.RANDBETWEEN (61)3.11.39.ROMAN (61)3.11.40.ROUND (61)3.11.41.ROUNDDOWN (61)3.11.42.ROUNDUP (61)3.11.43.SERIESSUM (61)3.11.44.SIGN (61)3.11.47.SQRT (62)3.11.48.SQRTPI (62)3.11.49.SUBTOTAL (62)3.11.50.SUM (62)3.11.51.SUMIF (62)3.11.52.SUMIFS (62)3.11.53.SUMPRODUCT (62)3.11.54.SUMSQ (62)3.11.55.SUMX2MY2 (62)3.11.56.SUMX2PY2 (62)3.11.57.SUMXMY2 (62)3.11.58.TAN (62)3.11.59.TANH (62)3.11.60.TRUNC (62)3.12.Statistical Functions (63)3.12.1.AVEDEV (63)3.12.2.AVERAGE (63)3.12.3.AVERAGEA (64)3.12.4.AVERAGEIF (65)3.12.5.AVERAGEIFS (65)3.12.6.BETADIST (65)3.12.7.BETAINV (66)3.12.8.BINOMDIST (66)3.12.9.CHIDIST (66)3.12.10.CHIINV (67)3.12.11.CHITEST (67)3.12.12.CONFIDENCE (67)3.12.13.CORREL (67)3.12.14.COUNT (67)3.12.15.COUNTA (67)3.12.16.COUNTBLANK (67)3.12.17.COUNTIF (67)3.12.18.COUNTIFS (68)3.12.19.COVAR (68)3.12.20.CRITBINOM (68)3.12.21.DEVSQ (68)3.12.22.EXPONDIST (68)3.12.23.FDIST (68)3.12.24.FINV (68)3.12.25.FISHER (68)3.12.26.FISHERINV (68)3.12.27.FORECAST (68)3.12.28.FREQUENCY (68)3.12.29.FTEST (68)3.12.30.GAMMADIST (68)3.12.31.GAMMAINV (68)3.12.32.GAMMALN (68)3.12.33.GEOMEAN (68)3.12.34.GROWTH (68)3.12.35.HARMEAN (69)3.12.36.HYPGEOMDIST (69)3.12.37.INTERCEPT (69)3.12.38.KURT (69)RGE (69)3.12.40.LINEST (69)3.12.41.LOGEST (69)3.12.42.LOGINV (69)3.12.43.LOGNORMDIST (69)3.12.46.MEDIAN (69)3.12.47.MIN (69)3.12.48.MINA (69)3.12.49.MODE (69)3.12.50.NEGBINOMDIST (69)3.12.51.NORMDIST (69)3.12.52.NORMINV (70)3.12.53.NORMSDIST (70)3.12.54.NORMSINV (70)3.12.55.PEARSON (70)3.12.56.PERCENTILE (70)3.12.57.PERCENTRANK (70)3.12.58.PERMUT (70)3.12.59.POISSON (70)3.12.60.PROB (70)3.12.61.QUARTILE (70)3.12.62.RANK (70)3.12.63.RSQ (70)3.12.64.SKEW (70)3.12.65.SLOPE (70)3.12.66.SMALL (70)3.12.67.STANDARDIZE (70)3.12.68.STDEV (70)3.12.69.STDEVA (71)3.12.70.STDEVP (71)3.12.71.STDEVPA (71)3.12.72.STEYX (71)3.12.73.TDIST (71)3.12.74.TINV (71)3.12.75.TREND (71)3.12.76.TRIMMEAN (71)3.12.77.TTEST (71)3.12.78.VAR (71)3.12.79.VARA (71)3.12.80.VARP (71)3.12.81.VARPA (71)3.12.82.WEIBULL (71)3.12.83.ZTEST (71)3.13.Text and Data Functions (72)3.13.1.ASC (72)3.13.2.BAHTTEXT (72)3.13.3.CHAR (72)3.13.4.CLEAN (72)3.13.5.CODE (72)3.13.6.CONCATENATE (72)3.13.7.DOLLAR (72)3.13.8.EXACT (72)3.13.9.FIND (72)3.13.10.FINDB (72)3.13.11.FIXED (72)3.13.12.JIS (72)3.13.13.LEFT (72)3.13.14.LEFTB (72)3.13.15.LEN (72)3.13.16.LENB (73)3.13.17.LOWER (73)3.13.18.MID (73)3.13.19.MIDB (73)3.13.22.REPLACE (73)3.13.23.REPLACEB (73)3.13.24.REPT (73)3.13.25.RIGHT (73)3.13.26.RIGHTB (73)3.13.27.SEARCH (73)3.13.28.SEARCHB (73)3.13.29.SUBSTITUTE (73)3.13.30.T (73)3.13.31.TEXT (73)3.13.32.TRIM (73)3.13.33.UPPER (74)3.13.34.VALUE (74)4.Credits (75)2. Frequently asked questionsThe up-to-date F.A.Q. page for PHPExcel can be found on/PHPExcel/Wiki/View.aspx?title=FAQ&referringTitle=Requirements. Formula s don’t seem to be calculated in Excel2003 using compatibility pack?This is normal behaviour of the compatibility pack, Excel2007 displays this correctly. Use PHPExcel_Writer_Excel5 if you really need calculated values, or force recalculation in Excel2003.3. Function Reference3.1. Function that are not Supported in Excel5Not all functions are supported by the Excel 5 Writer. Use of these functions within your workbooks will result in an error when trying to write to Excel5.The following is the list of those functions that are implemented within PHPExcel, but that cannot currently be written to Excel 5.Date and TimeEDATE Not a standard function within Excel 5, but an add-in from the Analysis ToolPak. EOMONTH Not a standard function within Excel 5, but an add-in from the Analysis ToolPak. 3.2. Date and Time Values3.2.1. Excel functions that return a Date and Time valueAny of the Date and Time functions that return a date value in Excel can return either an Excel timestamp or a PHP timestamp or date object.It is possible for scripts to change the data type used for returning date values by calling the PHPExcel_Calculation_Functions::setReturnDateType() method:PHPExcel_Calculation_Functions::setReturnDateType($returnDateType);where the following constants can be used for $returnDateTypePHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERICPHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECTPHPExcel_Calculation_Functions::RETURNDATE_EXCELThe method will return a Boolean True on success, False on failure (e.g. if an invalid value is passed in for the return date type).The PHPExcel_Calculation_Functions::getReturnDateType() method can be used to determine the current value of this setting:$returnDateType = PHPExcel_Calculation_Functions::getReturnDateType();The default is RETURNDATE_PHP_NUMERIC.PHP TimestampsIf RETURNDATE_PHP_NUMERIC is set for the Return Date Type, then any date value returned to the calling script by any access to the Date and Time functions in Excel will be an integer value that represents the number of seconds from the PHP base date. The PHP base date (0) is 00:00 GMT on 1st January 1970. This value can be positive or negative: so a value of -3600 would be 23:00 hrs on 31st December 1969; while a value of +3600 would be 01:00 hrs on 1st January 1970. This gives PHP a date range of between 14th December 1901 and 19th January 2038.PHP date/Time ObjectsIf the Return Date Type is set for RETURNDATE_PHP_NUMERIC, then any date value returned to the calling script by any access to the Date and Time functions in Excel will be a PHP date/time object1. Excel TimestampsIf RETURNDATE_EXCEL is set for the Return Date Type, then the returned date value by any access to the Date and Time functions in Excel will be a floating point value that represents a number of days from the Excel base date. The Excel base date is determined by which calendar Excel uses: the Windows 1900 or the Mac 1904 calendar. 1st January 1900 is the base date for the Windows 1900 calendar while 1st January 1904 is the base date for the Mac 1904 calendar.It is possible for scripts to change the calendar used for calculating Excel date values by calling the PHPExcel_Shared_Date::setExcelCalendar() method:PHPExcel_Shared_Date::setExcelCalendar($baseDate);where the following constants can be used for $baseDatePHPExcel_Shared_Date::CALENDAR_WINDOWS_1900PHPExcel_Shared_Date::CALENDAR_MAC_1904The method will return a Boolean True on success, False on failure (e.g. if an invalid value is passed in).The PHPExcel_Shared_Date::getExcelCalendar() method can be used to determine the current value of this setting:$baseDate = PHPExcel_Shared_Date::getExcelCalendar();The default is CALENDAR_WINDOWS_19002.Functions that return a Date/Time ValueDATEDATEVALUEEDATEEOMONTHNOWTIMETIMEVALUETODAY1 See /manual/en/ref.datetime.php for details of PHP date/time objects.2 When reading from an Excel file generated using the Windows 1900 or the Mac 1904 calendar, PHPExcel will set this flag automatically to the correct value for that workbook. However, the setting is applied globally. Note that when you are reading multiple workbooks, some of which use Windows 1900 and others using Mac 1904, then the calendar setting that is applied will be that of the latest file to be read. This may lead to errors in calculations.When writing an Excel file, the calendar in that file will be set to the current value of the calendar flag in PHPExcel_Shared_Date.3.2.2. Excel functions that accept Date and Time values as parametersDate values passed in as parameters to a function can be an Excel timestamp or a PHP timestamp; or date object; or a string containing a date value (e.g. ‘1-Jan-2009’). PHPExcel will attempt to identify their type based on the PHP datatype:∙An integer numeric value will be treated as a PHP date timestamp∙ A real (floating point) numeric value will be treated as an Excel date timestamp.∙Any PHP date object will be treated as a date object.∙Any string value (even one containing straight numeric data) will be converted to a date/time object for validation as a date value based on the server locale settings, so passing through an ambiguous value of ‘07/08/2008’ wi ll be treated as 7th August 2008 if your server settings are UK, but as 8th July 2008 if your server settings are US. However, if you pass through a value such as ‘31/12/2008’ that would be considered an error by a US-based server, but which is not ambiguous, then PHPExcel will attempt to correct this to 31st December 2008.If the content of the string doesn’t match any of the formats recognised by the php date/time object implementation of strtotime() (which can handle a wider range of formats than the nor mal strtotime() function), then the function will return a ‘#VALUE’ error. However, Excel recommends that you should always use date timestamps for your date functions, and the recommendation for PHPExcel is the same: avoid strings because the result is not predictable. The same principle applies when data is being written to Excel. Cells containing date actual values (rather than Excel functions that return a date value) are always written as Excel dates, converting where necessary. If a cell formatted as a date contains an integer or date/time object value, then it is converted to an Excel value for writing: if a cell formatted as a date contains a real value, then no conversion is required. Note that string values are written as strings rather than converted to Excel date timestamp values.Functions that expect a Date/Time ValueDATEDIFDAYDAYS360EDATEEOMONTHHOURMINUTEMONTHNETWORKDAYSSECONDWEEKDAYWEEKNUMWORKDAYYEARYEARFRAC3.2.3. Helper MethodsIn addition to the setExcelCalendar() and getExcelCalendar() methods, a number of other methods are available in the PHPExcel_Shared_Date class that can help when working with dates:PHPExcel_Shared_Date::ExcelToPHP($excelDate)Converts a date/time from an Excel date timestamp to return a PHP serialized date/timestamp. Note that this method does not trap for Excel dates that fall outside of the valid range for a PHP date timestamp.PHPExcel_Shared_Date::ExcelToPHPObject($excelDate)Converts a date from an Excel date/timestamp to return a PHP DateTime object.PHPExcel_Shared_Date::PHPToExcel($PHPDate)Converts a PHP serialized date/timestamp or a PHP DateTime object to return an Excel date timestamp.PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)Takes year, month and day values (and optional hour, minute and second values) and returns an Excel date timestamp value.3.3. Cube Functions3.3.1. CUBEKPIMEMBERNot yet implemented.3.3.2. CUBEMEMBERNot yet implemented.3.3.3. CUBEMEMBERPROPERTY Not yet implemented.3.3.4. CUBERANKEDMEMBER Not yet implemented.3.3.5. CUBESETNot yet implemented.3.3.6. CUBESETCOUNTNot yet implemented.3.3.7. CUBEVALUENot yet implemented.3.4. Database Functions3.4.1. DAVERAGEThe DAVERAGE function returns the average value of the cells in a column of a list or database that match conditions you specify.SyntaxDAVERAGE (database, field, criteria)Parametersdatabase The range of cells that makes up the list or database.A database is a list of related data in which rows of related information arerecords, and columns of data are fields. The first row of the list containslabels for each column.field Indicates which column of the database is used in the function.Enter the column label as a string (enclosed between double quotationmarks), such as "Age" or "Yield," or as a number (without quotation marks)that represents the position of the column within the list: 1 for the firstcolumn, 2 for the second column, and so on.criteria The range of cells that contains the conditions you specify.You can use any range for the criteria argument, as long as it includes atleast one column label and at least one cell below the column label in whichyou specify a condition for the column.Return Valuefloat The average value of the matching cells.This is the statistical mean.Examples$database = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit' ),array( 'Apple', 18, 20, 14, 105.00 ),array( 'Pear', 12, 12, 10, 96.00 ),array( 'Cherry', 13, 14, 9, 105.00 ),array( 'Apple', 14, 15, 10, 75.00 ),array( 'Pear', 9, 8, 8, 76.80 ),array( 'Apple', 8, 9, 6, 45.00 ),);$criteria = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit', 'Height' ),array( '="=Apple"', '>10', NULL, NULL, NULL, '<16' ),array( '="=Pear"', NULL, NULL, NULL, NULL, NULL ),);$worksheet->fromArray( $criteria, NULL, 'A1' );$worksheet->fromArray( $database, NULL, 'A4' );$worksheet->setCellValue('A12', '=DAVERAGE(A4:E10,"Yield",A1:B2)');$retVal = $worksheet->getCell('A12')->getCalculatedValue();// $retVal = 12NotesThere are no additional notes on this function3.4.2. DCOUNTThe DCOUNT function returns the count of cells that contain a number in a column of a list or database matching conditions that you specify.SyntaxDCOUNT(database, [field], criteria)Parametersdatabase The range of cells that makes up the list or database.A database is a list of related data in which rows of related information arerecords, and columns of data are fields. The first row of the list containslabels for each column.field Indicates which column of the database is used in the function.Enter the column label as a string (enclosed between double quotationmarks), such as "Age" or "Yield," or as a number (without quotation marks)that represents the position of the column within the list: 1 for the firstcolumn, 2 for the second column, and so on.criteria The range of cells that contains the conditions you specify.You can use any range for the criteria argument, as long as it includes atleast one column label and at least one cell below the column label in whichyou specify a condition for the column.Return Valuefloat The count of the matching cells.Examples$database = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit' ),array( 'Apple', 18, 20, 14, 105.00 ),array( 'Pear', 12, 12, 10, 96.00 ),array( 'Cherry', 13, 14, 9, 105.00 ),array( 'Apple', 14, 15, 10, 75.00 ),array( 'Pear', 9, 8, 8, 76.80 ),array( 'Apple', 8, 9, 6, 45.00 ),);$criteria = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit', 'Height' ),array( '="=Apple"', '>10', NULL, NULL, NULL, '<16' ),array( '="=Pear"', NULL, NULL, NULL, NULL, NULL ),);$worksheet->fromArray( $criteria, NULL, 'A1' );$worksheet->fromArray( $database, NULL, 'A4' );$worksheet->setCellValue('A12', '=DCOUNT(A4:E10,"Height",A1:B3)');$retVal = $worksheet->getCell('A12')->getCalculatedValue();// $retVal = 3NotesIn MS Excel, The field argument is optional. If field is omitted, DCOUNT counts all records in the database that match the criteria. This logic has not yet been implemented in PHPExcel.3.4.3. DCOUNTAThe DCOUNT function returns the count of cells that aren’t blank in a column of a list or database and that match conditions that you specify.SyntaxDCOUNTA(database, [field], criteria)Parametersdatabase The range of cells that makes up the list or database.A database is a list of related data in which rows of related information arerecords, and columns of data are fields. The first row of the list containslabels for each column.field Indicates which column of the database is used in the function.Enter the column label as a string (enclosed between double quotationmarks), such as "Age" or "Yield," or as a number (without quotation marks)that represents the position of the column within the list: 1 for the firstcolumn, 2 for the second column, and so on.criteria The range of cells that contains the conditions you specify.You can use any range for the criteria argument, as long as it includes atleast one column label and at least one cell below the column label in whichyou specify a condition for the column.Return Valuefloat The count of the matching cells.Examples$database = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit' ),array( 'Apple', 18, 20, 14, 105.00 ),array( 'Pear', 12, 12, 10, 96.00 ),array( 'Cherry', 13, 14, 9, 105.00 ),array( 'Apple', 14, 15, 10, 75.00 ),array( 'Pear', 9, 8, 8, 76.80 ),array( 'Apple', 8, 9, 6, 45.00 ),);$criteria = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit', 'Height' ),array( '="=Apple"', '>10', NULL, NULL, NULL, '<16' ),array( '="=Pear"', NULL, NULL, NULL, NULL, NULL ),);$worksheet->fromArray( $criteria, NULL, 'A1' );$worksheet->fromArray( $database, NULL, 'A4' );$worksheet->setCellValue('A12', '=DCOUNTA(A4:E10,"Yield",A1:A3)');$retVal = $worksheet->getCell('A12')->getCalculatedValue();// $retVal = 5NotesIn MS Excel, The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria. This logic has not yet been implemented in PHPExcel.3.4.4. DGETThe DGET function extracts a single value from a column of a list or database that matches conditions that you specify.SyntaxDGET(database, field, criteria)Parametersdatabase The range of cells that makes up the list or database.A database is a list of related data in which rows of related information arerecords, and columns of data are fields. The first row of the list containslabels for each column.field Indicates which column of the database is used in the function.Enter the column label as a string (enclosed between double quotationmarks), such as "Age" or "Yield," or as a number (without quotation marks)that represents the position of the column within the list: 1 for the firstcolumn, 2 for the second column, and so on.criteria The range of cells that contains the conditions you specify.You can use any range for the criteria argument, as long as it includes atleast one column label and at least one cell below the column label in whichyou specify a condition for the column.Return Valuemixed The value from the selected column of the matching row.Examples$database = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit' ),array( 'Apple', 18, 20, 14, 105.00 ),array( 'Pear', 12, 12, 10, 96.00 ),array( 'Cherry', 13, 14, 9, 105.00 ),array( 'Apple', 14, 15, 10, 75.00 ),array( 'Pear', 9, 8, 8, 76.80 ),array( 'Apple', 8, 9, 6, 45.00 ),);$criteria = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit', 'Height' ),array( '="=Apple"', '>10', NULL, NULL, NULL, '<16' ),array( '="=Pear"', NULL, NULL, NULL, NULL, NULL ),);$worksheet->fromArray( $criteria, NULL, 'A1' );$worksheet->fromArray( $database, NULL, 'A4' );$worksheet->setCellValue('A12', '=GET(A4:E10,"Age",A1:F2)');$retVal = $worksheet->getCell('A12')->getCalculatedValue();// $retVal = 14NotesThere are no additional notes on this function3.4.5. DMAXThe DMAX function returns the largest number in a column of a list or database that matches conditions you specify.SyntaxDMAX(database, field, criteria)Parametersdatabase The range of cells that makes up the list or database.A database is a list of related data in which rows of related information arerecords, and columns of data are fields. The first row of the list containslabels for each column.field Indicates which column of the database is used in the function.Enter the column label as a string (enclosed between double quotationmarks), such as "Age" or "Yield," or as a number (without quotation marks)that represents the position of the column within the list: 1 for the firstcolumn, 2 for the second column, and so on.criteria The range of cells that contains the conditions you specify.You can use any range for the criteria argument, as long as it includes atleast one column label and at least one cell below the column label in whichyou specify a condition for the column.Return Valuefloat The maximum value of the matching cells.Examples$database = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit' ),array( 'Apple', 18, 20, 14, 105.00 ),array( 'Pear', 12, 12, 10, 96.00 ),array( 'Cherry', 13, 14, 9, 105.00 ),array( 'Apple', 14, 15, 10, 75.00 ),array( 'Pear', 9, 8, 8, 76.80 ),array( 'Apple', 8, 9, 6, 45.00 ),);$criteria = array( array( 'Tree', 'Height', 'Age', 'Yield', 'Profit', 'Height' ),array( '="=Apple"', '>10', NULL, NULL, NULL, '<16' ),array( '="=Pear"', NULL, NULL, NULL, NULL, NULL ),);$worksheet->fromArray( $criteria, NULL, 'A1' );$worksheet->fromArray( $database, NULL, 'A4' );$worksheet->setCellValue('A12', '=DMAX(A4:E10,"Profit",A1:B2)');$retVal = $worksheet->getCell('A12')->getCalculatedValue();// $retVal = 105NotesThere are no additional notes on this function3.4.6. DMINThe DMIN function returns the smallest number in a column of a list or database that matches conditions you specify.SyntaxDMIN(database, field, criteria)Parametersdatabase The range of cells that makes up the list or database.A database is a list of related data in which rows of related information arerecords, and columns of data are fields. The first row of the list containslabels for each column.field Indicates which column of the database is used in the function.Enter the column label as a string (enclosed between double quotationmarks), such as "Age" or "Yield," or as a number (without quotation marks)that represents the position of the column within the list: 1 for the firstcolumn, 2 for the second column, and so on.。