Laravel development: How to read and write Excel files using Laravel Excel and PHPOffice?

With the widespread use of Excel files in the business and financial fields, integrating Excel files into web applications has become one of the skills required by developers. Since Laravel is a popular PHP framework, there are many packages in its ecosystem that can help us read and write Excel files. This article will introduce how to use Laravel Excel and PHPOffice library to read and write Excel files in Laravel applications.

What is Laravel Excel?

Laravel Excel is a powerful Excel processing tool developed for the Laravel framework. This tool provides an easy-to-use API that can help us import and export Excel files easily. One of the main advantages of this tool is that it allows direct manipulation of Excel files without relying on Excel software.

Installing Laravel Excel can be done through Composer. Go to the directory of the Laravel project in the terminal and run the following Composer command:

composer require maatwebsite/excel

This command will download the latest version of Laravel Excel from Packagist and automatically complete the installation process.

How to export Excel file using Laravel Excel?

Laravel Excel provides an Excel class, which is a proxy class through which you can create new Excel files or open and edit existing Excel files. We can use this class to export Excel files in Laravel application.

Create a new Excel file in the project, we can use the following template provided by Laravel Excel:

<?php

namespace AppExports;

use MaatwebsiteExcelConcernsFromCollection;
use MaatwebsiteExcelConcernsWithHeadings;

class ExampleExport implements FromCollection, WithHeadings
{
    public function headings(): array
    {
        return [
            'ID',
            'Name',
            'Email',
            'Phone',
        ];
    }

    /**
    * @return IlluminateSupportCollection
    */
    public function collection()
    {
        return User::select('id', 'name', 'email', 'phone')->get();
    }
}

In the above code, we define a class named ExampleExport. This class uses the FromCollection and WithHeadings interfaces, which provide the methods needed to generate Excel files.

In the headings() method, we define the headers of the Excel file, which are listed in the first row of the Excel worksheet. In the collect() method, we retrieve the user record from the database and return it to the controller that called the class.

To export an Excel file, we can instantiate ExampleExport in the controller method as follows:

public function export()
{
    return Excel::download(new ExampleExport, 'users.xlsx');
}

In the above code, we use the download() method provided by Laravel Excel from ExampleExport exports an Excel file. This method requires two parameters: the first parameter is the ExampleExport instance, and the second parameter is the Excel file name.

When you access the URL of the export method in your browser, you will be prompted to download the users.xlsx file.

How to import Excel files using Laravel Excel?

When reading an Excel file and importing its data into the database, we can use the import() method provided by Laravel Excel. This method accepts three parameters: the file object, the task callback, and the worksheet name.

To demonstrate how to import an Excel file, we will create a class called UserImport as shown below:

<?php

namespace AppImports;

use AppUser;
use MaatwebsiteExcelConcernsToModel;

class UserImport implements ToModel
{
    public function model(array $row)
    {
        return new User([
            'name' => $row[0],
            'email' => $row[1],
            'phone' => $row[2],
        ]);
    }
}

In the above code, we implemented the UserImport class using the ToModel interface. The ToModel interface provides a required model() method, which converts each row of Excel data into a model object. In the above code, we have parsed the first three columns from the Excel file and used them to create a new user record.

Use the import() method in the controller to import the Excel file, as shown below:

public function import(Request $request)
{
    $file = $request->file('file');
    Excel::import(new UserImport, $file);
    return redirect()->back()->with('success', 'Excel file imported successfully.');
}

In the above code, we get the uploaded Excel file object and pass it through Laravel Excel's import( ) method to import it into the UserImport class. If the import is successful, the system will send a redirect response to the user with a success message of "Excel file imported successfully".

How to use PHPOffice library to read and write Excel files?

PHPOffice is a PHP library used to read and write different types of office files, such as Excel, Word and PowerPoint, etc. PHPOffice, unlike Laravel Excel, is not developed for a specific framework and can be used in any PHP application.

Before installing PHPOffice, you need to ensure that the PHP Zip extension and the PHP XML extension are installed.

Use Composer to install the Spreadsheet library of PHPOffice, you can use the following command:

composer require phpoffice/phpspreadsheet

To create a new Excel file, we can use the following code:

<?php

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World!');
$sheet->setCellValue('B1', 'This is PHPOffice.');

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

In the above In the code, we first create a new spreadsheet and add "Hello World!" and "This is PHPOffice." cells to the first two columns of its first row. We then save the spreadsheet to the hello.xlsx file.

To open and edit an existing Excel file, we can use the following code:

<?php

use PhpOfficePhpSpreadsheetIOFactory;

$spreadsheet = IOFactory::load('hello.xlsx');
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('C1', 'This cell has been added.');

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

In the above code, we use the IOFactory class to load an existing Excel file from disk. We then opened the file's active worksheet and added a new cell to it. Finally, we save the updates to the original file.

Conclusion

In this article, we introduced how to read and write Excel files in Laravel application using Laravel Excel and PHPOffice library. We learned how to export and import Excel files using Laravel Excel, and how to create, open, and edit existing Excel files using the PHPOffice library. These techniques should make it easier for you to integrate Excel files into your Laravel application.

The above is the detailed content of Laravel development: How to read and write Excel files using Laravel Excel and PHPOffice?. For more information, please follow other related articles on the PHP Chinese website!