Yii2 how to export data in excel and export data with more than 26 columns-White Wolf stack

Yii2 data export excel export and export data when the column more than 26 columns when the solution-White Wolf stack author: White Wolf Source: http://www.manks.top/article/yii2_excel_extension this article copyright to the author, welcome to reprint, however, this statement must be retained without the author's consent and the original article connection should be given clearly on the article page; otherwise, the legal liability should be held accountable.

Let's summarize the following content:

Export excel data on the data list page,

1. you can search and export data based on the filter of the GridView.

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

// View the controller layer, receive the GridView parameters, and splice the parameters.

Php controller

// Export parameters
$ ParamsExcel = ''; // This parameter controls the parameters of the widget filter at the view layer.
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 what we need to do at the view layer

Html button on the php input page

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

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

// Export data
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 = '';
    
// You Need to splice the parameters of the view-layer GridView: widget filter with our own extended parameters.
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; // The url is the address of the exported data. the above processing is only for parameter processing.
}

// Next let's take a look at the export data action, which is now named actionExportData at the controller layer. CommonFunc is a public method of global Nature introduced by us.

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;
        
// Query data
$ Data = ......

If ($ data)
{
// Data processing
}
        
$ Header = ['id', 'user account', 'creation time']; // export the excel header

CommonFunc: exportData ($ data, $ header, 'header ', 'File name ');
}

The above CommonFunc: expertData method is a public method for extending the php-excel class package at the bottom layer. here is the key. you can download PHPExcel files by yourself.

No1. we took a small bend and shared it with you.

CommonFunc: expertData method:

/**
* @ DESC data export
* @ Notice max column is z OR 26, overiload will be ignored
* @ Notice disadvantage: an error is returned when the number of exported data columns exceeds 26.
* @ 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', '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 );

// Add a header
$ Cheader = count ($ header );
For ($ ci = 1; $ ci <= $ cheader; $ ci ++)
{
If ($ ci> 25) 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 2007 file
$ ObjWriter = new \ PHPExcel_Writer_Excel2007 ($ objPHPExcel );

Header ('pragma: public ');
Header ("Content-Type: application/x-msexecl; name = \" specify parameter filename=.xls \"");
Header ("Content-Disposition: inline; filename = \" specify parameter filename=.xls \"");

$ ObjWriter-> save ('php: // output ');
    
}

The following is the final solution and a very practical data export solution.

/**
* @ DESC data import
* @ Notice solves the problem of excessive number of imported columns
* @ 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 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 );

// Add a header
$ 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) // write data to the row
{
$ Span = 0;
Foreach ($ rows as $ keyName => $ value) // write a column
{
$ 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 = \" specify parameter filename=.xls \"");
Header ("Content-Disposition: inline; filename = \" specify parameter filename=.xls \"");

$ ObjWriter-> save ('php: // output ');
    
}