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

PHPExcel Generate Excel Spreadsheet Files in PHP with Different Formats

81 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.

81 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
  35. I have read your blog its very attractive and impressive. I like it your blog.

    Guest posting sites
    Education

    ReplyDelete
  36. Nice post... Really you are done a wonderful job. Thanks for sharing such wonderful information with us. Please keep on updating...

    Best Java Training Academy in Kanchipuram

    ReplyDelete
  37. the blog is very useful, interesting and informative. thank you for sharing the blog with us. keep on updating. Best web designing and development courses in kanchipuram|

    ReplyDelete
  38. Really very great information to be provided and the All points discussed were worth reading and i’ll surely work with them all one by one.
    Best C and C++ Programming Training Academy in Kanchipuram

    ReplyDelete
  39. Awesome post. Really you are shared very informative concept... Thank you for sharing. Keep on updating...

    Best Graphics Designing Training Academy in Kanchipuram

    ReplyDelete
  40. Thanks for this greatful information. all this information is very important to all the users and can be used good at all this process
    Best Tally ERP 9.0 Training Academy in Kanchipuram

    ReplyDelete
  41. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me

    Best Cloud Computing Training Academy in Kanchipuram

    ReplyDelete
  42. Thanks for this grateful information. all this information is very important to all the users and can be used good at all this process.


    Best Web Designing Training Academy in Kanchipuram

    ReplyDelete
  43. Your Blog is really awesome with useful and helpful content for us.Thanks for sharing ..keep updating more information.
    Best java Training Institute in in Kanchipuram

    ReplyDelete
  44. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site
    Best web design Training Institute in Kanchipuram

    ReplyDelete
  45. thank you for such a great article with us. hope it will be much useful for us. please keep on updating..
    Best software testing Training Institute in Kanchipuram

    ReplyDelete
  46. i was analyzing your article and wondered if you had taken into consideration growing an ebook going concerning for this difficulty. Your writing would sell it sudden. you have lots of writing school. Advanced Excel Training Mumbai

    ReplyDelete
  47. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
    Best Advanced Amazon Web Services Course Training Institute in kanchipuram|

    ReplyDelete
  48. REALLY VERY EXCELLENT INFORMATION. I AM VERY GLAD TO SEE YOUR BLOG FOR THIS INFORMATION. THANKS FOR SHARING. KEEP UPDATING.
    Best Hardware and Networking Course Training Institute in kanchipuram|

    ReplyDelete
  49. This post was quite awesome and interesting to read. Congrats for your work. Thanks a lot for providing this with us. Keep on updating this with us regularly:
    Best Selenium Automation Course Training Institute in kanchipuram|

    ReplyDelete