php利用PHPExcel类导出导入Excel用法

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" );

$finalFileName = (Yii::app()->basePath.'/runtime/'.time().'.xls'; 

$xlsWriter->save($finalFileName); 

echo file_get_contents($finalFileName); 

<?
if($_POST['leadExcel'] == "true")
{
    $filename = $_FILES['inputExcel']['name'];
    $tmp_name = $_FILES['inputExcel']['tmp_name'];
    $msg = uploadFile($filename,$tmp_name);
    echo $msg;
}

//导入Excel文件
function uploadFile($file,$filetempname)
{
    //自己设置的上传文件存放路径
    $filePath = 'upFile/';
    $str = "";  
    //下面的路径按照你PHPExcel的路径来修改
    require_once '../PHPExcel/PHPExcel.php';
    require_once '../PHPExcel/PHPExcel/IOFactory.php';
    require_once '../PHPExcel/PHPExcel/Reader/Excel5.php';

    //注意设置时区
    $time=date("y-m-d-H-i-s");//去当前上传的时间
    //获取上传文件的扩展名
    $extend=strrchr ($file,'.');
    //上传后的文件名
    $name=$time.$extend;
    $uploadfile=$filePath.$name;//上传后的文件名地址
    //move_uploaded_file() 函数将上传的文件移动到新位置。若成功，则返回 true，否则返回 false。
    $result=move_uploaded_file($filetempname,$uploadfile);//假如上传到当前目录下
    //echo $result;
    if($result) //如果上传文件成功，就执行导入excel操作
    {
        include "conn.php";
        $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
        $objPHPExcel = $objReader->load($uploadfile);
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();           //取得总行数
        $highestColumn = $sheet->getHighestColumn(); //取得总列数
       
        /* 第一种方法

        //循环读取excel文件,读取一条,插入一条
        for($j=1;$j<=$highestRow;$j++)                        //从第一行开始读取数据
        {
            for($k='A';$k<=$highestColumn;$k++)            //从A列读取数据
            {
                //
                这种方法简单，但有不妥，以'\'合并为数组，再分割\为字段值插入到数据库
                实测在excel中，如果某单元格的值包含了\导入的数据会为空       
                //
                $str .=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue().'\';//读取单元格
            }
            //echo $str; die();
            //explode:函数把字符串分割为数组。
            $strs = explode("\",$str);
            $sql = "INSERT INTO te(`1`, `2`, `3`, `4`, `5`) VALUES (
            '{$strs[0]}',
            '{$strs[1]}',
            '{$strs[2]}',
            '{$strs[3]}',
            '{$strs[4]}')";
            //die($sql);
            if(!mysql_query($sql))
            {
                return false;
                echo 'sql语句有误';
            }
            $str = "";
        } 
        unlink($uploadfile); //删除上传的excel文件
        $msg = "导入成功！";
        */

        /* 第二种方法*/
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        echo 'highestRow='.$highestRow;
        echo "<br>";
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
        echo 'highestColumnIndex='.$highestColumnIndex;
        echo "<br>";
        $headtitle=array();
        for ($row = 1;$row <= $highestRow;$row++)
        {
            $strs=array();
            //注意highestColumnIndex的列数索引从0开始
            for ($col = 0;$col < $highestColumnIndex;$col++)
            {
                $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
            }   
            $sql = "INSERT INTO te(`1`, `2`, `3`, `4`, `5`) VALUES (
            '{$strs[0]}',
            '{$strs[1]}',
            '{$strs[2]}',
            '{$strs[3]}',
            '{$strs[4]}')";
            //die($sql);
            if(!mysql_query($sql))
            {
                return false;
                echo 'sql语句有误';
            }
        }
    }
    else
    {
       $msg = "导入失败！";
    }
    return $msg;
}
?>

<form action="upload.php" method="post" enctype="multipart/form-data">
    <input type="hidden" name="leadExcel" value="true">
    <table align="center" width="90%" border="0">
    <tr>
       <td>
        <input type="file" name="inputExcel"><input type="submit" value="导入数据">
       </td>
    </tr>
    </table>
</form>

        $z->Zip($zipfiles, $zipfile);

<?php
#
# PHPZip v1.2 by Sext (sext@neud.net) 2002-11-18
#     (Changed: 2003-03-01)
#
# Makes zip archive
#
# Based on "Zip file creation class", uses zLib
#
#

