Codeigniter调用PHPExcel 导入excel例子

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>&nbsp;

</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>”);
}
}

乱码方法:http://hi.baidu.com/xuelongpaox/blog/item/c71f0bfa769e6676034f56f3.html

发表回复