学步园

IExcel.php
<?php<br /> /**<br /> * @desc excel接口<br /> * @author mengdejun<br /> */<br /> interface IExcel<br /> {<br /> //导入excel<br /> public function import($fileName,$convert_callback_function=null);<br /> //导出excel<br /> public function export($fileName="excel");<br /> //添加行<br /> public function addRow(array $array,$sheet="sheet1");<br /> //添加表头<br /> public function addHead(array $array,$sheet="sheet1");<br /> //添加工作簿<br /> public function addSheet($sheet);<br /> //释放资源<br /> public function release();<br /> }<br /> ?>
HtmlExcel.php
<?php<br /> include_once ('IExcel.php');<br /> /**<br /> * @desc php生成excel类函数<br /> * @author mengdejun<br /> * @date 20100806<br /> * @version 1.1.2<br /> */<br /> class HtmlExcel implements IExcel<br /> {<br /> private $_line=array();<br /> private $isHeader=false;<br /> private $convert=false;<br /> public function __construct(){}<br /> /**<br /> * @desc 添加表头信息,注:表头仅可添加一次,若无表头则将内容行的第一行作为表头<br /> * @param array $array<br /> * @param unknown_type $sheet<br /> */<br /> public function addHead(array $array, $sheet = "sheet1")<br /> {<br /> if(!$this->isHeader)<br /> $this->_line[]=$this->getLine($array);<br /> $this->isHeader=true;<br /> }<br /> /**<br /> * @desc 添加自定义字符转换或过滤函数<br /> * @param unknown_type $functionName 回调函数名<br /> */<br /> public function addConvert($functionName="convert")<br /> {<br /> $this->convert=$functionName;<br /> }<br /> protected function getLine(array $array,$sheet='sheet1')<br /> {<br /> $_temp="";<br /> $_count=sizeof($array);<br /> $index=0;<br /> foreach($array as $value):<br /> $_temp.=$this->convert($value);<br /> if($index==$_count-1):<br /> $_temp.="/t/n";<br /> else:<br /> $_temp.="/t";<br /> endif;<br /> $index++;<br /> endforeach;<br /> return $_temp;<br /> }<br /> /**<br /> * @desc 添加excel行,若没有设置表头则将该数组的第一个元素作为表头<br /> * @param array $array<br /> * @param unknown_type $sheet<br /> */<br /> public function addRow(array $array, $sheet = "sheet1")<br /> {<br /> $this->_line[]=$this->getLine($array,$sheet);<br /> return $this->_line;<br /> }<br /> /**<br /> * @desc 嵌套添加excel行,若没有设置表头则将该数组的第一个元素作为表头<br /> * @param array $array<br /> * @param unknown_type $sheet<br /> */<br /> public function addRows(array $array,$sheet = "sheet1")<br /> {<br /> foreach($array as $value):<br /> if(is_array($value)):<br /> $this->addRow($value);<br /> else:<br /> $this->addRow($array);<br /> endif;<br /> endforeach;<br /> }<br /> /**<br /> * @desc 添加工作簿,暂不支持V1.0<br /> * @deprecated<br /> * @param array $array<br /> */<br /> public function addSheet($sheet) {return;}<br /> /**<br /> * @desc 返回excel表行数<br /> */<br /> public function getRows()<br /> {<br /> return sizeof($this->_line);<br /> }<br /> /**<br /> * @desc 返回excel内容<br /> */<br /> public function getBody()<br /> {<br /> return $this->_line;<br /> }<br /> /**<br /> * @desc 导出excel文件<br /> * @param unknown_type $fileName 导出文件名<br /> */<br /> public function export($fileName = "excel")<br /> {<br /> header("Content-Type: application/vnd.ms-excel;");<br /> header("Content-Disposition:filename={$fileName}.xls");<br /> for($index=0;$index<sizeof($this->_line);$index++):<br /> echo $this->_line[$index];<br /> endfor;<br /> }<br /> /**<br /> * @desc 用户自定义编码转化以及数据筛选函数<br /> * @param unknown_type $str<br /> */<br /> protected function convert($str)<br /> {<br /> if(function_exists($this->convert)):<br /> return call_user_func($this->convert,$str);<br /> else:<br /> return $str;<br /> endif;<br /> }<br /> /**<br /> * @desc 设置表格内容<br /> * @param unknown_type $array<br /> */<br /> public function setBody($array)<br /> {<br /> $this->_line=$array;<br /> }<br /> /**<br /> * @desc 释放资源<br /> */<br /> public function release()<br /> {<br /> unset($this->_line);<br /> }<br /> public function import($fileName,$convert_callback_function=null){}<br /> }<br /> ?>

XmlExcel.php
<?php<br /> include'IExcel.php';<br /> /**<br /> * @desc php生成excel类函数 支持导入 导出 多工作薄(数据分卷技术)<br /> * @filesource XmlExcel.php<br /> * @author mengdejun<br /> * @date 20100801<br /> * @version 1.8.1<br /> */<br /> if(!defined("CHARSET")):define("CHARSET","UTF-8");endif;<br /> if(!defined("VERSION")):define("VERSION","12.00");endif;<br /> if(!defined("THIS_VERSION")):define("THIS_VERSION","1.8.1");endif;<br /> if(!defined("NULL")):define("NULL",null);endif;<br /> class XmlExcel implements IExcel<br /> {<br /> private $header = "<?xml version=/"1.0/" encoding=/"%s/"?>/n<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/" xmlns:x=/"urn:schemas-microsoft-com:office:excel/" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/" xmlns:html=/"http://www.w3.org/TR/REC-html40/">";<br /> private $documentInfo="<DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/"><Author>{author}</Author><Created>{time}</Created><Company>{company}</Company><Version>{version}</Version></DocumentProperties>";<br /> private $footer = "</Workbook>";<br /> private $align_left="<Style ss:ID=/"s62/"><Alignment ss:Horizontal=/"Left/" ss:Vertical=/"Center/"/></Style>";<br /> private $align_center="<Style ss:ID=/"s63/"><Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/"/></Style>";<br /> private $align_right="<Style ss:ID=/"s64/"><Alignment ss:Horizontal=/"Right/" ss:Vertical=/"Center/"/></Style>";<br /> private $align_bold="<Style ss:ID=/"s65/"><Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Center/"/><Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#000000/" ss:Bold=/"1/"/></Style>";<br /> private $align_default="<Style ss:ID=/"Default/" ss:Name=/"Normal/"><Alignment ss:Horizontal=/"%s/" ss:Vertical=/"Center/"/><Borders/><Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"11/" ss:Color=/"#000000/"/><Interior/><NumberFormat/><Protection/></Style>";<br /> private $charset=CHARSET;<br /> private $convert="convert";<br /> private static $pre_workBook=NULL;<br /> private $_line=NULL;<br /> private $_column=NULL;<br /> private $_columnType=NULL;<br /> private $_styles=NULL;<br /> private $_style=NULL;<br /> private $_title=NULL;<br /> private $_align="Left";<br /> private $defaultHeight=13.5;<br /> private $defaultWidth=54;<br /> private $_sheets=NULL;<br /> private $_heads=NULL;<br /> /**<br /> * @desc 构造方法 PHP5.X<br /> * @param string $charset 字符编码<br /> */<br /> public function __construct($charset = 'UTF-8')<br /> {<br /> $this->charset=$charset;<br /> }<br /> /**<br /> * @desc 构造方法 PHP4.X<br /> * @param string $charset 字符编码<br /> */<br /> public function XmlExcel($charset = 'UTF-8')<br /> {<br /> $this->charset=$charset;<br /> }<br /> /**<br /> * @desc 析构方法<br /> */<br /> public function __destruct(){}<br /> /**<br /> * @desc 释放可用资源<br /> * @return null<br /> */<br /> public function release()<br /> {<br /> unset($this->_line,$this->_column,$this->_heads,$this->_sheets,$this->_styles,$this->_style,$this->_title,self::$pre_workBook);<br /> }<br /> /**<br /> * @desc 数组行转换函数<br /> * @param array $array<br /> */<br /> protected function getLine(array $array)<br /> {<br /> $_temp="<Row ss:AutoFitHeight=/"0/">";<br /> foreach($array as $key=>$val):<br /> #读取指定数据类型,默认String<br /> $_type=!empty($this->_columnType)&&isset($this->_columnType)?!empty($this->_columnType[$key])&&isset($this->_columnType)?$this->_columnType[$key]:"String":"String";<br /> $_temp.="<Cell><Data ss:Type=/"{$_type}/">{$this->convert($val)}</Data></Cell>";<br /> endforeach;<br /> $_temp.="</Row>";<br /> return $_temp;<br /> }<br /> /**<br /> * @desc 添加表格头,默认的第一个数组将作为表头<br /> * @param array $array<br /> * @param string $sheet 工作表名<br /> * @exception $array 不能为空<br /> */<br /> public function addHead(array $array, $sheet = "sheet1")<br /> {<br /> $this->_line[$sheet][0]=$this->getLine($array);<br /> $this->_title[$sheet]['width']=count($array)-1;<br /> $this->_sheets[]=$sheet;<br /> $this->_heads[$sheet][0]=$array;<br /> }<br /> /**<br /> * @desc 添加行<br /> * @param array $array<br /> * @param string $sheet<br /> */<br /> public function addRow(array $array, $sheet = "sheet1",$isErrorReport=true)<br /> {<br /> if($isErrorReport):<br /> if(empty($array)||!isset($array)||count($array)==0):<br /> exit("data can't null'");<br /> else:<br /> $this->_line[$sheet][]=$this->getLine($array);<br /> endif;<br /> else:<br /> $this->_line[$sheet][]=$this->getLine($array);<br /> endif;<br /> }<br /> /**<br /> * @desc 设置工作簿的表头对象<br /> * @param $head 表头数据<br /> * @param $sheet 工作簿名称<br /> */<br /> public function setSheetHead(array $head,$sheet="Sheet1")<br /> {<br /> $this->_line[$sheet][]=$this->getLine($head);<br /> }<br /> /**<br /> * @desc 添加多行 支持嵌套数组<br /> * @param array $array<br /> * @param unknown_type $sheet<br /> */<br /> public function addRows(array $array,$sheet = "Sheet1")<br /> {<br /> foreach($array as $value):<br /> if(is_array($value)):<br /> $this->addRow($value,$sheet);<br /> else:<br /> $this->addRow($array,$sheet);<br /> endif;<br /> endforeach;<br /> }<br /> /**<br /> * @desc 获取制定工作薄的列宽度<br /> * @param @sheet 工作薄名称<br /> */<br /> public function getColumnLength($sheet="Sheet1")<br /> {<br /> return $this->_title[$sheet]['width'];<br /> }<br /> /**<br /> * @desc 添加工作薄<br /> * @param unknown_type unknown_type $sheet<br /> */<br /> public function addSheet($sheet,$array=array())<br /> {<br /> $this->_line[$sheet][]=$array;<br /> }<br /> /**<br /> * @desc 工作薄添加标题<br /> * @param string $str 标题<br /> * @param string $sheet 工作薄名<br /> */<br /> public function addTitle($str,$sheet="Sheet1")<br /> {<br /> $str=$this->convert($str);<br /> $this->_title[$sheet]['title']="<Row ss:AutoFitHeight=/"0/" ss:StyleID=/"s65/"><Cell ss:MergeAcross=/"{num}/"><Data ss:Type=/"String/">{$str}</Data></Cell></Row>";<br /> }<br /> /**<br /> * @desc excel导出<br /> * @param string $fileName 导出的文件名<br /> */<br /> public function export($fileName = "excel",$isConvert=false)<br /> {<br /> if($isConvert):<br /> $fileName=$this->getConvertString($fileName);<br /> endif;<br /> header("Content-Type: application/vnd.ms-excel; charset=" . $this->charset);<br /> header("Content-Disposition:attachment; filename=/"{$fileName}.xls/"");<br /> echo stripslashes(sprintf($this->header, $this->charset));<br /> echo str_replace("{company}","Wuhan Haicai Technology Company Limited",str_replace("{time}",date("Y-m-dH:i:s",time()),str_replace("{author}","mengdejun",str_replace("{version}",VERSION,$this->documentInfo))));<br /> echo "<Styles>";<br /> echo stripslashes(sprintf($this->align_default, $this->_align));<br /> echo $this->align_left;<br /> echo $this->align_right;<br /> echo $this->align_center;<br /> echo $this->align_bold;<br /> echo "</Styles>";<br /> $_hasData=count($this->_line)==0?false:true;<br /> if($_hasData):<br /> #有数据,解析数组对象到excel表格<br /> foreach($this->_line as $key=>$value):<br /> echo "<Worksheet ss:Name=/"{$this->convert($key)}/"><Table ss:DefaultColumnWidth=/"{$this->defaultWidth}/" ss:DefaultRowHeight=/"{$this->defaultHeight}/">";<br /> #列样式和宽度<br /> if(isset($this->_column[$key]['style_width'])):<br /> foreach($this->_column[$key]['style_width'] as $s_key=>$s_value):<br /> echo "<Column ss:Index=/"{$s_key}/" ss:AutoFitWidth=/"1/" ss:Width=/"$s_value/"/>";<br /> endforeach;<br /> endif;<br /> #表格标题<br /> if(!empty($this->_title[$key]['title'])):<br /> echo str_replace("{num}",$this->_title[$key]['width'],$this->_title[$key]['title']);<br /> endif;<br /> #单元格<br /> foreach($value as $_v):<br /> echo $_v;<br /> endforeach;<br /> echo "</Table></Worksheet>";<br /> endforeach;<br /> #加载标准工作薄(默认三个工作簿)<br /> $length=count($this->_line);<br /> while($length<3):<br /> $length++;<br /> echo "<Worksheet ss:Name=/"Sheet{$length}/"><Table></Table></Worksheet>";<br /> endwhile;<br /> else:<br /> #无数据,添加默认工作簿和数据支持(错误处理:文件读取失败)<br /> for($index=1;$index<=3;$index++):<br /> echo "<Worksheet ss:Name=/"Sheet{$index}/"><Table></Table></Worksheet>";<br /> endfor;<br /> endif;<br /> echo $this->footer;<br /> }<br /> /**<br /> * @desc excel导入函数,注该函数的文件名必须是非中文<br /> * @param unknown_type $fileName 导入的文件<br /> * @param unknown_type $convert_callback_function 回调函数 支持编码转换,需返回转换后的字符串<br /> * @return 三维数组,分别对应 工作薄/行/单元格<br /> */<br /> public function import($fileName,$convert_callback_function=null)<br /> {<br /> $xls=simplexml_load_file($fileName);<br /> $is_convert=!empty($convert_callback_function)&&function_exists($convert_callback_function);<br /> $index=0;<br /> $_ra=array();<br /> foreach($xls->Worksheet as $worksheet):#循环工作薄<br /> $index_i=1;<br /> foreach($worksheet->Table->Row as $cells):#循环行<br /> if($index_i!==1):<br /> foreach($cells as $cell):#循环单元格<br /> $_ra[$index][$index_i][]=$is_convert?call_user_func($convert_callback_function,$cell->Data):$cell->Data;<br /> endforeach;<br /> endif;<br /> $index_i++;<br /> endforeach;<br /> $index++;<br /> endforeach;<br /> return $_ra;<br /> }<br /> /**<br /> * @desc 设置字符编码<br /> * @param string $charset 设置导出文件的编码<br /> */<br /> public function setCharset($charset="GBK")<br /> {<br /> $this->charset = $charset;<br /> }<br /> /**<br /> * 设置工作薄的列的宽度 array(1=>10,2=>23,3=>23,4=>213,5=>asd) 重复设置该值 将覆盖前一次操作的结果<br /> * @param string $sheet 工作薄名<br /> * @param array $array 列数组<br /> */<br /> public function setColumnWidth($sheet="sheet1",$array)<br /> {<br /> if(!empty($this->_column[$sheet]['style_width'])&&isset($this->_column[$sheet]['style_width'])):<br /> unset($this->_column[$sheet]['style_width']);<br /> endif;<br /> $this->_column[$sheet]['style_width']=$array;<br /> }<br /> /**<br /> * @desc 设置所有工作薄的列宽度<br /> * @param array $array 列宽度<br /> */<br /> public function setAllColumnWidth(array $array)<br /> {<br /> $_temp=$this->getAllSheetNames();<br /> foreach($_temp as $value):<br /> $this->setColumnWidth($value,$array);<br /> endforeach;<br /> }<br /> /**<br /> * @desc 设置默认行高<br /> * @param integer $height<br /> */<br /> public function setDefaultRowHeight($height="54")<br /> {<br /> $this->defaultHeight=$height;<br /> }<br /> /**<br /> * 设置字符编码转换函数(回调函数)<br /> * @param string $convert 设置转换函数 默认名称为convert<br /> */<br /> public function addConvert($convert="convert")<br /> {<br /> $this->convert = $convert;<br /> }<br /> /**<br /> * @desc 内部回调函数，完成字符编码的转化<br /> * @param unknown_type $str<br /> */<br /> protected function convert($str)<br /> {<br /> if(function_exists($this->convert)):<br /> return call_user_func($this->convert,$str);<br /> else:<br /> return $str;<br /> endif;<br /> }<br /> /**<br /> * 获取工作薄个数<br /> * @param int $sheet 获取工作薄的个数<br /> * @return integer<br /> */<br /> public function getSheets()<br /> {<br /> return sizeof($this->_line);<br /> }<br /> /**<br /> * 获取工作薄表格行数<br /> * @param String $sheet 工作薄名<br /> * @return integer<br /> */<br /> public function getRows($sheet)<br /> {<br /> return sizeof($this->_line[$sheet]);<br /> }<br /> /**<br /> * @desc 获取指定工作薄的表头信息<br /> * @param string $sheet 工作薄名称<br /> */<br /> public function getHead($sheet)<br /> {<br /> return $this->_heads[$sheet][0];<br /> }<br /> /**<br /> * @desc 设置默认行高度<br /> * @param integer $defaultHeight 行的默认高度 无默认值<br /> */<br /> public function setDefaultHeight($defaultHeight) {<br /> $this->defaultHeight = $defaultHeight;<br /> }<br /> /**<br /> * @desc 设置默认的列宽度<br /> * @param integer $defaultWidth 列的默认宽度 无默认值<br /> */<br /> public function setDefaultWidth($defaultWidth) {<br /> $this->defaultWidth = $defaultWidth;<br /> }<br /> /**<br /> * @desc 当前工作薄可用行数<br /> */<br /> public function currentSheetsLength()<br /> {<br /> return sizeof($this->_line)+1;<br /> }<br /> /**<br /> * @desc 设置默认的居中方式<br /> * @param string $_align 可选值 Left(left),Center(center),Right(right)<br /> */<br /> public function setDefaultAlign($_align)<br /> {<br /> $this->_align = ucfirst($_align);<br /> }<br /> /**<br /> * @desc 自动创建工作薄,支持自动分卷技术,该方法与addHead冲突,使用该方法时请勿调用addHead,否则将添加一个空白的工作薄<br /> * @param array $head 表头<br /> * @param array $data 数据<br /> * @param int $pageSize 页面行数 默认60000,excel最大支持65536<br /> * @param string $defaultName 工作薄名,工作簿不能重名<br /> */<br /> public function addPageRow(array $head,array $data,$pageSize=60000,$defaultName="Sheet")<br /> {<br /> if(!isset($defaultName)||$defaultName=="Sheet")$defaultName="Sheet".($this->getSheets()+1);<br /> if(empty(self::$pre_workBook)):<br /> self::$pre_workBook=$defaultName;<br /> if(!isset($this->_heads[self::$pre_workBook][0]))<br /> $this->addHead($head,self::$pre_workBook);<br /> $this->addRow($data,self::$pre_workBook);<br /> else:<br /> if($this->getRows(self::$pre_workBook)>=($pageSize+1)):<br /> $this->addHead($head,$defaultName);<br /> $this->addRow($data,$defaultName);<br /> self::$pre_workBook=$defaultName;<br /> else:<br /> $this->addRow($data,self::$pre_workBook);<br /> endif;<br /> endif;<br /> }<br /> /**<br /> * @desc 返回所有工作薄名<br /> * @param null<br /> */<br /> public function getAllSheetNames()<br /> {<br /> return $this->_sheets;<br /> }<br /> /**<br /> * @desc 设置所有表格标题(分卷) 默认为合并当前工作薄的所有列,并居中显示(粗体) 该方法必须在工作簿存在的情况下调用.<br /> * @param string $title 标题<br /> */<br /> public function setAllTitle($title)<br /> {<br /> $_temp=$this->getAllSheetNames();<br /> foreach($_temp as $value):<br /> $this->addTitle($title,$value);<br /> endforeach;<br /> }<br /> /**<br /> * @desc 编码转换函数<br /> * @param string $str 转换的字符串<br /> * @param string $source_code 原编码 默认UTF-8<br /> * @param string $target_code 目标编码 默认GBK<br /> */<br /> protected function getConvertString($str,$source_code='UTF-8',$target_code='GBK')<br /> {<br /> return !empty($str)&&is_string($str)?iconv($source_code,$target_code,$str):$str;<br /> }<br /> /**<br /> * @desc 打印调试信息<br /> * @param null<br /> */<br /> public function debug($out=true)<br /> {<br /> if($out):<br /> var_dump($this->_line);<br /> else:<br /> return $this->_line;<br /> endif;<br /> }<br /> /**<br /> * @desc 工作薄命名后缀 调用此方法将生成全局唯一工作薄名<br /> * @param $name 自定义工作薄名<br /> */<br /> public function uniqueName($name)<br /> {<br /> $size=$this->getSheets();<br /> if($size==0)return $name;<br /> else return $name.$size;<br /> }<br /> /**设置单位格数据类型,该方法需在填充数据前完成 数据类型参照指定版本的excel<br /> * @param $_columnType the $_columnType to set array 指定的键值对数组<br /> */<br /> public function set_columnType($_columnType)<br /> {<br /> $this->_columnType = $_columnType;<br /> }<br /> }<br /> ?>

demo1.php
<?php<br /> include'lib/XmlExcel.php';<br /> $xls=new XmlExcel;<br /> $xls->setDefaultWidth(80);<br /> $xls->setDefaultAlign("center");<br /> $xls->setDefaultHeight(18);<br /> $xls->addHead(array("title1","title2","title3","title4","title5","title6"),"demo1");<br /> for($ind=0;$ind<10;$ind++):<br /> $xls->addRow(array($ind,$ind,$ind,$ind,$ind,$ind),"demo1");<br /> endfor;<br /> $xls->export("demo1");<br /> ?>

demo2.php
<?php<br /> include'lib/XmlExcel.php';<br /> $xls=new XmlExcel;<br /> $xls->setDefaultWidth(80);<br /> $xls->setDefaultAlign("center");<br /> $xls->setDefaultHeight(18);<br /> for($ind=0;$ind<50;$ind++):<br /> $xls->addPageRow(array("title1","title2","title3","title4","title5","title6"),array($ind,$ind,$ind,$ind,$ind,$ind),10,$xls->uniqueName("demo"));<br /> endfor;<br /> $xls->export("demo2");<br /> ?>