PHPExcel Tutorial – Read an Excel File (.xlsx)


In previous video, we covered about how to install PHPExcel to your
project and read the xlsx and put them on the webpage In this video, we’ll explain in detail how to read xlsx file with PHPExcel This is the excel file we use First of all, after we included the PHPExcel, we’ve to specify the path to
the xlsx file we want to read The path is relative to the php file Next, create a reader using PHPExcel_IOFactory::createReaderForFile Once we got the reader, we’ll use it to load a file to get a PHPExcel object Next, use the PHPExcel object to get an active sheet object. Using
getActiveSheet() But if your xlsx file contains more than one sheet, active sheet is always
the last one I’m going to add more sheet to the test.xlsx You’ll see that we got the last sheet data as an active sheet To specify a sheet to read, use getSheet(index) instead of
getActiveSheet() 0 is the first sheet and increament respectively Let’s refresh the page and see the result OK! We got the first sheet back Once we got the sheet object, there are several ways to read through it. The current method we’re using right now is looping through the sheet So we need to know the number of rows we have by using
getHighestRow() You can also get the number of columns the same way by using
getHighestColumn() Next is using a simple for loop to iterate through all rows Then for each row, we can use getCell to get the Cell object We can pass the cell name to getCell just like you see them on the Excel
(A1, A2, B3, etc.) And finally, we’ll call getValue() to get the cell value from Cell object However, if you want to read a random cell instead of reading through
each row. There is another more suitable method! That is we can transform the entire sheet into an array! By calling toArray() from the sheet object The first parameter is the value to be returned if the cell doesn’t exist. So
I’ll pass null as it’s easier to handle later Next boolean is to specify whether formulas on the sheet should be
calculated (ex. SUM, AVG) The second boolean to specify if the cell format should be applied to the
returned data or not For example we’ll get “1246.00” instead of “1246” if the cell format is 2
decimal places And the last one to specify whether the array index should be a simple
running number or the actual cell number on the sheet For example, we can access the first cell (A1) by array[1][‘A’] instead of
array[0][0] if this option was set to true Now I’m going to put the cell A1 value on the webpage I guess we’ve covered all “Need to know” things in order to read the xlsx
file! On the next video, we’ll talk about how to use PHP to download an excel
file from another site and read it on the fly! Thanks for watching and let us know your feedback! Like or subscribe if
you like us 🙂

27 thoughts on “PHPExcel Tutorial – Read an Excel File (.xlsx)

  1. Hi there ! great tutorial 😉
    I just want to know to make PHP Excel even faster, because I've got a big script and the time execution is veeeery large.
    I'm just seein execution of your code is very quick. How did you that ?

  2. This course has helped me a lot, but I would like to know how can I read filtered data (NOT ALL THE DATA) from the Excel File, for example: You have 20 registers in the Excel File:

    Code | Product Name
    1 | Fish
    1 | Notebook
    1 | Wine
    1 | Juice
    1 | Pen
    1 | Book

    2 | Computer
    2 | Wallet

    NOTE: (Code and Product Name are columns…)

    And you know you can filter by something in the Excel file, and I'm filtering by CODE and I will get all the registers with CODE = 1, imagine that the registers that have CODE = 1 are 10.
    So…

    There's any way to get just the registers that have the CODE = 1 and ignore the other ones with CODE = 2 from the Excel file? DON'T CONFUSE WITH THIS CONDITION:

    if($worksheet->getCell('C'.$row)->getValue() != 2) {…

    I'M REFERING TO THE FILTERS INTO EXCEL, WHERE YOU CAN DISPLAY SOME DATA THROUGH BY FILTERS….

  3. Hi, Implement this code and read the .xls, but the .xlsx does not read it, does it require some configuration on the server (IIS) for php?

  4. Be sure to read Heaven Gifts review on my blog before you buy. Please go to marvinreviews. com/heaven-gifts-review/ Thanks, Anselm.

  5. Hello, I use a drop-down list on Excel and I would like to be able to retrieve the values ​​and not the keys of it. Can you help me? for example
    1 = drop-down
    2 = list
    I would like to retrieve the "drop-down" or "list" values. Thank you.

  6. Just one doubt..I have a cell which is having a user defined function. Can i get the calculated value on my web page? I have an excel add-in. The function is defined in that add-in. Is it possible…..Please let me know..

  7. how can I add up an entire column?
    I would like the sum of this column
    echo $worksheet->getCell('B'.$row)->getValue();

    Thanks

  8. Great video! This was a really good tutorial. Does anybody know how to store the values of the for-loop in a value and put it in a excel-sheet? Like: file_put_contents("test.xlsx", $ForLoopValue);

Leave a Reply

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