php - 动态字段 Excel 输出 PHP

Question

假设我在 SQL 数据库上有这张表：

+---------------+-----------------+-----------------+
| category      | name            | month           |
+---------------+-----------------+-----------------+
| Fruit         | Mango           |January          |
| Fruit         | Apple           |January          |
| Fruit         | Grape           |February         |
| Vegetable     | Tommato         |February         |
| Vegetable     | Cuccumber       |March            |
| Animal        | Chicken         |March            |
+---------------+-----------------+-----------------+

然后我想使用 PHPExcel 动态输出一个 excel 文件，如下所示：

所以，因为它是动态的，所以每当数据库发生更新时，excel 输出就会跟随：SqlTable 更新：

+---------------+-----------------+-----------------+
| category      | name            | month           |
+---------------+-----------------+-----------------+
| Fruit         | Mango           |January          |
| Fruit         | Apple           |January          |
| Fruit         | Grape           |February         |
| Vegetable     | Tommato         |February         |
| Vegetable     | Cuccumber       |March            |
| Animal        | Chicken         |March            |
| Vegetable     | Onion           |April            |
| Animal        | Fish            |April            |
+---------------+-----------------+-----------------+

使用 phpExcel 可以做到这一点吗？

我试过的：

public function excel()
    {
        $spreadsheet = new Spreadsheet();

        $month = $this->db
            ->select('month')
            ->distinct('category,name')
            ->order_by('category,name')
            ->get('mytable')->result_array();
        $data = $this->db
            ->select('category,name')
            ->order_by('category,name')
            ->get('mytable')->result_array();
        $col_data = 'b';
        $row_category = 1;
        $row_name = 2;
        foreach ($data as $d) {                     
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setCellValue($col_data.$row_category, $d['category']);
            $sheet->setCellValue($col_data.$row_name, $d['name']);
            $col_data++;
        }

        $col_month = 'a';
        $row_month = 3;
        foreach ($month as $m) {
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setCellValue($col_month.$row_month, $m['month']);
            $row_month++;
        }       

        $writer = new Xlsx($spreadsheet);
        $writer->save('hello world.xlsx');
    }

结果是这样的：

请让我知道该怎么做

标签： phpmysqlexcel