How to use ThinkPHP6 to import and export Excel?

With the development of the Internet, there are increasing demands for data import and export, especially in enterprises. As a very popular office software, Excel is also widely used for data storage and processing. Therefore, how to use ThinkPHP6 to import and export Excel has become a very important issue. This article will introduce the steps for Excel import and export using ThinkPHP6.

1. Excel export

ThinkPHP6 provides a very convenient Excel export tool class - PHPExcel. You can use PHPExcel to export data into Excel files. The specific steps are as follows:

1. Install the PHPExcel class library

Add the PHPExcel class library in the composer.json file:

"require": {
    "phpoffice/phpexcel": "^1.8"
},

Execute the command to install the PHPExcel class library:

composer install

2. Create an Excel export Controller

Create a controller class to handle Excel export requests:

namespace appdmincontroller;

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx as Writer;

class Excel extends Base
{
    public function export()
    {
        // TODO: 导出Excel
    }
}

3. Construct the data source

Before exporting Excel, you need to prepare the data to be exported source. There are usually two ways to obtain data sources:

(1) Get data from the database

namespace appdmincontroller;

use appdminmodelUser as UserModel;
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx as Writer;

class Excel extends Base
{
    public function export()
    {
        $users = UserModel::select()->toArray();

        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '姓名');
        $sheet->setCellValue('C1', '性别');
        $sheet->setCellValue('D1', '年龄');

        $row = 2;
        foreach ($users as $user) {
            $sheet->setCellValue('A' . $row, $user['id']);
            $sheet->setCellValue('B' . $row, $user['name']);
            $sheet->setCellValue('C' . $row, $user['gender']);
            $sheet->setCellValue('D' . $row, $user['age']);
            $row++;
        }

        $writer = new Writer($spreadsheet);
        $writer->save('users.xlsx');
    }
}

(2) Get data from other data sources

If we want to Some data are exported to Excel files, but these data are not stored in the database. For example, we want to export some order information in the form of Excel, etc. At this time, we can obtain this data through other methods, such as obtaining it from the network API interface.

namespace appdmincontroller;

use GuzzleHttpClient;
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx as Writer;

class Excel extends Base
{
    public function export()
    {
        $client = new Client();

        $response = $client->get('https://api.example.com/orders');

        $orders = json_decode($response->getBody()->getContents(), true);

        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', '订单编号');
        $sheet->setCellValue('B1', '下单人');
        $sheet->setCellValue('C1', '订单金额');

        $row = 2;
        foreach ($orders as $order) {
            $sheet->setCellValue('A' . $row, $order['id']);
            $sheet->setCellValue('B' . $row, $order['user']);
            $sheet->setCellValue('C' . $row, $order['amount']);
            $row++;
        }

        $writer = new Writer($spreadsheet);
        $writer->save('orders.xlsx');
    }
}

2. Excel import

It is also very convenient to use ThinkPHP6 to process Excel import. It is also implemented using the PHPExcel class library. The specific steps are as follows:

1. Install the PHPExcel class library

Same as the steps for Excel export, you need to install the PHPExcel class library first.

2. Create Excel Import Controller

Create a controller class to handle Excel import requests:

namespace appdmincontroller;

use PhpOfficePhpSpreadsheetIOFactory;

class Excel extends Base
{
    public function import()
    {
        $file = request()->file('file');

        $info = $file->validate(['ext' => 'xlsx'])->move('uploads');

        if ($info) {
            $filename = 'uploads/' . $info->getSaveName();

            $reader = IOFactory::createReader('Xlsx');
            $spreadsheet = $reader->load($filename);

            $sheet = $spreadsheet->getActiveSheet();
            $highestRow = $sheet->getHighestRow();
            $highestColumn = $sheet->getHighestColumn();

            $data = [];

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

            unlink($filename);

            dump($data);
        } else {
            echo $file->getError();
        }
    }
}

3. Upload Excel file

We need Add an upload form to the view to allow users to upload Excel files to be imported.

<form method="post" action="admin/excel/import"
      enctype="multipart/form-data">
      <input type="file" name="file">
      <input type="submit" value="上传">
</form>

4. Processing imported data

After importing Excel, we can obtain the imported data through the API provided by PHPExcel. In the above code, we use the following code to obtain data:

$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

$data = [];

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

The imported data is stored in the $data variable. We can perform subsequent data processing operations, such as inserting data into the database.

In summary, using ThinkPHP6 to import and export Excel is relatively simple. By using the PHPExcel class library, we can easily read and export Excel files.

The above is the detailed content of How to use ThinkPHP6 to import and export Excel?. For more information, please follow other related articles on the PHP Chinese website!