In this post i am going to write How to Split and Export into Multiple Excel Sheet Files using PHP.
This is a list page displaying all the records from the database. You can integrate PHP pagination to display paginated results. In this page, an export button control will be shown above the list. By clicking this button the export action will be handled with the jQuery script.
I used the prepared statement with MySQLi for executing database queries. The PHP code to read and display the database result is as follows.
Read Split Data via jQuery AJAX
First, the data are requested from the database and split into several HTML tables. This process is done with a jQuery AJAX call. The splitData.php file is accessed via AJAX to get the partitioned HTML table results. The array of HTML table results will be encoded in a JSON format. The AJAX success callback receives the JSON response from the PHP and executes the JavaScript export action.
This PHP code set the maximum records that are allowed per excel file with a limit constant. Based on these constants the database result iteration count and the HTML table result count will be varied.
JavaScript Code to Convert HTML Table to Excel
Multiple HTML table results are iterated using jQuery each() with the reference of the JSON object. In each iteration, the HTML formatted table data will be converted into the excel format using JavaScript.
In this example, the file names for the multiple excel files will be varied by using JavaScript aggregate function Math.random(). All the file names will have the same prefix as defined. With this prefix, the generated random number and the extensions will be appended.
DBController.php
This is the Database controller to prepare query statement with MySQLi to handle database access from PHP.
Database Script
The tbl_employee table data is required to run this example in your PHP environment. Import the below script by using your database client before running this code.
In our previous tutorial you have learned Live Table Edit with Jquery and Ajax. In this tutorial you will learn Live HTML table edit or inline table edit is a very user friendly feature that enable users to edit HTML table value directly by clicking on table cells. In this tutorial you will learn how to implement live editable HTML table with jQuery and PHP. We will use jQuery plugin Tabledit that provides AJAX enabled in place editing for HTML table cells.
Good luck and I hope this article can be useful. See you in the next article…