限时 5折! 详情

yii2 数据导出 excel导出以及导出数据时列超过26列时解决办法

博主推荐:yii2实战式教程是一套简单易学、幽默风趣、干货多多、众人大赞的高质量教程,囊括 yii2基础入门、yii2高级进阶的很多知识点,可以说是迄今最好的yii2系列教程,真的不容错过哦~

先概括下我们接下来要说的大致内容:

数据列表页面导出excel数据,

1、可以根据GridView的filter进行搜索数据并导出

2、可以自行扩展数据导出的时间直接导出数据

//先来看controller层,接收GridView参数并做拼接处理

php controller

//传参导出
$paramsExcel = ''; //这个参数是控制接收view层GridView::widget filter的参数
if ( ($params = Yii::$app->request->queryParams) )
{
    if ($params && isset($params['xxSearch']) && $params['xxSearch'])
    {
        foreach ($params['xxSearch'] as $k => $v) 
        {
            if ($v)
            {
                $paramsExcel .= $k.'='.$v.'&';
            }
        }


    }
    $paramsExcel = rtrim($paramsExcel, '&');
}

//看view层我们需要做什么

php 输入页面上的html按钮

<div style="margin-bottom: 30px;">
        <?= Html::a("导出", "javascript:ed();", ["class" => "btn btn-success"]) ?>
        开始时间:<input type="text" name="start_time" />
        结束时间:<input type="text" name="end_time" />
</div>

上面javascript:ed()方法如下,注意这里我们拼接了controller层传递过来的参数,并自行扩展了时间进行搜索数据

//数据导出
function ed ()
{
    var paramsExcel = "<?php echo $paramsExcel; //controller传递过来的参数?>", 
        url = "/xx/export-data", //此处xx是控制器
        startTime = $.trim($("input[name=start_time]").val()), 
        endTime = $.trim($("input[name=end_time]").val()),
        temp = "";
    //需要把view层GridView::widget filter的参数与我们自行扩展的参数拼接融合
    if (paramsExcel)
    {
        temp += "?"+paramsExcel;
        if (startTime)
            temp += "&start_time="+startTime;
        if (endTime)
            temp += "&end_time="+endTime;
    } 
    else if (startTime)
    {
        temp += "?start_time="+startTime;
        if (endTime)
            temp += "&end_time="+endTime;
    }
    else if (endTime)
    {
        temp += "?end_time="+endTime;
    }
    url += temp;
    window.location.href=url; //url是我们导出数据的地址,上面的处理都只是进行参数的处理
}

//下面我们来看下导出数据的action,暂且命名为controller层的 actionExportData,其中CommonFunc是我们引入的全局性质的公共方法

use common\components\CommonFunc;
/**
 * @DESC 数据导出
 */
public function actionExportData ()
{
    $where = "1";
    $temp = "";
    if ($_GET)
    {
        foreach ($_GET as $k => $v)
        {
            if ($k == "start_time")
            {
                $t = date("Y-m-d", strtotime($v))." 00:00:00";
                $temp .= "create_time >= \"". $t . "\" AND ";
            }
            elseif ($k == "end_time")
            {
                $t = date("Y-m-d", strtotime($v))." 23:59:59";
                $temp .= "create_time <= \"". $t . "\" AND ";
            }
            else
            {
                $temp .= $k . "=\"" . $v . "\" AND ";
            }
        }
        $temp = rtrim($temp, " AND");
    }
    if ($temp) $where .= " AND ".$temp;
    //查询数据
    $data = ......
    if ($data)
    {
        //数据处理
    }
    $header = ["id", "用户账号", "创建时间"]; //导出excel的表头
    CommonFunc::exportData($data, $header, "表头", "文件名称");
}


上面CommonFunc::expertData方法是我们底层扩展php-excel类封装的公共方法,这里才是我们要说的关键,关于 PHPExcel类文件大家可自行下载

No1. 我们走了一个小的弯,分享给大家看看

