yii2 数据导出 excel导出以及导出数据时列超过26列时解决办法
先概括下我们接下来要说的大致内容：
数据列表页面导出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, '&');
}
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");
}
