下载文档原格式
PHPExcel扩展（支持读取和写入Excel2007/Excel5）本库继承了PHPExcel工作组的源码PHPExcel是一款开源的支持读取和写入Excel2007/Excel5的全功能PHP程序注意：本类和各款Excel桌面程序均默认使用UTF-8编码，故，本类输入和输出文本均为UTF-8编码本人为其添加和扩展了几个函数，方便大家使用使用方法和例子在SpeedPHP程序中使用spClass函数初始化类$test = spClass('icPHPExcel');//初始化本类后，本类将默认操作一个空白的Excel表$test -> create('A','1','hello');//在A列第1行，写入hello数据/*在第2行A列写入world,B列写入ok;第3行A列写入is,B列写入it*/$testdata = array('2'=>array('A'=>'world','B'=>'ok'),'3'=>array('A'=>'is','B'=>'it'));$test ->createAll($testdata);$test ->saveExcel('test.xls');//将Excel保存至根目录中test.xls是文件名，这里可以填相对路径或绝对路径--------------------------------------------------------$test = spClass('icPHPExcel');//初始化本类后，本类将默认操作一个空白的Excel表$test ->Load_Excel('test.xls');//执行此代码，程序将加载我们刚刚保存得到的test.xls 文件print_r($test -> find('A','1'));//将输出A1单元格的数据，这里应该是helloprint_r($test ->findAll());//将输出整个表的数据--------------------------------------------------------本程序使用了魔术函数，你可以自行参考PHPExcel的官方API，直接通过$test -> API(); 操作但目前PHPExcel文档尚无中文版更多的可用函数请参考PHPExcel文档或本程序后续文档下载地址/p/icase-speedphp/downloads/list。
excel vba autofilter order 用法-回复Excel VBA AutoFilter Order 用法：一步一步回答引言：Excel是一款功能强大的电子表格软件，而Visual Basic for Applications （简称VBA）是一种在Excel中编写宏的编程语言。
在Excel VBA中，AutoFilter Order是一个常用的函数，可以用于对数据进行排序和筛选。
本文将逐步介绍AutoFilter Order的用法和示例，并详细解释各个步骤。
总体来说，AutoFilter Order可以帮助用户更高效地处理和分析Excel中的数据。
第一步：理解AutoFilter Order的概念和功能在Excel中，AutoFilter Order是一个用于筛选和排序数据的函数。
它可以根据用户指定的条件对数据进行排序和筛选，并将符合条件的数据显示在一个表格中。
通过使用AutoFilter Order，用户可以快速找到所需的数据，而无需手动翻找或使用复杂的筛选功能。
第二步：创建一个新的VBA宏要使用AutoFilter Order，首先需要创建一个新的VBA宏。
在Excel中，按下Alt + F11可以打开Visual Basic for Applications编辑器。
在编辑器中，可以创建新的宏模块，并在其中编写VBA代码。
第三步：编写VBA代码在VBA宏模块中，我们可以使用AutoFilter Order函数来实现数据的筛选和排序。
以下是一个简单的示例代码，用于根据指定的条件对数据进行排序和筛选：Sub FilterAndSortData()Dim ws As WorksheetSet ws = ThisWorkbook.Worksheets("Sheet1") '将"Sheet1"修改为你的工作表名称ws.Range("A1").AutoFilter Field:=1, Criteria1:="Value1" '按列A 中的数值筛选数据ws.Range("A1").AutoFilter Field:=1, Criteria1:="Value1", Operator:=xlOr, Criteria2:="Value2" '按列A中的数值筛选数据（可同时满足Value1和Value2的条件）ws.Range("A1").AutoFilter Field:=2, Criteria1:=">10" '按列B中的数值筛选大于10的数据ws.Range("A1").AutoFilter Field:=2, Criteria1:="<=10" '按列B中的数值筛选小于或等于10的数据ws.AutoFilter.Sort.SortFields.Clear '清除之前的排序条件ws.AutoFilter.Sort.SortFields.Add Key:=ws.Range("C1"), Order:=xlAscending '按列C中的值升序排序ws.AutoFilter.Sort.SortFields.Add Key:=ws.Range("D1"), Order:=xlDescending '按列D中的值降序排序With ws.AutoFilter.Sort.Header = xlYes '保留表头.MatchCase = False '不区分大小写.Orientation = xlTopToBottom '排序的方向从上至下.SortMethod = xlPinYin '排序方法按拼音顺序.Apply '应用排序End Withws.AutoFilterMode = False '关闭筛选End Sub第四步：运行VBA宏在VBA编辑器中，可以按下F5键运行VBA宏，或者返回到Excel中，手动执行宏。
php利用PHPExcel类导出导入Excel用法PHPExcel类是php一个excel表格处理插件了，下面我来给大家介绍利用PHPExcel类来导入与导出excel表格的应用方法，有需要了解的朋友不防参考参考(PHPExcel自己百度下载这里不介绍了)。
导出Excel用法//设置环境变量（新增PHPExcel）代码如下复制代码set_include_path('.'. PATH_SEPARATOR . Yii::app()->basePath.'/lib/PHPExcel' .PATH_SEPARATOR .get_include_path());//注：在yii中，也可以直接Yii::import(“application.lib.PHPExcel.*”);//引入PHPExcel相关文件require_once "PHPExcel.php";require_once 'PHPExcel/IOFactory.php';require_once 'PHPExcel/Writer/Excel5.php';//把要导出的内容放到表格代码如下复制代码//新建$resultPHPExcel = new PHPExcel();//设置参数//设值$resultPHPExcel->getActiveSheet()->setCellValue('A1', '季度');$resultPHPExcel->getActiveSheet()->setCellValue('B1', '名称');$resultPHPExcel->getActiveSheet()->setCellValue('C1', '数量');$i = 2;foreach($data as $item){$resultPHPExcel->getActiveSheet()->setCellValue('A' . $i, $item['quarter']); $resultPHPExcel->getActiveSheet()->setCellValue('B' . $i, $item['name']); $resultPHPExcel->getActiveSheet()->setCellValue('C' . $i, $item['number']);$i ++;}设置导出参数代码如下复制代码//设置导出文件名$outputFileName = 'total.xls';$xlsWriter = new PHPExcel_Writer_Excel5($resultPHPExcel);//ob_start(); ob_flush();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-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");$xlsWriter->save( "php://output" );输出有错。
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。
phpexcel使用方法有哪些phpexcel使用方法有哪些phpexcel是用来操作OfficeExcel文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。
可以使用它来读取、写入不同格式的`电子表格。
下面店铺给大家整理了phpexcel使用方法，供大家参阅。
equire_once 'PHPExcel.php';require_once'PHPExcel/Writer/Excel5.php'; 用于其他低版本xlsorrequire_once'PHPExcel/Writer/Excel2007.php'; 用于excel-2007 格式创建一个处理对象实例$objExcel = new PHPExcel();创建文件格式写入对象实例, uncomment$objWriter = newPHPExcel_Writer_Excel5($objExcel); 用于其他版本格式or$objWriter = newPHPExcel_Writer_Excel2007($objExcel); 用于2007 格式$objProps = $objExcel->getProperties ();设置创建者$objProps->setCreator ( 'XuLulu');设置最后修改者$objProps->setLastModifiedBy("XuLulu");描述$objProps->setDescription("摩比班级");设置标题$objProps->setTitle ( '管理器' );设置题目$objProps->setSubject("OfficeXLS Test Document, Demo");设置关键字$objProps->setKeywords ( '管理器' );设置分类$objProps->setCategory ( "Test");工作表设置$objExcel->setActiveSheetIndex( 0 );$objActSheet = $objExcel->getActiveSheet ();单元格赋值例：$objActSheet->setCellValue ( 'A1', 'ID');$objActSheet->setCellValue ( 'B1', 'HashCode');$objActSheet->setCellValue ( 'C1', 'ModelName');$objActSheet->setCellValue ( 'D1', 'IndexName');$objActSheet->setCellValue('A1', '字符串内容'); 字符串内容$objActSheet->setCellValue('A2', 26); 数值$objActSheet->setCellValue('A3', true); 布尔值$objActSheet->setCellValue('A4', '=SUM(A2:A2)'); 公式自动设置单元格宽度例：$objActSheet->getColumnDimension('A')->setAutoSize(tru e);手动设置单元格的宽度例：$objActSheet->getColumnDimension('A')->setWidth(10);导出的文件名$outputFileName = iconv ( 'UTF-8', 'gb2312', 'XuLulu_'. time() . '.xlsx' );直接导出文件$objWriter->save ( $outputFileName );文件直接输出到浏览器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-excel');header ( 'Content-Type:application/octet-stream');header ( 'Content-Type:application/download');header ( 'Content-Disposition:attachment;filename='. $outputFileName );header ( 'Content-Transfer-Encoding:binary');$objWriter->save ( 'php:output');其他设置：显式指定内容类型$objActSheet->setCellValueExplicit('A5','8474758478574875 84',PHPExcel_Cell_DataType::TYPE_STRING);合并单元格$objActSheet->mergeCells('B1:C22');分离单元格$objActSheet->unmergeCells('B1:C22');得到单元格的样式$objStyleA5 = $objActSheet->getStyle('A5');设置字体$objFontA5 = $objStyleA5->getFont();$objFontA5->setName('Courier New');$objFontA5->setSize(10);$objFontA5->setBold(true);$objFontA5->setUnderline(PHPExcel_Style_Font::UNDERLIN E_SINGLE);$objFontA5->getColor()->setARGB('FF999999');设置对齐方式$objAlignA5 = $objStyleA5->getAlignment();$objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HO RIZONTAL_RIGHT);$objAlignA5->setVertical(PHPExcel_Style_Alignment::VERTI CAL_CENTER);设置边框$objBorderA5 = $objStyleA5->getBorders();$objBorderA5->getTop()->setBorderStyle(PHPExcel_Style_B order::BORDER_THIN);$objBorderA5->getTop()->getColor()->setARGB('FFFF0000') ; color$objBorderA5->getBottom()->setBorderStyle(PHPExcel_Styl e_Border::BORDER_THIN);$objBorderA5->getLeft()->setBorderStyle(PHPExcel_Style_B order::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 ed byZeal');$objDrawing->setPath('./.logo.gif');$objDrawing->setHeight(36);$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');PHPExcel在cakephp中应用：在Vendors/下创建一个文件夹Excel,将PHPExcel的目录如下：在要调用的Controller下的方法写如下代码：App::import ( 'Vendor', 'Excel', array ('file' =>'PHPExcel.php' ) );后，就可以实例化PHPExcel;具体跟以上情况一样。
PHP操作Excel–PHPExcel基本⽤法详解1.headerheader("Content-Type:application/vnd.ms-excel");header("Content-Disposition:attachment;filename=sample.xls");header("Pragma:no-cache");header("Expires:0");2.PHPExcel开发包Tests⽬录有详细使⽤实例 ⽀持中⽂,注意⽂件编码 ⽂件保存为utf-83.写excel//Include classrequire_once('Classes/PHPExcel.php');require_once('Classes/PHPExcel/Writer/Excel2007.php');$objPHPExcel = new PHPExcel();//Set properties 设置⽂件属性$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");//Add some data 添加数据$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');//可以指定位置$objPHPExcel->getActiveSheet()->setCellValue('A2', true);$objPHPExcel->getActiveSheet()->setCellValue('A3', false);$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');$objPHPExcel->getActiveSheet()->setCellValue('B3', 2);$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');//循环for($i = 1;$i<200;$i++) {$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i);$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, 'Test value');}//⽇期格式化$objPHPExcel->getActiveSheet()->setCellValue('D1', time());$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);//Add comment 添加注释$objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');$objCommentRichText->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');//Add rich-text string 添加⽂字可设置样式$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18') );$objRichText->createText('This invoice is ');$objPayable = $objRichText->createTextRun('payable within thirty days after the end of the month');$objPayable->getFont()->setBold(true);$objPayable->getFont()->setItalic(true);$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );$objRichText->createText(', unless specified otherwise on the invoice.');//Merge cells 合并分离单元格$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');//Protect cells 保护单元格$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 formats 数字格式化$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );//Set column widths 设置列宽度$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);//Set fonts 设置字体$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);//Set alignments 设置对齐$objPHPExcel->getActiveSheet()->getStyle('D11')->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); $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true);//Set column borders 设置列边框$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); //Set border colors 设置边框颜⾊$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()->getRight()->getColor()->setARGB('FF993300');//Set fills 设置填充$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');//Add a hyperlink to the sheet 添加链接$objPHPExcel->getActiveSheet()->setCellValue('E26', '');$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('');$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');$objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//Add a drawing to the worksheet 添加图⽚$objDrawing = new PHPExcel_Worksheet_Drawing();$objDrawing->setName('Logo');$objDrawing->setDescription('Logo');$objDrawing->setPath('./images/officelogo.jpg');$objDrawing->setHeight(36);$objDrawing->setCoordinates('B15');$objDrawing->setOffsetX(110);$objDrawing->setRotation(25);$objDrawing->getShadow()->setVisible(true);$objDrawing->getShadow()->setDirection(45);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());//Play around with inserting and removing rows and columns$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);$objPHPExcel->getActiveSheet()->removeRow(6, 10);$objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5);$objPHPExcel->getActiveSheet()->removeColumn('E', 5);//Add conditional formatting$objConditional1 = new PHPExcel_Style_Conditional();$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);$objConditional1->setCondition('0');$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);$objConditional1->getStyle()->getFont()->setBold(true);//Set autofilter ⾃动过滤$objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');//Hide "Phone" and "fax" column 隐藏列$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);//Set document security 设置⽂档安全$objPHPExcel->getSecurity()->setLockWindows(true);$objPHPExcel->getSecurity()->setLockStructure(true);$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");//Set sheet security 设置⼯作表安全$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled in order to enable any of the following! $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);//Calculated data 计算echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() . "\r\n";//Set outline levels$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);//Freeze panes$objPHPExcel->getActiveSheet()->freezePane('A2');//Rows to repeat at top$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);//Set data validation 验证输⼊值$objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation();$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE );$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );$objValidation->setAllowBlank(true);$objValidation->setShowInputMessage(true);$objValidation->setShowErrorMessage(true);$objValidation->setErrorTitle('Input error');$objValidation->setError('Number is not allowed!');$objValidation->setPromptTitle('Allowed input');$objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');$objValidation->setFormula1(10);$objValidation->setFormula2(20);$objPHPExcel->getActiveSheet()->getCell('B3')->setDataValidation($objValidation);//Create a new worksheet, after the default sheet 创建新的⼯作标签$objPHPExcel->createSheet();$objPHPExcel->setActiveSheetIndex(1);//Set header and footer. When no different headers for odd/even are used, odd header is assumed. 页眉页脚$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!');$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');//Set page orientation and size ⽅向⼤⼩$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);//Rename sheet 重命名⼯作表标签$objPHPExcel->getActiveSheet()->setTitle('Simple');//Set active sheet index to the first sheet, so Excel opens this as the first sheet$objPHPExcel->setActiveSheetIndex(0);//Save Excel 2007 file 保存$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);$objWriter->save(str_replace('.php', '.xlsx', __FILE__));//Save Excel 5 file 保存require_once('Classes/PHPExcel/Writer/Excel5.php');$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);$objWriter->save(str_replace('.php', '.xls', __FILE__));//1.6.2新版保存require_once('Classes/PHPExcel/IOFactory.php');$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');$objWriter->save(str_replace('.php', '.xls', __FILE__));4.读excel//Include classrequire_once('Classes/PHPExcel/Reader/Excel2007.php');$objReader = new PHPExcel_Reader_Excel2007;$objPHPExcel = $objReader->load("05featuredemo.xlsx");5.读写csvrequire_once("05featuredemo.inc.php");require_once('Classes/PHPExcel/Writer/CSV.php');require_once('Classes/PHPExcel/Reader/CSV.php');require_once('Classes/PHPExcel/Writer/Excel2007.php');//Write to CSV format 写$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);$objWriter->setDelimiter(';');$objWriter->setEnclosure('');$objWriter->setLineEnding("\r\n");$objWriter->setSheetIndex(0);$objWriter->save(str_replace('.php', '.csv', __FILE__));//Read from CSV format 读$objReader = new PHPExcel_Reader_CSV();$objReader->setDelimiter(';');$objReader->setEnclosure('');$objReader->setLineEnding("\r\n");$objReader->setSheetIndex(0);$objPHPExcelFromCSV = $objReader->load(str_replace('.php', '.csv', __FILE__));//Write to Excel2007 format$objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);$objWriter2007->save(str_replace('.php', '.xlsx', __FILE__));6.写htmlrequire_once("05featuredemo.inc.php");require_once('Classes/PHPExcel/Writer/HTML.php');//Write to HTML format$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);$objWriter->setSheetIndex(0);$objWriter->save(str_replace('.php', '.htm', __FILE__));7.写pdfrequire_once("05featuredemo.inc.php");require_once('Classes/PHPExcel/IOFactory.php');//Write to PDF format$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');$objWriter->setSheetIndex(0);$objWriter->save(str_replace('.php', '.pdf', __FILE__));//Echo memory peak usageecho date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";。
Excel VBA解读（52）：自动筛选——AutoFilter方法在面对大量数据时，我们可以使用Excel的筛选功能，滤出我们需要的信息。
在本文中，我们先从Excel中的“筛选”命令谈起。
如下图所示的工作表，将活动单元格置于任一数据单元格中，单击功能区中的“排序和筛选”中的“筛选”命令，可以看到表头单元格中出现了筛选下拉箭头。
上述操作录制的VBA代码如下：Sub Macro1()'' Macro1 Macro''Selection.AutoFilterEnd Sub接着操作。
单击内容为“语文”的下拉箭头（即表头第3列），选择“数字筛选——大于(G)…”，在“自定义自动筛选方式”对话框的“显示行”中，第一个组合框左侧选择“大于或等于”，右侧输入“80”，第二个组合框左侧选择“小于”，右侧输入“90”，即筛选语文分数大于或等于80且小于90的数据，结果如下图所示。
录制的代码如下：Sub Macro2()'' Macro2 Macro''Selection.AutoFilterActiveSheet.Range("$A$1:$F$19").AutoFilter Field:=3,Criteria1:=">=80", _Operator:=xlAnd,Criteria2:="<90"End Sub观察上面录制的代码，可以看出，Excel VBA使用AutoFilter方法来实现“筛选”功能，并提供了一系列可选的参数来进一步执行筛选操作。
AutoFilter方法的语法及说明下面是Range对象的AutoFilter方法的语法：Range对象.AutoFilter(Field,Criterial1,Operator,Criteria2,VisibleDropDown)说明：1.参数Field，指定想要基于筛选的字段的整数偏移量。
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扩展。
Author: Mark Baker Version: 1.7.7 PHPExcel AutoFilter Reference Developer Documentation1. ContentsPHPExcel AutoFilter Reference Developer Documentation (1)1.Contents (2)2.AutoFilters (3)3.Setting an AutoFilter area on a worksheet (5)4.Autofilter Expressions (6)4.1.Simple filters (7)4.1.1.Matching Blanks (8)4.2.DateGroup Filters (9)4.3.Custom filters (11)4.4.Dynamic Filters (13)4.5.Top Ten Filters (16)2. AutoFiltersEach worksheet in an Excel Workbook can contain a single autoFilter range. Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed. You can filter by more than one column: filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.When an AutoFilter is applied to a range of cells, the first row in an autofilter range will be the heading row, which displays the autoFilter dropdown icons. It is not part of the actual autoFiltered data. All subsequent rows are the autoFiltered data. So an AutoFilter range should always contain the heading row and one or more data rows (one data row is pretty meaningless), but PHPExcel won't actually stop you specifying a meaningless range: it's up to you as the developer to avoid such errors.To determine if a filter is applied, note the icon in the column heading. A drop-down arrow () means that filtering is enabled but not applied. In MS Excel, when you hover over the heading of a column with filtering enabled but not applied, a screen tip displays the cell text for the first row in that column, and the message “(Showing All)”.A Filter button ( ) means that a filter is applied. When you hover over the heading of a filtered column, a screen tip displays the filter applied to that column, such as "Equals a red cell color" or "Larger than 150".3. Setting an AutoFilter area on a worksheetTo set an autoFilter on a range of cells.The first row in an autofilter range will be the heading row, which displays the autoFilter dropdown icons. It is not part of the actual autoFiltered data. All subsequent rows are the autoFiltered data. So an AutoFilter range should always contain the heading row and one or more data rows (one data row is pretty meaningless, but PHPExcel won't actually stop you specifying a meaningless range: it's up to you as the developer to avoid such errors.If you want to set the whole worksheet as an autofilter regionThis enables filters, but does not actually apply any filters.4. Autofilter ExpressionsPHPEXcel 1.7.8 introduced the ability to actually create, read and write filter expressions; initially only for Excel2007 files, but later releases will extend this to other formats.To apply a filter expression to an autoFilter range, you first need to identify which column you're going to be applying this filter to.This returns an autoFilter column object, and you can then apply filters to that column.There are a number of different types of autofilter expressions. The most commonly used are: •Simple Filters•DateGroup Filters•Custom filters•Dynamic Filters•Top Ten FiltersThese different types are mutually exclusive within any single column. You should not mix the different types of filter in the same column. PHPExcel will not actively prevent you from doing this, but the results are unpredictable.4.1. Simple filtersIn MS Excel, Simple Filters are a dropdown list of all values used in that column, and the user can select which ones they want to display and which ones they want to hide by ticking and unticking the checkboxes alongside each option. When the filter is applied, rows containing the checked entries will be displayed, rows that don't contain those values will be hidden.To create a filter expression, we need to start by identifying the filter type. In this case, we're just going to specify that this filter is a standard filter.Now we've identified the filter type, we can create a filter rule and set the filter values:When creating a simple filter in PHPExcel, you only need to specify the values for "checked" columns: you do this by creating a filter rule for each value.This creates two filter rules: the column will be filtered by values that match “France” OR “Germany”. For Simple Filters, you can create as many rules as you wantSimple filters are always a comparison match of EQUALS, and multiple standard filters are always treated as being joined by an OR condition.4.1.1. Matching BlanksIf you want to create a filter to select blank cells, you would use:4.2. DateGroup FiltersIn MS Excel, DateGroup filters provide a series of dropdown filter selectors for date values, so you can specify entire years, or months within a year, or individual days within each month.DateGroup filters are still applied as a Standard Filter type.Creating a dateGroup filter in PHPExcel, you specify the values for "checked" columns as an associative array of year. month, day, hour minute and second. To select a year and month, you need to create a DateGroup rule identifying the selected year and month:The key values for the associative array are:•year•month•day•hour•minute•secondLike Standard filters, DateGroup filters are always a match of EQUALS, and multiple standard filters are always treated as being joined by an OR condition.Note that we alse specify a ruleType: to differentiate this from a standard filter, we explicitly set the Rule's Type to AUTOFILTER_RULETYPE_DATEGROUP. As with standard filters, we can create any number of DateGroup Filters.4.3. Custom filtersIn MS Excel, Custom filters allow us to select more complex conditions using an operator as well as a value. Typical examples might be values that fall within a range (e.g. between -20 and +20), or text values with wildcards (e.g. beginning with the letter U). To handle this, theyCustom filters are limited to 2 rules, and these can be joined using either an AND or an OR.We start by specifying a Filter type, this time a CUSTOMFILTER.And then define our rules.The following shows a simple wildcard filter to show all column entries beginning with the letter 'U'.MS Excel uses * as a wildcard to match any number of characters, and ? as a wildcard to match a single character. 'U*' equates to "begins with a 'U'"; '*U' equates to "ends with a 'U'"; and '*U*' equatesto "contains a 'U'"If you want to match explicitly against a * or a ? character, you can escape it with a tilde (~), so ?~** would explicitly match for a * character as the second character in the cell value, followed by any number of other characters. The only other character that needs escaping is the ~ itself.To create a "between" condition, we need to define two rules:We also set the rule type to CUSTOMFILTER.This defined two rules, filtering numbers that are >= -20 OR <= 20, so we also need to modify the join condition to reflect AND rather than OR.The valid set of operators for Custom Filters are defined in the PHPExcel_Worksheet_AutoFilter_Column_Rule class, and comprise:AUTOFILTER_COLUMN_RULE_EQUAL = 'equal';AUTOFILTER_COLUMN_RULE_NOTEQUAL = 'notEqual';AUTOFILTER_COLUMN_RULE_GREATERTHAN = 'greaterThan';AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL = 'greaterThanOrEqual';AUTOFILTER_COLUMN_RULE_LESSTHAN = 'lessThan';AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL = 'lessThanOrEqual';4.4. Dynamic FiltersDynamic Filters are based on a dynamic comparison condition, where the value we're comparing against the cell values is variable, such as 'today'; or when we're testing against an aggregate of the cell data (e.g. 'aboveAverage'). Only a single dynamic filter can be applied to a column at a time.Again, we start by specifying a Filter type, this time a DYNAMICFILTER.When defining the rule for a dynamic filter, we don't define a value (we can simply set that to NULL) but we do specify the dynamic filter category.We also set the rule type to DYNAMICFILTER.The valid set of dynamic filter categories is defined in the PHPExcel_Worksheet_AutoFilter_Column_Rule class, and comprises:AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY = 'yesterday';AUTOFILTER_RULETYPE_DYNAMIC_TODAY = 'today';AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW = 'tomorrow';AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE = 'yearToDate';AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR = 'thisYear';AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER = 'thisQuarter';AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH = 'thisMonth';AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK = 'thisWeek';AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR = 'lastYear';AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER = 'lastQuarter';AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH = 'lastMonth';AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK = 'lastWeek';AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR = 'nextYear';AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER = 'nextQuarter';AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH = 'nextMonth';AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK = 'nextWeek';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_1 = 'M1';AUTOFILTER_RULETYPE_DYNAMIC_JANUARY = 'M1';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_2 = 'M2';AUTOFILTER_RULETYPE_DYNAMIC_FEBRUARY = 'M2';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_3 = 'M3';AUTOFILTER_RULETYPE_DYNAMIC_MARCH = 'M3';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_4 = 'M4';AUTOFILTER_RULETYPE_DYNAMIC_APRIL = 'M4';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_5 = 'M5';AUTOFILTER_RULETYPE_DYNAMIC_MAY = 'M5';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_6 = 'M6';AUTOFILTER_RULETYPE_DYNAMIC_JUNE = 'M6';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_7 = 'M7';AUTOFILTER_RULETYPE_DYNAMIC_JULY = 'M7';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_8 = 'M8';AUTOFILTER_RULETYPE_DYNAMIC_AUGUST = 'M8';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_9 = 'M9';AUTOFILTER_RULETYPE_DYNAMIC_SEPTEMBER = 'M9';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_10 = 'M10';AUTOFILTER_RULETYPE_DYNAMIC_OCTOBER = 'M10';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_11 = 'M11';AUTOFILTER_RULETYPE_DYNAMIC_NOVEMBER = 'M11';AUTOFILTER_RULETYPE_DYNAMIC_MONTH_12 = 'M12';AUTOFILTER_RULETYPE_DYNAMIC_DECEMBER = 'M12';AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_1 = 'Q1';AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_2 = 'Q2';AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_3 = 'Q3';AUTOFILTER_RULETYPE_DYNAMIC_QUARTER_4 = 'Q4';AUTOFILTER_RULETYPE_DYNAMIC_ABOVEAVERAGE = 'aboveAverage';AUTOFILTER_RULETYPE_DYNAMIC_BELOWAVERAGE = 'belowAverage' We can only apply a single Dynamic Filter rule to a column at a time.4.5. Top Ten FiltersTop Ten Filters are similar to Dynamic Filters in that they are based on a summarisation of the actual data values in the cells. However, unlike Dynamic Filters where you can only select a single option, Top Ten Filters allow you to select based on a number of criteria:•You can identify whether you want the top (highest) or bottom (lowest) values.•You can identify how many values you wish to select in the filter•You can identify whether this should be a percentage or a number of items.Like Dynamic Filters, only a single Top Ten filter can be applied to a column at a time.We start by specifying a Filter type, this time a DYNAMICFILTER.Then we create the rule:This will filter the Top 5 percent of values in the column.To specify the lowest (bottom 2 values), we would specify a rule of:The option values for TopTen Filters top/bottom value/percent are all defined in the PHPExcel_Worksheet_AutoFilter_Column_Rule class, and comprise:AUTOFILTER_COLUMN_RULE_TOPTEN_BY_VALUE = 'byValue';AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT = 'byPercent';andAUTOFILTER_COLUMN_RULE_TOPTEN_TOP = 'top';AUTOFILTER_COLUMN_RULE_TOPTEN_BOTTOM = 'bottom';。
