How to export Excel tables in the YII2 framework

Recently in the study of the YII framework of PHP, like, encountered the problem of exporting Excel, studied, there is the following article, this article is mainly about the YII2 framework in the Excel table export related data, the article through the sample code introduced in very detailed, Need friends can reference, below to see together.

Objective

Table import and export is a feature that we often encounter in the daily development, coincidentally in the recent project to achieve the function of the table output, and the previous use of TP has also done, so think of this function compared to a variety of opportunities to collate, convenient later when needed, or have the need of friends reference learning, The following words do not say much, come together to see the detailed introduction:

This article was developed based on the YII2 framework, and different frameworks might need to be changed

A. Normal Excel format table output

First, the most common form of exporting. xls format. First look at how the table looks on the site

Here you can see that the entire table is 7 columns altogether. Here's a look at the implementation of the Code.

1.controller file

Export statistics Public Function actionstatistics () {//Set memory Ini_set ("Memory_limit", "2048M"); set_time_limit (0);//get user id $id = Yii :: $app->user->identity->getid (); Go to User table for user information $user = employee::find ()->where ([' id ' = ' + $id])->one (); Get the message (time, company ID, etc.) $params = Yii:: $app->request->get (); $objectPHPExcel = new \phpexcel (); Sets the output of the table header $objectPHPExcel->setactivesheetindex ()->setcellvalue (' A1 ', ' agency '); $objectPHPExcel->setactivesheetindex ()->setcellvalue (' B1 ', ' income '); $objectPHPExcel->setactivesheetindex ()->setcellvalue (' C1 ', ' cost '); $objectPHPExcel->setactivesheetindex ()->setcellvalue (' D1 ', ' Number of manuscripts '); $objectPHPExcel->setactivesheetindex ()->setcellvalue (' E1 ', ' gross margin (revenue-cost) '); $objectPHPExcel->setactivesheetindex ()->setcellvalue (' F1 ', ' gross margin (gross profit/income) *100% '); $objectPHPExcel->setactivesheetindex ()->setcellvalue (' G1 ', ' ARPU value '); Jump to recharge this model file statistics method to process the data $data = Recharge::statistics ($params); Specifies the number of rows to start output data $n = 2; ForeacH ($data as $v) {$objectPHPExcel->getactivesheet ()->setcellvalue (' A '. $n), $v [' company_name ']); $objectPHPExcel->getactivesheet ()->setcellvalue (' B '. ( $n), $v [' company_cost ']); $objectPHPExcel->getactivesheet ()->setcellvalue (' C '. ( $n), $v [' cost ']; $objectPHPExcel->getactivesheet ()->setcellvalue (' D '. ( $n), $v [' num ']); $objectPHPExcel->getactivesheet ()->setcellvalue (' E '. ( $n), $v [' Gross_margin ']); $objectPHPExcel->getactivesheet ()->setcellvalue (' F '. ( $n), $v [' gross_profit_rate ']); $objectPHPExcel->getactivesheet ()->setcellvalue (' G '. ( $n), $v [' ARPU ']); $n = $n +1; } Ob_end_clean (); Ob_start (); Header (' Content-type:application/vnd.ms-excel '); Set the output file name and format header (' Content-disposition:attachment;filename= ' agent statistics '. Date ("Ymdhis"). XLS "'); Export. xls format using EXCEL5, if you want to export. xlsx needs to use Excel2007 $objWriter = \phpexcel_iofactory::createwriter ($objectPHPExcel, ' Excel5 '); $objWriter->save (' php://output '); Ob_end_flush (); Emptying the data cache unset ($DATA);}

2.model File

 <?php namespace App\models;//model Layer's namespace//note to refer to Yii's arrayhelper use Yii\helpers\arrayhelper; Use Yii;  Class Recharge extends \yii\db\activerecord {//excel One export bar number const EXCEL_SIZE = 10000; Statistics export public static function statistics ($PARAMS) {//Export time condition if (empty ($params [' min ')]) {$date _max = date ("Y-m-d", Strtotime ("-1 day")); $date _min = Date ("y-m-d", Strtotime (" -31 Day"); }else{$date _min = $params [' min ']; $date _max = $params [' Max '];} $where = '; $where. = ' (' issue_date ' between '. ') \ '. $date _min. '. ' and '. ' \ '. $date _max. ' \')'; Find specified data $sql = ' Select article.company_id, Article.cost, article.company_cost from article WHERE article.status=2 and '. $ where $article = Article::findbysql ($sql)->asarray ()->all (); $article = Arrayhelper::index ($article, NULL, ' company_id '); $companys = []; foreach ($article as $key = + $v) {if (empty ($key)) {continue;} else{$number = count ($v), $company = Company::find ()->where ([' id ' = $key])->select (' name ')->one (); $ Company_Name = $company[' name ']; $cost = 0; $company _cost = 0; foreach ($v as $n) {$cost + = $n [' cost ']; $company _cost + = $n [' Company_cost '];} if ($company _cost = = 0) {$company _cost = 1; }//Note that the data is stored in the same order as the output table $companys [] = [//company name ' company_name ' + $company _name,//Revenue ' company_cost ' + $company _cost,//costs ' cost ' = $cost,//Number of manuscripts ' num ' + $number,//Gross margin ' gross_margin ' = $company _cost-$cost,//gross margin ' gross_pro Fit_rate ' = Round (($company _cost-$cost)/$company _cost*100,2). ' % ',//arpu value ' ARPU ' = round ($company _cost/$number, 2),]; }} return $companys; }}

The effect of the final export (the cell size is adjusted after export) can be seen as basic as the Web page displays.

Two. Big Data table Export

At this time the boss said, we can not only look at the sum of data, it is best to give detailed data also guide out. Now that the boss has spoken, do it. Or the first way to do it, the results suggest that I PHP crashes, and then try to find the prompt to write bytes exceeded. Open PHP configuration file php.ini

Memory_limit = 128M

It should be sufficient to find that the default memory has been given to 128M. So I opened the database a look, ho!

Close to 830,000 data to query and export, it is not a problem! What to do, so I Google a bit, found that for big data (more than 20,000) of the export, preferably in the form of. csv. Don't talk nonsense, directly on the code

1.controller file

Export manifest Public Function actioninventory () {Ini_set ("Memory_limit", "2048M"); set_time_limit (0); $id = Yii:: $app- User->identity->getid (); $user = Employee::find ()->where ([' id ' = ' = $id])->one ();  $params = Yii:: $app->request->get ();  Similarly, jump to the inventory method in the Recharge model file to process the data $data = Recharge::inventory ($params);  Set the exported file name $fileName = Iconv (' utf-8 ', ' GBK ', ' Dealer statistics list '. Date ("y-m-d")); Set Header $headlist = Array (' Agents ', ' article ID ', ' article title ', ' Media ', ' Statistics time range ', ' status ', ' Creation time ', ' Audit Time ', ' press time ', ' withdrawal time ', ' financial status ', ' cost ', ' Sales ', ' is the pre-payment media type ', ' order category ');  Header (' Content-type:application/vnd.ms-excel '); Indicates the exported format header (' Content-disposition:attachment;filename= '. $fileName. CSV "');  Header (' cache-control:max-age=0 ');  Open the PHP file handle, php://output indicates the direct output to the browser $fp = fopen (' php://output ', ' a '); Output Excel column name information foreach ($headlist as $key + $value) {//csv Excel supports GBK encoding, be sure to convert otherwise garbled $headlist [$key] = iconv (' Utf-8 ', ' g BK ', $value);  }//Writes data through Fputcsv to the file handle Fputcsv ($FP, $headlist); Every $limit line, refresh the output buffer, not too big, nor tooSmall $limit = 100000; Row-by-line data extraction without wasting memory foreach ($data as $k + $v) {//Refresh output buffer to prevent problems due to excessive data if ($k% $limit = = 0 && $k!=0) {OB_FL Ush (); Flush (); } $row = $data [$k]; foreach ($row as $key = + $value) {$row [$key] = iconv (' utf-8 ', ' GBK ', $value);} fputcsv ($fp, $row); }}

2.model file (because this Part I have to deal with too much, so only selected part of the code), in the query data that part, because the data to check more, so can be combined with my previous written on the MySQL Big Data query processing article to look at

List Export

public static function inventory ($params) {//Statistical time range if (!empty ($params [' min ')] &&!empty ($params [' Max ']) {$ti = Strtotime ($params [' Max ']) +3600*24; $max = Date (' y-m-d ', $ti); $time = $params [' min ']. ' -'. $params [' Max ']; $date _min = $params [' min ']; $date _max = $max; }else{$date _max = Date (' y-m-d '), $date _min = Date (' y-m-d ', Strtotime (" -31 Day"), $time = $date _min. '-'. $date _max;}//Query number According to if ($params [' state '] = = 1) {$where = '; $where. = ' and (' issue_date ' between '. ') \ '. $date _min. '. ' and '. ' \ '. $date _max. ' \')'; $map = ' Select Company.name, Article.id, Article.title, Media.media_name, Article.status, article.created, article. Audit_at, Article.issue_date, Article.back_date, Article.finance_status, Article.cost, Article.company_cost, MEDIA.I S_advance from article left join custom_package on custom_package.id = article.custom_package_id left join ' order ' on C ustom_package.order_id = ' order '. ' ID ' left JOIN company on company.id = article.company_id left JOIN media ON media.id = article.media_id where article.status=2 and ' order '. package=0 '. $where; Looking for the first part of the data, using the Asarray method allows us to find the result directly into the form of an array, with no additional data to occupy the space (note: I'm looking for three parts here because I want to look at three different kinds of data) $list 1 = article::findbysql ($ MAP)->asarray ()->all (); $where 2 = "; $where 2. = ' and (' issue_date ' between '. ') \ '. $date _min. '. ' and '. ' \ '. $date _max. ' \')'; $where 2. = ' and (' back_date ' > \ '. $date _max. ' \')'; $map 2 = ' Select Company.name, Article.id, Article.title, Media.media_name, Article.status, article.created, article . Audit_at, Article.issue_date, Article.back_date, Article.finance_status, Article.cost, Article.company_cost, media. Is_advance from article left join custom_package on custom_package.id = article.custom_package_id left join ' order ' on custom_package.order_id = ' order '. ' ID ' left JOIN company on company.id = article.company_id left join media on Media.id = article.media_id where article.status=3 and ' order '. Package=0 '. $where 2; Find the second part of the data $list 2 = Article::findbysql ($map 2)-≫asarray ()->all (); $where 3 = "; $where 3. = ' and (' issue_date ' between '. ') \ '. $date _min. '. ' and '. ' \ '. $date _max. ' \')'; $map 3 = ' Select Company.name, Article.id, Article.title, Media.media_name, Article.status, article.created, article . Audit_at, Article.issue_date, Article.back_date, Article.finance_status, Article.cost, Article.company_cost, media. Is_advance from article left join custom_package on custom_package.id = article.custom_package_id left join ' order ' on custom_package.order_id = ' order '. ' ID ' left JOIN company on company.id = article.company_id left join media on Media.id = article.media_id where article.status=5 '. $where 3; Find the third part of data $list 3 = Article::findbysql ($map 3)->asarray ()->all (); $list 4 = Arrayhelper::merge ($list 1, $list 2); $list = Arrayhelper::merge ($list 4, $list 3); }//The results are stored in the displayed order in the returned array if (!empty ($list)) {foreach ($list as $key = + $value) {//Agency $inventory [$key] [' company_name '] = $ value[' name ']; Article ID $inventory [$key] [' id '] = $value[' id ']; Article title $inventory [$key] [' title '] = $value [' title ']; Media $inventory [$key] [' media '] = $value [' Media_name ']; Statistical time $inventory [$key] = $time;  Status switch ($value [' status ']) {case 2: $inventory [$key] [' status '] = ' published '; Break  Case 3: $inventory [$key] [' status '] = ' retired draft '; Break  Case 5: $inventory [$key] [' status '] = ' abnormal manuscript '; Break }//Creation time $inventory [$key] [' created '] = $value [' Created ']; Audit time $inventory [$key] [' audit '] = $value [' Audit_at ']; Time $inventory [$key] [' issue_date '] = $value [' issue_date ']; Withdrawal time $inventory [$key] [' back_date '] = $value [' back_date '];  Financial status switch ($value [' finance_status ']) {case 0: $inventory [$key] [' finance_status '] = ' failed to settle period '; Break  Case 1: $inventory [$key] [' finance_status '] = ' may be settled '; Break  Case 2: $inventory [$key] [' finance_status '] = ' resource approval '; Break  Case 3: $inventory [$key] [' finance_status '] = ' financial approval '; Break  Case 4: $inventory [$key] [' finance_status '] = ' closed '; Break  Case 5: $inventory [$key] [' finance_status '] = ' failed '; BreakCase 6: $inventory [$key] [' finance_status '] = ' financial approval '; Break }//costs $inventory [$key] [' cost '] = $value [' costing ']; Sales $inventory [$key] [' company_cost '] = $value [' Company_cost '];  Whether it is a pre-sale switch ($value [' is_advance ']) {case 0: $inventory [$key] [' is_advance '] = ' no '; Break  Case 1: $inventory [$key] [' is_advance '] = ' yes '; Break  Case 2: $inventory [$key] [' is_advance '] = ' contract '; Break  }//Order class switch ($params [' state ']) {Case 1: $inventory [$key] [' order_type '] = ' time zone no cancellation of the order '; Break  Case 2: $inventory [$key] [' order_type '] = ' return order before the time zone is released '; Break  Case 3: $inventory [$key] [' order_type '] = ' time interval post release order '; Break  Case 4: $inventory [$key] [' order_type '] = ' time period before the release order ' in the time range; Break  Case 5: $inventory [$key] [' order_type '] = ' abnormal order '; Break }}}else{$inventory [0][' company_name '] = ' no data export ';} return $inventory;}

3. Export Results

Export quantity

The exported file

Basically, the entire process can be processed in 2-4 seconds.

Three. Merging cells

The boss as a good, said you by the way to the export of the top-up statistics have also done, think I have been dealing with so many data people, is not divided into minutes to fix things? Here, on the prototype map.

Poof, a mouthful of old blood, words are said, do it. In doing, I found that this time the export is mainly to solve the problem of cell merging. After checking the data found that PHP itself is not able to achieve cell merging, so I intend to use phpexcel to achieve

If you are using Phpexcel, the basic operation is this (merging A1 to E1)

$objPHPExcel->getactivesheet ()->mergecells (' a1:e1 ');//Table Fill content $objphpexcel->getactivesheet () Setcellvalue (' A1 ', ' The quick brown fox. ');

Results

Or something like this (merging A1 to E4)

$objPHPExcel->getactivesheet ()->mergecells (' a1:e4 '); $objPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' The quick brown fox. ');

Results

This does not meet my requirements, first of all it is a merger, and then I want to show the amount of recharge the following types will change, it is impossible to fix the write dead, and then change each time. So I gave up this method.

Later, with the help of a small partner, try to use HTML to dump Excel

1. method file (because I have to do it regularly every day, so I did not write to the controller layer)

Public Function Actionexcelrechargestatistics () {///First define an Excel file $filename = date (' y-m-d '). ' (export) '). ". XLS "; Header ("Content-type:application/vnd.ms-execl"); Header ("Content-type:application/vnd.ms-excel; Charset=utf-8 "); Header ("content-disposition:attachment; Filename= $filename "); Header ("Pragma:no-cache"); Header ("expires:0"); The time condition if (empty ($params [' min ')]) {$time = date (' y-m-d ', Strtotime ("+1 Day")), $where = ' created < \ '. $time. ' \''; }else{$time = $params [' min ']+3600*24; $time _end = $params [' Max ']+3600*24; $where = ' created <= \ '. $time _end. ' \ ' and created >= \ '. $time. ' \' '; }//Recharge type list $recharge _type = Recharge::find ()->asarray ()->all (); if (Empty ($recharge _type)) {$rechargelist [0]= ';} else{$rechargelist = Arrayhelper::map ($recharge _type, ' id ', ' recharge_name ');} $rechargelist 1 = $rechargelist; $count = count ($rechargelist 1); Use HTML statements to generate the displayed format $excel _content = ' <meta http-equiv= "Content-type" content= "application/ms-excel; Charset=utf-8 "/> '; $excel _content. = ' <table border= "1" style= "FONT-SIZE:14PX;" > '; $excel _content. = ' <thead> <tr> <th rowspan= "2" >ID</th> <th rowspan= "2" > Company name </th&gt   ; <th colspan= '. $count. ' > Recharge Amount </th> <th rowspan= "2" > Recharge size </th> <th rowspan= "2" > Actual consumption </th> <th rowspan= "2" &G t; current balance </th> </tr> <tr> '; foreach ($rechargelist 1 as $v = + $t) {$excel _content. = ' <th colspan= ' 1 ' > '. $t. ' </th> ';} $excel _content. = ' </tr> </thead> '; Find the latest cure data $search = Rechargestatistics::find ()->where ($where)->asarray ()->all (); if (!empty ($search)) {foreach ($search as $key = = $value) {$search [$key] [' recharge '] = unserialize ($value [' Recharge '] ); }}//html statement to populate the data if (empty ($search)) {}else{foreach ($search as $k) {$excel _content. = ' <td> '. $k [' company_id ']. ' </td> '; $excel _content. = ' <td> ' $k [' company_name ']. ' </td> '; foreach ($rechargelist 1 as $v =>$T) {$price = 0; foreach ($k [' recharge '] as $q = + $w) {if ($w [' recharge_id '] = = $v) {$price = $w [' Price '];  Break }} $excel _content. = ' <td> ' $price. ' </td> '; } $excel _content. = ' <td> '. $k [' Total ']. ' </td> '; $excel _content. = ' <td> ' $k [' consume ']. ' </td> '; $excel _content. = ' <td> '. ($k [' Total ']-$k [' consume ']). ' </td></tr> '; }} $excel _content. = ' </table> '; echo $excel _content; Die;}

2. Results

The basic task is to complete all the tasks here!