Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up

Read and write simple Excel and CSV files

License

NotificationsYou must be signed in to change notification settings

spatie/simple-excel

Repository files navigation

Latest Version on PackagistGitHub Workflow StatusTotal Downloads

This package allows you to easily read and write simple Excel and CSV files. Behind the scenes generators are used to ensure low memory usage, even when working with large files.

Here's an example on how to read an Excel or CSV.

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

If$pathToFile ends with.csv a CSV file is assumed. If it ends with.xlsx, an Excel file is assumed.

Support us

We invest a lot of resources into creatingbest in class open source packages. You can support us bybuying one of our paid products.

We highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using. You'll find our address onour contact page. We publish all received postcards onour virtual postcard wall.

Installation

You can install the package via composer:

composer require spatie/simple-excel

Usage

Reading a CSV

Imagine you have a CSV with this content.

email,first_namejohn@example.com,johnjane@example.com,jane
useSpatie\SimpleExcel\SimpleExcelReader;// $rows is an instance of Illuminate\Support\LazyCollection$rows = SimpleExcelReader::create($pathToCsv)->getRows();$rows->each(function(array$rowProperties) {// in the first pass $rowProperties will contain// ['email' => 'john@example.com', 'first_name' => 'john']});

Reading an Excel file

Reading an Excel file is identical to reading a CSV file. Just make sure that the path given to thecreate method ofSimpleExcelReader ends withxlsx.

Working with LazyCollections

getRows will return an instance ofIlluminate\Support\LazyCollection. This class is part of the Laravel framework. Behind the scenes generators are used, so memory usage will be low, even for large files.

You'll find a list of methods you can use on aLazyCollectionin the Laravel documentation.

Here's a quick, silly example where we only want to process rows that have afirst_name that contains more than 5 characters.

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

Reading a file without headers

If the file you are reading does not contain a header row, then you should use thenoHeaderRow() method.

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

Manually setting the headers

If you would like to use a specific array of values for the headers, you can use theuseHeaders() method.

// $rows is an instance of Illuminate\Support\LazyCollection$rows = SimpleExcelReader::create($pathToCsv)    ->useHeaders(['email_address','given_name'])    ->getRows()    ->each(function(array$rowProperties) {// in the first pass $rowProperties will contain// ['email_address' => 'john@example', 'given_name' => 'john']});

If your file already contains a header row, it will be ignored and replaced with your custom headers.

If your file does not contain a header row, you should also usenoHeaderRow(), and your headers will be used instead of numeric keys, as above.

Working with multiple sheet documents

Excel files can include multiple spreadsheets. You can select the sheet you want to use with thefromSheet() method to select by index.

$rows = SimpleExcelReader::create($pathToXlsx)    ->fromSheet(3)    ->getRows();

With multiple spreadsheets, you can too select the sheet you want to use with thefromSheetName() method to select by name.

$rows = SimpleExcelReader::create($pathToXlsx)    ->fromSheetName("sheet1")    ->getRows();

If you want to check if a sheet exists, use thehasSheet() method.

$hasSheet = SimpleExcelReader::create($pathToXlsx)    ->hasSheet("sheet1");

Retrieving header row values

If you would like to retrieve the header row as an array, you can use thegetHeaders() method.

If you have useduseHeaders() to set custom headers, these will be returned instead of the actual headers in the file. To get the original headers from the file, usegetOriginalHeaders().

$headers = SimpleExcelReader::create($pathToCsv)->getHeaders();// $headers will contain// [ 'email', 'first_name' ]

Dealing with headers that are not on the first line

If your file has headers that are not on the first line, you can use theheaderOnRow() methodto indicate the line at which the headers are present. Any data above this linewill be discarded from the result.

headerOnRow accepts the line number as an argument, starting at 0. Blank lines are not counted.

Since blank lines will not be counted, this method is mostly useful for filesthat include formatting above the actual dataset, which can be the case with Excel files.

This is my data sheetSee worksheet 1 for the data, worksheet 2 for the graphs.email , firstnamejohn@example.com,johnjane@example.com,jane
// $rows is an instance of Illuminate\Support\LazyCollection$rows = SimpleExcelReader::create($pathToCsv)    ->trimHeaderRow()    ->headerOnRow(3)    ->getRows()    ->each(function(array$rowProperties) {// in the first pass $rowProperties will contain// ['email' => 'john@example', 'first_name' => 'john']});

Trimming headers

If the file you are reading contains a title row, but you need to trim additional characters on the title values, then you should use thetrimHeaderRow() method.This functionality mimics thetrim method, and the default characters it trims, matches that function.

