在 Yii 2.0 中导出资源列表为 Excel 文件的实现流程
1、我的任务的资源列表，计划在资源列表头部添加一个导出按钮，如图1
图1
2、在 GitHub 中搜索：yii2 export excel，其结果：moonlandsoft/yii2-phpexcel、kartik-v/yii2-export，如图2
图2
3、经过对比分析，主要从支持的导出格式的丰富性、后续可能需要支持导入的扩展性上考虑，最终决定选择：moonlandsoft/yii2-phpexcel
moonlandsoft/yii2-phpexcel的导出格式：Xls、Xlsx、Xml、Ods、Slk、Gnumeric、Csv、Html
kartik-v/yii2-export的导出格式：Xls、Xlsx、Csv、Html、Txt、Pdf
4、基于 Composer 安装此扩展，执行如下命令：，注：由于网络问题，总计执行了 3 次，第 3 次为翻墙之后再执行，如图3
图3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | 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 3759703https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be out of date./composer.json has been updatedLoading composer repositories with package informationUpdating dependencies (including require-dev)https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be out of datePackage 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 "*"" 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 out of date./composer.json has been updatedLoading composer repositories with package informationUpdating dependencies (including require-dev)Content-Length mismatch, received 16128 bytes out of the expected 3759943https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be out of datePackage 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 3759943https://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be out of date./composer.json has been updatedLoading composer repositories with package informationhttps://asset-packagist.org could not be fully loaded, package information was loaded from the local cache and may be out of dateUpdating 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 PDFor HTML Writers)Writing lock fileGenerating autoload files |
5、用于导出数据的新功能，如果您熟悉 yii gridview，则可以使用此功能。这与 gridview 数据列相同。数组模式中的列有效参数是 ‘attribute’，’header’，’format’，’value’和footer(TODO)。字符串模式有效布局中的列是’attribute：format：header：footer(TODO)’。如图4
图4
6、复制 \api\rests\plan_task\IndexAction.php 至 \api\rests\plan_task\ExportAction.php，由于导出任务为全部记录，因此设置每页资源数量为资源总数
1 2 3 | // 设置每页资源数量为资源总数$count = $query->count();$requestParams['per-page'] = $count; |
7、编辑 \api\rests\plan_task\Serializer.php，判断操作ID，如果为 export，则导出，新增请求参数，file_type：文件类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 | 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、新增模型的语言包文件
\common\messages\en-US\model\plan-task-export-param.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | <?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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | <?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、在浏览器中打开网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，成功下载文件：我的任务-2019-09-20-19-39-11.xlsx，如图5
图5
10、打开文件：我的任务-2019-09-20-19-39-11.xlsx，符合预期，如图6
图6
11、在 Postman 中打开网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，点击 “Send” 按钮，响应乱码，如图7
图7
12、在 Postman 中打开网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，点击 “Send and download” 按钮，下载名为：response.txt 的文件，且文件中无内容，如图8
图8
13、在 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 的文件，文件名中的中文乱码，但文件中的内容符合预期，如图9
图9
14、在前端(基于 Ant Design)页面中，请求网址：http://api.pcs-api.localhost/v1/plan-tasks/export ，响应乱码，并未下载文件，如图10
图10
15、初步分析原因，应该在于前端代码对于响应头并未实现完整地解析，因此，即使服务端响应要求前端下载文件，但是前端仍然基于 json 格式来解析数据，如图11
图11
16、最终决定基于纯粹的链接来实现，使用 download 属性，此属性指示浏览器下载 URL 而不是导航到它，因此将提示用户将其保存为本地文件。，点击 “导出” 按钮，下载名为：我的任务-2019-09-26-10-44-40.xlsx 的文件，如图12
图12
1 |
近期评论