Yii2 how to export data in excel or when the number of columns exceeds 26 when exporting data, yii226 column _ PHP Tutorial

Yii2 is a solution for exporting data from an excel file and exporting data with more than 26 columns, and yii226 columns. Yii2 data export excel export and export data when the column exceeds 26 columns solution, yii226 column author: White Wolf Source: www. manks. toparticipates ii2_excel_extension this article copyright yii2 data export excel export and export data when the column exceeds 26 columns solution, yii226 columns

Author: White Wolf Source: http://www.manks.top/article/yii2_excel_extension this article copyright to the author, welcome to reprint, but without the author's consent must retain this paragraph of the statement, and in the article page clearly given the original connection, otherwise, you are entitled to pursue legal liability.

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

Http://www.bkjia.com/PHPjc/1121569.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/1121569.htmlTechArticleyii2 data export excel export and export data when the column exceeds 26 when the solution, yii226 column author: White Wolf Source: http://www.manks.top/article/yii2_excel_extension this article copyright...