YII2 data export Excel export and export data when columns more than 26 columns when the workaround, yii226 column _php tutorial

YII2 data export Excel export and export data when the column more than 26 columns when the workaround, yii226 column

Author: White Wolf Source: http://www.manks.top/article/yii2_excel_extension This article copyright belongs to the author, welcome reprint, but without the author's consent must retain this paragraph statement, and in the article page obvious location gives the original text connection, Otherwise, the right to pursue legal liability is retained.

Let's summarize what we're going to say next:

Data list page to export Excel data,

1, can be based on the GridView filter to search data and export

2, you can extend the data export time to export data directly

First look at the controller layer, receive the GridView parameters and do the splicing processing

PHP Controller

Parameter export
$paramsExcel = "; This parameter is the parameter that controls the Receive view Layer Gridview::widget filter
if (($params = Yii:: $app->request->queryparams))
{
    if ($params && isset ($params [' Xxsearch ']) && $params [' Xxsearch '])
    {
        
        {
            if ($v)
            {
                $paramsExcel. = $k. ' = '. $v. ' & ';
            }
        }

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

What do we need to do to see the view layer?

HTML buttons on the PHP input page

        
 
  ' Btn btn-success '])?>
        Start time:
        End Time:

The above javascript:ed () method is as follows, notice here we splicing the controller layer passed over the parameters, and self-expanding time to search data

Data export
function Ed ()
{
var paramsexcel = "
 ",
url = '/xx/export-data ',//Here XX is the Controller
StartTime = $.trim ($ (' input[name=start_time] '). Val ()),
EndTime = $.trim ($ (' input[name=end_time] '). Val ()),
temp = ';
    
The parameters of the view Layer Gridview::widget filter need to be fused with the parameters of our own extension.
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 is the address of our exported data, the above processing is only the processing of parameters
}

Let's take a look at the action of exporting the data, which is named the Actionexportdata of the controller layer, where Commonfunc is the public method of the global nature we introduce

Use Common\components\commonfunc;
/**
* @DESC Data export
*/
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;
        
Querying data
$data = ...

if ($data)
{
Data processing
}
        
$header = [' id ', ' User account ', ' creation Time ']; Export headers for Excel

Commonfunc::exportdata ($data, $header, ' table header ', ' File name ');
}

The above Commonfunc::expertdata method is our underlying extension Php-excel class encapsulation public method, here is the key we want to say, about Phpexcel class files can be downloaded by themselves

No1. We walked a small corner and shared it with everyone.

The Commonfunc::expertdata method is as follows:

/**
* @DESC Data export
* @notice Max column is Z OR 26,overiload'll be ignored
* @notice Disadvantage: The number of columns to export data is greater than 26 times wrong
* @example
* $data = [1, ' xiaoming ', ' 25 '];
* $header = [' id ', ' name ', ' age '];
* 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;

Number of columns
$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" XLSX Test Document ");
$objPHPExcel->getproperties ()->setsubject ("Office" XLSX Test Document ");
$objPHPExcel->getproperties ()->setdescription ("Test document for Office-XLSX, generated using PHP classes.");

ADD some data
$objPHPExcel->setactivesheetindex (0);

Add Head
$cheader = count ($header);
for ($ci = 1; $ci <= $cheader; $ci + +)
{
if ($ci >) break;
$objPHPExcel->getactivesheet ()->setcellvalue ($captions [$ci-1]. ' 1 ', $header [$ci-1]);
}

Add data
$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 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 ');
    
}

The following is the final solution, but also a very useful data export solution

/**
* @DESC Data Guide
* @notice resolves an issue where the number of exported columns is too large
* @example
* $data = [1, ' xiaoming ', ' 25 '];
* $header = [' id ', ' name ', ' age '];
* 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" XLSX Test Document ");
$objPHPExcel->getproperties ()->setsubject ("Office" XLSX Test Document ");
$objPHPExcel->getproperties ()->setdescription ("Test document for Office-XLSX, generated using PHP classes.");

ADD some data
$objPHPExcel->setactivesheetindex (0);

Add Head
$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)//Line Write
{
$span = 0;
foreach ($rows as $keyName = + $value)//Column Write
{
$j = \phpexcel_cell::stringfromcolumnindex ($span);
$objActSheet->setcellvalue ($j. $column, $value);
$span + +;
}
$column + +;
}

Rename Sheet
$objPHPExcel->getactivesheet ()->settitle ($title);

Save Excel 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 ');
    
}

http://www.bkjia.com/PHPjc/1121569.html www.bkjia.com true http://www.bkjia.com/PHPjc/1121569.html techarticle YII2 Data export Excel export and export data when the column more than 26 columns when the solution, yii226 column Author: White Wolf Source: http://www.manks.top/article/yii2_excel_extension This article copyright ...

