thinkphp5 excel导入导出

50 阅读 0 评论 33 点赞

我是靠谱客的博主飘逸裙子，最近开发中收集的这篇文章主要介绍thinkphp5 excel导入导出，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.类库下载

composer require phpoffice/phpexcel

2.导入

public function import(){
        if(request()->isPost()){
            $result = ['status'=>false, 'msg'=>'操作失败'];
            $file = request()->file('file');
            $file_name = $file->getInfo()['name'];//文件名称
            $file_size = $file->getInfo()['size'];//文件大小
            $file_extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
            $file_path = 'static/uploads/files/';
            // 移动到框架应用根目录/public/uploads/ 目录下
            $info = $file->move($file_path);
            if($info){
                //获取文件所在目录名
                $new_path = $file_path.$info->getSaveName();

                $objPHPExcel = new PHPExcel();
                $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); //设置保存版本格式

                //实例化PHPExcel类
                if ($file_extension == 'xlsx'){
                    $objReader=new PHPExcel_Reader_Excel2007();
                } else if ($file_extension == 'xls') {
                    $objReader = new PHPExcel_Writer_Excel5();
                } 

                $objPHPExcel = $objReader->load($new_path,$encode='utf-8');//获取excel文件
                $sheet = $objPHPExcel->getSheet(0); //激活当前的表

                $highestRow = $sheet->getHighestRow(); // 取得总行数
                $highestColumn = $sheet->getHighestColumn(); // 取得总列数
                $a=0;
                $abModel = new abModel();
                //将表格里面的数据循环到数组中
                for($i=2;$i<=$highestRow;$i++)
                {
                    //*为什么$i=2? (因为Excel表格第一行应该是标题，从第二行开始，才是我们要的数据。)
                    $Channel = new Channel();
                    $data[$a]['pro_code'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();//产品编码
                    $data[$a]['cardno'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();//卡号
                    $data[$a]['cardkey'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();//卡密
                    $data[$a]['validity'] = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue()));//有效期
                    $a++;
                  
                }

                //往数据库添加数据
                if(!empty($data)){
                    $res = $abModel->insertAll($data);
                    if($res){
                        return ['status'=>true, 'msg'=>'操作成功！共导入'.$a.'条数据'];
                    }
                }
                
                return $result;
            }else{
                return ['status'=>false, 'msg'=>$file->getError()];
            }
        }

    }

3、导出
3.1 导出CSV文件

public function exportOrderCsv()
    {
        $post = json_decode(input("param.where"), true);
        $ids = input("param.ids");
        $where = [];
        if(!empty($ids)){
            $ids = explode(',', $ids);
            $where[] = ['id', 'in', $ids];
        }
       
        $csv = new Csv();
        $list = Db::table('h_sw_orders')->field('large_order,sub_order,pro_code,pro_name,company_id,pro_attribute,mobile,rece_mobile,rece_name,rece_address,ctime,logistics_company,logistics_orderno,quantity,point,money,channel_name,status,deliver_status,cancel_remarks,remarks')
                ->where($where)
                ->select();
       
        foreach ($list as $key => $value) {
            if($value['status'] != 1){
                $list[$key]['point'] = 0;
                $list[$key]['money'] = 0;
            }
        }
        $title = array('大订单号','子订单号','商品编码','产品名称','所属公司','商品销售属性','兑换手机号','收货手机号','收货人姓名','收货地址','下单时间','物流公司','物流单号','商品数量','积分值','面值','渠道名称','订单状态','货物状态','撤单备注','备注','成本价');
        $fileName = date("Y-m-d H:i:s", time());
        $csv->put_csv($list, $title, $fileName);
    }

Csv文件

