C/C++教程

Yii2实现Excel大量数据快速导出

本文主要是介绍Yii2实现Excel大量数据快速导出，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

效果图：
在这里插入图片描述
在这里插入图片描述
下载插件：PHP_XLSXWriter
PHP_XLSXWriter是一个小而强悍的Excel读写插件，它并没有PHPExcel功能丰富，很多高级操作比如冻结表头，并不具备，但是它导出速度非常快，非常适合于数据量特别大,报表格式不是很复杂的导出需求。

1.TestController.php

 /**
     * 用户数据导出
     * @return String
     * */
    public function actionExport(){
        if(Yii::$app->request->isPost) {
            $post = Yii::$app->request->post();
            $data[] = ['id'=>1,'username'=>'earnest','nickname'=>'御风','dept_name'=>'信息部'];
            
            $this->xlsWriteExcel($post['field'], $post['title'], $data['data'], '用户信息_');
        }

        $header = [
            'id' => 'ID',
            'username' => '用户名',
            'nickname' => '昵称',
            'created_at' => '创建时间',
            'updated_at' => '更新时间',
            'dept_name' => '部门名称',
            'status' => '状态'
        ];

        return $this->render('export',[
            'header' => $header,
            'url'=>Yii::$app->request->get('url').'export',
            'param'=>str_replace('"',"'",json_encode(Yii::$app->request->get()))
        ]);
    }

public function xlsWriteExcel($field,$header,$data,$fileName='自定义导出'){
    require_once Yii::getAlias('@vendor') . '/phpxlsxwriter/vendor/autoload.php';
    header("Content-Type: text/html;charset=utf-8");
    error_reporting(E_ALL);
    set_time_limit(0);
    ob_start();
    $sheet = 'Sheet1';
    $writer = new \XLSXWriter();
    $field = explode(',',$field);
    $header = explode(',',$header);
    $header = array_fill_keys($header,"string");
    $styles1 = array(
        'font'=>'宋体','font-size'=>10,'font-style'=>'bold', 'fill'=>'#eee',
        'halign'=>'center','border'=>'left,right,top,bottom'
    );
    $title = array_keys($header);
    $writer->writeSheetRow($sheet, $title, $styles1);
    if(!empty($data)){
        foreach ($data as $k=>$v){
            foreach ($field as $k1=>$v1){
                if(isset($v[$v1])){
                    $rows[$k1] = $v[$v1];
                }else{
                    $rows[$k1] = '';
                }
            }
            $writer->writeSheetRow($sheet, $rows, $styles1);
        }
    }
    $filename = $fileName.date('YmdHis',time()).'.xlsx';
    $tempFile = tempnam(sys_get_temp_dir(), 'excel');
    $writer->writeToFile($tempFile);
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename=' . $filename);
    readfile($tempFile);
    unlink($tempFile);
}

2.export.php

<?php
$this->registerJs($this->render('js/export.js'));
?>
<style>
    .layui-select-title{
        position: relative;
    }
    .option-checkbox-container{
        position: absolute;
        top:50px;
        left: 0px;
        padding: 10px;
        background: white;
        margin-top: -10px;
        z-index: 999;
        display: none;
    }
    .layui-filter-panel{
        max-height:220px;
        overflow-y: auto;
    }
    .layui-filter-panel::-webkit-scrollbar {
        width: 6px;
        height: 6px;
    }
</style>
<body class="pear-container">
<form class="layui-form" action="" onsubmit="return false">
    <div class="layui-form-item">
        <input type="hidden" id="param" value="<?php echo $param?>">
        <input type="hidden" id="url" value="<?php echo $url?>">
        <label class="layui-form-label test">表头</label>
        <div class="layui-col-xs5">
            <div class="layui-select-title">
                <div class="layui-form-select">
                    <div class="layui-select-title">
                        <input id="diy_header" type="text" placeholder="自定义表头"  readonly class="layui-input">
                        <i class="layui-edge"></i>
                    </div>
                </div>
                <div class="option-checkbox-container ">
                    <ul class="layui-filter-panel">
                        <?php foreach ($header as $k=>$v): ?>
                            <li>
                                <input type="checkbox" name="<?php echo $k?>" title="<?php echo $v?>" checked lay-skin="primary" >
                                <div class="layui-unselect layui-form-checkbox layui-form-checked" lay-skin="primary">
                                    <span><?php echo $v?></span>
                                    <i class="layui-icon layui-icon-ok"></i>
                                </div>
                            </li>
                        <?php endforeach;?>
                    </ul>
                </div>
            </div>
        </div>
        <div >
            <button style="margin-left:10px;" class="layui-btn pear-btn-primary" id="export">导出</button>
        </div>
    </div>
</form>
</body>

3.export.js

"<?php use yii\helpers\Url;?>"
$(function(){
    $('#diy_header').on('click',function (){
        var display = $('.option-checkbox-container').css('display');
        if(display == 'none'){
            $('.option-checkbox-container').css('display','inline');
            $(this).parent().parent().find('div').addClass('layui-form-selected layui-form-selectup');
        }else{
            $('.option-checkbox-container').css('display','none');
            $(this).parent().parent().find('div').removeClass('layui-form-selected layui-form-selectup');
        }
    });
    $('.layui-filter-panel li').on('click',function (){
        if($(this).find('input:checkbox').is(':checked')){
            $(this).find('input:checkbox').removeAttr('checked');
            $(this).find('div').removeClass('layui-form-checked');
        }else{
            $(this).find('input:checkbox').attr('checked','');
            $(this).find('div').addClass('layui-form-checked');
        }
    });
    $('#export').on('click',function (){
        var title = [];
        var field = [];
        $('.layui-filter-panel li input[type="checkbox"][checked]').each(function(){
            field.push($(this).attr('name'));
            title.push($(this).attr('title'));
        })
        var url = $('#url').val();
        Post("<?php echo Url::toRoute('"+url+"');?>",[
            { name:'field',value:field.join(',')},
            { name:'title',value:title.join(',')},
            { name:'param',value:$('#param').val()}
        ]);
    });
    /*
    *功能： 模拟form表单的提交
    *参数： URL 跳转地址 PARAMTERS 参数
    */
    function Post(URL, PARAMTERS) {
        //创建form表单
        var temp_form = document.createElement("form");
        temp_form.action = URL;
        //如需打开新窗口，form的target属性要设置为'_blank'
        temp_form.target = "_blank";
        temp_form.method = "post";
        temp_form.style.display = "none";
        //添加参数
        for (var item in PARAMTERS) {
            var opt = document.createElement("textarea");
            opt.name = PARAMTERS[item].name;
            opt.value = PARAMTERS[item].value;
            temp_form.appendChild(opt);
        }
        document.body.appendChild(temp_form);
        //提交数据
        temp_form.submit();
    }
});

完毕！

这篇关于Yii2实现Excel大量数据快速导出的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
栏目导航