yii2 数据导出 excel导出以及导出数据时列超过26列时解决办法
作者：白狼 出处：http://www.manks.top/article/yii2_excel_extension? 本文版权归作者，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
先概括下我们接下来要说的大致内容：
数据列表页面导出excel数据，
1、可以根据GridView的filter进行搜索数据并导出
2、可以自行扩展数据导出的时间直接导出数据
//先来看controller层，接收GridView参数并做拼接处理
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 />}//看view层我们需要做什么
php 输入页面上的html按钮
<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>上面javascript:ed()方法如下，注意这里我们拼接了controller层传递过来的参数,并自行扩展了时间进行搜索数据
//数据导出<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 />}//下面我们来看下导出数据的action，暂且命名为controller层的 actionExportData，其中CommonFunc是我们引入的全局性质的公共方法
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 /> }
上面CommonFunc::expertData方法是我们底层扩展php-excel类封装的公共方法，这里才是我们要说的关键，关于 PHPExcel类文件大家可自行下载
No1. 我们走了一个小的弯，分享给大家看看
CommonFunc::expertData方法如下：
/**<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([email protected]').'/components/phpexcel/PHPExcel.php');<br /> require(Yii::getAlias([email protected]').'/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 /> }
下面是最终的解决方案，也是非常实用的数据导出方案
/**<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([email protected]').'/components/phpexcel/PHPExcel.php');<br /> require(Yii::getAlias([email protected]').'/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 /> }
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
