PHPSpreadsheet installation and basic usage | Make an Excel File in PHP | PHPSpreadsheet Tutorial #1

PHPSpreadsheet installation and basic usage | Make an Excel File in PHP | PHPSpreadsheet Tutorial #1


Hi everyone. Welcome to my php tutorial video. in this video, i’m about to explain how to use PHP Spreadsheet to generate excel file in PHP. So what is the difference with the PHP Excel library? In fact, this library are made by the same creator of PHP Excel. As the readme says, PHP Spreadsheet is simply the next version of PHP Excel which breaks compatibility to improve the code base quality. and because all efforts have shifted to PHP Spreadsheet, PHP Excel will no longer be maintained. So you should start consider using this library instead of old PHP Excel for your next project. let’s take a look at the documentation. this library supported more file format and also able to make a pdf file out of spreadsheet. You need php version 5.6 or newer to run this library. And also some php extension. For the installation, this library needs composer to manage it’s dependency. For those who are not familiar with composer, composer is a dependency manager for PHP. At it’s getting started page, you can see how to install composer on linux by using the installer. either locally or globally. Or in windows, you can download the executable installer, run it, and it will take care of the rest. Composer is a text-based program so you need to run it from command terminal. In my machine, i already have composer. Before proceeding, make sure that composer is already up and running in your local machine. Now, let’s make a new directory in the root directory. From your terminal, change the current working directory to our new directory by using cd command followed by the path. We can install PHP Spreadsheet by using it’s repo name. The repo name format should looks like the lower-cased github url. For now, let’s just paste the composer command. Composer will make a new json file which contain package name installed in our project. Then composer will try to download the required packages required by PHP Spreadsheet. Looks like we’re encountering a problem here. if we read the message carefully, we’ll see that one of our package requires an php extension called fileinfo, which somehow is not available in my machine. Now let’s make it available. Open the php configuration file. Usually called php.ini find the text “fileinfo”. It seems that the fileinfo extension is commented out in my configuration. So let’s fix it. You may also run through same problem but with different extension in your machine. If that happens, just do a quick google search on how to enable the required extension in your PHP. if nothing goes wrong, the required dependency will be installed without any error. you can also see here that PHPSpreadsheet recommends you to install libraries to handle pdf and render chart. there is a vendor directory created by composer. inside, you will find all package installed in this project and all dependencies. there is a file called autoload. we just need to call this autoload in our script in order to use PHPSpreadsheet. the good thing of using composer is you can add libraries like fpdf, domPDF, or tcpdf with just one command. and use the same autoload file in order to use it in your script. in order to use this library, you can use autoload.php or directly call Bootstrap.php inside PHPSpreadsheet src directory. basically both does the same thing, which is calling the autoload. i recommend you to use autoload.php. so let’s make the spreadsheet script i named it simple1.php. first, just call the autoload file. then to use the spreadsheet library, we call them here using : use PhpOfficePhpSpreadsheetSpreadsheet. for now, let’s just see the getting started page and copy the hello world script there. now let me explain what this codes does. first we call the autoload script. then load the PHPSpreadsheet library by it’s namespace. and also the xlsx writer class to make an excel file. you can read more about php namespace in the link i’ve put in description. then we make a new spreadsheet object. get the current active sheet, which is first sheet. set the value of cell A1 to “Hello World !” next, make an xlsx object with the spreadsheet we’ve just created. save it into a file named hello world dot xlsx in the server. now let’s execute the script in our browser. sorry, seems like i forgot to start the apache server. see there’s nothing happening in browser. because as i’ve said before, the file result is saved in server. now what if instead of saved in the server, i want this file to be downloaded in the browser. to do that, we can simply redirect the page to our file after generation is done. but i don’t recommend this way. let’s just try it out for now. looks like i forgot the semicolon and the name is somewhat wrong. as i have said, i don’t recommend this way. now, the recommended way is to use IOFactory class which came with PHPSpreadsheet. IOFactory are built-in class which allows us to read from and write into files. IOFactory are also shipped with another PHPOffice libraries such as PHPWord and PHPPresentation. now let’s copy our script into a new one. let’s just remove the writing part and use IOFactory instead. we will use createWriter method of IOFactory. also do not forget to load the IOFactory class in our script. we don’t need to save the excel as a file, so we save it directly to PHP output. let’s try it out. it looks scrambled. just alot of strange character. this is not what we expected. this is because what we do is making an excel binary file, yet the server and browser treats it as a normal text file. let’s tell the browser and the server to treat this file as an excel file by changing the header. let’s do google’s magic to see what’s the excel header look like. let’s copy the content type header to define the content type as an excel file. it’s now working. but the file name is somewhat wrong for an excel file. let’s add something to the header. and now it’s working as expected. this concludes this chapter of tutorial. i put the link to the source codes in the description. feel free to ask anything in the comment section. please kindly like my video and subscribe to my channel. see you in the next chapter.

14 thoughts on “PHPSpreadsheet installation and basic usage | Make an Excel File in PHP | PHPSpreadsheet Tutorial #1”

  1. Hi, agree to do a tutorial with phpSpreadSheet explaining the automatic creation of a pivot table in a second sheet from the library of this tutorial?
    please
    thank you

  2. In the minute 10:56 I have a problem, when i am going to write that code, it doesn't work for me. I've your same code but the headers doesn't work. Any help?

Leave a Reply

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