Skip to main content

PHP Split Export convert Multiple Excel Sheet Files

Today, We want to share with you PHP Split Export convert Multiple Excel Sheet Files.
In this post we will show you Exporting CSV to multiple excel worksheets in PHP, hear for Split large Excel/Csv file to multiple files on PHP or Javascrip we will give you demo and example for implement.
In this post, we will learn about Split and Export into Multiple Excel Sheet Files using PHP with an example.

PHP Split Export convert Multiple Excel Sheet Files

There are the Following The simple About PHP Split Export convert Multiple Excel Sheet Files Full Information With Example and Source code.First of all You simple Create There are The folder and file structure list below.

  1. jquery-3.2.1.min
  2. tbl_member
  3. index.php
  4. splitData.php
  5. DBController.php

Database Script

tbl_member

--
-- Table structure for table `tbl_member`
--

CREATE TABLE `tbl_member` (
  `id` int(11) NOT NULL COMMENT 'primary key',
  `member_name` varchar(255) NOT NULL COMMENT 'member name',
  `member_salary` double NOT NULL COMMENT 'member salary',
  `member_age` int(11) NOT NULL COMMENT 'member age'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table';

--
-- Dumping data for table `tbl_member`
--

INSERT INTO `tbl_member` (`id`, `member_name`, `member_salary`, `member_age`) VALUES
(1, 'Krunal', 20800, 61),
(2, 'Ankit', 10750, 63),
(6, 'Chirag', 37200, 61),
(7, 'Rahul Gandhi', 137500, 59),
(8, 'Jaydeep', 32790, 55),
(11, 'Modi', 90560, 40),
(13, 'Vijay', 85000, 36);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_member`
--
ALTER TABLE `tbl_member`
  ADD PRIMARY KEY (`id`);
COMMIT;

Database Result List with Export Action Control

index.php

runBaseQuery($query);

?>




<title>PHP Split and Export into Multiple Excel Sheet Files</title>



body {
    font-family: Arial;
    width: 550px;
}

.member-table-card {
    border: #e0dfdf 1px solid;
    border-radius: 2px;
    width: 100%;
}

.member-table-card th {
    background: #3d3d3d;
    padding: 15px;
    text-align: left;
}

.member-table-card td {
    padding: 15px;
    border-bottom: #e8e8e8 1px solid;
}

.btn-submit {
    padding: 15px 30px;
    background: #c60000;
    border: #1d1d1d 1px solid;
    color: #f0f0f0;
    font-size: 0.9em;
    border-radius: 2px;
    cursor: pointer;
    margin-top: 15px;
}



    <h3>PHP Split and Export into Multiple Excel Sheet Files</h3>
    
    <table class="member-table-card" cellspacing="0">

        <thead>
            <tr>
                <th>Member ID</th>
                <th>Member Name</th>
                <th>Member Salary</th>
                <th>Member Age</th>

            </tr>
        </thead>
                     $v) {
            ?>
                        <tr>
            <td width="10%"> </td>
            <td width="40%"> </td>
            <td width="30%"> </td>
            <td width="20%"> </td>
        </tr>
                        
                </table>
    


    <button class="btn-submit" id="btn-export">Export to Excel File</button>





function getHTMLSplit() {
	$.ajax({
    		url: 'splitData.php',
    		type: 'POST',
    		dataType: 'JSON',
        data: {record_count:},
    		success:function(response){
           exportHTMLSplit(response); 
    		}
    	});
}

function exportHTMLSplit(response) {
	var random = Math.floor(100000 + Math.random() * 900000)
    $(response).each(function (index) {
      
        var excelContent = response[index];

        var memberExcelList = "";
        memberExcelList += "";
        memberExcelList += "<!--[if gte mso 9]>-->";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "{worksheet}";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += "";
        memberExcelList += excelContent;
        memberExcelList += "";
        memberExcelList += "";

        var memSourceHTML = memberExcelList + response[index];

        var memSource = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(memSourceHTML);
        var fileDownload = document.createElement("a");
        document.body.appendChild(fileDownload);
        fileDownload.href = memSource;
        fileDownload.download = "sheet_" + random + '_'+(index+1)+'.xls';
        fileDownload.click();
        document.body.removeChild(fileDownload);
    }) 
}

splitData.php

<?php
require_once ("DBController.php");
$conn_manage = new DBController();

define("RECORD_LIMIT_PER_FILE", 5);
$counter = 0;

$rowcount = $_POST["record_count"];
$lastPageNo = ceil($rowcount / RECORD_LIMIT_PER_FILE);

for ($i = $counter; $i runBaseQuery($query);
    
    $splitHTML[$i] = '<table class="table table-bordered">

        <thead>
            <tr>
                <th>ID</th>
                <th>Member Name</th>
                <th>Member Salary</th>
                <th>Member Age</th>

            </tr>
        </thead>';
    
    foreach ($response as $k => $v) {
        $splitHTML[$i] .= '<tr>
            <td width="10%">' . $response[$k]['id'] . '</td>
            <td width="40%">' . $response[$k]['member_name'] . '</td>
            <td width="30%">' . $response[$k]['member_salary'] . '</td>
            <td width="20%">' . $response[$k]['member_age'] . '</td>
        </tr>';
    }
    $splitHTML[$i] .= '</table>';
    
    $counter = $counter + RECORD_LIMIT_PER_FILE;
}
print json_encode($splitHTML);
?>

DBController

DBController.php

conn = $this->connectDB();
	}	
	
	function connectDB() {
		$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
		return $conn;
	}
	
    function runBaseQuery($query) {
        $response = $this->conn->query($query);	
        if ($response->num_rows > 0) {
            while($row = $response->fetch_assoc()) {
                $responseset[] = $row;
            }
        }
        return $responseset;
    }
    
    
    
    function runQuery($query, $member_type, $member_value_array) {
        $sql = $this->conn->prepare($query);
        $this->bindQueryParams($sql, $member_type, $member_value_array);
        $sql->execute();
        $response = $sql->get_response();
        
        if ($response->num_rows > 0) {
            while($row = $response->fetch_assoc()) {
                $responseset[] = $row;
            }
        }
        
        if(!empty($responseset)) {
            return $responseset;
        }
    }
    
    function bindQueryParams($sql, $member_type, $member_value_array) {
        $member_value_reference[] = & $member_type;
        for($i=0; $i

Angular 6 CRUD Operations Application Tutorials
PHP-Split-and-Export-into-Multiple-Excel-Sheet-Files-Output
PHP-Split-and-Export-into-Multiple-Excel-Sheet-Files-Output

Read :

Also Read :  Laravel Tips Tricks and Techniques for Developers

Summary

You can also read about AngularJS, ASP.NET, VueJs, PHP.

I hope you get an idea about How to split a large excel file into multiple smaller files.
I would like to have feedback on my Pakainfo.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

Web Developer

Hi, I’m Web developer, PHP developer.Pakainfo.com is the most popular Programming & Web Development blog. Our mission is to provide the best online resources on programming and web development.Any visitors of this site are free to browse our tutorials, live demos, Examples and download scripts.If you have a project that you want to get started, launch quickly, contact me. Email : [email protected]