在 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
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、用于导出数据的新功能，如果您熟悉 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，由于导出任务为全部记录，因此设置每页资源数量为资源总数
// 设置每页资源数量为资源总数
$count = $query->count();
$requestParams['per-page'] = $count;
7、编辑 \api\rests\plan_task\Serializer.php，判断操作ID，如果为 export，则导出，新增请求参数，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、新增模型的语言包文件
\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、在浏览器中打开网址：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
<a href='http://api.pcs-api.localhost/v1/plan-tasks/export' download>导出</a>
近期评论