<?php
namespace think;
class Csv
{
    //导出csv文件
    public function put_csv($list, $title, $fileName = '')
    {
        $file_name = "exam".time().".csv";
        if(!empty($fileName)){
            $file_name = $fileName.".csv";
        }
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$file_name );
        header('Cache-Control: max-age=0');
        $file = fopen('php://output',"a");
        $limit = 1000;
        $calc = 0;//行数
        foreach ($title as $v){
            $tit[] = iconv('UTF-8', 'GB2312//IGNORE',$v);
        }
        fputcsv($file,$tit);
        foreach ($list as $v){
            $calc++;
            if($limit == $calc){
                ob_flush();
                flush();
                $calc = 0;
            }

                file_put_contents('$v.txt', $v);
            foreach($v as $t){
                
                if(is_numeric($t)){
                    $tarr[] = iconv('UTF-8', 'GB2312//IGNORE',$t)."t";
                }else{
                    $tarr[] = iconv('UTF-8', 'GB2312//IGNORE',$t);
                }
                
            }
            fputcsv($file,$tarr);
            unset($tarr);
        }
        unset($list);
        fclose($file);
        exit();
    }

    // csv导入,此格式每次最多可以处理1000条数据（我觉得这个是不对的，他规定的是读取一行的最大长度）
    //$filename  文件路径
    public function input_csv($filename) {
        $csv_file = $handle = fopen($filename,'r');//只读方式打开，将文件指针指向文件头]
        $result_arr = array ();
        $i = 0;
        //函数从文件指针中读入一行并解析 CSV 字段（一维数组）
        while($data_line = fgetcsv($csv_file,1000)) {
            //跳过第一行标题读取
            if ($i == 0) {
                $GLOBALS ['csv_key_name_arr'] = $data_line;//将标题存储起来
                $i ++;
                continue;
            }
            //读取内容
            foreach($GLOBALS['csv_key_name_arr'] as $csv_key_num => $csv_key_name ) {
                $csv_key_name = iconv('gbk','utf-8', $csv_key_name);//标题
                if(!empty($data_line[$csv_key_num]) && $data_line[$csv_key_num]!='') {
                    /*$result_arr[$i][$csv_key_name] = '';
                }else {*/
                    $value = iconv('gbk','utf-8', $data_line[$csv_key_num]);//标题对应的内容
                    $result_arr[$i][$csv_key_name] = $value;
                }
            }
            $i++;
        }
        fclose($handle); // 关闭指针
        return $result_arr;
    }


}

3.2 导出Excel

public function exportPayOrder(){
		$where=input("param.where");
		$list=[];
        $ok=$this->where($map)->order("id desc")->select();
		foreach($ok as $k=>$v){
			$list[$k]['id']=$v['id'];
			$list[$k]['payment_no']='&nbsp;'.$v['payment_no'];
			$list[$k]['user']=(new User)->where(['id'=>$v['uid']])->value("user");
			$list[$k]['pro_name']=(new Channel)->where(['id'=>$v['channel_id']])->value("name");

		}
		$title=['ID','支付单号','用户名','产品名称'];
		return daochu_excel($list,$title,'支付单数据'.date("Y-m-d"));
	}

/**
 * @param array $data 要导出的数据
 * @param array $title excel表格的表头
 * @param string $filename 文件名
 */
function daochu_excel($data=array(),$title=array(),$filename='报表'){//导出excel表格
    set_time_limit(0);
    ini_set("memory_limit", "1024M");
    //处理中文文件名
    ob_end_clean();
    Header('content-Type:application/vnd.ms-excel;charset=utf-8');
    header("Content-Disposition:attachment;filename=export_data.xls");
    //处理中文文件名
    $ua = $_SERVER["HTTP_USER_AGENT"];
    $encoded_filename = urlencode($filename);
    $encoded_filename = str_replace("+", "%20", $encoded_filename);
    if (preg_match("/MSIE/", $ua) || preg_match("/LCTE/", $ua) || $ua == 'Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko') {
        header('Content-Disposition: attachment; filename="' . $encoded_filename . '.xls"');
    }else {
        header('Content-Disposition: attachment; filename="' . $filename . '.xls"');
    }
    header ( "Content-type:application/vnd.ms-excel" );
    $html = "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>";
    $html.="<html xmlns='http://www.w3.org/1999/xhtml'>";
    $html.="<meta http-equiv='Content-type' content='text/html;charset=UTF-8' /><head><title>".$filename."</title>";
    $html.="<style>td{text-align:center;font-size:12px;font-family:Arial, Helvetica, sans-serif;border:#1C7A80 1px solid;color:#152122;";
    $html.="width:auto;}table,tr{border-style:none;}.title{background:#7DDCF0;color:#FFFFFF;font-weight:bold;}</style>";
    $html.="</head><body><table width='100%' border='1'><tr>";
    foreach($title as $k=>$v){
        $html .= " <td class='title' style='text-align:center;'>".$v."</td>";
    }   
    $html .= "</tr>";    
    foreach ($data as $key =>$value) {
        $html .= "<tr>";
        foreach($value as $aa){
            $html .= "<td>".$aa."</td>";
        }
        $html .= "</tr>";
        }
    $html .= "</table></body></html>";
    echo $html;
    exit;
}