class PHPZip
{
    function Zip($dir, $zipfilename)
    {
        if (@function_exists('gzcompress'))
        {   
            $curdir = getcwd();
            if (is_array($dir))
            {
                    $filelist = $dir;
            }
            else
            {
                $filelist = $this -> GetFileList($dir);
            }

            if ((!empty($dir))&&(!is_array($dir))&&(file_exists($dir))) chdir($dir);
            else chdir($curdir);

            if (count($filelist)>0)
            {
                foreach($filelist as $filename)
                {
                    if (is_file($filename))
                    {
                        $fd = fopen ($filename, "r");
                        $content = fread ($fd, filesize ($filename));
                        fclose ($fd);

                        if (is_array($dir)) $filename = basename($filename);
                        $this -> addFile($content, $filename);
                    }
                }
                $out = $this -> file();

                chdir($curdir);
                $fp = fopen($zipfilename, "w");
                fwrite($fp, $out, strlen($out));
                fclose($fp);
            }
            return 1;
        }
        else return 0;
    }

    function GetFileList($dir)
    {
        if (file_exists($dir))
        {
            $args = func_get_args();
            $pref = $args[1];

            $dh = opendir($dir);
            while($files = readdir($dh))
            {
                if (($files!=".")&&($files!=".."))
                {
                    if (is_dir($dir.$files))
                    {
                        $curdir = getcwd();
                        chdir($dir.$files);
                        $file = array_merge($file, $this -> GetFileList("", "$pref$files/"));
                        chdir($curdir);
                    }
                    else $file[]=$pref.$files;
                }
            }
            closedir($dh);
        }
        return $file;
    }

    var $datasec      = array();
    var $ctrl_dir     = array();
    var $eof_ctrl_dir = "x50x4bx05x06x00x00x00x00";
    var $old_offset   = 0;

    /**
     * Converts an Unix timestamp to a four byte DOS date and time format (date
     * in high two bytes, time in low two bytes allowing magnitude comparison).
     *
     * @param  integer  the current Unix timestamp
     *
     * @return integer  the current date in a four byte DOS format
     *
     * @access private
     */
    function unix2DosTime($unixtime = 0) {
        $timearray = ($unixtime == 0) ? getdate() : getdate($unixtime);

        if ($timearray['year'] < 1980) {
            $timearray['year']    = 1980;
            $timearray['mon']     = 1;
            $timearray['mday']    = 1;
            $timearray['hours']   = 0;
            $timearray['minutes'] = 0;
            $timearray['seconds'] = 0;
        } // end if

        return (($timearray['year'] - 1980) << 25) | ($timearray['mon'] << 21) | ($timearray['mday'] << 16) |
                ($timearray['hours'] << 11) | ($timearray['minutes'] << 5) | ($timearray['seconds'] >> 1);
    } // end of the 'unix2DosTime()' method

