Detailed description of how to export an excel table in the YII2 framework, yii2excel

Detailed description of how to export an excel table in the YII2 framework, yii2excel

Preface

Table import and export is a feature that we often encounter in daily development. It happened that the table output function was implemented in the recent project, and it was also done before using TP, so I want to take advantage of this diverse opportunity to sort it out, so that you can easily refer to and learn from friends who need it in the future. I will not talk about it here. Let's take a look at the detailed introduction:

This article is developed based on the YII2 framework. Different frameworks may need to be modified.

I. Normal excel format table output

First, the most common table in the. xls format. First, let's take a look at the display effect of the table on the website.

The table contains seven columns. The following describes the implementation of the Code.

1. controller File

// Export the public function actionStatistics () {// set the memory ini_set ("memory_limit", "2048 M"); set_time_limit (0); // obtain the user id $ id = Yii:: $ app-> user-> identity-> getId (); // obtain user information from the user table $ user = Employee: find () -> where (['id' => $ id])-> one (); // obtain the passed information (time, company id, and so on, generate a table based on the required information.) $ params = Yii: $ app-> request-> get (); $ objectPHPExcel = new \ PHPExcel (); // set the output of the table header $ objectPHPExcel-> setActiveSheetIndex ()-> setCellValue ('a1', 'agent company'); $ objectPHPExcel-> setActiveSheetIndex () -> setCellValue ('b1 ', 'revenue'); $ objectPHPExcel-> setActiveSheetIndex ()-> setCellValue ('c1', 'cost'); $ objectPHPExcel-> setActiveSheetIndex () -> setCellValue ('d1 ', 'number of manuscripts'); $ objectPHPExcel-> setActiveSheetIndex ()-> setCellValue ('e1 ', 'Gross profit (revenue-cost )'); $ objectPHPExcel-> setActiveSheetIndex ()-> setCellValue ('f1 ', 'Gross profit margin (gross profit/income) * 100'); $ objectPHPExcel-> setActiveSheetIndex () -> setCellValue ('g1 ', 'arpu value'); // jump to the statistics method of the recharge model file to process data $ data = Recharge: statistics ($ params ); // specify the number of rows starting to 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 '); // sets the output file name and format header ('content-Disposition: attachment; filename = "agent company statistics '. date ("YmdHis" ).'.xls "'); // you need to use Excel2007 $ objWriter = \ PHPExcel_IOFactory: createWriter ($ objectPHPExcel, 'excel5 '); $ objWriter-> save ('php: // output'); ob_end_flush (); // clear the data cache unset ($ data );}

2. model file

<? Php namespace app \ models; // namespace of the model layer // note that arrayhelper use yii \ helpers \ ArrayHelper; use yii; class Recharge extends \ yii \ db \ ActiveRecord {// number of entries exported at a time in excel. const EXCEL_SIZE = 10000; // calculate and 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' '. '\''. $ date_min. '\''. AND '. '\''. $ date_max. '\') '; // query the 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 here, the data storage sequence must be the same as that in the output table $ companys [] = [// company name 'Company _ name' => $ company_name, // revenue 'Company _ cost' => $ company_cost, // cost 'cost' => $ cost, // number of articles 'num' => $ number, // gross profit 'gross _ margin '=> $ company_cost-$ cost, // gross profit margin 'gross _ profit_rate' => round ($ company_cost-$ cost) /$ company_cost * 100,2 ). '%', // ARPU value 'arpu' => round ($ company_cost/$ number, 2),] ;}} return $ companys ;}}

The final export effect (cell size adjusted after export) is similar to that displayed on the webpage.

Ii. Export Big Data Tables

The boss said, we can't just look at the total data, it is best to export the detailed data. Now that the boss has spoken, do it. Follow the first method. The result shows that php has crashed. Try again and find that the number of bytes written has exceeded. Open the php configuration file php. ini.

memory_limit = 128M

It is found that the default memory has been allocated to 128 MB, which should be sufficient. So I opened the database and looked at it. Sorry!

It is not a problem to query and export nearly 0.83 million pieces of data! I just Googled the website and found that for big data (entries), it is best to use .csv format. Go directly to the code without talking nonsense.

