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
  • Pingback: Create and Download Excel File Using PHP | Tuts Heap()

  • Pingback: Create and Download Excel File Using PHP | Tuts Heap()

  • Pedram Vdl

    Thanks 🙂

  • prash

    i need code like form fields, instead of displaying what ever i give data in excel sheet.
    i need form like row fields given in the excel sheet according to fields display input field for each row field.
    finally i need form type empty labels w.r.t row fields which we given in the excel sheet. please help 🙂

  • Pyungdoo Jeong

    i’m having problem in importing excel file written with date like 2017-01-01 . it displayed as 42370.
    how can I correct it

    • Vaibhav Goyal

      42370 is the Excel serial number for the date 2017-01-01 because that is the number of days since January 1, 1900 (Excels base date). Excel saves date in this format. You would get the same result by entering: =DATEVALUE(“22/10/2013”) in excel.
      You need to either save the date in text format in excel file or convert these number of days into date by writing your custom PHP function.

    • Josh

      Did you get a fix for this? I tried using another function to format it correctly however I didn’t have any luck….

      Thanks

  • Hello, I am running into a slight issue. When I press submit it goes to excel-upload.php, but the page stays white (Blank). Any thoughts?

    • Josh

      Mine is showing up blank as well. What did you do to resolve this?

      Thanks!

      • Just for others: When php returns a blank page make sure your display errors are turned on. Once display errors is on then that blank page will actually be a bunch of error messages.

  • Oh sorry I fixed it. It was not sending through the file, my bad.

  • Shobith

    I am getting an error like this

    Warning: require_once(Classes/PHPExcel.php): failed to open stream: No such file or directory in C:xampphtdocsimportexcel-upload.php on line 4

    Fatal error: require_once(): Failed opening required ‘Classes/PHPExcel.php’ (include_path=’C:xamppphpPEAR’) in C:xampphtdocsimportexcel-upload.php on line 4

    • Vaibhav Goyal

      You are not including the PHPExcel.php file properly. Please check if you have the specified the correct path.

      • Shobith

        Thanks a lot it’s working now. How can I generate a word report with graph representation from the same uploaded excel sheet. Appreciate if you could help

  • Varga Richard – VargaSoft

    I got following error messages: Fatal error: ‘break’ not in the ‘loop’ or ‘switch’ context in D:xampphtdocsclient-engineering.compublic_htmlread_xlsx3PHPExcelCalculationFunctions.php on line 581

  • Sana Sarwar Memon

    thanks alot! i tried from two weeks but from this article i am able to do the script…

  • Mark Cabalar

    hi please show us how to import this on database… Thank you! nice tuts

    • Maxim Krauskopf

      $Query = “INSERT into teilnehmer ( termin_id, weg_id, mea, pers_nr, flaechen_bez, anrede, name_1, name_2, anschrift, ort, email ) values (‘” . $weg_termin_id . “‘, ‘” . $weg_id . “‘”;

      //echoing every cell in the selected row for simplicity. You can save the data in database too.
      $ignoreRow = 0;
      foreach($rowData[0] as $k=>$v) {
      //echo “”.$v.””;
      if($k == 0) {
      $Query .= ” ,'” . str4db($v) . “‘”; // Erste Spalte!
      if( empty($v) || !is_numeric($v) ){
      $ignoreRow = 1;
      }
      } else {
      $Query .= ” ,'” . str4db($v) . “‘”;
      }
      }

      if($ignoreRow == 0 ) {
      $Query .= ” )”;
      if( doDbQuery($Query, $Link) ) {
      $teilnehmerAnzahl++;
      }
      } else {
      $ignoreRow = 1;
      }

      str4db, doDbQuery is a function

  • Vinayak Barge

    not working

  • Vinayak Barge

    Uploading Excel File Using PHP please send code .

  • Maxim Krauskopf

    SUPER!
    $dateiPfad = sprintf(‘%s/Excel-uploader-downloader-php/PHPExcel/IOFactory.php’, $_SERVER[“DOCUMENT_ROOT”]);
    include $dateiPfad;