TP5使用PHPExcel

安装

composer require phpoffice/phpexcel

引入

use PHPExcel_IOFactory;
use PHPExcel;

使用

/**
* 导入excel
**/
public function export($data){
    //$data参数是要循环输出的数据数组
    $filename = iconv('utf-8', 'gb2312', '导出数据测试');//设置导出文件名，转码，否则可能出现文件名乱码
    $excel = new PHPExcel();//创建phpexcel类的实例
    $excel->setactivesheetindex(0);//设置当前活动的sheet，0表示第一个
    $excel->getActiveSheet()->setTitle('201904考核排名');//设置当前活动的sheet名，默认就是第一个sheet
    $excel->getactivesheet()->setcellvalue('A1', '工号');//设置第一列名
    $excel->getactivesheet()->setcellvalue('B1', '姓名');//设置第二列名
    $excel->getactivesheet()->setcellvalue('C1', '得分');//设置第三列名
    //循环数组，设置第一列、第二列、第三列的数据
    foreach($data as $key => $value)
    {
        $key = $key+2;
        $excel->getactivesheet()->setcellvalue('A'.$key, $value['number']);
        $excel->getactivesheet()->setcellvalue('B'.$key, $value['name']);
        $excel->getactivesheet()->setcellvalue('C'.$key, $value['score']);
    }
    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$filename.'.xls"');
    header("Content-Disposition:attachment;filename=$filename.xls");
    $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}

/**
* 导出excel录入sql
**/
public function import(){
    //$file是上传到服务器后的excel文件路径
    //判断excel文件格式
    $file = 'test.xls';
    $type = pathinfo($file);
    $type = strtolower($type["extension"]);
    if ($type=='xlsx') {
        $type='Excel2007';
    }elseif($type=='xls') {
        $type = 'Excel5';
    }
    $objReader = \PHPExcel_IOFactory::createReader($type);
    $objPHPExcel = $objReader->load($file, $encode = 'utf-8');
    $excel_array = $objPHPExcel->getsheet(0)->toArray();
    $res = [];
    array_shift($excel_array);//去掉第一行，因为我上传的excel文件第一行为列名
    if(!is_array($excel_array) || empty($excel_array)){
        return $res;
    }else{
        //循环读取数据
        foreach ($excel_array as $key => $value) {
            $res[$key]['product_id'] = (string)$value[0];       //平台商品id
            $res[$key]['name'] = (string)$value[1];             //商品名称
            $res[$key]['pic_url'] = (string)$value[2];          //图片主图
            $res[$key]['info_url'] = (string)$value[3];         //商品详情链接
            $res[$key]['shop_name'] = (string)$value[4];        //店铺名称
            $res[$key]['price'] = (string)$value[5];            //商品价格
            $res[$key]['sales'] = (string)$value[6];            //月销售量
            $res[$key]['extend_info_url'] = (string)$value[10]; //平台详情短链接
            $res[$key]['long_url'] = (string)$value[11];        //平台长链接
            $res[$key]['coupon_num'] = (string)$value[13];      //优惠券总量
            $res[$key]['coupon_surplus'] = (string)$value[14];  //剩余优惠券
            $res[$key]['coupon_price'] = (string)$value[15];    //优惠券面额
            $res[$key]['coupon_start'] = (string)$value[16];    //优惠券开始时间
            $res[$key]['coupon_end'] = (string)$value[17];      //优惠券结束时间
            $res[$key]['coupon_url'] = (string)$value[18];      //优惠券链接
            $res[$key]['coupon_small_url'] = (string)$value[20];//优惠券短链接
            $res[$key]['add_time'] = time();                    //新增时间
            $res[$key]['gold'] = 200;                           //抽奖金币
            $res[$key]['type'] = config('platform.taobaoke');//平台类型
        }
    }
    Db::startTrans();
    try{
        db('goods')->insertAll($res);
        Db::commit();
    }catch (\Exception $e){
        Db::rollback();
        return $e->getMessage();
    }
}