1. For the resource list of My Tasks, I planned to add an export button to the header of the resource list, as shown in Figure 1.
2. I searched for “yii2 export excel” on GitHub, and the results were moonlandsoft/yii2-phpexcel and kartik-v/yii2-export, as shown in Figure 2.
3. After a comparative analysis, primarily considering the variety of supported export formats and the extensibility for potentially supporting imports later, I finally decided to choose moonlandsoft/yii2-phpexcel.
Export formats for moonlandsoft/yii2-phpexcel: Xls, Xlsx, Xml, Ods, Slk, Gnumeric, Csv, Html
Export formats for kartik-v/yii2-export: Xls, Xlsx, Csv, Html, Txt, Pdf
4. I installed this extension via Composer by executing the following command. Note: Due to network issues, I executed it a total of 3 times; the 3rd time, I switched network environments and executed it again, as shown in 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 (including 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/49c795e8ff9b455adc45e73e45b8fa84fd39ce4761894526a7d05b455390a960.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 (including 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 (including 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/mpdf (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. This is a new feature for exporting data. If you are familiar with Yii GridView, you can use this feature. It works the same as GridView data columns. The valid parameters for columns in array mode are ‘attribute’, ‘header’, ‘format’, ‘value’, and footer (TODO). The valid layout for columns in string mode is ‘attribute:format:header:footer(TODO)’. See Figure 4.
6. I copied \api\rests\plan_task\IndexAction.php to \api\rests\plan_task\ExportAction.php. Since the export task requires all records, I set the number of resources per page to the total number of resources.
// 设置每页资源数量为资源总数
$count = $query->count();
$requestParams['per-page'] = $count;
7. I edited \api\rests\plan_task\Serializer.php to check the action ID. If it is export, it exports the data. I added a new request parameter: file_type, which specifies the file type.
use moonland\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 serializeDataProvider($dataProvider)
{
// 导出文件
$actionId = Yii::$app->controller->action->id;
if ($actionId == 'export') {
$fileType = isset($requestParams['file_type']) ? $requestParams['file_type'] : 'xlsx';
$allowedFileTypes = ['xlsx'];
// 判断文件类型范围
if (!in_array($fileType, $allowedFileTypes)) {
throw new UnprocessableEntityHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '226823'), ['file_types' => implode(",", $allowedFileTypes)])), 226823);
}
// 导出文件的名称
$fileName = Yii::t('application', '326001') . '-' . date("Y-m-d-H-i-s");
// 导出文件的格式，将字符串的首字母转换为大写
$format = ucfirst($fileType);
Excel::export([
'models' => $result['items'],
'asAttachment' => true,
'fileName' => $fileName,
'format' => $format,
'columns' => [
[
'attribute' => 'id',
'header' => Yii::t('model/plan-task-export-param', 'ID'),
],
[
'attribute' => 'is_deferred',
'header' => Yii::t('model/plan-task-export-param', 'Is Deferred'),
'value' => function($model) {
return $model['is_deferred'] == PlanTask::IS_DEFERRED_NO ? Yii::t('model/plan-task-export-param', 'No') : Yii::t('model/plan-task-export-param', 'Yes');
},
],
[
'attribute' => 'title',
'header' => Yii::t('model/plan-task-export-param', 'Title'),
],
[
'attribute' => 'task_info',
'header' => Yii::t('model/plan-task-export-param', 'Task Info'),
],
[
'attribute' => 'config_task_name',
'header' => Yii::t('model/plan-task-export-param', 'Config Task Name'),
],
[
'attribute' => 'config_column_name',
'header' => Yii::t('model/plan-task-export-param', 'Config Column Name'),
],
[
'attribute' => 'create_name',
'header' => Yii::t('model/plan-task-export-param', 'Create Name'),
],
[
'attribute' => 'exec_name',
'header' => Yii::t('model/plan-task-export-param', 'Exec Name'),
],
[
'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'], 'relation_user_name');
return implode(",", $planTaskAttendedUserNames);
},
],
[
'attribute' => 'sort_order',
'header' => Yii::t('model/plan-task-export-param', 'Sort Order'),
],
[
'attribute' => 'place',
'header' => Yii::t('model/plan-task-export-param', 'Place'),
],
[
'attribute' => 'occur_at',
'header' => Yii::t('model/plan-task-export-param', 'Occur At'),
'format' => ['date', 'php:Y-m-d H:i:s'],
],
[
'attribute' => 'ended_at',
'header' => Yii::t('model/plan-task-export-param', 'Ended At'),
'format' => ['date', 'php:Y-m-d H:i:s'],
],
[
'attribute' => 'task_location_status',
'header' => Yii::t('model/plan-task-export-param', 'Task Location Status'),
'value' => function($model) {
return $model['task_location_status'] == 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'] == 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'] == 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', 'Started');
} 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'] == 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', 'Started');
} 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'], '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'] == 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'],
],
[
'attribute' => 'updated_at',
'header' => Yii::t('model/plan-task-export-param', 'Updated At'),
'format' => ['date', 'php:Y-m-d H:i:s'],
],
[
'attribute' => 'deleted_at',
'header' => Yii::t('model/plan-task-export-param', 'Deleted At'),
'format' => ['date', 'php:Y-m-d H:i:s'],
],
],
]);
}
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. I added language pack files for 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',
'Title' => 'Title',
'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',
'Started' => 'Started',
'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',
];\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' => '是否已延期',
'Title' => '任务名称',
'Task Info' => '任务内容',
'Config Task Name' => '任务类型',
'Config Column Name' => '任务栏目',
'Create Name' => '创建人',
'Exec Name' => '负责人',
'Attended Names' => '参与人',
'Sort Order' => '排序',
'Place' => '地点',
'Occur At' => '开始时间',
'Ended At' => '结束时间',
'Task Location Status' => '任务定位状态',
'Off' => '禁用',
'On' => '启用',
'Is United' => '是否联合',
'Prev Status' => '上一状态',
'Status' => '状态',
'Disabled' => '禁用',
'Not Started' => '未认领',
'Started' => '已认领',
'Completed' => '已完成',
'Task Steps' => '任务步骤',
'Is Not Isolated' => '是否跨租户',
'No' => '否',
'Yes' => '是',
'Created At' => '创建时间',
'Updated At' => '更新时间',
'Deleted At' => '删除时间',
];9. I opened the URL http://api.pcs-api.localhost/v1/plan-tasks/export in the browser and successfully downloaded the file: 我的任务-2019-09-20-19-39-11.xlsx, as shown in Figure 5.
10. I opened the file: 我的任务-2019-09-20-19-39-11.xlsx, and it met expectations, as shown in Figure 6.
11. I opened the URL http://api.pcs-api.localhost/v1/plan-tasks/export in Postman and clicked the “Send” button; the response was garbled, as shown in Figure 7.
12. I opened the URL http://api.pcs-api.localhost/v1/plan-tasks/export in Postman and clicked the “Send and download” button. It downloaded a file named response.txt, but the file was empty, as shown in Figure 8.
13. I set the Headers in Postman to Accept:application/xml; version=0.0, opened the URL http://api.pcs-api.localhost/v1/plan-tasks/export, and clicked the “Send and download” button. It downloaded a file named *-2019-09-26-10-44-40.xlsx. The Chinese characters in the file name were garbled, but the content of the file met expectations, as shown in Figure 9.
14. On the frontend page (based on Ant Design), I requested the URL http://api.pcs-api.localhost/v1/plan-tasks/export. The response was garbled, and no file was downloaded, as shown in Figure 10.
15. My preliminary analysis of the cause is that the frontend code does not fully parse the response headers. Therefore, even though the server response requires the frontend to download a file, the frontend still parses the data based on the JSON format, as shown in Figure 11.
16. I finally decided to implement this using a plain link with the download attribute. This attribute instructs the browser to download the URL instead of navigating to it, prompting the user to save it as a local file. After clicking the “Export” button, a file named 我的任务-2019-09-26-10-44-40.xlsx was downloaded, as shown in Figure 12.
<a href='http://api.pcs-api.localhost/v1/plan-tasks/export' download>导出</a>