CakePHP フレームワークを使用してデータのエクスポートとインポートを実装する手順

// UserController.php
class UserController extends AppController {
    public function export(){
        // 获取用户数据
        $users = $this->User->find('all');
        
        // 导出数据
        $this->response->type('application/vnd.ms-excel');

        // 设置文件名
        $filename = 'users_export_' . date('YmdHis') . '.xls';
        $this->response->download($filename);

        // 将数据写入到Excel文件
        $this->loadModel('Excel');
        $this->Excel->writeDataToExcel($users, $this->response->file);
    }
}

// ExcelHelper.php
App::uses('Helper', 'View');
class ExcelHelper extends Helper {
    // 将数据写入到Excel文件
    public function writeDataToExcel($data, $filename) {
        // 创建PHPExcel对象
        $phpExcel = new PHPExcel();

        // 设置默认属性
        $phpExcel->getProperties()
                ->setCreator('')
                ->setLastModifiedBy('')
                ->setTitle('')
                ->setSubject('')
                ->setDescription('')
                ->setKeywords('')
                ->setCategory('');

        // 创建工作表
        $phpExcel->setActiveSheetIndex(0);
        $sheet = $phpExcel->getActiveSheet();

        // 设置标题行
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '姓名');
        $sheet->setCellValue('C1', '年龄');
        // ...

        // 填充数据
        $row = 2;
        foreach ($data as $user) {
            $sheet->setCellValue('A' . $row, $user['User']['id']);
            $sheet->setCellValue('B' . $row, $user['User']['name']);
            $sheet->setCellValue('C' . $row, $user['User']['age']);
            // ...

            $row++;
        }

        // 保存Excel文件
        $writer = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
        $writer->save($filename);
    }
}

// index.ctp
<?php echo $this->Html->link('导出数据', array('controller' => 'user', 'action' => 'export')); ?>
<?php echo $this->Html->link('导入数据', array('controller' => 'user', 'action' => 'import')); ?>

// UserController.php
class UserController extends AppController {
    public function import(){
        if ($this->request->is('post')) {
            // 获取上传的文件
            $file = $this->request->data['User']['file'];

            // 保存上传的文件
            $filename = 'users_import_' . date('YmdHis') . '.xls';
            
            // 将文件保存到指定位置
            move_uploaded_file($file['tmp_name'], WWW_ROOT . 'uploads' . DS . $filename);

            // 导入数据
            $this->loadModel('Excel');
            $this->Excel->readDataFromExcel(WWW_ROOT . 'uploads' . DS . $filename);
        }
    }
}

// ExcelHelper.php
App::uses('Helper', 'View');
class ExcelHelper extends Helper {
    // 从Excel文件中读取数据
    public function readDataFromExcel($filename) {
        // 读取Excel文件
        $phpExcel = PHPExcel_IOFactory::load($filename);
        $sheet = $phpExcel->getActiveSheet();

        // 获取最大行和列数
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();

        // 读取数据
        $data = array();
        for ($row = 2; $row <= $highestRow; $row++) {
            $rowData = array();
            for ($column = 'A'; $column <= $highestColumn; $column++) {
                $cellValue = $sheet->getCell($column . $row)->getValue();
                $rowData[] = $cellValue;
            }
            $data[] = $rowData;
        }

        // 将数据保存到数据库
        $this->loadModel('User');
        foreach ($data as $rowData) {
            $user = array(
                'User' => array(
                    'id' => $rowData[0],
                    'name' => $rowData[1],
                    'age' => $rowData[2],
                    // ...
                )
            );
            $this->User->save($user);
        }
    }
}