Programming, tutorials, mechatronics, operating systems, and other tech stuff

PHPExcel Generate Excel Spreadsheet Files in PHP with Different Formats

58 comments
PHPExcel is a powerful PHP library (class) which allows us to generate and read Excel spreadsheet file in PHP server-side. Developed by PHPOffice team.

Depending on the circumstances, generating an excel spreadsheet file as a reporting medium is a great way to present reporting data in offline, independent manner. Especially when dealing about printing and lots of numeric data such as financial report.

Reading an excel file using PHP is also a good way to automate data entry. Especially when dealing with batch data entries. And on the end-user side, this method will also ease the user as the interface for entering the data is an excel spreadsheet file which most people are familiar with. And absolutely more agile when compared to web-interfaced forms.

To use this library, simply download the library files here.

Extract it anywhere in your web root directories. Call the PHPExcel.php file in your script and you're good to go.

Here's a Hello World PHP script example to generate an excel file.
I think the comments in pretty self-explanatory.
<?php
require_once 'Classes/PHPExcel.php';

//create PHPExcel object
$excel = new PHPExcel();

//insert some data to PHPExcel object
$excel->setActiveSheetIndex(0)
 ->setCellValue('A1','Hello')
 ->setCellValue('B1','World');

//redirect to browser (download) instead of saving the result as a file

//this is for MS Office Excel 2007 xlsx format
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="test.xlsx"');

//this is for MS Office Excel 2003 xls format
//header('Content-Type: application/vnd.ms-excel');
//header('Content-Disposition: attachment; filename="test.xlsx"');


header('Cache-Control: max-age=0');

//write the result to a file
//for excel 2007 format
$file = PHPExcel_IOFactory::createWriter($excel,'Excel2007');

//for excel 2003 format
//$file = PHPExcel_IOFactory::createWriter($excel,'Excel5');

//output to php output instead of filename
$file->save('php://output');

?>

You can choose in what format the excel file will be generated by uncomment/comment the headers and IOFactory line.

Basically, what needs to be change to save it in different formats is:
for Excel 2007 or above
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="test.xlsx"');
$file = PHPExcel_IOFactory::createWriter($excel,'Excel2007');

and for Excel 2003
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="test.xls"');
$file = PHPExcel_IOFactory::createWriter($excel,'Excel5');

And here's the video if you need a step-by-step explanation.

Part #1 How to install and test php excel (basic usage).

Part #2 Download PHP Generated Excel File.

