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 Symfony\Component\HttpFoundation\Request;
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>