CommonFunc::expertData方法如下:

/**
 *  @DESC 数据导出 
 *  @notice max column is z OR 26,overiload will be ignored
 *  @notice 缺点:导出数据的列数大于26时报错
 *  @example 
 *  $data = [1, "小明", "25"];
 *  $header = ["id", "姓名", "年龄"];
 *  Myhelpers::exportData($data, $header);
 *  @return void, Browser direct output
 */
public static function exportData ($data, $header, $title = "simple", $filename = "data")
{
    //require relation class files
    require(Yii::getAlias("@common")."/components/phpexcel/PHPExcel.php");
    require(Yii::getAlias("@common")."/components/phpexcel/PHPExcel/Writer/Excel2007.php");
    if (!is_array ($data) || !is_array ($header)) return false;
    //列数
    $captions = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
    $objPHPExcel = new \PHPExcel();
    // Set properties
    $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
    $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
    $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
    $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
    $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
    // Add some data
    $objPHPExcel->setActiveSheetIndex(0);
    //添加头部
    $cheader = count($header);
    for ($ci = 1; $ci <= $cheader; $ci++) 
    {
        if ($ci > 25) break; 
        $objPHPExcel->getActiveSheet()->SetCellValue($captions[$ci-1]."1", $header[$ci-1]);
    }
    //添加数据
    $i = 2;
    $count = count($data);
    foreach ($data as $v)
    {
        $j = 0;
        foreach ($v as $_k => $_v)
        {
            $objPHPExcel->getActiveSheet()->SetCellValue($captions[$j].$i, $_v);
            $j++;
        }
        if ($i <= $count)
        {
            $i ++;
        }
    }
    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle($title);
    // Save Excel 2007 file
    $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
    header("Pragma:public");
    header("Content-Type:application/x-msexecl;name=\"{$filename}.xls\"");
    header("Content-Disposition:inline;filename=\"{$filename}.xls\"");
    $objWriter->save("php://output");
}

下面是最终的解决方案,也是非常实用的数据导出方案

/**
 *  @DESC 数据导
 *  @notice 解决了上面导出列数过多的问题
 *  @example 
 *  $data = [1, "小明", "25"];
 *  $header = ["id", "姓名", "年龄"];
 *  Myhelpers::exportData($data, $header);
 *  @return void, Browser direct output
 */
public static function exportData ($data, $header, $title = "simple", $filename = "data")
{
    //require relation class files
    require(Yii::getAlias("@common")."/components/phpexcel/PHPExcel.php");
    require(Yii::getAlias("@common")."/components/phpexcel/PHPExcel/Writer/Excel2007.php");
    if (!is_array ($data) || !is_array ($header)) return false;
    $objPHPExcel = new \PHPExcel();
    // Set properties
    $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
    $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
    $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
    $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
    $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
    // Add some data
    $objPHPExcel->setActiveSheetIndex(0);
    //添加头部
    $hk = 0;
    foreach ($header as $k => $v)
    {
        $colum = \PHPExcel_Cell::stringFromColumnIndex($hk);
        $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum."1", $v);
        $hk += 1;
    }
    $column = 2;
    $objActSheet = $objPHPExcel->getActiveSheet();
    foreach($data as $key => $rows)  //行写入
    {
        $span = 0;
        foreach($rows as $keyName => $value) // 列写入
        {
            $j = \PHPExcel_Cell::stringFromColumnIndex($span);
            $objActSheet->setCellValue($j.$column, $value);
            $span++;
        }
        $column++;
    }
    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle($title);
    // Save Excel 2007 file
    $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
    header("Pragma:public");
    header("Content-Type:application/x-msexecl;name=\"{$filename}.xls\"");
    header("Content-Disposition:inline;filename=\"{$filename}.xls\"");
    $objWriter->save("php://output");
}
作者 白狼
本文版权归作者,欢迎转载,但未经作者同意必须保留 此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。