Export MySQL data to Excel in PHP – PHP Tutorial | VPS and VPN
Web Hosting

Export MySQL data to Excel in PHP – PHP Tutorial

In this PHP Video tutorial we are going to learn how to export mysql data from web page to excel sheet in php. This things is very important in big enterprise application where lots of data are there to make excel file from that data. For Source code – http://www.webslesson.info/2016/02/export-mysql-data-to-excel-in-php-php-tutorial.html

48 thoughts on “Export MySQL data to Excel in PHP – PHP Tutorial

  1. Hello. I have special characters like ÆØÅ in my database table. When the database table is displayed as html everything is fine, but when I export it as excel strange characters appear insted of ÆÅØ. Can anybody help me?

  2. Could you let me know how to export the data on excel when the user selects the row using check box and only check box selected rows gets exported to excel. Thanks

  3. GOOD READ FOR THOSE INTERESTED IN AN EXPANDED SOLUTION to this tutorial: I believe the SOLE purpose of this lesson is to introduce the "working" model of extracting data from the database and output the results in an excel (.xls) file. It is handy. It's a horse being led to water, but one has to continue the remaining way to drink the said water. E.g. There are those asking how to format this example output excel file with a look and desired style as-in the same fashion one would do while solely working in excel.

    The ANSWER is in the latter of the statement. The smartest method of achieving such a goal is to start and begin source-level solely in Excel by creating a worksheet with the same row headers that will be selected from the database…and filling in only a few rows of data; enough to formulate the look and style desired. Then SAVE the excel file as a Web Page (.htm) and review the Excel-based HTML code with what you are looking for included in the inline style section…also noting the Microsoft vendor prefix "mso-" used to author styles via HTML in MS Office documents. One has to read between their programming knowledge lines on how to include the necessary parts to the example excel.php file.

    However and even better, and more Microsoft file compliant way of going about it is to SAVE your formatted and styled file as an XML file from Excel. It is a super solid and true replication of an excel file. Regardless of file save types, you need to open the file in a code editor and review the structure and contents. As you can see, the XML file provides the greatest amount of excel workbook properties; including author, creation and save dates, window size/position, font, sheet name, zoom, AND active position of the selected cell when the document was saved, etc…totally different than extracting data and creating a vanilla excel file.

    As a programmer, you should be able to view the code and know exactly how you will add the full XML code structure and where the WHILE loop from extracted data belongs and how and where to close the XML ending after the loop statement. AND yes the file ($output concatenation) can be saved as .xls file as-in the example. To be assured: test the model and proof-of-concept by using the FIRST formatted sample file you created and saved in XML format; by also making a copy of it on your desktop by holding control-click PC (option-click MAC) and change the file extension of the copy from .xml to .xls and then double-click to open it in Excel. You see it works (parses) just as it does by reading the great, but basic-vanilla table-tag generated .xls file in this tutorial.

    Again, this is an awesome tutorial/example to jumpstart creative programmer juices, but if you want to expand on this and result in a true Microsoft-compliant Excel file the way Excel expects to read it then you have to do the leg work of knowing what it expects by reviewing the code from its perspective FIRST. If you are super serious about having a solid Database-to-Excel conversion program and service for your company; the XML file alone provides enough information to generate a relational database system where all of the properties within can be stored enabling the ability to edit/modify revisions by adding relational form components to the initial viewing page…user roles…even taking the initial page a step further and adding pagination to the VIEW while leaving the OUTPUT as-is. But you would want to create a login page first…use the PDO protocol to connect to the database for security and data-sanitation (that can be done using mysqli as well) due to modified or input data and protection against sql injection.

    Excel is made for expanded records without pagination unlike visual data tools like phpMyAdmin where it's needed, but your web page to review and save-to-file may look a bit strange expanded to display 1,000 records at once. Big Data/Data Warehouse/Data Lake??? No way, don't even…not without pagination. Lol, that's a lot of page scrolling. BTW: It was not mentioned/written (jn code) in the tutorial but make sure you mysqli_close($connect) "out of habit" the SQL connection. I see the reason why, and it is ok for THIS learning example, but you would have to refresh the page anyway to view any newly added records…it is not like there is a refresh button or icon with the addition of AJAX to refresh data without reloading the page (where it re-connects and closes)

    Treat your database(s) like Public Restrooms: Open the door to go in and do your thing (could be any relation of CRUD) while closing the door. When you're done doing your thing (CRUD) you need to get back out in the world to do your other thing(s) so you re-open the door to get back to life while closing the door behind you. #inandout #io #peace #helloworld

  4. Since last week I was trying to search for PHP library to export data to excel but after this video I could export in just 2 minutes. Thank you for sharing this video. You are Great!!!

Leave a Reply

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