cakephp:将过滤后的数据导出到Excel

Question

在我看来，我有 2 个按钮，一个在表单中，用于根据下拉列表过滤数据，另一个用于将过滤后的数据导出到 Excel。

<?php
$options = array();
$options[0] = 'All';
$options[1] = 'Due last 6 months';
$options[2] = 'Due current month';
$options[3] = 'Due next 3 months';
$options[4] = 'Due next 6 months';
$options[5] = 'Due next 12 months';            
?>
<div class="row">
<?= $this->Form->create() ?>
<fieldset>                
<div class="row">
<div class="col-xs-3"><?= $this->Form->input('select_period_id', ['options' => $options, 'empty' => false, 'label' => __('Select Period')]) ?></div>
<div class="col-sm-2" style="padding-top:25px;"><?= $this->Form->button(__('Search'), ['class' => 'btn btn-primary']) ?></div>
</div>
</fieldset>
<?= $this->Form->end() ?>                
</div>
<div class="timetables index large-9 medium-8 columns content">
<?= $this->Html->link(__('Export to xlsx'), array_merge($this->request->query, ['_ext' => 'xlsx']), ['class' => 'btn btn-primary']) ?>
</div>

我假设第一个按钮是开机自检，第二个按钮是 GET。 我能够使用第一个按钮过滤数据，但不能使用第二个按钮：

$data = $this->request->data;
$select_period = $this->request->data('select_period_id');
$today = Time::now()->format('Y-m-d');
$second_date = Time::now();
$assetsAssignations = $this->AssetsAssignations->find()
->contain(['Assets']);
if($this->request->is(['patch', 'post', 'put'])) 
{
if ($select_period == 0) {
$second_date = $second_date->modify('-6 months');                
$second_date = $second_date->format('Y-m-d');
} elseif ($select_period == 1) {                
$second_date = $second_date->modify('-6 months');                
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'end_date >=' => $second_date,
'end_date <=' => $today
]);
// etc..
} elseif($this->request->is(['get'])) 
{
???
}
$_filename = "xls_report_replacement_" . date('Ymd');
$this->set(compact('assetsAssignations', '_filename'));        
}       

我没有找到导出到 Excel 按钮 (GET( 时应该放什么。

请帮忙吗？

_ 更新：我试过这个，打开 excel 文件时出现错误

{
$data = $this->request->data;
$select_period = $this->request->data('select_period_id');
$today = Time::now()->format('Y-m-d');
$second_date = Time::now();
$assetsAssignations = $this->AssetsAssignations->find()
->contain('Assets');
debug($this->request->data);
if (isset($this->request->data['btn1'])) 
{
if ($select_period == 0) {
$second_date = $second_date->modify('-6 months');                
$second_date = $second_date->format('Y-m-d');
} elseif ($select_period == 1) {                
$second_date = $second_date->modify('-6 months');                
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $second_date,
'Assets.life_end_date <=' => $today
]);
} elseif ($select_period == 2) {
$second_date = $second_date->modify('1 month');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 3) {
$second_date = $second_date->modify('3 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 4) {
$second_date = $second_date->modify('6 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 5) {
$second_date = $second_date->modify('12 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
}
} elseif (isset($this->request->data['btn2'])) {
if ($select_period == 0) {
$second_date = $second_date->modify('-6 months');                
$second_date = $second_date->format('Y-m-d');
} elseif ($select_period == 1) {                
$second_date = $second_date->modify('-6 months');                
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $second_date,
'Assets.life_end_date <=' => $today
]);
} elseif ($select_period == 2) {
$second_date = $second_date->modify('1 month');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 3) {
$second_date = $second_date->modify('3 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 4) {
$second_date = $second_date->modify('6 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
} elseif ($select_period == 5) {
$second_date = $second_date->modify('12 months');
$second_date = $second_date->format('Y-m-d');
$assetsAssignations->where([
'Assets.life_end_date >=' => $today,
'Assets.life_end_date <=' => $second_date
]);
}
debug($select_period);
die();
return $this->redirect(['action' => 'test.xlsx']);
}
$_filename = "xls_report_replacement_" . date('Ymd');
$this->set(compact('assetsAssignations', '_filename'));        
}   

在我看来，我在表单中包含第二个按钮：

<div class="row">
<?= $this->Form->create('form_name') ?>
<fieldset>                
<div class="row">
<div class="col-xs-3"><?= $this->Form->input('select_period_id', ['options' => $options, 'empty' => false, 'label' => __('Select Period')]) ?></div>
<div class="col-sm-2" style="padding-top:25px;"><?php echo $this->Form->submit('Search', array('name'=>'btn1'))?></div>
<div class="col-sm-2" style="padding-top:25px;"><?php echo $this->Form->submit('Excel', array('name'=>'btn2'))?></div>
</div>
</fieldset>
<?= $this->Form->end() ?>
</div>

当我使用第一个按钮进行调试时：

[
'select_period_id' => '1',
'btn2' => 'Excel'
]
select_period_id = '1'

使用 Excel 按钮进行调试：

[
'select_period_id' => '2',
'btn2' => 'Excel'
]
select_period_id = '2'

Answer 1

通过在生成 xlsx 文件时包含调试语句，会损坏输出。通过重定向到没有任何参数的test.xlsx，您将丢失已发布的数据。你可以搞砸这样的东西：

$this->redirect(['action' => 'test.xlsx', 'select_period_id' => $select_period])

但这很丑陋。无需重定向，只需按如下方式设置响应类型：

$this->RequestHandler->renderAs($this, 'xlsx');

此外，你有很多重复的代码;其中大部分可以消除。

而且，在将日期传递到查询生成器之前，您不需要格式化日期，只需从Time类切换到Date。

这是我建议的版本：

{
$data = $this->request->data;
$select_period = $this->request->data('select_period_id');
$today = FrozenDate::now();
$assetsAssignations = $this->AssetsAssignations->find()
->contain('Assets');
// This block could be done with a switch instead of if-elseif
if ($select_period == 0) {
$start_date = $end_date = null;
} elseif ($select_period == 1) {                
// Alternately, $start_date = $today->subMonths(6);
$start_date = $today->modify('-6 months');
$end_date = $today;
} elseif ($select_period == 2) {
$start_date = $today;
// Alternately, $start_date = $today->addMonth();
$end_date = $today->modify('1 month');
} elseif ($select_period == 3) {
$start_date = $today;
// Alternately, $start_date = $today->addMonths(3);
$end_date = $today->modify('3 months');
} elseif ($select_period == 4) {
$start_date = $today;
$end_date = $today->modify('6 months');
} elseif ($select_period == 5) {
$start_date = $today;
$end_date = $today->modify('12 months');
}
if ($start_date) {
$assetsAssignations->where([
'Assets.life_end_date >=' => $start_date,
'Assets.life_end_date <=' => $end_date
]);
}
// Personally, I'd rename this from btn2 to something like xlsx, here and in the form template
if (isset($this->request->data['btn2'])) {
$this->RequestHandler->renderAs($this, 'xlsx');
}
$_filename = "xls_report_replacement_" . date('Ymd');
$this->set(compact('assetsAssignations', '_filename'));
}

如您所见，代码要短得多，并且应该在功能上相同。

更新：在控制器中，在顶部添加：

use CakeI18nFrozenDate;