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

  1. Tutorial it is very helpful, thank you so much though the file created to my computer it alert as if harmful file when trying to open it

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

  3. 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!!!

  4. Hey, it worked on a test file, but on my main file is has some conflict with other scripts… what can I do? :/

  5. Judging by the code, I think this will print all the rows regardless of the number.. Can I just use a For loop instead of while?

  6. this code is not working on live project.but its working on localhost…..can anyone plz help me to fix this problem

  7. Please help about my error

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

  8. this is a great tutorial.
    but it is working only in localhost, but not in website.
    i want to filter data by date and that filtered data should be exported to excel. can anyone help me.
    i am using PHP as backend.

  9. that tutorial really helped solve doubts about doing downloadable files! i just have one question, how can i add bordering and design to my table? thanks!

  10. Simple to Understand Thank you so Much.
    i have one question how can i change excel sheet direction (rtl or ltr) !!?

  11. Hello! I have a problem concerning the code. If I were to export this data " 8/10 " it becomes " 10-Aug" in the excel file. Care to help an old man? 😀

  12. This is a surprisingly helpful video, thank you. I only say surprisingly because the majority of video's I've watched haven't been and I'd more or less given up. Good job.

Leave a Reply

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