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 :)