58 comments :

  1. Thanks a lot very much for the high your blog post quality and results-oriented help. I won’t think twice to endorse to anybody who wants and needs support about this area. https://www.besanttechnologies.com/training-courses/php-training/php-training-institute-in-chennai

    ReplyDelete
  2. Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
    hadoop-training-institute-in-chennai

    ReplyDelete
  3. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Best Electrical Project Center in Chennai | IEEE Electrical Projects in Velachery

    ReplyDelete
  4. Great information. I have got some important suggestions from it. Thank you for sharing..
    Best AWS Training Institute in Chennai | Best AWS Training Institute in Velachery

    ReplyDelete
  5. Hi, am a big follower of your blog. I am really happy to found such a helpful and fascinating post that is written in well manner.
    Final Year Project Center in Chennai | Final Year Project Center in Velachery

    ReplyDelete
  6. Nice post.It Was simple and Effective..Thanks for sharing your valuable information and time. Please keep updating.
    Best cloud Computing Training Institute in Chennai | Best cloud Computing Training Institute in Velachery

    ReplyDelete
  7. Appreciation for really being thoughtful and also for deciding on certain marvelous guides most people really want to be aware of.

    AWS Training in Chennai

    ReplyDelete
  8. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging… Selenium Training in Chennai| Oracle Training in Chennai

    ReplyDelete
  9. Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information.. Big Data Training in Chennai Digital Marketing Training in Chennai

    ReplyDelete
  10. Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information. iOS Training in Chennai | iOS Training institutes in Chennai

    ReplyDelete
  11. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging..
    B.Com Project Center in Chennai | B.Com Project Center in Velachery

    ReplyDelete
  12. Excellent Article ...thank u for sharing, such a valuable content Learners to get good knowledge after read this article.. Devops Training in Chennai | AWS Training in Chennai

    ReplyDelete
  13. Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information... iOS Training in Chennai | Big Data Training in Chennai

    ReplyDelete
  14. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it... Oracle Training in Chennai | Selenium Training in Chennai

    ReplyDelete
  15. Thanks for the useful information about PHP course, give more updates on PHP course, First time I visit your blog nice, I bookmark your blog here after a daily visit. Learn PHP course in chennai

    ReplyDelete
  16. Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating...
    Dotnet Training Institute in Chennai |Dotnet Training Institute in Velachery

    ReplyDelete
  17. The information you have here is really useful to make my knowledge good. It is truly supportive for us and I have accumulated some essential data from this blog.
    C, C++ Training Institute in Chennai | C, C++ Training Institute in Ashok Nagar

    ReplyDelete
  18. Post is very informative… It helped me with great information so I really believe you will do much better in the future.
    AWS Training Institute in Chennai|AWS Training Institute in Velachery

    ReplyDelete
  19. Thank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.
    MS office course in chennai

    ReplyDelete
  20. Awesome Post with unique content. I really get interest to read this post. I hope this article help many of them…
    Best MCSE/MCSA Certificate Training Institute in Chennai|Best MCSE/MCSA Certificate Training Institute in Velachery

    ReplyDelete
  21. The information you have here is really useful to make my knowledge good. It is truly supportive for us and I have accumulated some essential data from this blog.
    Redhat Linux Exam Centre in Chennai| Redhat Linux Exam Centre in Velachery

    ReplyDelete
  22. Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating...
    No.1 MCSE and MCSA Certificate Exam Centre in Chennai |No.1 MCSE and MCSA Certificate Exam Centre in Chennai.

    ReplyDelete
  23. Very useful information to everyone thanks for sharing, learn the latest updated Technology at Best Training institutions
    Salesforce Lightning is the latest updated technology
    Salesforce Training online in India
    Salesforce Training

    ReplyDelete
  24. hi there.
    i liked ur tutorial and i need some help.is there a way to automatically read the excel file column headers and automatically create a database with those headers instead of predefiing the column headers in database for eg

    data1.xlsx

    name emailid company
    tom t@gmail.com gmail ltd
    dick d@gold.com gold industries
    harry h@siver.com silver enterprises

    now without mention the column headers of this file in your php file can we upload it automatically to the server and a database will be created with the column headers as name emailid and company.

    ReplyDelete
  25. I have never read more interesting articles than yours before. You make me so easy to understand and I will continue to share this site. Thank you very much and more power!
    Selenium Training in Chennai
    software testing selenium training
    iOS Course in Chennai
    iOS Training Chennai
    Java training institute in chennai
    Best JAVA Training institute in Chennai

    ReplyDelete
  26. Outstanding blog thanks for sharing such wonderful blog with us ,after long time came across such knowlegeble blog. keep sharing such informative blog with us.
    Air Hostess Training in Chennai | Air Hostess Training Institute in Chennai | Air Hostess Academy in Chennai | Air Hostess Course in Chennai | Air Hostess Institute in Chennai

    ReplyDelete
  27. This comment has been removed by the author.

    ReplyDelete
  28. Thank you for sharing this valuable information. Good job.

    Guest posting sites
    Education

    ReplyDelete
  29. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way. T hank you so much.
    CCNA Training in Chennai
    DevOps Training in Chennai
    Best devOps Training in Chennai
    DevOps foundation certificate
    CCNA Training
    CCNA courses in Chennai

    ReplyDelete
  30. Interesting blog, it gives lots of information to me. Thanks for sharing such a nice blog.

    superbfacts
    Article submission sites

    ReplyDelete
  31. Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information.
    web designing course
    web designing courses
    web development courses
    PHP course
    PHP Training
    Best PHP training in chennai

    ReplyDelete
  32. This is a good post. This post give truly quality information. I’m definitely going to look into it. Really very useful tips are provided here. thank you so much. Keep up the good works.
    Android Development Course in Chennai
    Android app Development Course in Chennai
    Android Training Institute in Chennai
    AWS Certification Training in Chennai
    AWS Training near me
    AWS Training in Chennai

    ReplyDelete
  33. Often programmers find it daunting to understand and modify the code written by others. Hence, they need extra time to maintain and update the PHP applications efficiently.plakatų spausdinimas

    ReplyDelete
  34. Exciting online games with us best casinos online come in and win right now.

    ReplyDelete