Imagine you have a csv file with this content.

email , first_namejohn@example.com,johnjane@example.com,jane
// $rows is an instance of Illuminate\Support\LazyCollection$rows = SimpleExcelReader::create($pathToCsv)    ->trimHeaderRow()    ->getRows()    ->each(function(array$rowProperties) {// in the first pass $rowProperties will contain// ['email' => 'john@example', 'first_name' => 'john']});

trimHeaderRow() additionally accepts a param to specify what characters to trim. This param can utilize the same functionality allowed by the trim function's$characters definition including a range of characters.

Convert headers to snake_case

If you would like all the headers to be converted to snake_case, use the theheadersToSnakeCase() method.

Email,First Name,Last Namejohn@example.com,john,doemary-jane@example.com,mary jane,doe
$rows = SimpleExcelReader::create($pathToCsv)    ->headersToSnakeCase()    ->getRows()    ->each(function(array$rowProperties) {// rowProperties converted to snake_case// ['email' => 'john@example', 'first_name' => 'John', 'last_name' => 'doe']    });

Manually formatting headers

You can use a custom formatter to change the headers using theformatHeadersUsing method and passing a closure.

email,first_name,last_namejohn@example.com,john,doemary-jane@example.com,mary jane,doe
$rows = SimpleExcelReader::create($pathToCsv)    ->formatHeadersUsing(fn($header) =>"{$header}_simple_excel")    ->getRows()    ->each(function(array$rowProperties) {// ['email_simple_excel' => 'john@example', 'first_name_simple_excel' => 'John', 'last_name_simple_excel' => 'doe']    });

Manually working with the reader object

Under the hood this package uses thebox/spout package. You can get to the underlying reader that implements\OpenSpout\Reader\ReaderInterface by calling thegetReader method.

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

Limiting the result set

Thetake method allows you to specify a limit on how many rows should be returned.

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

Theskip method allows you to define which row to start reading data from. In this example we get rows 11 to 16.

$rows = SimpleExcelReader::create($pathToCsv)    ->skip(10)    ->take(5)    ->getRows();

Reading cells that contain formulas

Normally, cells containing formulas are parsed and their computed value will be returned. If you want to keep the actual formula as a string, you can use thekeepFormulas method.

$rows = SimpleExcelReader::create($pathToXlsx)    ->keepFormulas()    ->getRows();

Preserve date formatting

By default, when reading a spreadsheet with dates or times, the values are returned asDateTimeImmutable objects. To return a formatted date (e.g., “9/20/2024”) instead, use thepreserveDateTimeFormatting method. The date format will match what’s specified in the spreadsheet.

$rows = SimpleExcelReader::create($pathToXlsx)    ->preserveDateTimeFormatting()    ->getRows();

Preserve empty rows

You can preserve empty rows by using thepreserveEmptyRows method.

$rows = SimpleExcelReader::create($pathToXlsx)    ->preserveEmptyRows()    ->getRows();

Writing files

Here's how you can write a CSV file:

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

The file atpathToCsv will contain:

first_name,last_nameJohn,DoeJane,Doe

Manually set the header from array

Instead of letting the package automatically detect a header row, you can set it manually.

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

Writing an Excel file

Writing an Excel file is identical to writing a csv. Just make sure that the path given to thecreate method ofSimpleExcelWriter ends withxlsx.One other thing to be aware of when writing an Excel file is that the file doesn't get written until the instance ofSimpleExcelWriter is garbage collected.That's when theclose method is called. Theclose method is what finalizes writing the file to disk. If you need to access the file before the instance is garbage collected you will need to call theclose method first.

$writer->close();

Streaming an Excel file to the browser

Instead of writing a file to disk, you can stream it 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();

Make sure to callflush() if you're sending large streams to the browser

$writer = SimpleExcelWriter::streamDownload('your-export.xlsx');foreach (range(1,10_000)as$i) {$writer->addRow(['first_name' =>'John','last_name' =>'Doe',    ]);if ($i %1000 ===0) {flush();// Flush the buffer every 1000 rows    }}$writer->toBrowser();

You could also use a callback.

useSpatie\SimpleExcel\SimpleExcelWriter;useOpenSpout\Common\Entity\Row;$writer = SimpleExcelWriter::streamDownload('user-list.xlsx',function ($writerCallback,$downloadName) {$writerCallback->openToBrowser($downloadName);$writerCallback->addRow(Row::fromValues(['first_name' =>'First Name','last_name' =>'Last Name',    ]));$writerCallback->addRow(Row::fromValues(['first_name' =>'Rakib','last_name' =>'Hossain',    ]));foreach (range(1,10_000)as$i) {$writerCallback->addRow(Row::fromValues(['first_name' =>'Rakib','last_name' =>'Hossain',        ]));if ($i %1000 ===0) {flush();        }    }});$writer->toBrowser();

Writing multiple rows at once

You can useaddRows instead ofaddRow to add multiple rows at once.

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

Writing a file without titles

If the file you are writing should not have a title row added automatically, then you should use thenoHeaderRow() method.

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

This will output:

Jane,Doe

Adding layout

Under the hood this package uses theopenspout/openspout package. That package contains aStyle builder that you can use to format rows. Styles can only be used on excel documents.

useOpenSpout\Common\Entity\Style\Color;useOpenSpout\Common\Entity\Style\CellAlignment;useOpenSpout\Common\Entity\Style\Style;useOpenSpout\Common\Entity\Style\Border;useOpenSpout\Common\Entity\Style\BorderPart;/* Create a border around a cell */$border =newBorder(newBorderPart(Border::BOTTOM, Color::LIGHT_BLUE, Border::WIDTH_THIN, Border::STYLE_SOLID),newBorderPart(Border::LEFT, Color::LIGHT_BLUE, Border::WIDTH_THIN, Border::STYLE_SOLID),newBorderPart(Border::RIGHT, Color::LIGHT_BLUE, Border::WIDTH_THIN, Border::STYLE_SOLID),newBorderPart(Border::TOP, Color::LIGHT_BLUE, Border::WIDTH_THIN, Border::STYLE_SOLID)    );$style = (newStyle())   ->setFontBold()   ->setFontSize(15)   ->setFontColor(Color::BLUE)   ->setShouldWrapText()   ->setBackgroundColor(Color::YELLOW)   ->setBorder($border);$writer->addRow(['values','of','the','row'],$style);

To style your HeaderRow simply call thesetHeaderStyle($style) Method.

$writer->setHeaderStyle($style);

For more information on styles head over tothe Spout docs.

Setting column widths and row heights

By accessing the underlying OpenSpout Writer you can set default column widths and row heights and change the width of specific columns.

SimpleExcelWriter::create(    file:'document.xlsx',    configureWriter:function ($writer) {$options =$writer->getOptions();$options->DEFAULT_COLUMN_WIDTH=25;// set default width$options->DEFAULT_ROW_HEIGHT=15;// set default height// set columns 1, 3 and 8 to width 40$options->setColumnWidth(40,1,3,8);// set columns 9 through 12 to width 10$options->setColumnWidthForRange(10,9,12);    })

Creating an additional sheets

By default, the writer will write to the first sheet. If you want to write to an additional sheet, you can use theaddNewSheetAndMakeItCurrent method.

$writer = SimpleExcelWriter::create($pathToXlsx);Posts::all()->each(function (Post$post)use ($writer) {$writer->nameCurrentSheet($post->title);$post->comments->each(function (Comment$comment)use ($writer) {$writer->addRow(['comment' =>$comment->comment,'author' =>$comment->author,        ]);    });if(!$post->is($posts->last())) {$writer->addNewSheetAndMakeItCurrent();    }});

Using an alternative delimiter

By default theSimpleExcelReader will assume that the delimiter is a,.

This is how you can use an alternative delimiter:

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

Getting the number of rows written

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

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

Disable BOM

You can also disable adding a BOM to the start of the file. BOM must be disabled on create and cannot be disabled after creation of the writer.

A BOM, or byte order mark, indicates a number of things for the file being written including the file being unicode as well as it's UTF encoding type.

SimpleExcelWriter::createWithoutBom($this->pathToCsv,$type);

Additional information about BOM can be foundhere.

Manually working with the writer object

Under the hood this package uses theopenspout/openspout package. You can get to the underlying writer that implements\OpenSpout\Reader\WriterInterface by calling thegetWriter method.

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

Testing

composertest

Changelog

Please seeCHANGELOG for more information on what has changed recently.

Contributing

Please seeCONTRIBUTING for details.

Security

If you've found a bug regarding security please mailsecurity@spatie.be instead of using the issue tracker.

Postcardware

You're free to use this package, but if it makes it to your production environment we highly appreciate you sending us a postcard from your hometown, mentioning which of our package(s) you are using.

Our address is: Spatie, Kruikstraat 22, 2018 Antwerp, Belgium.

We publish all received postcardson our company website.

Credits

Alternatives

License

The MIT License (MIT). Please seeLicense File for more information.


[8]ページ先頭

©2009-2025 Movatter.jp