Are you looking for ways to programmatically generate excel file using PHP? You search may ends here. This article is the answer to your question.
I am a web application developer, so I can relate to the emerging need of integrating third party libraries on a project to cover various functionality.
While creating an accounting related web application you may need to deal with excel file reading and writing.
There is no meaning in creating a PHP library that gets you job done when you have a well tested free library available for the same purpose.
Are you aware of the PHPExcel library? It was formerly hosted on the Microsoft's codeplex.com network.
It was a very popular library that helped thousands of PHP open source developers creating and reading excel file right under the PHP code.
Apparently the project has moved from codeplex to github. The last stable release under codeplex.com was 1.8.0 around 2014. There has been active development going under the github repository.
The initial PHPExcel library can be found under PHPExcel github repository. But the next version development has been moved to newer PHPSpreadsheet github repository.
The both the PHPExcel and PHPspreadsheet github repositories are created under the PHPOffice project. You will able to find other office related PHP libraries like PHPPresentation , PHPWord , PHPVisio .
See Also: How to Integrate Authorize.Net AIM Using PHP
Now let's start with the library and the PHP code for generating excel file. We will be creating a simple excel file with single workbook with meta information.
Here is how we will address the problem:
- Project creation
- Resolving dependency using composer
- Code
- Creating required objects
- Setting document properties
- Adding data to excel cells
- Naming the working sheet
- Making excel file download
1. Project Creation
Setup your project. I prefer using either netbeans IDE or sublime as a code editor. I will be using the Netbeans IDE throughout this tutorial.
Create a project named phpexcel under your htdocs directory. I recommend you create a new test project as we will be using composer to inject the dependency. Once you find the test results promising, you may perform the same action on your existing project as well.
2. Resolving Dependency Using Composer
I hope you are comfortable with composer. If you are using any code editors like the Sublime or NPP you may need to use composer command line utility.
To be able to use composer via command line or via IDE you must need to have installed and configured composer on your machine. Refer composer website for installation and configuration guidance.
Now create a composer.json file on the root of your project directory. Copy the composer.json file data from the official PHPSpreadsheet github repository.
Once done save the file. Run the composer install command via command prompt. Alternatively you can perform the Composer -> Install action on the Netbeans.
Make sure you have the src folder containing all the required PHPspreadsheet classes.
3. Code
Let's start the most awaited part. I hope you made all the requisites filled by now.
Creating Required Objects
The first this we will include is the Bootstrap.php file. Do you see the magic constant __DIR__ ? It is used to get the directory of the included file. In our case it will be the directory location of Bootstrap.php file.
include __DIR__. './vendor/src/Bootstrap.php';
Next we will create an instance of Sample class. You can find the class under the src/PHPSpreadsheet/Helper/Sample.php file.
We will be using this class's isCli() method to identify whether the request is made from command line or the web interface. As we are offering the excel file generation and download on the fly. We will only allow the web requests.
$helper = new \PhpOffice\PhpSpreadsheet\Helper\Sample();
if ($helper->isCli()) {
echo 'This example should only be run from a Web Browser' . PHP_EOL;
return;
}
Now let's create new Spreadsheet object. We will use this only object to create an excel file, add values to various cells, setting active sheet and adding the title.
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
Setting Document Properties
If you have observed almost all the MS office files have the meta file information. The information such as the author, title, publisher, organization, description and some others.
Let's set these information for the file we will be generating now. We will use the same $spreadsheet object for this purpose.
All the method that we used to set the document property are pretty self explanatory. For instance setCreator will set the name of document creator, setTitle will set the title of the document.
// Set document properties
$spreadsheet->getProperties()->setCreator('Darshan')
->setLastModifiedBy('Your Name')
->setTitle('First excel file')
->setSubject('Excel test document')
->setDescription('Generating the excel file using phpspreadsheet library.')
->setKeywords('php excel file IO');
Adding Data To Excel Cells
Here comes the interesting part. In your PHP web application you may need to write values to various excel cells. A1, A2, A3 are the three cell of the first excel row A.
Ideally you will be looping through the rows and columns to set the desired data to certain cells. Having a clear excel row/column or cell mapping in mind help you code faster.
$spreadsheet->setActiveSheetIndex(0)
->setCellValue('A1', 'Hello')
->setCellValue('B2', 'world!')
->setCellValue('C1', 'Hello')
->setCellValue('D2', 'world!');
Got question with setActiveSheetIndex(0)? If you remember while creating a MS excel file you get three default sheets named something like sheet1, sheet2, and sheet3.
So, we created our first sheet using the setActiveSheetIndex(0) function. We are using the setCellValue(‘CELL', ‘Value').
The function is pretty straight forward. We are passing two arguments. The first one represents the excel cell and the second represents the value that will be setting to specified cell.
Naming the Working Sheet
Handing over an excel file with sheet named as sheet1 or sheet2 would not be a good idea. How about renaming the sheet?
You probably need to perform this action on your production code. Let's cover it now. To be able to do we need to set the desired sheet as an active sheet.
Here is how you will be doing it:
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setTitle('simple');
We have set the first sheet (zeroth index is referred as the first sheet) as an active sheet and then we are using the setTitle(‘STRING') method to set the title of the active sheet.
Making Excel File Download
Ideally in your PHP web application you may want to generate the excel file on the fly based on user's selected data or specific action and prompt the excel file download.
There is no meaning keeping those excel file on your server when you are generating the file from the data on your database.
We will be using the header properties to make the file download prompt as the response.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01-simple.xlsx"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;
We have set the content-type in the first line that helps browser understand that the response content will be an excel or spreadsheet document.
The second line content-disposition will actually make the file download. Please pay attention to the filename attribute. You will be replacing the 01-simple.xlsx name with an appropriate name in your web application.
We made the cache-control max-age to 0 meaning every time a request made our code will generate a new excel file. No browser cache should be used. Useful when you want to make sure that on every request the file should have the newest data from the database.
The last two lines before the exit statements are important. The first line creates an instance of createWriter and store the spreadsheet data into the $writer object.
And the last statement we are writing the excel/spreadsheet file data into the output stream using the php://output . We are writing the data into the output stream which help us skipping the physical file creation process. Or this is how we are generating file on the fly!
Conclusion
Working with excel file would be a tough job if you need to create the entire code by yourself. But hopefully there is a popular library called PHPSpreadsheet that makes your job easy! Just use this library to create excel file on your web application on the fly!