    /**
     * Adds "file" to archive
     *
     * @param  string   file contents
     * @param  string   name of the file in the archive (may contains the path)
     * @param  integer  the current timestamp
     *
     * @access public
     */
    function addFile($data, $name, $time = 0)
    {
        $name     = str_replace('\', '/', $name);

        $dtime    = dechex($this->unix2DosTime($time));
        $hexdtime = 'x' . $dtime[6] . $dtime[7]
                  . 'x' . $dtime[4] . $dtime[5]
                  . 'x' . $dtime[2] . $dtime[3]
                  . 'x' . $dtime[0] . $dtime[1];
        eval('$hexdtime = "' . $hexdtime . '";');

        $fr   = "x50x4bx03x04";
        $fr   .= "x14x00";            // ver needed to extract
        $fr   .= "x00x00";            // gen purpose bit flag
        $fr   .= "x08x00";            // compression method
        $fr   .= $hexdtime;             // last mod time and date

        // "local file header" segment
        $unc_len = strlen($data);
        $crc     = crc32($data);
        $zdata   = gzcompress($data);
        $c_len   = strlen($zdata);
        $zdata   = substr(substr($zdata, 0, strlen($zdata) - 4), 2); // fix crc bug
        $fr      .= pack('V', $crc);             // crc32
        $fr      .= pack('V', $c_len);           // compressed filesize
        $fr      .= pack('V', $unc_len);         // uncompressed filesize
        $fr      .= pack('v', strlen($name));    // length of filename
        $fr      .= pack('v', 0);                // extra field length
        $fr      .= $name;

        // "file data" segment
        $fr .= $zdata;

        // "data descriptor" segment (optional but necessary if archive is not
        // served as file)
        $fr .= pack('V', $crc);                 // crc32
        $fr .= pack('V', $c_len);               // compressed filesize
        $fr .= pack('V', $unc_len);             // uncompressed filesize

        // add this entry to array
        $this -> datasec[] = $fr;
        $new_offset        = strlen(implode('', $this->datasec));

        // now add to central directory record
        $cdrec = "x50x4bx01x02";
        $cdrec .= "x00x00";                // version made by
        $cdrec .= "x14x00";                // version needed to extract
        $cdrec .= "x00x00";                // gen purpose bit flag
        $cdrec .= "x08x00";                // compression method
        $cdrec .= $hexdtime;                 // last mod time & date
        $cdrec .= pack('V', $crc);           // crc32
        $cdrec .= pack('V', $c_len);         // compressed filesize
        $cdrec .= pack('V', $unc_len);       // uncompressed filesize
        $cdrec .= pack('v', strlen($name) ); // length of filename
        $cdrec .= pack('v', 0 );             // extra field length
        $cdrec .= pack('v', 0 );             // file comment length
        $cdrec .= pack('v', 0 );             // disk number start
        $cdrec .= pack('v', 0 );             // internal file attributes
        $cdrec .= pack('V', 32 );            // external file attributes - 'archive' bit set

        $cdrec .= pack('V', $this -> old_offset ); // relative offset of local header
        $this -> old_offset = $new_offset;

        $cdrec .= $name;

        // optional extra field, file comment goes here
        // save to central directory
        $this -> ctrl_dir[] = $cdrec;
    } // end of the 'addFile()' method

    /**
     * Dumps out file
     *
     * @return  string  the zipped file
     *
     * @access public
     */
    function file()
    {
        $data    = implode('', $this -> datasec);
        $ctrldir = implode('', $this -> ctrl_dir);

        return
            $data .
            $ctrldir .
            $this -> eof_ctrl_dir .
            pack('v', sizeof($this -> ctrl_dir)) .  // total # of entries "on this disk"
            pack('v', sizeof($this -> ctrl_dir)) .  // total # of entries overall
            pack('V', strlen($ctrldir)) .           // size of central dir
            pack('V', strlen($data)) .              // offset to start of central dir
            "x00x00";                             // .zip file comment length
    } // end of the 'file()' method

} // end of the 'PHPZip' class
?>

<?php  
session_start();  
if(isset($_GET['progress_key'])) {  
  $status = apc_fetch('upload_'.$_GET['progress_key']);  
  echo ($status['current']/$status['total'])*100;  
}  
?>  
upload.php

PHP Code
<?php  
   $id = $_GET['id'];  
?>  
<form enctype="multipart/form-data" id="upload_form" action="target.php" method="POST">  
<input type="hidden" name="APC_UPLOAD_PROGRESS"   
       id="progress_key"  value="<?php echo $id?>"/>  
<input type="file" id="test_file" name="test_file"/><br/>  
<input onclick="window.parent.startProgress(); return true;"  
 type="submit" value="上传"/>  
</form>  

<?php  
   $id = md5(uniqid(rand(), true));  
?>  
<html>  
<head><title>上传进度</title></head>  
<body>  
<script src="js/jquery-1.4.4.min.js" language="javascript"></script>  
  
  
<script language="javascript">  
var proNum=0;  
var loop=0;  
var progressResult;  
function sendURL() {  
            $.ajax({  
                        type : 'GET',  
                        url : "getprogress.php?progress_key=<?php echo $id;?>",  
                        async : true,  
                        cache : false,  
                        dataType : 'json',  
                        data: "progress_key=<?php echo $id;?>",  
                        success : function(e) {  
                                     progressResult = e;  
                                      proNum=parseInt(progressResult);  
                                      document.getElementById("progressinner").style.width = proNum+"%";  
                                      document.getElementById("showNum").innerHTML = proNum+"%";  
                                      if ( proNum < 100){  
                                        setTimeout("getProgress()", 100);  
                                      }   
                                   
                        }  
            });  
    
}  
  
function getProgress(){  
 loop++;  
  
 sendURL();  
}  
var interval;  
function startProgress(){  
    document.getElementById("progressouter").style.display="block";  
   setTimeout("getProgress()", 100);  
}  
</script>  
<iframe id="theframe" name="theframe"   
        src="upload.php?id=<?php echo $id; ?>"   
        style="border: none; height: 100px; width: 400px;" >   
</iframe>  
<br/><br/>  
<div id="progressouter" style="width: 500px; height: 20px; border: 6px solid red; display:none;">  
   <div id="progressinner" style="position: relative; height: 20px; background-color: purple; width: 0%; "></div>  
</div>  
<div id='showNum'></div><br>  
<div id='showNum2'></div>  
</body>  
</html>  

<?php
$homepage = file_get_contents('http://www.111cn.net/');
echo $homepage;
?>

<?php

function fetch_urlpage_contents($url){
$c=file_get_contents($url);
return $c;
}
//获取匹配内容
function fetch_match_contents($begin,$end,$c)
{
$begin=change_match_string($begin);
$end=change_match_string($end);
$p = "{$begin}(.*){$end}";
if(eregi($p,$c,$rs))
{
return $rs[1];}
else { return "";}
}//转义正则表达式字符串
function change_match_string($str){
//注意，以下只是简单转义
//$old=array("/","$");
//$new=array("/","$");
$str=str_replace($old,$new,$str);
return $str;
}

//采集网页
function pick($url,$ft,$th)
{
$c=fetch_urlpage_contents($url);
foreach($ft as $key => $value)
{
$rs[$key]=fetch_match_contents($value["begin"],$value["end"],$c);
if(is_array($th[$key]))
{ foreach($th[$key] as $old => $new)
{
$rs[$key]=str_replace($old,$new,$rs[$key]);
}
}
}
return $rs;
}

$url="http://www.111cn.net"; //要采集的地址
$ft["title"]["begin"]="<title>"; //截取的开始点
$ft["title"]["end"]="</title>"; //截取的结束点
$th["title"]["中山"]="广东"; //截取部分的替换

$ft["body"]["begin"]="<body>"; //截取的开始点
$ft["body"]["end"]="</body>"; //截取的结束点
$th["body"]["中山"]="广东"; //截取部分的替换

$rs=pick($url,$ft,$th); //开始采集

echo $rs["title"];
echo $rs["body"]; //输出
?>

<?php

function fetch_urlpage_contents($url){
$c=file_get_contents($url);
return $c;
}
//获取匹配内容
function fetch_match_contents($begin,$end,$c)
{
$begin=change_match_string($begin);
$end=change_match_string($end);
$p = "#{$begin}(.*){$end}#iU";//i表示忽略大小写，U禁止贪婪匹配
if(preg_match_all($p,$c,$rs))
{
return $rs;}
else { return "";}
}//转义正则表达式字符串
function change_match_string($str){
//注意，以下只是简单转义
$old=array("/","$",'?');
$new=array("/","$",'?');
$str=str_replace($old,$new,$str);
return $str;
}

//采集网页
function pick($url,$ft,$th)
{
$c=fetch_urlpage_contents($url);
foreach($ft as $key => $value)
{
$rs[$key]=fetch_match_contents($value["begin"],$value["end"],$c);
if(is_array($th[$key]))
{ foreach($th[$key] as $old => $new)
{
$rs[$key]=str_replace($old,$new,$rs[$key]);
}
}
}
return $rs;
}

$url="http://www.111cn.net"; //要采集的地址
$ft["a"]["begin"]='<a'; //截取的开始点<br />
$ft["a"]["end"]='>'; //截取的结束点

$rs=pick($url,$ft,$th); //开始采集

print_r($rs["a"]);

?>

<?php
function GetSources($Url,$User_Agent='',$Referer_Url='') //抓取某个指定的页面
{
//$Url 需要抓取的页面地址
//$User_Agent 需要返回的user_agent信息 如“baiduspider”或“googlebot”
$ch = curl_init();
curl_setopt ($ch, CURLOPT_URL, $Url);
curl_setopt ($ch, CURLOPT_USERAGENT, $User_Agent);
curl_setopt ($ch, CURLOPT_REFERER, $Referer_Url);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION,1);
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
$MySources = curl_exec ($ch);
curl_close($ch);
return $MySources;
}
$Url = "http://www.111cn.net"; //要获取内容的也没
$User_Agent = "baiduspider+(+http://www.baidu.com/search/spider.htm)";
$Referer_Url = 'http://www.111cn.net/';
echo GetSources($Url,$User_Agent,$Referer_Url);
?>

