lavarel里面Excel导出及导入

//导出

//第一步

在lavarel框架根目录app里新建libs目录

//第二步

在网上下载Excel类,并解压放到libs目录里。解压好的里面应该有两个东西一个是PHPExcel文件夹和PHPExcel.PHP文件.

//第三步

运行命令行进入项目根目录，执行“composer dumpautoload”，

在控制器里面引入:

use PHPExcel;//引入excel类
use IOFactory;

在控制器所要导出的方法代码为:

 //导出
数据库里的goods表结构为:
-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `goods` varchar(255) DEFAULT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=110 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES ("1", "西服", "100022");
INSERT INTO `goods` VALUES ("13", "领带", "2222");
INSERT INTO `goods` VALUES ("12", "西服", "1");
 public function Excel(){
    $query=DB::table("goods")->get();//查询表
    //print_r($query);
    if(!$query) return false;//判断是否为空值
    $obj=new PHPExcel();//实例化excel类
    include_once("../app/libs/PhpExcel/PHPExcel/IOFactory.php");//引入IOFactory.php
    $obj->getProperties()-> setTitle("export") ->setDescription("none");
    $obj-> setActiveSheetIndex(0);
    $fields = DB::select("SHOW COLUMNS FROM goods");//查询goods表中所有列名,并返回为数组。
     //print_r($fields);die;
    $col = 0;//定义列
    foreach($fields as $field){
        $field =$field["Field"];
        $obj-> getActiveSheet() -> setCellValueByColumnAndRow($col, 1,$field);
        $col++;
    }
    $row = 2;//定义行
    foreach($query as $data)
    {
        $col =0;
        foreach($fields as $field)
        {

            //print_r($data);

            $field =$field["Field"];

            $obj->getActiveSheet()->setCellValueByColumnAndRow($col,$row,!empty($data["$field"])?$data["$field"]:"");

            $col++;

        }

        $row++;

    }
    $obj-> setActiveSheetIndex(0);
    $objWriter =IOFactory :: createWriter($obj, "Excel5");
    header("Content-Type:application/vnd.ms-excel");
    header("Content-Disposition:attachment;filename="Brand_" .date("Y-m-d") . ".xls"");
    header("Cache-Control:max-age=0");
    $objWriter-> save("php://output");

}
//第四步
将框架目录里面的config目录里面的配置文件database.php里面的

"fetch" => PDO::FETCH_CLASS,//返回对象

改为

"fetch" => PDO::FETCH_ASSOC,//返回数组
//导入
第一步:复制一份导出的文件夹。
第二步:控制器中上面引入的为：

use PHPExcel;//引入excel类
use IOFactory;
use DB;
use PHPExcel_Cell;
use SymfonyComponentHttpFoundationRequest;
header("content-type:text/html;charset=utf8");

第三步:控制器里面的方法:

//进入导入页面方法
public function ru(){
    return view("Excel/ru");
}

//执行导入方法
public function daoru(Request $request)
{
    // print_R($_FILES);die;
    $tmp_file =$_FILES ["file_stu"] ["tmp_name"];
    $file_types =explode ( ".", $_FILES ["file_stu"] ["name"] );
    $file_type =$file_types [count ( $file_types ) - 1];
    /*判别是不是.xls文件，判别是不是excel文件*/
    if (strtolower( $file_type ) != "xls"){
        $this->error ( "不是Excel文件，重新上传" );
    }
    $savePath ="./excel/";
    /*以时间来命名上传的文件*/
    $str =date("Ymdhis");
    $file_name =$str . "." . $file_type;
    //echo$file_name;die;
    $request->file("file_stu")->move($savePath, $file_name);
    /*是否上传成功*/
    /*if(!copy($tmp_file,$savePath.$file_name)){
       $this->error ( "上传失败" );
    }*/
    //要获得新的文件路径+名字
    $fullpath =$savePath.$file_name;
    //echo$fullpath;die;
    $re =$this->read($fullpath,"utf-8");
    //print_r($re);die;
    for($i=1;$i<count($re);$i++){
        //print_r($re);
        //echo$re[$i][1];
        $adds =DB::table("goods")->insert(["goods" => $re[$i][1], "price" =>$re[$i][2]]);
    }
    //die;
    if($adds){
        return Redirect("lists");
       // echo"<script>alert("导入成功");location.href="Excel/lists"</script>";
    }else{
        echo"<script>alert("导入失败");location.href="ru"</script>";
    }
}
//执行读取导入的数据表的方法
public function read($filename,$encode="utf-8")
{
    include_once("../app/libs/phpexcel/phpexcel/IOFactory.php");
    //$this->load ->library("PHPExcel/IOFactory");
    $objReader =IOFactory::createReader("Excel5");
    $objReader->setReadDataOnly(true);
    $objPHPExcel= $objReader->load($filename);
    $objWorksheet= $objPHPExcel->getActiveSheet();
    $highestRow =$objWorksheet->getHighestRow();
    //echo$highestRow;die;
    $highestColumn = $objWorksheet->getHighestColumn();
    //echo$highestColumn;die;
    $highestColumnIndex =PHPExcel_Cell::columnIndexFromString($highestColumn);
    /*$highestColumnIndex =PHPExcel::columnIndexFromString($highestColumn);*/
    $excelData =array();
    for($row = 1;$row <= $highestRow; $row++) {
        for ($col= 0; $col < $highestColumnIndex; $col++) {
            $excelData[$row][]=(string)$objWorksheet->getCellByColumnAndRow($col,$row)->getValue();
        }
    }
    return$excelData;
}
public function lists(){
    $arr=DB::table("goods")->get();
    //print_r($arr);die;
    return view("Excel/listss",["arr"=>$arr]);
}

第四步:视图层有俩个:
一个是导入的页面:

<form action="{{url("daoru")}}" method="post" enctype="multipart/form-data">
    <table>
        <tr>
            <td>文件上传</td>
            <td><input type="file" name="file_stu"/></td>
        </tr>
        <tr>
            <td><input type="submit" value="提交"/></td>
            <td></td>
        </tr>
    </table>
</form>

一个是导入成功之后所跳转的展示页面:

<?php
header("content-type:text/html;charset=utf8");
?>
<center>
    <table border="1">
        <tr>
            <td>ID</td>
            <td>商品名</td>
            <td>商品价格</td>
            <td>操作</td>
        </tr>

        @foreach($arr as $key => $va)
        <tr>
            <td><?php echo $va["id"]?></td>
            <td><?php echo $va["goods"]?></td>
            <td><?php echo $va["price"]?></td>
            <td><a href="{{url("#")}}?id=<?php echo $va["id"]?>">删除</a></td>
        </tr>
            @endforeach
    </table>
</center>