如何添加多选表单筛选MySQL数据并导出指定列至Excel？

我正在开发一个需要频繁将MySQL数据导出到Excel的项目，当前通过控制器硬编码查询语句和Excel列的方式实现导出，但需要新增自定义列筛选导出功能：点击导出按钮时弹出包含所有可导出列的多选表单，仅导出用户选中的列（比如原列有name、age、gender，用户只选name就只导出该列）。现有代码如下：

public function exportExcelAction()
{
    
    ini_set('memory_limit', '-1');
    ini_set('max_execution_time', 1800);
    ini_set('max_input_time', 1800);
    $this->view->disable();

    $spreadsheet = new Spreadsheet();
    $Excel_writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

    $sheetName = 'reportHistory';
    $spreadsheet->setActiveSheetIndex(0);
    $activeSheet = $spreadsheet->getActiveSheet()->setTitle($sheetName);

    $activeSheet->setCellValue('A1', 'name');
    $activeSheet->setCellValue('B1', 'age');
    $activeSheet->setCellValue('C1', 'gender');

    $activeSheet->getStyle('A1:C1')->getFont()->setBold(true);

    $builder = $this->modelsManager->createBuilder()
    ->columns([
       'name' => 'a.name',
       'age' => 'a.age',
       'gender' => 'a.gender'
   ])
    ->addFrom(Admins::class, 'a');


    $result = $builder->getQuery()->execute();

    if (!empty($result)) {
        $i = 2;
        foreach ($result as $value) {
            $activeSheet->setCellValue('A' . $i, $value->name);
            $activeSheet->setCellValue('B' . $i, $value->age);
            $activeSheet->setCellValue('C' . $i, $value->gender);
            $i++;
        }
    }

    foreach (range('A', 'C') as $columnID) {
        $activeSheet->getColumnDimension($columnID)
            ->setAutoSize(true);
    }

    $filename = 'reportHistory_' . date('Y-m-d_H-i-s') . '.xlsx';

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename=' . $filename);
    header('Cache-Control: max-age=0');
    $Excel_writer->save('php://output');
}

实现步骤 #

1. 前端添加多选表单

在触发导出的页面，添加包含所有可导出列的多选表单，提交到导出接口：

<form action="/your-controller/exportExcel" method="post">
  <label>选择要导出的列：</label>
  <input type="checkbox" name="columns[]" value="name">姓名<br>
  <input type="checkbox" name="columns[]" value="age">年龄<br>
  <input type="checkbox" name="columns[]" value="gender">性别<br>
  <button type="submit">导出Excel</button>
</form>

2. 后端修改导出逻辑

修改exportExcelAction，通过配置化+动态处理的方式实现自定义列导出，核心代码如下：

public function exportExcelAction()
{
    ini_set('memory_limit', '-1');
    ini_set('max_execution_time', 1800);
    ini_set('max_input_time', 1800);
    $this->view->disable();

    // 配置所有允许导出的列，后续新增列只需修改这里
    $allowedColumns = [
        'name' => ['label' => '姓名', 'field' => 'a.name'],
        'age' => ['label' => '年龄', 'field' => 'a.age'],
        'gender' => ['label' => '性别', 'field' => 'a.gender'],
    ];

    // 获取用户选中的列，默认全选（未选择时）
    $selectedColumns = $this->request->getPost('columns', []);
    // 过滤非法列，仅保留允许导出的列
    $selectedColumns = array_intersect($selectedColumns, array_keys($allowedColumns));
    // 未选中任何列时，默认导出全部列
    if (empty($selectedColumns)) {
        $selectedColumns = array_keys($allowedColumns);
    }

    // 动态构建查询语句，只查询选中的列
    $builder = $this->modelsManager->createBuilder();
    foreach ($selectedColumns as $col) {
        $builder->addColumn($allowedColumns[$col]['field'], $col);
    }
    $builder->addFrom(Admins::class, 'a');
    $result = $builder->getQuery()->execute();

    // 初始化Excel
    $spreadsheet = new Spreadsheet();
    $Excel_writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $sheetName = 'reportHistory';
    $spreadsheet->setActiveSheetIndex(0);
    $activeSheet = $spreadsheet->getActiveSheet()->setTitle($sheetName);

    // 动态生成表头
    $columnIndex = 0;
    foreach ($selectedColumns as $col) {
        $columnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnIndex + 1);
        $activeSheet->setCellValue($columnLetter . '1', $allowedColumns[$col]['label']);
        $columnIndex++;
    }
    // 设置表头加粗
    $lastColumnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($selectedColumns));
    $activeSheet->getStyle('A1:' . $lastColumnLetter . '1')->getFont()->setBold(true);

    // 动态填充数据行
    if (!empty($result)) {
        $row = 2;
        foreach ($result as $value) {
            $columnIndex = 0;
            foreach ($selectedColumns as $col) {
                $columnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnIndex + 1);
                $activeSheet->setCellValue($columnLetter . $row, $value->$col);
                $columnIndex++;
            }
            $row++;
        }
    }

    // 自适应列宽
    foreach (range('A', $lastColumnLetter) as $columnID) {
        $activeSheet->getColumnDimension($columnID)->setAutoSize(true);
    }

    // 输出Excel文件
    $filename = 'reportHistory_' . date('Y-m-d_H-i-s') . '.xlsx';
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename=' . $filename);
    header('Cache-Control: max-age=0');
    $Excel_writer->save('php://output');
}

关键优化点

• 配置化管理列：用$allowedColumns统一管理可导出列的显示标签和数据库字段，后续新增/修改列只需调整配置，无需修改多处代码。
• 安全过滤：通过array_intersect确保仅处理合法列，避免SQL注入风险。
• 动态生成内容：用数字索引+stringFromColumnIndex方法转换Excel列字母，彻底摆脱硬编码A、B、C的局限。
• 友好默认逻辑：用户未选择任何列时自动导出全部列，提升使用体验。

内容的提问来源于stack exchange，提问作者ceaiius