How To Read CSV And Excel File In PHP Using PhpSpreadsheet

While working on web applications, sometimes we come across a situation where we need to import CSV or Excel file into the database. This is a common way to import large data in the database rather than entering it one by one.

PhpSpreadsheet is the library which provides a support for reading and writing different types of file formats. Below is the screenshot of supported file formats.

File Supported

Before we came to know this library, we were using fgetcsv method for reading the CSV file. And in case of Excel file, we were converting Excel to CSV first and then reading it using fgetcsv() function.

Using PhpSpreadsheet you don’t need to convert Excel to CSV for reading it. This library directly can read the Excel file.

That being said let’s take a look on how to read CSV and Excel file using PhpSpreadsheet.

Installation

For installation of this library, we recommend to use the Composer. Open the terminal in your project root directory and run the command:

composer require phpoffice/phpspreadsheet

Read CSV/Excel File

For the sake of our tutorial, let’s create a file form.php which has file input and submit button.

form.php

<form method="post" enctype="multipart/form-data" action="readfile.php">
    <div class="form-group">
        <label for="exampleInputFile">File Upload</label>
        <input type="file" name="file" class="form-control" id="exampleInputFile">
    </div>
    <button type="submit" class="btn btn-primary">Submit</button>
</form>

We set the action as ‘readfile.php’ where we write the actual code to read CSV/Excel file.

readfile.php

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

$file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

if(isset($_FILES['file']['name']) && in_array($_FILES['file']['type'], $file_mimes)) {

    $arr_file = explode('.', $_FILES['file']['name']);
    $extension = end($arr_file);

    if('csv' == $extension) {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
    } else {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
    }

    $spreadsheet = $reader->load($_FILES['file']['tmp_name']);
    
    $sheetData = $spreadsheet->getActiveSheet()->toArray();
    print_r($sheetData);
}
?>

In the above code, we find out the type of file and basis of that we read the file using the library provided functions. You can read more about this on official documentation here.

That’s it! We hope you understand how to read CSV and Excel file using PhpSpreadsheet. We would like to know your thoughts in the comment section below.

Related Articles

If you liked this article, then please subscribe to our Youtube Channel for video tutorials.

5 thoughts on “How To Read CSV And Excel File In PHP Using PhpSpreadsheet

Leave a Reply

Your email address will not be published. Required fields are marked *