How to use ThinkPHP6 to implement Excel import and export

With the rapid development of the Internet, Excel has become one of the important tools in daily office work for companies and individuals. Therefore, Excel's ability to import and export has become a necessary part of many applications. How to use ThinkPHP6 to implement Excel import and export? Below, this article will introduce it to you in detail.

1. ThinkPHP6 system environment verification

To use ThinkPHP6 to implement Excel import and export, you first need to meet the system environment requirements. In ThinkPHP6, you can use composer to install the phpoffice/phpspreadsheet library to implement Excel processing functions. Execute the following command in the command line to install:

composer require phpoffice/phpspreadsheet

After installation, introduce the PhpOfficePhpSpreadsheetSpreadsheet and PhpOfficePhpSpreadsheetWriterXlsx class libraries in the controller layer. These classes The library will be used in our code behind.

2. Excel export

1. Export Excel data

To use ThinkPHP6 to implement Excel export, you first need to import the data into Excel. We can write corresponding code in the controller layer as needed. For example, when exporting student information, you can obtain relevant information by querying the database.

use appcommonmodelStudent;

public function export()
{
    $students = Student::all();
}

2. Setting the header of the Excel table

The header of the Excel table is a very important part. In order to make the header of the Excel table clear, we can write code to generate the header information of the table. In the code below, we use a loop to achieve this.

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('学生信息表');

$sheet->setCellValue('A1', '序号');
$sheet->setCellValue('B1', '学号');
$sheet->setCellValue('C1', '姓名');
$sheet->setCellValue('D1', '性别');
$sheet->setCellValue('E1', '日期');

3. Write data to Excel table

Next, write the obtained data into Excel table. Loop through the data and write each student's information into the table in turn.

foreach ($students as $key => $item) {
    $num = $key + 2;
    $sheet->setCellValue('A' . $num, $num - 1);
    $sheet->setCellValue('B' . $num, $item->number);
    $sheet->setCellValue('C' . $num, $item->name);
    $sheet->setCellValue('D' . $num, $item->sex);
    $sheet->setCellValue('E' . $num, $item->date);
}

4. Output and save the Excel table to the local

Finally, output and save the generated Excel table to the local. In the code below, we use writer to save the Excel table in .xlsx format and output it to the browser.

$writer = new Xlsx($spreadsheet);
$filename = "学生信息表.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=' . $filename);
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit();

3. Excel import

In addition to exporting Excel, ThinkPHP6 can also implement the import function of Excel. Next, we will introduce to you how to implement Excel import in ThinkPHP6.

1. Upload Excel files and read data

Before importing Excel, we need to upload the Excel file first. Here, we use the file upload class library that comes with ThinkPHP6. After the upload is successful, use the method in the PhpOfficePhpSpreadsheetIOFactory class library to read the data in the uploaded Excel file.

use thinkacadeFilesystem;

$uploadFile = request()->file('file');
$saveName = Filesystem::disk('public')->putFile('excel', $uploadFile);
$filePath = Filesystem::disk('public')->path($saveName);

$spreadsheet = PhpOfficePhpSpreadsheetIOFactory::load($filePath);
$sheet = $spreadsheet->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$data = [];

2. Traverse the Excel table and get the data

After obtaining the Excel table data, we need to traverse the data and use an array to save each row of data in the Excel table.

for($i = 2; $i <= $highestRow; $i++){
    $item['number'] = $sheet->getCellByColumnAndRow(1, $i)->getValue();
    $item['name'] = $sheet->getCellByColumnAndRow(2, $i)->getValue();
    $item['sex'] = $sheet->getCellByColumnAndRow(3, $i)->getValue();
    $item['date'] = date('Y-m-d H:i:s',$sheet->getCellByColumnAndRow(4, $i)->getValue() - 25569)*86400;
    $data[] = $item;
}

3. Import the Excel table into the database

Finally, we import the data in the Excel table into the database. Here, we use the ORM operation that comes with ThinkPHP6 to save the data into the database table.

use appcommonmodelStudent;

Db::startTrans();
try {
    Student::insertAll($data);
    Db::commit();
} catch (Exception $e) {
    Db::rollback();
    return json(['code' => '500', 'msg' => '操作失败']);
}
return json(['code' => '200', 'msg' => '操作成功']);

Summary

This article introduces in detail how to use ThinkPHP6 to implement Excel import and export functions. By using the PhpOfficePhpSpreadsheet class library, we can easily complete Excel-related operations. Excel import and export functions are widely used in enterprise management systems. Understanding and mastering related skills will be of great help to developers.

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