1. controller File

// Export the public function actionInventory () {ini_set ("memory_limit", "2048 M"); set_time_limit (0); $ id = Yii :: $ app-> user-> identity-> getId (); $ user = Employee: find ()-> where (['id' => $ id]) -> one (); $ params = Yii: $ app-> request-> get (); // similar, jump to the inventory method in the recharge model file to process data $ data = Recharge: inventory ($ params ); // set the exported file name $ fileName = iconv ('utf-8', 'gbk', 'agent statistics list '. date ("Y-m-d"); // you can specify the header $ headlist = ar. Ray ('agent', 'Article id', 'Article title', 'media', 'statistical time range ', 'status', 'creation time', 'audit time ', 'publication time', 'publication time', 'Financial status', 'cost', 'sale', 'Whether it is a pre-receipt media type', 'order category '); header ('content-Type: application/vnd. ms-excel '); // specify the exported header Format ('content-Disposition: attachment; filename = "'.w.filename.'.csv"'); header ('cache-Control: max-age = 0'); // open the php file handle. php: // output indicates that the file is directly output to the browser $ fp = fopen ('php: // output ', 'A'); // output Excel column Name Information foreach ($ headlist as $ key => $ value) {// CSV Excel supports GBK encoding and must be converted. Otherwise, garbled code $ headlist [$ key] = iconv ('utf-8', 'gbk', $ value );} // write data to the file handle fputcsv ($ fp, $ headlist) through fputcsv; // refresh the output buffer every $ limit line, not too large, do not be too small $ limit = 100000; // retrieve data row by row without wasting the memory foreach ($ data as $ k => $ v) {// refresh the output buffer, prevent problems caused by excessive data. if ($ k % $ limit = 0 & $ k! = 0) {ob_flush (); 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 is too much to process, so only part of the code is selected), in the data query section, because there is a large amount of data to be queried, so you can take a look at my previous articles on Mysql Big Data Query and processing.

