[PHP open source class library] simple excel - a reading and writing tool for Excel and CSV files with elegance and performance

This extension package allows you to easily read and write simple Excel and CSV files. Use generators in the background to ensure low memory usage, even when working with large files.

This is an example of how to read Excel or CSV.

SimpleExcelReader::create($pathToFile)->getRows()
   ->each(function(array $rowProperties) {
        // process the row
    });

  

If $pathToFile ends with. csv. The csv file is assumed. If it ends with. xlsx, it is assumed to be an Excel file.

install

You can install the package through composer:

$ composer require spatie/simple-excel

  

usage

 

Read CSV

Imagine you have a CSV file with the following

email,first_name
john@example.com,john
jane@example.com,jane

  

 

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)->getRows();

$rows->each(function(array $rowProperties) {
   // The first $rowProperties of the loop should look like this
   // ['email' => 'john@example', 'first_name' => 'john']
});

  

Read Excel file

Reading Excel files is the same as reading CSV files. Just make sure that the path to the create method provided to SimpleExcelReader ends with xlsx.

Use lazy sets

getRows will return an instance of LazyCollection, which is part of the Laravel framework. Because generators are used in the background, even large files will use less memory.

You can Ad locum . find out about LazyCollection

This is a simple and stupid example. We just want to deal with the first name line with a length greater than 5.

SimpleExcelReader::create($pathToCsv)->getRows()
    ->filter(function(array $rowProperties) {
       return strlen($rowProperties['first_name']) > 5
    })
    ->each(function(array $rowProperties) {
        // processing rows
    });

  

Read a file without a title

If you want to read a file without a title, you should use noHeaderRow()

// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::create($pathToCsv)
    ->noHeaderRow()
    ->getRows()
    ->each(function(array $rowProperties) {
       // The first loop of $rowProperties will look like this
       // [0 => 'john@example', 1 => 'john']
});

  

Create your own reader

First of all, we have introduced box/spout This bag. You can get the interface of a reader through the getReader method \ box \ spin \ reader \ readerinterface

$reader = SimpleExcelReader::create($pathToCsv)->getReader(); 

write file

This shows how to write a CSV file:

$writer = SimpleExcelWriter::create($pathToCsv)
     ->addRow([
        'first_name' => 'John',
        'last_name' => 'Doe',
    ])
    ->addRow([
        'first_name' => 'Jane',
        'last_name' => 'Doe',
    ])

  

; 

The pathToCsv file will contain the following:

first_name,last_name
John,Doe
Jane,Doe

  

Write to Excel file

Writing Excel files is the same as writing CSV files. Just make sure that the path to the create method provided to SimpleExcelWriter ends with xlsx.

Streaming Excel files to browser

Instead of writing files to disk, you can stream them directly to the browser.

$writer = SimpleExcelWriter::streamDownload('your-export.xlsx')
     ->addRow([
        'first_name' => 'John',
        'last_name' => 'Doe',
    ])
    ->addRow([
        'first_name' => 'Jane',
        'last_name' => 'Doe',
    ])
    ->toBrowser();

  

Write to a file without a title

If the file being written does not have a header row, use the noHeaderRow() method.

$writer = SimpleExcelWriter::create($pathToCsv)
    ->noHeaderRow()
    ->addRow([
        'first_name' => 'Jane',
        'last_name' => 'Doe',
    ]);
});

  

This will output:

Jane,Doe

  

Add layout

This package is used at the bottom box/spout Bag. The package contains a StyleBuilder that can be used to format rows. Note that styles can only be used on Excel documents.

use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Common\Entity\Style\Color;

$style = (new StyleBuilder())
   ->setFontBold()
   ->setFontSize(15)
   ->setFontColor(Color::BLUE)
   ->setShouldWrapText()
   ->setBackgroundColor(Color::YELLOW)
   ->build();

$writer->addRow(['values, 'of', 'the', 'row'], $style)

  

For more information about styles, see Spout document.

 

Use alternate delimiter

By default, SimpleExcelReader assumes a separator of,.

How to use other separators:

SimpleExcelWriter::create($pathToCsv)->useDelimiter(';');

  

Get the number of rows written

You can get the number of rows written. This number includes automatically added header rows.

$writerWithAutomaticHeader = SimpleExcelWriter::create($this->pathToCsv)
    ->addRow([
        'first_name' => 'John',
        'last_name' => 'Doe',
    ]);

$writerWithoutAutomaticHeader->getNumberOfRows() // returns 2

  

Using the writer object manually

Based on box/spout Package, so you can get the bottom layer's \ box \ spin \ reader \ writerinterface implementation through getWriter:

$writer = SimpleExcelWriter::create($pathToCsv)->getWriter();

  

For more information, please visit:

Tencent T3-T4 standard boutique PHP architect tutorial directory, as long as you read it to ensure a higher salary (continuous update)

Tags: PHP Excel Laravel less

Posted on Sat, 28 Mar 2020 05:03:36 -0700 by Thresher