Implementation flow of exporting resource list to Excel file in Yii 2.0
1. The resource list of my task, plan to add an export button at the head of the resource list, as shown in Figure 1
Figure 1
2. Search in github: yii2 export excel, the result: moonsoft/yii2-phpexcel, kartik-v/yii2-export, as shown in Figure 2
Figure 2
3. After comparative analysis, it is mainly considered from the richness of the supported export formats and the extension of the support for importing in the future, and the final decision is decided: Moonlandsoft/Yii2-phpExcel
The export format of MoonLandsoft/Yii2-phpExcel: XLS, XLSX, XML, ODS, SLK, GNumeric, CSV, HTML
Export formats of kartik-v/yii2-export: XLS, XLSX, CSV, HTML, TXT, PDF
4. Install this extension based on Composer, execute the following commands:, Note: Due to network problems, a total of 3 times are executed, and the third time is to climb the wall and then execute, as shown in Figure 3
Figure 3
PS e:\wwwroot\pcs-api-feature-task-statistics-print> composer require --prefer-dist Moonlandsoft/yii2-phpexcel "*" Content-length mismatch, received 16128 bytes out of the expected 3759703 https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be ou T of date ./composer.json has been updated Loading composer repositories with package information Updating dependencies (included require-dev) https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be ou T of date Package operations: 5 installs, 0 updates, 0 removals - Installing Markbaker/Matrix (1.1.4): downloading (100%) - Installing Markbaker/Complex (1.4.7): downloading (100%) - Installing PSR/simple-cache (1.0.1): downloading (100%) - Installing PhpOffice/phpspreadsheet (1.9.0): downloading (100%) PS e:\wwwroot\pcs-api-feature-task-statistics-print> composer require --prefer-dist Moonlandsoft/yii2-phpexcel "*" the "https://asset-packagist.org/p/provider-latest/49c795e8ff9 B455ADC45E73E45B8FA84FD39CE4761894526A7D05B455390A960.json " File could not be downloaded: failed to open stream: HTTP request failed! https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be ou T of date ./composer.json has been updated Loading composer repositories with package information Updating dependencies (included require-dev) Content-length mismatch, received 16128 bytes out of the expected 3759943 https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be ou T of date Package operations: 2 installs, 0 updates, 0 removals - Installing phpoffice/phpspreadsheet (1.9.0): PS e:\wwwroot\pcs-api-feature-task-statistics-print> composer require --prefer-dist Moonlandsoft/yii2-phpexcel "*" Content-length mismatch, received 16128 bytes out of the expected 3759943 https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be ou T of date ./composer.json has been updated Loading composer repositories with package information https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be ou T of date Updating dependencies (included require-dev) Package operations: 2 installs, 0 updates, 0 removals -Installing phpoffice/phpspreadsheet (1.9.0): loading from cache - Installing MoonLandsoft/Yii2-phpExcel (2.0.0): downloading (100%) PHPOFFICE/PHPSPreadSheet Suggests Installing MPDF/MPPDF (Option for rendering PDF with PDF writer) PHPOFFICE/PHPSPreadSheet Suggests installing DOMPDF/DOMPDF (Option for rendering PDF with PDF writer) PHPOFFICE/PHPSPreadSheet Suggests Installing Tecnickcom/TCPDF (Option for rendering PDF with PDF writer) PHPOFFICE/PHPSPreadSheet Suggests installing jpGraph/jpgraph (Option for rendering charts, or including charts with pdf or html writers) Writing lock file GENERATING AUTOLOAD FILES
5. New features for exporting data, if you are familiar with Yii GridView, you can use this function. This is the same as the GridView data column. The valid parameters of the columns in the array pattern areAttribute,Header,Format,valueand footer(todo). The columns in the valid layout of the string pattern areattribute: format: header: footer(todo). as shown in Figure 4
Figure 4
6. Copy \api\rests\plan_task\indexaction.php to \API\RESTS\plan_task\exportaction.php, since the export task is all records, set the number of resources per page to the total number of resources
// Set the number of resources per page to the total number of resources $count = $query->count(); $requestParams['per-page']= $count;
7. Edit \api\rests\plan_task\serializer.php to determine the operation ID, if it is export, then export, add request parameters, file_type: file type
use moon\phpexcel\excel;
use yii\helpers\arrayHelper;
/**
* Serializes a data provider.
* @Param DataProviderInterface $DataProvider
* @return array|null the array representation of the data provider.
* @throws unprocessableEntityHttpException
*/
protected function serialized dataProvider($dataProvider)
{
// export file
$actionId = yii::$app->controller->action->id;
if ($ActionId ==export) {
$filetype = isset($requestParams['file_type']) ? $requestParams['file_type']:xlsx;
$AllowedFileTypes =['xlsx'];
// Determine the file type range
if (!in_array($filetype, $allowedFileTypes)) {
Throw new UnprocessableEntityHttpException(yii::t(Error, yii::t(Error, yii::t(Error,226823,['file_types' => implode(",", $allowedFileTypes)])), 226823);
}
// the name of the export file
$filename = yii::t(Application,326001) .-. date("Y-M-D-H-I-S");
// Export the format of the file, convert the first letter of the string to uppercase
$format = ucfirst($filetype);
excel::export([
Models=> $result[items]#ATFP_CLOSE_Translate_span#,
asttachment=> true,
filename=> $filename,
Format=> $format,
columns=>[
[
Attribute=>ID,
Header=> yii::t(Model/plan-task-export-param,ID,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>is_deferred,
Header=> yii::t(Model/plan-task-export-param,is deferred,
value=> function($model) {
return $model[is_deferred]#atfp_close_translate_span# == Plantask::is_deferred_no ? yii::t(Model/plan-task-export-param,NO) : yii::t(Model/plan-task-export-param,yes);
},
],
[
Attribute=>Totle,
Header=> yii::t(Model/plan-task-export-param,Totle,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>task_info,
Header=> yii::t(Model/plan-task-export-param,task info,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>CONFIG_TASK_NAME,
Header=> yii::t(Model/plan-task-export-param,config task name,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>CONFIG_COLUMN_NAME,
Header=> yii::t(Model/plan-task-export-param,config column name,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>create_name,
Header=> yii::t(Model/plan-task-export-param,Create name,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>EXEC_NAME,
Header=> yii::t(Model/plan-task-export-param,exec name,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>PLAN_TASK_ATTENDED_USERS,
Header=> yii::t(Model/plan-task-export-param,Attended names,
value=> function($model) {
$plantasKattendedUserNames = ArrayHelper::getColumn($model[PLAN_TASK_ATTENDED_USERS]#ATFP_CLOSE_Translate_span#,RELATION_USER_NAME);
return implode(",", $plantaskattedUsernames);
},
],
[
Attribute=>Sort_order,
Header=> yii::t(Model/plan-task-export-param,Sort order,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>place,
Header=> yii::t(Model/plan-task-export-param,place,
]#ATFP_CLOSE_Translate_span#,
[
Attribute=>OCCUR_AT,
Header=> yii::t(Model/plan-task-export-param,occur at,
Format=> [Date,php:y-m-d h:i:s]#ATFP_CLOSE_Translate_span#,
],
[
Attribute=>ended_at,
Header=> yii::t(Model/plan-task-export-param,ended at,
Format=> [Date,php:y-m-d h:i:s]#ATFP_CLOSE_Translate_span#,
],
[
Attribute=>Task_Location_Status,
Header=> yii::t(Model/plan-task-export-param,Task Location Status,
value=> function($model) {
return $model[Task_Location_Status]#atfp_close_translate_span# == PlanTask::Plan_Task_Location_Status_Off ? yii::t(Model/plan-task-export-param,off) : yii::t(Model/plan-task-export-param,on);
},
],
[
Attribute=>is_united,
Header=> yii::t(Model/plan-task-export-param,is united,
value=> function($model) {
return $model[Task_Location_Status]#atfp_close_translate_span# == Plantask::is_deleted_no ? yii::t(Model/plan-task-export-param,NO) : yii::t(Model/plan-task-export-param,yes);
},
],
[
Attribute=>prev_status,
Header=> yii::t(Model/plan-task-export-param,prev status,
value=> function($model) {
if ($model[prev_status]#atfp_close_translate_span# == Plantask::Status_Not_Started) {
$prevStatusValue = yii::t(Model/plan-task-export-param,Not started);
} elseif ($model['prev_status']== PlanTask::Status_Started) {
$prevStatusValue = yii::t(Model/plan-task-export-param,startedled);
} elseif ($model['prev_status']== PlanTask::Status_Completed) {
$prevStatusValue = yii::t(Model/plan-task-export-param,completed);
} else {
$prevStatusValue = yii::t(Model/plan-task-export-param,disabled);
}
return $prevStatusValue;
},
],
[
Attribute=>status,
Header=> yii::t(Model/plan-task-export-param,status,
value=> function($model) {
if ($model[status]#atfp_close_translate_span# == Plantask::Status_Not_Started) {
$statusValue = yii::t(Model/plan-task-export-param,Not started);
} elseif ($model['status']== PlanTask::Status_Started) {
$statusValue = yii::t(Model/plan-task-export-param,startedled);
} elseif ($model['status']== PlanTask::Status_Completed) {
$statusValue = yii::t(Model/plan-task-export-param,completed);
} else {
$statusValue = yii::t(Model/plan-task-export-param,disabled);
}
return $statusValue;
},
],
[
Attribute=>Plan_Task_Steps,
Header=> yii::t(Model/plan-task-export-param,task steps,
value=> function($model) {
$planTaskStepNames = ArrayHelper::getColumn($model[Plan_Task_Steps]#ATFP_CLOSE_Translate_span#,step_name);
return implode(",", $planTaskStepNames);
},
],
[
Attribute=>is_not_isolated,
Header=> yii::t(Model/plan-task-export-param,is not isolated,
value=> function($model) {
return $model[is_not_isolated]#atfp_close_translate_span# == Plantask::is_not_isolated_no ? yii::t(Model/plan-task-export-param,NO) : yii::t(Model/plan-task-export-param,yes);
},
],
[
Attribute=>created_at,
Header=> yii::t(Model/plan-task-export-param,created at,
Format=> [Date,php:y-m-d h:i:s]#ATFP_CLOSE_Translate_span#,
],
[
Attribute=>updated_at,
Header=> yii::t(Model/plan-task-export-param,updated at,
Format=> [Date,php:y-m-d h:i:s]#ATFP_CLOSE_Translate_span#,
],
[
Attribute=>deleted_at,
Header=> yii::t(Model/plan-task-export-param,Deleted at,
Format=> [Date,php:y-m-d h:i:s]#ATFP_CLOSE_Translate_span#,
],
],
]);
}
if ($pagination !== false) {
return['code' => 10000, 'message' => Yii::t('success', '126007'), 'data' => array_merge($result, $this->serializePagination($pagination))];
}
return['code' => 10000, 'message' => Yii::t('success', '126007'), 'data' => $result];
}
8. Added the language package file of the model
\common\messages\en-us\model\plan-task-export-param.php
<?php /** created by phpstorm. * user: qiang wang * date: 2019/09/20 * time: 17:31 */ return[ 'ID' => 'ID', is deferred=>is deferred, Totle=>Totle, task info=>task info, config task name=>config task name, config column name=>config column name, Create name=>Create name, exec name=>exec name, Attended names=>Attended names, Sort order=>Sort order, place=>place, occur at=>occur at, ended at=>ended at, Task Location Status=>Task Location Status, off=>off, on=>on, is united=>is united, prev status=>prev status, status=>status, disabled=>disabled, Not started=>Not started, startedled=>startedled, completed=>completed, task steps=>task steps, is not isolated=>is not isolated, NO=>NO, yes=>yes, created at=>created at, updated at=>updated at, Deleted at=>Deleted at, ]#atfp_close_translate_span#;
\common\messages\zh-cn\model\plan-task-export-param.php
<?php /** created by phpstorm. * user: qiang wang * date: 2019/09/20 * time: 15:47 */ return[ 'ID' => '编号', is deferred=>Has it been postponed, Totle=>task name, task info=>task content, config task name=>Task type, config column name=>task column, Create name=>founder, exec name=>Principal, Attended names=>participant, Sort order=>Sequence, place=>Location, occur at=>Starting time, ended at=>End Time, Task Location Status=>task positioning status, off=>disabled, on=>Start using, is united=>Whether to unite, prev status=>last state, status=>Status, disabled=>disabled, Not started=>Unclaimed, startedled=>Claimed, completed=>Completed, task steps=>mission step, is not isolated=>cross-tenant, NO=>Deny, yes=>Be, created at=>creation time, updated at=>Update time, Deleted at=>delete time, ]#atfp_close_translate_span#;
9. Open the URL in the browser: http://api.pcs-api.localhost/v1/plan-tasks/export , successfully downloaded the file: my task-2019-09-20-19-39-11.xlsx, as shown in Figure 5
Figure 5
10. Open the file: my task-2019-09-20-19-39-11.xlsx, as expected, as shown in Figure 6
Figure 6
11. Open the URL in Postman: http://api.pcs-api.localhost/v1/plan-tasks/export , click the “send” button to respond to garbled characters, as shown in Figure 7
Figure 7
12. Open the URL in Postman: http://api.pcs-api.localhost/v1/plan-tasks/export , click “Send and download” button, download a file named: response.txt, and there is no content in the file, as shown in Figure 8
8
13. Set headers in postman, accept:application/xml; version=0.0, open the URL: http://api.pcs-api.localhost/v1/plan-tasks/export , click “Send and download” button, the download name is: *-2019-09-26-10-44-40.xlsx, the Chinese in the file name is garbled, but the content in the file is as expected, as shown in Figure 9
Figure 9
14. In the front-end (based on the Ant Design) page, the request URL: http://api.pcs-api.localhost/v1/plan-tasks/export , in response to garbled characters, the file is not downloaded, as shown in Figure 10
Figure 10
15. The reason for the preliminary analysis should be that the front-end code does not realize the complete parsing of the response header. Therefore, even if the server responds to the front-end to download the file, the front-end still parses the data based on the JSON format, as shown in Figure 11
Figure 11
16. The final decision is implemented based on pure links, using the download attribute, this attribute instructs the browser to download the URL instead of navigating to it, so the user will be prompted to save it as a local file. , click the “Export” button, download the file named: My Task-2019-09-26-10-44-40.xlsx, as shown in Figure 12
Figure 12
<a href=http://api.pcs-api.localhost/v1/plan-tasks/exportdownload>export</a>
