ThinkPHP6 data import and export: realize data batch processing

ThinkPHP6 data import and export: realizing batch processing of data

In actual development, we often encounter the need to import and export data in batches, such as importing Excel tables to the database, or export the data in the database to an Excel file. Such operations can improve development efficiency and reduce the workload of manual data entry. This article will introduce how to use the ThinkPHP6 framework to implement batch processing of data, including specific steps and code examples for data import and export.

1. Data import

1. Preparation work

First, you need to install the PHPExcel library in the project to process Excel files. You can use Composer to install PHPExcel and execute the following command:

composer require phpoffice/phpexcel

After the installation is completed, a vendor directory will be generated, which contains the relevant files of the PHPExcel library.

2. Import Excel files

In ThinkPHP6, you can use the request() function to obtain files uploaded by users. First, create a method in the controller to handle the import operation:

public function import()
{
    // 获取上传的文件
    $file = request()->file('file');
    
    // 移动到框架应用根目录/uploads/目录下
    $info = $file->validate(['size' => 1048576, 'ext' => 'xls,xlsx'])->move(ROOT_PATH . 'uploads/');

    if ($info) {
        // 获取上传文件的路径
        $filename = $info->getSaveName();
        
        // 处理Excel导入逻辑
        // ...
        
        // 返回成功信息
        return '数据导入成功！';
    } else {
        // 返回错误信息
        return $file->getError();
    }
}

In the above code, the uploaded file is first obtained through the request() function, and the validity is verified. , limits file size to 1MB, and only allows uploading files in .xls and .xlsx formats. Then use the move() method to move the file to the uploads directory of the framework, and save the file name to the $filename variable.

Next, you can use the PHPExcel library in the import logic to read and process the Excel file. The following is a simple example:

public function import()
{
    // ...

    // 创建PHPExcel对象
    $excel = new PHPExcel();

    // 读取Excel文件
    $reader = PHPExcel_IOFactory::createReader('Excel2007');
    $PHPExcel = $reader->load(ROOT_PATH . 'uploads/' . $filename);

    // 获取第一个工作表
    $sheet = $PHPExcel->getSheet(0);

    // 获取总行数
    $totalRow = $sheet->getHighestRow();

    // 遍历每一行数据
    for ($i = 2; $i <= $totalRow; $i++) {
        // 获取单元格数据
        $name = $sheet->getCell('A' . $i)->getValue();
        $age = $sheet->getCell('B' . $i)->getValue();

        // 处理数据插入操作
        // ...
    }

    // ...
}

In the above code, we use the PHPExcel library to create a PHPExcel object and use the createReader() method to read the Excel file. Then use the getSheet() method to get the object of the first worksheet, and use the getHighestRow() method to get the total number of rows.

Next, by traversing the data of each row, use the getCell() method to obtain the value of the specified cell, and insert the data into the database to complete the import operation.

2. Data export

1. Export database data

First, create a method in the controller to handle the export operation:

public function export()
{
    // 查询数据库数据
    $data = Db::name('user')->select();
    
    // 处理Excel导出逻辑
    // ...
}

In the above code, use the query constructor of ThinkPHP6Db::name('user')->select() to query user data in the database.

2. Export to Excel file

Next, we use the PHPExcel library to export the data to an Excel file:

public function export()
{
    // ...
    
    // 创建PHPExcel对象
    $excel = new PHPExcel();
    
    // 设置工作表标题
    $excel->getActiveSheet()->setTitle('用户数据');

    // 设置表头
    $excel->getActiveSheet()->setCellValue('A1', 'ID');
    $excel->getActiveSheet()->setCellValue('B1', '姓名');
    $excel->getActiveSheet()->setCellValue('C1', '年龄');

    // 设置数据内容
    $row = 2;
    foreach($data as $item) {
        $excel->getActiveSheet()->setCellValue('A' . $row, $item['id']);
        $excel->getActiveSheet()->setCellValue('B' . $row, $item['name']);
        $excel->getActiveSheet()->setCellValue('C' . $row, $item['age']);
        $row++;
    }

    // 导出Excel文件
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="user_data.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
    $writer->save('php://output');
}

In the above code, we created A PHPExcel object and use the setTitle() method to set the title of the worksheet. Then use the setCellValue() method to set the header and data content.

Finally, send the exported Excel file to the browser for download by setting the response header.

Summary

This article introduces how to use the ThinkPHP6 framework to implement batch processing of data, including specific steps and code examples for data import and export. By using the PHPExcel library, we can easily process Excel files, improve development efficiency and reduce the workload of manual data entry. I hope this article is helpful to you and can play a role in actual development.

The above is the detailed content of ThinkPHP6 data import and export: realize data batch processing. For more information, please follow other related articles on the PHP Chinese website!