PHP tp5 导入 导出大数据处理方法 csv

 首先新建PHP文件

在thinkphp/library/think下新建Csv.php

<?php

namespace think;

class Csv
{

    //导出csv文件
    public function put_csv(&$data, $titleList = array(), $fileName = '')
    {
        ini_set("max_execution_time", "3600");
        $csvData = '';

        // 标题
        $nums = count($titleList);
        for ($i = 0; $i < $nums - 1; $i++)
        {
            $csvData .= '"' . $titleList[$i] . '",';
        }
        $csvData .= '"' . $titleList[$nums - 1] . "\"\r\n";

        foreach ($data as $key => $row)
        {
            $i = 0;
            foreach ($row as $_key => $_val)
            {
                $_val = str_replace("\"", "\"\"", $_val);
                if ($i < ($nums - 1))
                {
                    $csvData .= '"' . $_val . '",';
                }
                elseif ($i == ($nums - 1))
                {
                    $csvData .= '"' . $_val . "\"\r\n";
                }
                $i++;
            }
            unset($data[$key]);
        }

        $csvData = mb_convert_encoding($csvData, "cp936", "UTF-8");
        $fileName = empty($fileName) ? date('Y-m-d-H-i-s', time()) : $fileName;
        $fileName = $fileName . '.csv';
        header("Content-type:text/csv;");
        header("Content-Disposition:attachment;filename=" . $fileName);
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Expires:0');
        header('Pragma:public');
        echo $csvData;
        exit();
    }

    // csv导入,支持10W+数据
    public function input_csv($filename,$field,$table) {
        //$field 打散为数组
        $fieldArr = explode(',', $field);
        //CSV转数组 $excelData
        $content = trim(file_get_contents($filename));
        $excelData = explode("\n",$content); //把字符串打散为数组
        //删除第一行标题
        array_splice($excelData, 0, 1);

        // 将这个大量数据（10W+）的数组分割成5000一个的小数组。这样就一次批量插入5000条数据。mysql 是支持的。
        $chunkData = array_chunk($excelData ,5000);
        $count = count($chunkData);
        for ($i = 0; $i < $count; $i++) {
            $insertRows = array();
            foreach($chunkData[$i] as $value){
                //转码，有中文不要用这种方式，会出乱码
                //$string = mb_convert_encoding(trim(strip_tags($value)), 'utf-8', 'gbk');
                $string = trim(strip_tags($value));//转码
                $v = explode(',', $string); //把字符串打散为数组
                $row = array();
                for($j=0;$j<count($fieldArr);$j++){
                    $row[$fieldArr[$j]] = $v[$j];
                }
                $sqlString = "('".implode( "','", $row )."')"; //把数组元素组合为字符串 批量
                $insertRows[] = $sqlString;
            }
            $result = $this->addData($table,$insertRows,$field); //批量将sql插入数据库。
        }
        return $result;
    }

    public function addData($table,$rows,$field)
    {
        if(empty($rows)){
            return false;
        }
        //数据量较大,采取批量插入
        $data = implode(',', $rows);
        $sql = "INSERT IGNORE INTO $table ($field) VALUES {$data}";
        $result = Db::execute($sql);
        return $result;
    }
}

然后控制器代码

<?php


namespace app\index\controller;

use think\Controller;
use think\Db;
use think\Request;
use think\Csv;

class Css extends Controller
{
    public  function index(){
        return $this->fetch();
    }

    public  function import(){
        return $this->fetch();
    }
    public function addStory()
    {
        //表名
        $table = 'tp_cs';

        //清空表中数据，自己权衡是否使用
        //$sql = "TRUNCATE TABLE $table";
        //Db::execute($sql);

        //数据库中字段名
        $field = 'phone,username,nickname,end_time,level';

        //上传的CSV文件
        $files = Request::instance()->file("file");
        $info = $files->move(ROOT_PATH . 'public' . DS . 'uploads');
        $filename = ROOT_PATH.'public'.DS.'uploads/'.$info->getSaveName();

        $csv = new Csv();

        $result = $csv->input_csv($filename,$field,$table);

        if ($result){
           $this->success('导入成功');

        }
        $this->error('导入失败');
    }

//接受表名并导出表格
    public function putStory()
    {
        //导出csv表格
        $csv = new Csv();
        //使用原生写法，thinkPHP查询方法会有额外数据，导致导出失败
        $table = 'tp_cs';
        $sql = "select * from $table";
        $data = Db::query($sql);
        $titleList = array('手机号','用户名','昵称','结束时间','等级');

        $csv->put_csv($data, $titleList);

    }
}

对应的 view 文件

<a href="{:url('putStory')}">导出文件</a>
<a href="{:url('import')}">导入数据</a>

<form action="{:url('addStory')}" method="post" enctype="multipart/form-data">
文件：<input type="file" name="file" ><br/>
    <input type="submit" value="提交">
    
</form>