Split and Export into Multiple Excel Sheet Files using PHP
by Vincy. Last modified on July 3rd, 2022.
When we are dealing with huge volume of records export into excel or CSV, we cannot blindly export them into a file. Imagine we have a million records, what would happen if we export those records into a single excel file! In this kind of situation, we should group the records and split them into multiple files so that it will be easy to handle them.
We have seen how to export a long HTML page content into PDF split into a multi-page document.
Exporting a database records via program can be done in various ways. For example, we can do by using web interface with export action control or by requesting REST API providing export services. With the relevant necessity, people will also create the CRON job to take automatic backup by export periodically.
In this article, I have created a web interface with the Export button control. The MySQL database results are retrieved and listed in a web page with an Export option. While triggering the export action, the database results will be separated by a limit constant. By applying the limit barrier, the results are split and downloaded into multiple excel files.
Database Result List with Export Action Control
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.
<?php
require_once ("DBController.php");
$db_handle = new DBController();
$query = "select * from tbl_employee";
$result = $db_handle->runBaseQuery($query);
?>
<!DOCTYPE html>
<head>
<title>Split and Export into Multiple Excel Sheet Files using PHP</title>
<script type="text/javascript"
src="jquery-3.2.1.min.js"></script>
</head>
<body>
<h3>Split and Export into Multiple Excel Sheet Files using PHP</h3>
<?php
if (! empty($result)) {
?>
<table class="table-container" cellspacing="0">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</thead>
<?php
foreach ($result as $k => $v) {
?>
<tr>
<td width="10%"> <?php echo $result[$k]['id']; ?></td>
<td width="40%"> <?php echo $result[$k]['employee_name']; ?></td>
<td width="30%"> <?php echo $result[$k]['employee_salary']; ?></td>
<td width="20%"> <?php echo $result[$k]['employee_age']; ?></td>
</tr>
<?php
}
?>
</table>
<?php
}
?>
<button class="btn-submit" id="btn-export" onclick="getHTMLSplit();">Export to Excel File</button>
</body>
</html>
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.
<script type="text/javascript">
function getHTMLSplit() {
$.ajax({
url: 'splitData.php',
type: 'POST',
dataType: 'JSON',
data: {record_count:<?php echo count($result); ?>},
success:function(response){
exportHTMLSplit(response);
}
});
}
</script>
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.
<?php
require_once ("DBController.php");
$db_handle = new DBController();
define("RECORD_LIMIT_PER_FILE", 5);
$start = 0;
$rowcount = $_POST["record_count"];
$lastPageNo = ceil($rowcount / RECORD_LIMIT_PER_FILE);
for ($i = $start; $i < $lastPageNo; $i ++) {
$query = " SELECT * FROM tbl_employee limit " . $start . " , " . RECORD_LIMIT_PER_FILE;
$result = $db_handle->runBaseQuery($query);
$splitHTML[$i] = '<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</thead>';
foreach ($result as $k => $v) {
$splitHTML[$i] .= '<tr>
<td width="10%">' . $result[$k]['id'] . '</td>
<td width="40%">' . $result[$k]['employee_name'] . '</td>
<td width="30%">' . $result[$k]['employee_salary'] . '</td>
<td width="20%">' . $result[$k]['employee_age'] . '</td>
</tr>';
}
$splitHTML[$i] .= '</table>';
$start = $start + RECORD_LIMIT_PER_FILE;
}
print json_encode($splitHTML);
?>
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 JavaScript, a download link will be created and its properties are set dynamically via programming. This is for triggering the action to download the output excel files to the browser. We have already seen this dynamic download in the code to convert HTML to MS Word format.
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.
<script type="text/javascript">
function exportHTMLSplit(response) {
var random = Math.floor(100000 + Math.random() * 900000)
$(response).each(function (index) {
var excelContent = response[index];
var excelFileData = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
excelFileData += "<head>";
excelFileData += "<!--[if gte mso 9]>";
excelFileData += "<xml>";
excelFileData += "<x:ExcelWorkbook>";
excelFileData += "<x:ExcelWorksheets>";
excelFileData += "<x:ExcelWorksheet>";
excelFileData += "<x:Name>";
excelFileData += "{worksheet}";
excelFileData += "</x:Name>";
excelFileData += "<x:WorksheetOptions>";
excelFileData += "<x:DisplayGridlines/>";
excelFileData += "</x:WorksheetOptions>";
excelFileData += "</x:ExcelWorksheet>";
excelFileData += "</x:ExcelWorksheets>";
excelFileData += "</x:ExcelWorkbook>";
excelFileData += "</xml>";
excelFileData += "<![endif]-->";
excelFileData += "</head>";
excelFileData += "<body>";
excelFileData += excelContent;
excelFileData += "</body>";
excelFileData += "</html>";
var sourceHTML = excelFileData + response[index];
var source = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(sourceHTML);
var fileDownload = document.createElement("a");
document.body.appendChild(fileDownload);
fileDownload.href = source;
fileDownload.download = "sheet_" + random + '_'+(index+1)+'.xls';
fileDownload.click();
document.body.removeChild(fileDownload);
})
}
</script>
DBController.php
This is the Database controller to prepare query statement with MySQLi to handle database access from PHP.
<?php
class DBController {
private $host = "localhost";
private $user = "root";
private $password = "test";
private $database = "blog_samples";
private $conn;
function __construct() {
$this->conn = $this->connectDB();
}
function connectDB() {
$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
return $conn;
}
function runBaseQuery($query) {
$result = $this->conn->query($query);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$resultset[] = $row;
}
}
return $resultset;
}
function runQuery($query, $param_type, $param_value_array) {
$sql = $this->conn->prepare($query);
$this->bindQueryParams($sql, $param_type, $param_value_array);
$sql->execute();
$result = $sql->get_result();
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$resultset[] = $row;
}
}
if(!empty($resultset)) {
return $resultset;
}
}
function bindQueryParams($sql, $param_type, $param_value_array) {
$param_value_reference[] = & $param_type;
for($i=0; $i<count($param_value_array); $i++) {
$param_value_reference[] = & $param_value_array[$i];
}
call_user_func_array(array(
$sql,
'bind_param'
), $param_value_reference);
}
}
?>
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.
--
-- Table structure for table `tbl_employee`
--
CREATE TABLE `tbl_employee` (
`id` int(11) NOT NULL COMMENT 'primary key',
`employee_name` varchar(255) NOT NULL COMMENT 'employee name',
`employee_salary` double NOT NULL COMMENT 'employee salary',
`employee_age` int(11) NOT NULL COMMENT 'employee age'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table';
--
-- Dumping data for table `tbl_employee`
--
INSERT INTO `tbl_employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES
(1, 'Zim Kary', 20800, 61),
(2, 'George', 10750, 63),
(6, 'Williamson', 37200, 61),
(7, 'Harry', 137500, 59),
(8, 'David', 32790, 55),
(11, 'Jenny', 90560, 40),
(13, 'Kim', 70600, 36);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
ADD PRIMARY KEY (`id`);
COMMIT;
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.