// Export the list

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 data if ($ params ['state'] = 1) {$ where = ''; $ where. = 'AND ('issue _ date' '. '\''. $ 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. is_advance from article left join custom_package ON custom_package.id = article. custom_package_id left join 'order' ON custom_package.ord Er_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; // the first part of the data to be searched. Using the asArray method, we can directly form the search result in the form of an array, with no additional data occupying space (note: the search here is divided into three parts because I want to query three different data types.) $ list1 = Article: findBySql ($ map)-> asArray ()-> all (); $ where2 = ''; $ where2. = 'AND ('issue _ date' '. '\''. $ date_min. '\''. AND '. '\''. $ Date_max. '\') '; $ where2. = 'AND ('back _ date'> \''. $ date_max. '\') '; $ map2 = '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 LE Ft 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 '. $ where2; // the second part of the data to be searched $ list2 = Article: findBySql ($ map2)-> asArray ()-> all (); $ where3 = ''; $ where3. = 'AND ('issue _ date' '. '\''. $ date_min. '\''. AND '. '\''. $ date_max. '\') '; $ map3 = '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 compa Ny. id = article. company_id left join media ON media. id = article. media_id where article. status = 5 '. $ where3; // search for the third part of the data $ list3 = Article: findBySql ($ map3)-> asArray ()-> all (); $ list4 = ArrayHelper :: merge ($ list1, $ list2); $ list = ArrayHelper: merge ($ list4, $ list3);} // Save the result to the returned array in the display order if (! Empty ($ list) {foreach ($ list as $ key => $ value) {// agent company $ 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'] = $ time; // status switch ($ value ['status']) {case 2: $ inventory [$ key] ['status'] = 'release'; break; case 3: $ inventory [$ key] ['status'] = 'decompressed '; break; case 5: $ inventory [$ key] ['status'] = 'Abnormal script'; break ;} // creation time $ inventory [$ key] ['created '] = $ value ['created']; // audit time $ inventory [$ key] ['audit'] = $ value ['audit_at']; // release time $ inventory [$ key] ['issue _ date'] = $ value ['issue _ date']; // return time $ inventory [$ key] ['back _ date'] = $ value ['back _ date']; // financial status switch ($ value ['finance _ status']) {case 0: $ inventory [$ key] ['finance _ status'] = 'unsettled '; break; case 1: $ inventory [$ key] ['finance _ status'] = 'clear'; break; case 2: $ inventory [$ key] ['finance _ status'] = 'Resource approving '; break; case 3: $ inventory [$ key] ['finance _ status'] = 'financial approval'; break; case 4: $ inventory [$ key] ['finance _ status'] = 'finalized'; break; case 5: $ inventory [$ key] ['finance _ status'] = 'failed'; break; case 6: $ inventory [$ key] ['finance _ status'] = 'finance approved '; break ;} // cost $ inventory [$ key] ['cost'] = $ value ['cost']; // sales $ inventory [$ key] ['Company _ cost '] = $ value ['Company _ cost']; // whether it is a presale 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;} // switch ($ params ['state']) {case 1: $ inventory [$ key] ['order _ type'] = 'order not decompressed within the timestance'; break; case 2: $ inventory [$ key] ['order _ type'] = 'Return order before publishing timestance'; break; case 3: $ inventory [$ key] ['order _ type'] = 'time range decompress order after publishing '; break; case 4: $ inventory [$ key] ['order _ type'] = 'Return order within the release time range before timestance'; 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

Exported file

Basically, the entire process can be 2 ~ Processing completed in 4 seconds

3. Merge Cells

I think it's good for the boss. You have also exported the top-up statistics by the way. I think it's not a matter of minutes that I have handled so many data? Come on to the original graph

Sorry, I spoke a bit of old blood. Let's get it done. During the process, I found that this export mainly aims to solve the problem of cell merging. After checking the information, we found that PHP itself cannot merge cells, so I intend to use phpexcel to implement

If PHPExcel is used, the basic operation is as follows (merging A1 to E1)

$ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a1: E1 '); // table filled content $ objPHPExcel-> getActiveSheet ()-> setCellValue ('a1 ', 'The quick brown fox. ');

Result

Or (merge A1 to E4)

$objPHPExcel->getActiveSheet()->mergeCells('A1:E4');$objPHPExcel->getActiveSheet()->setCellValue('A1','The quick brown fox.');

Result

This does not meet my requirements. First, it is merged one by one. Second, the type below the recharge amount that I want to display will change. It is impossible to fix it and change it every time. So I gave up this method.

Later, with the help of a small partner, I tried to use html to transfer to excel.

1. Method file (because I have to regularly execute it every day, it is not written to the controller layer)

Public function actionExcelRechargeStatistics () {// first defines an excel file $ filename = date ('[Recharge statistics table ]('. 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"); // 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 ') ;} $ Rechargelist1 = $ rechargelist; $ count = count ($ rechargelist1 ); // use an html statement to generate the display 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> <th colspan ='. $ count. '> Recharge Amount </th> <th rowspan = "2"> recharge size </th> <th rowsp An = "2"> actual consumption </th> <th rowspan = "2"> current balance </th> </tr> <tr> '; foreach ($ rechargelist1 as $ v => $ t) {$ excel_content. = '<th colspan = "1"> '. $ t. '</th>';} $ excel_content. = '</tr> </thead>'; // find the latest solidified data $ search = RechargeStatistics: find ()-> where ($ where)-> asArray () -> all (); if (! Empty ($ search) {foreach ($ search as $ key => $ value) {$ search [$ key] ['recharge'] = unserialize ($ value ['recharge']) ;}// fill data with html statements if (empty ($ search )) {} else {foreach ($ search as $ k) {$ excel_content. = '<td> '. $ k ['Company _ id']. '</td>'; $ excel_content. = '<td> '. $ k ['Company _ name']. '</td>'; foreach ($ rechargelist1 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 ['sume']. '</td>'; $ excel_content. = '<td> '. ($ k ['Total']-$ k ['sume']). '</td> </tr>'; }}$ excel_content. = '</table>'; echo $ excel_content; die ;}

2. Results

All the tasks are basically completed here!

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message. Thank you for your support.