yii2 data export excel export and the solution when the columns exceed 26 columns when exporting data, yii226 columns

Yii2 data export excel export and the solution when the columns exceed 26 columns when exporting data, yii226 columns

Author: Bailang Source: http://www.manks.top/ article/yii2_excel_extension The copyright of this article belongs to the author, and you are welcome to reprint it. However, this statement must be retained without the author's consent, and a link to the original text must be provided in an obvious position on the article page. Otherwise, we reserve the right to pursue legal liability.

Let’s first summarize what we are going to say next:

Export excel data from the data list page,

1. Data can be searched and exported based on GridView’s filter

2. You can expand the data export time and export the data directly

//Let’s look at the controller layer first, receive GridView parameters and perform splicing processing

php controller

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

Copy after login

//Look at what we need to do in the view layer

php input html button on the page

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

Copy after login

The above javascript:ed() method is as follows. Note that here we splice the parameters passed by the controller layer and extend the time to search for data

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

Copy after login

//Let’s take a look at the action to export data, temporarily named actionExportData of the controller layer, where CommonFunc is the global public method we introduced

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

Copy after login

The above CommonFunc::expertData method is the public method for our bottom-level extension of the php-excel class encapsulation. This is the key we want to talk about. You can download the PHPExcel class file by yourself

No1. We took a small detour and share it with everyone

CommonFunc::expertData method is as follows:

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

Copy after login

The following is the final solution, which is also a very practical data export solution

/**<br />     *  @DESC 数据导<br />     *  @notice 解决了上面导出列数过多的问题<br />     *  @example <br />     *  $data = [1, '小明', '25'];<br />     *  $header = ['id', '姓名', '年龄'];<br />     *  Myhelpers::exportData($data, $header);<br />     *  @return void, Browser direct output<br />     */<br />    public static function exportData ($data, $header, $title = 'simple', $filename = 'data')<br />    {<br />        //require relation class files<br />        require(Yii::getAlias('@common').'/components/phpexcel/PHPExcel.php');<br />        require(Yii::getAlias('@common').'/components/phpexcel/PHPExcel/Writer/Excel2007.php');<br />    <br />        if (!is_array ($data) || !is_array ($header)) return false;<br /><br />        $objPHPExcel = new \PHPExcel();<br /><br />        // Set properties<br />        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");<br />        $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");<br />        $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");<br />        $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");<br />        $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");<br /><br />        // Add some data<br />        $objPHPExcel->setActiveSheetIndex(0);<br /><br />        //添加头部<br />        $hk = 0;<br />        foreach ($header as $k => $v)<br />        {<br />            $colum = \PHPExcel_Cell::stringFromColumnIndex($hk);<br />            $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);<br />            $hk += 1;<br />        }<br /><br />        $column = 2;<br />        $objActSheet = $objPHPExcel->getActiveSheet();<br />        foreach($data as $key => $rows)  //行写入<br />        {<br />            $span = 0;<br />            foreach($rows as $keyName => $value) // 列写入<br />            {<br />                $j = \PHPExcel_Cell::stringFromColumnIndex($span);<br />                $objActSheet->setCellValue($j.$column, $value);<br />                $span++;<br />            }<br />            $column++;<br />        }<br /><br />        // Rename sheet<br />        $objPHPExcel->getActiveSheet()->setTitle($title);<br /><br />        // Save Excel 2007 file<br />        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);<br /><br />        header('Pragma:public');<br />        header("Content-Type:application/x-msexecl;name=\"{$filename}.xls\"");<br />        header("Content-Disposition:inline;filename=\"{$filename}.xls\"");<br /><br />        $objWriter->save('php://output');<br />    <br />    }

Copy after login