转：Codeigniter调用PHPExcel例子

时间：2011-01-30 来源：harry.guo

1.把PHPExcel放到application\libraries下,并新建个tool.php用来调用

PHPExcel官方网站： http://www.codeplex.com/PHPExcel

<?php

class Tool{

/**导入Excel文件到MySql数据库
$file：Excel文件的名称；
$filetempname：Excel文件上传到服务器的临时文件名
$sql:传入的sql语句
**/
function UploadExcel($file,$filetempname,$strsql)
{
set_include_path(get_include_path().PATH_SEPARATOR .BASEPATH.'libraries/PHPExcel/PHPExcel');

require_once 'PHPExcel/PHPExcel.php';
require_once 'PHPExcel/PHPExcel/IOFactory.php';
require_once 'PHPExcel/PHPExcel/Reader\Excel5.php';

$tools_filePath = dirname(dirname(dirname(dirname(__FILE__))))."/UploadFiles/";
$tools_filePath = str_replace('\\','/',$tools_filePath);

//防止最大可使用的内存总量过少
if(intval(ini_get('memory_limit')) <= 20)
{
   ini_set('memory_limit','20M');
}

$uploadDir = $tools_filePath;

if (!is_dir($uploadDir))
{
   if (!mkdir($uploadDir, 0777,TRUE) || !chmod($uploadDir, 0777))
   {
    exit("创建上传目录失败");
   }
}

$filename = explode(".",$file);//把上传的文件名以“.”好为准做一个数组。
$date = date("YmdHis");
//$time=date("Y-m-d-H-i-s");//取当前上传的时间
//$filename[0]=$time;//取文件名t替换
//$name=implode(".",$filename); //上传后的文件名
//exit($filename[1]);
$filename_sever = $date.".".$filename[count($filename)-1];
$filename_sever = $uploadDir."/".$filename_sever;
$result=move_uploaded_file($filetempname,$filename_sever);//假如上传到当前目录下

    if($result) //如果上传文件成功，就执行导入excel操作
    {
   $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
   $objPHPExcel = $objReader->load($filename_sever);
   $sheet = $objPHPExcel->getSheet(0);
   $highestRow = $sheet->getHighestRow(); // 取得总行数
   $highestColumn = $sheet->getHighestColumn(); // 取得总列数

   $CountFlag =1;
   $everyTimes=200;

   if($highestRow >=$everyTimes)
   {
    $CountFlag=ceil($highestRow /$everyTimes);
   }

   for($m=1;$m<=$CountFlag;$m++)
   {
    $startdata =2;
    $endata=$everyTimes*($CountFlag-1) ;
    if($m > 1)$startdata=($m-1)*$everyTimes+1;
    if($m==$CountFlag )$endata =$highestRow;

    $sql=$strsql;
    $str = "";$strdata="";$msgstr="";

    for($j=$startdata;$j<=$endata;$j++)
    {
     for($k='A';$k<=$highestColumn;$k++)
     {
      if($str != "")
      {
       $str .= ",'".iconv('utf-8','gbk',$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue())."'";//读取单元格
      }
      else
      {
       $str .= "'".iconv('utf-8','gbk',$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue())."'";//读取单元格
      }

     }
     $str= "(".$str.")";
     if($strdata !="")
      $strdata .=",".$str;
     else $strdata .=$str ;
      $str="";

    }

    $sql .= $strdata;

    if(!mysql_query($sql))
    {
     continue;
    }
   }

   if(!is_dir($filename_sever))
   {
    unlink($filename_sever); ////删除文件
   } else
   {
    deldir($filename_sever);   ////删除文件
   }
   $msg = true;
    }
    else
    {
   $msg = false;
    }

return $msg;
}

}

2.在views的sellNember.php页面,注意:form必须是 form_open_multipart 使用 form_open无法上传

    <?=form_open_multipart('sellNemberC/upload')?>
    <table width="100%" border="0" cellspacing="0" cellpadding="0">
     <tr>
      <td> 

      </td>
     </tr>
     <tr>
      <td align="left">
       上传号码资源：
       <input type="hidden" name="site_url" value="<?=site_url()?>">
       <input type="file" name="inputExcel" size="20" />
       <input type="submit" name="sub_up" value="提交" />
      </td>
     </tr>
    </table>
    <?=form_close()?>

3.在controllers下的sellNemberC.php进行调用

/*
*导入excel文件
*/
function upload()
{

$this->load->library('tool');
   $site_url = $_POST['site_url'];
   $name = iconv("UTF-8","gbk",$_FILES['inputExcel']['name']);
   $tempName = iconv("UTF-8","gbk",$_FILES['inputExcel']['tmp_name']);

   $sql = "insert into tcombinedsetnumber (number,type,price,remark,commision) values ";
   $msg = $this->tool->UploadExcels($name,$tempName,$sql);

   if($msg === true)
   {
    show_error("导入成功<a href='".$site_url."/Cextensions/sellNemberC'>返回</a>");
   }else
   {
    show_error("导入失败<a href='".$site_url."/Cextensions/sellNemberC'>返回</a>");
   }
}