<?php
class Cache {
 /** 缓存目录 **/
 var $CacheDir        = './c';
 /** 缓存的文件 **/
 var $CacheFile        = '';
 /** 文件缓存时间(分钟) **/
 var $CacheTime        = 0;
 /** 文件是否已缓存 **/
 var $CacheFound        = False;
 /** 错误及调试信息 **/
 var $DebugMsg        = NULL;

 function Cache($CacheTime = 0) {
  $this->CacheTime    = $CacheTime;
 }

 private function Run() {
  /** 缓存时间大于0,检测缓存文件的修改时间,在缓存时间内为缓存文件名,超过缓存时间为False，
                小于等于0,返回false,并清理已缓存的文件
         **/
  Return $this->CacheTime ? $this->CheckCacheFile() : $this->CleanCacheFile();
 }
 function GetCache($VistUrl,$CacheFileType = 'html')
 {
  $this->SetCacheFile($VistUrl,$CacheFileType);

  $fileName=$this->CheckCacheFile();
  if($fileName)
  {
   $fp = fopen($fileName,"r");
   $content_= fread($fp, filesize($fileName));
   fclose($fp);
   return $content_;
  }
  else
  {
   return false;
  }
 }
 private function SetCacheFile($VistUrl,$CacheFileType = 'html') {
  if(empty($VistUrl)) {
   /** 默认为index.html **/
   $this->CacheFile = 'index';
  }else {
   /** 传递参数为$_POST时 **/
   $this->CacheFile = is_array($VistUrl) ? implode('.',$VistUrl) : $VistUrl;
  }
  $this->CacheFile = $this->CacheDir.'/'.md5($this->CacheFile);
  $this->CacheFile.= '.'.$CacheFileType;
 }

