laravel框架实现phpExcel导入导出

最新推荐文章于 2024-01-19 11:52:13 发布

Quiet_boy

最新推荐文章于 2024-01-19 11:52:13 发布

阅读量614

点赞数

CC 4.0 BY-SA版权

文章标签：框架 laravel

本文链接：https://blog.youkuaiyun.com/Quiet_boy/article/details/53635906

本文介绍如何在Laravel框架中利用PHPExcel库进行Excel数据的导入和导出操作。首先需配置Composer并引入PHPExcel类，然后通过具体代码示例展示了数据导出过程，包括设置文件属性、字段名及内容填充；同时也分享了数据导入的方法，涉及文件类型判断、读取及插入数据库等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

laravel框架实现phpExcel导入导出，首先必须在laravel引入第三方类

1、在app目录下创建一个新的文件夹，命名libs（可自定义）

2、（可选）考虑到后面可能会引用很多库，so，在libs下再创建一个phpExcel文件夹,把phpExcel类放入此文件夹下。

3、找到根目录下的composer.json文件

4、找到composer.json中定义的（看我备注）

"autoload": {

"classmap": [

"database",

"app/libs/phpExcel" //加入phpExcel类的路径

"psr-4": {

"App\\": "app/"

}

5、安装composer，windows下可以在百度上下载

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

7、在控制器中use PHPExcel

8、在方法中实例化phpExccel对象，打印该对象看phpExcel类是否引入成功。

$objPHPExcel = new PHPExcel();

print_r($objPHPExcel);

==========以上是引入phpExcel类步骤（其它第三方类与此类似）============

[html] view plain copy

print ?

<span style="font-size:18px;">以下开始excel导入导出</span>

<span style="font-size:18px;">以下开始excel导入导出</span>

//导出控制器中use PHPExcel; use IOFactory;

[php] view plain copy

print ?

publicfunction phpexcel()
{
//$objPHPExcel = new PHPExcel();
//print_r($objPHPExcel);
$query =DB::table('goods')->get();
//$query =$this ->db->query($sql);
//print_r($query);
if(!$query)return false;
//StartingthePHPExcellibrary
//加载PHPExcel类
//$this->load->library('PHPExcel');
//$this->load ->library('PHPExcel/IOFactory');
$objPHPExcel= new PHPExcel();
include_once('../app/libs/phpexcel/phpexcel/IOFactory.php');
$objPHPExcel->getProperties()-> setTitle("export") ->setDescription("none");
$objPHPExcel-> setActiveSheetIndex(0);
//Fieldnamesinthefirstrow
$fields = DB::select("select COLUMN_NAME from information_schema.COLUMNS where
table_name = 'goods';");
//print_r($fields);die;
$col = 0;
foreach($fields as $field){
$field =$field['COLUMN_NAME'];
$objPHPExcel-> getActiveSheet() -> setCellValueByColumnAndRow($col, 1,$field);
$col++;
}
// die;
//Fetchingthetabledata
$row = 2;
foreach($query as $data)
{
$col =0;
foreach($fields $field)
{
//print_r($data);
$field =$field['COLUMN_NAME'];
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,!empty($data["$field"])?$data["$field"]:'');
$col++;
}
$row++;
}
//die;
$objPHPExcel-> setActiveSheetIndex(0);
$objWriter =IOFactory :: createWriter($objPHPExcel, 'Excel5');
//Sendingheaderstoforcetheusertodownloadthefile
header('Content-Type:application/vnd.ms-excel');
//header('Content-Disposition:attachment;filename="Products_' .date('dMy') . '.xls"');
header('Content-Disposition:attachment;filename="Brand_' .date('Y-m-d') . '.xls"');
header('Cache-Control:max-age=0');
$objWriter-> save('php://output');
}

publicfunction phpexcel()
{

       //$objPHPExcel = new PHPExcel();

       //print_r($objPHPExcel);

        $query =DB::table('goods')->get();

        //$query =$this ->db->query($sql);

       //print_r($query);

        if(!$query)return false;

        //StartingthePHPExcellibrary

        //加载PHPExcel类

       //$this->load->library('PHPExcel');

        //$this->load ->library('PHPExcel/IOFactory');

        $objPHPExcel= new PHPExcel();

        include_once('../app/libs/phpexcel/phpexcel/IOFactory.php');

        $objPHPExcel->getProperties()-> setTitle("export") ->setDescription("none");

        $objPHPExcel-> setActiveSheetIndex(0);

        //Fieldnamesinthefirstrow

        $fields = DB::select("select COLUMN_NAME from information_schema.COLUMNS where

           table_name = 'goods';");

       //print_r($fields);die;

        $col = 0;

       foreach($fields as $field){

            $field =$field['COLUMN_NAME'];

            $objPHPExcel-> getActiveSheet() -> setCellValueByColumnAndRow($col, 1,$field);

            $col++;

        }

       // die;
       //Fetchingthetabledata

       $row = 2;

        foreach($query as $data)
        {

             $col =0;

             foreach($fields $field)

             {

                 //print_r($data);

                 $field =$field['COLUMN_NAME'];

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

                 $col++;

             }

            $row++;

        }

        //die;

        $objPHPExcel-> setActiveSheetIndex(0);

        $objWriter =IOFactory :: createWriter($objPHPExcel, 'Excel5');

        //Sendingheaderstoforcetheusertodownloadthefile

        header('Content-Type:application/vnd.ms-excel');

       //header('Content-Disposition:attachment;filename="Products_' .date('dMy') . '.xls"');

        header('Content-Disposition:attachment;filename="Brand_' .date('Y-m-d') . '.xls"');

        header('Cache-Control:max-age=0');

        $objWriter-> save('php://output');

    }

//导入控制器中use IOFactory; use PHPExcel_Cell;

[php] view plain copy

print ?

public functionru(Request $request){
$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(['gname' => $re[$i][1], 'gprice' =>$re[$i][2]]);
}
//die;
if($adds){
echo"<script>alert('导入成功');location.href='daoru'</script>";
}else{
echo"<script>alert('导入失败');location.href='daoru'</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);
$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 functionru(Request $request){
       $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(['gname' => $re[$i][1], 'gprice' =>$re[$i][2]]);

       }

       //die;

       if($adds){

            echo"<script>alert('导入成功');location.href='daoru'</script>";

        }else{

            echo"<script>alert('导入失败');location.href='daoru'</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);

        $excelData =array();

        for($row = 1;$row <= $highestRow; $row++) {

            for ($col= 0; $col < $highestColumnIndex; $col++) {

                   $excelData[$row][]=(string)$objWorksheet->getCellByColumnAndRow($col,$row)->getValue();

             }

        }

        return$excelData;

}

phpExcel导入导出终于完成了，赶快尝试一下吧！