Implementation flow of exporting resource list to Excel file in Yii 2.0

Post Views: 58

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

2. Search in github: yii2 export excel, the result: moonsoft/yii2-phpexcel, kartik-v/yii2-export, as shown in Figure 2

在 GitHub 中搜索：yii2 export excel，其结果：moonlandsoft/yii2-phpexcel、kartik-v/yii2-export — 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

基于 Composer 安装此扩展，执行如下命令：，注：由于网络问题，总计执行了 3 次，第 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 (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. 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

用于导出数据的新功能，如果您熟悉 yii gridview，则可以使用此功能。这与 gridview 数据列相同。数组模式中的列有效参数是 'attribute'，'header'，'format'，'value'和footer(TODO)。字符串模式有效布局中的列是'attribute：format：header：footer(TODO)'。 — 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



        // 设置每页资源数量为资源总数
        $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 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. 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',
    '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. 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

在浏览器中打开网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，成功下载文件：我的任务-2019-09-20-19-39-11.xlsx — Figure 5

10. Open the file: my task-2019-09-20-19-39-11.xlsx, as expected, as shown in Figure 6

打开文件：我的任务-2019-09-20-19-39-11.xlsx，符合预期 — 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

在 Postman 中打开网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，点击 "Send" 按钮，响应乱码 — 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

在 Postman 中打开网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，点击 "Send and download" 按钮，下载名为：response.txt 的文件，且文件中无内容 — 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

在 Postman 中设置 Headers ， Accept:application/xml; version=0.0，打开网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，点击 "Send and download" 按钮，下载名为：*-2019-09-26-10-44-40.xlsx 的文件，文件名中的中文乱码，但文件中的内容符合预期 — 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

在前端(基于 Ant Design)页面中，请求网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，响应乱码，并未下载文件 — 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

初步分析原因，应该在于前端代码对于响应头并未实现完整地解析，因此，即使服务端响应要求前端下载文件，但是前端仍然基于 json 格式来解析数据 — 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

最终决定基于纯粹的链接来实现，使用 download 属性，此属性指示浏览器下载 URL 而不是导航到它，因此将提示用户将其保存为本地文件。，点击 "导出" 按钮，下载名为：我的任务-2019-09-26-10-44-40.xlsx 的文件 — Figure 12



<a href='http://api.pcs-api.localhost/v1/plan-tasks/export' download>导出</a>

Implementation flow of exporting resource list to Excel file in Yii 2.0

Comments

Leave a Reply Cancel reply

More posts

Troubleshooting and Repair of One FSE Page Loss: From Pure Layout Template to Query Loop

Add the multi-language “Personal Brand” block to the sidebar of the blog homepage

Fixed the issue of “getting dissatisfaction” in the sidebar: WordPress 2025 theme calendar style optimization

Debugging record of the beautification and rendering mechanism of the drop-down menu of the category list