 function SetCacheTime($t = 60) {
  $this->CacheTime = $t;
 }

 private function CheckCacheFile() {
  if(!$this->CacheTime || !file_exists($this->CacheFile)) {Return False;}
  /** 比较文件的建立/修改日期和当前日期的时间差 **/
  $GetTime=(Time()-Filemtime($this->CacheFile))/(60*1);
  /** Filemtime函数有缓存,注意清理 **/
  Clearstatcache();
  $this->Debug('Time Limit '.($GetTime*60).'/'.($this->CacheTime*60).'');
  $this->CacheFound = $GetTime <= $this->CacheTime ? $this->CacheFile : False;
  Return $this->CacheFound;
 }

 function SaveToCacheFile($VistUrl,$Content,$CacheFileType = 'html') {
  $this->SetCacheFile($VistUrl,$CacheFileType);
  if(!$this->CacheTime) {
   Return False;
  }
  /** 检测缓存目录是否存在 **/
  if(true === $this->CheckCacheDir()) {
   $CacheFile = $this->CacheFile;
   $CacheFile = str_replace('//','/',$CacheFile);
   $fp = @fopen($CacheFile,"wb");
   if(!$fp) {
    $this->Debug('Open File '.$CacheFile.' Fail');
   }else {
    if(@!fwrite($fp,$Content)){
     $this->Debug('Write '.$CacheFile.' Fail');
    }else {
     $this->Debug('Cached File');
    };
    @fclose($fp);
   }
  }else {
   /** 缓存目录不存在，或不能建立目录 **/
   $this->Debug('Cache Folder '.$this->CacheDir.' Not Found');
  }
 }

 private function CheckCacheDir() {
  if(file_exists($this->CacheDir)) { Return true; }
  /** 保存当前工作目录 **/
  $Location = getcwd();
  /** 把路径划分成单个目录 **/
  $Dir = split("/", $this->CacheDir);
  /** 循环建立目录 **/
  $CatchErr = True;
  for ($i=0; $i<count($Dir); $i++){
   if (!file_exists($Dir[$i])){
    /** 建立目录失败会返回False 返回建立最后一个目录的返回值 **/
    $CatchErr = @mkdir($Dir[$i],0777);
   }
   @chdir($Dir[$i]);
  }
  /** 建立完成后要切换到原目录 **/
  chdir($Location);
  if(!$CatchErr) {
   $this->Debug('Create Folder '.$this->CacheDir.' Fail');
  }
  Return $CatchErr;
 }

 private function CleanCacheFile() {
  if(file_exists($this->CacheFile)) {
   @chmod($this->CacheFile,777);
   @unlink($this->CacheFile);
  }
  /** 置没有缓存文件 **/
  $this->CacheFound = False;
  Return $this->CacheFound;
 }

 function Debug($msg='') {
  if(DEBUG) {
   $this->DebugMsg[] = '[Cache]'.$msg;
  }
 }

 function GetError() {
  Return empty($this->DebugMsg) ? '' : "<br>n".implode("<br>n",$this->DebugMsg);
 }
}/* end of class */

?>