php导入excel表格

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› PHP›PHP编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

我们做网站的时候经常要用到excel导入和导出的功能，我们通常的做法是用phpexcel工具包来完成，具体方法如下：

html代码：

<form action="{:U('Mall/updExcel')}" method="POST" enctype="multipart/form-data">    //提交form表单到Mall控制器下的upExcel方法中
              <div style="float:left;width:41%;">   
                <div style="float:left;">
                    <input type='submit' value="更改价格" style="margin: 0px 0px 7px 10px;cursor: pointer;background-color:#C30D23;border:0px;color:#FFFFFF;width:90px;border-radius:5px;padding:3px 0;font-size:13px;"/>
                </div>
                <div style="float:left;width:45%">
                    <input type='file' value="" name="import"/>
                </div>
                <input type="hidden" id="url" name="url" value=""/>      //添加一个隐藏域 传递url
             </div>

 </form>

写ExcelController工具类：此类用来被实例化

<?php
namespace Home\Controller;
use Think\Controller;
include "./Public/Plugin/PHPExcel.class.php";
include "./Public/Plugin/PHPExcel/Writer/Excel5";
include "./Public/Plugin/PHPExcel/IOFactory.php";

class ExcelController extends Controller {
    //导入功能
    public function updExcel($file){ 
        if(!file_exists($file)){ 
            return array("error"=>0,'message'=>'file not found!');
        }
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        
        $objPHPExcel = $objReader->load($file,$encode='utf-8');
        
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数
        $j=0;
        for($i=2;$i<=$highestRow;$i++){
            
            $data[$j]['id']= $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
            
            $data[$j]['result_price']= $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
            $j++;
        }
        return $data;
    }

下面书写控制器来上传excel表格：此类方法不用把excel表格传递到服务器，直接写入数据：

<?php
namespace Admin\Controller;
use Think\Controller;
use Think\Page;
use Home\Controller\IndexController;
use Common;
use Org\Util\Date;
use Home\Controller\ExcelController;

class MallController extends Controller {
            /*商品列表批量导入*/
public function updExcel(){
        $excel=new ExcelController();
        $goods=M('shop_goods_subinfo');
        
        if(isset($_FILES["import"]) && ($_FILES["import"]["error"] == 0)){
            $result = $excel->updExcel($_FILES["import"]["tmp_name"]);
            /*
             * 业务逻辑代码
             * */
            $true="";
            $false="";
            foreach($result as $value){
                //$where=array('id'=>$value['id'],'status'=>0);
                $where=array(
                        'code'=>$value['id'],
                );
                $data=array(
                        'result_price'=>$value['result_price'],
                );
                $state=$goods->where($where)->save($data);
                if($state > 0){
                    $true .= ";".$value['id'];
                }else{
                    $false .= ";".$value['id'];
                }
                
            }
            echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />';
            echo "<script type='text/javascript'>";
            echo "alert('更改成功".$true.";更改失败".$false."');window.location.href='".I('param.url')."';";
            echo "</script>";
            exit;
        }else{
            echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />';
            echo "<script type='text/javascript'>";
            echo "alert('文件读取失败');window.location.href='".I('param.url')."';";
            echo "</script>";
            exit;
        }
    }
}