Importing Excel File Using PHP

In order to read and import an excel file in PHP, we use the PHPExcel library. This library is used to read as well as write to an excel file. It provides a set of classes for the PHP programming language, which allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML etc.

In this tutorial, we will import an MS Excel 2007 file which has xlsx extension. Note that the xls extension also represents excel file but it refers to MS Excel 2003 files and since it has become outdated, we will not work on that.

We create two files – index.php where we upload the excel file and excel-upload.php where we read the excel file using classes provided in PHPExcel and then display the contents of the file in a table. The following code explains how to use this library to read from an uploaded excel file.

index.php – Uploading Excel File

<h2>Excel File Uploader</h2>

<form enctype="multipart/form-data" action="excel-upload.php" method="post" >

    <label class="form-label span3" for="file">File</label>
    <input type="file" name="file" id="file" required />


    <br><br>
    <input type="submit" value="Submit" />

</form>

excel-upload.php – Reading Excel File

<?php

/** Set default timezone (will throw a notice otherwise) */
date_default_timezone_set('Asia/Kolkata');

include '../PHPExcel/IOFactory.php';

if(isset($_FILES['file']['name'])){

    $file_name = $_FILES['file']['name'];
    $ext = pathinfo($file_name, PATHINFO_EXTENSION);

    //Checking the file extension
    if($ext == "xlsx"){

            $file_name = $_FILES['file']['tmp_name'];
            $inputFileName = $file_name;

    /**********************PHPExcel Script to Read Excel File**********************/

        //  Read your Excel workbook
        try {
            $inputFileType = PHPExcel_IOFactory::identify($inputFileName); //Identify the file
            $objReader = PHPExcel_IOFactory::createReader($inputFileType); //Creating the reader
            $objPHPExcel = $objReader->load($inputFileName); //Loading the file
        } catch (Exception $e) {
            die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) 
            . '": ' . $e->getMessage());
        }

        //Table used to display the contents of the file
        echo '<center><table style="width:50%;" border=1>';

        //  Get worksheet dimensions
        $sheet = $objPHPExcel->getSheet(0);     //Selecting sheet 0
        $highestRow = $sheet->getHighestRow();     //Getting number of rows
        $highestColumn = $sheet->getHighestColumn();     //Getting number of columns

        //  Loop through each row of the worksheet in turn
        for ($row = 1; $row <= $highestRow; $row++) {

            //  Read a row of data into an array

            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, 
            NULL, TRUE, FALSE);
            // This line works as $sheet->rangeToArray('A1:E1') that is selecting all the cells in that row from cell A to highest column cell

            echo "<tr>";

            //echoing every cell in the selected row for simplicity. You can save the data in database too.
            foreach($rowData[0] as $k=>$v)
                echo "<td>".$v."</td>";

            echo "</tr>";
        }

        echo '</table></center>';

    }

    else{
        echo '<p style="color:red;">Please upload file with xlsx extension only</p>'; 
    }   

}
?>

In this code,

  • The user uploads the excel file and submits the form.
  • The script first checks if the file extension is correct or not.
  • The script then checks for other discrepancies in the file and throws a warning if anything is found to be incorrect.
  • If everything is found to be alright, then the contents of the file are displayed in a table.

Working of excel-upload.php file

Step 1 – Specify time zone

We start by specifying a time zone. This is necessary because PHPExcel issues a warning if it is not specified.

Step2 – Create object

We create a new PHPExcel object and load the file in that object.

Step 3 – Rows and Columns

We get the maximum number of rows and columns in the excel file.

Step 4 – Read rows

We iteratively read the rows and store the contents of 1 row in an array.

Step 5 – Show contents

The data that is stored in the array is also displayed in the table simultaneously. However, at this point, the data can also be stored in the database.

Original Excel File Contents

Original Excel File

Index Page

Uploading File

Contents shown

Contents shown

Please feel free to download the source code for reference.

View Demo Download source

Other related article can be found at this link Create and Download Excel File Using PHP

This content has been helpful to you?

Thanks for contributing!

Yes No