Exporting Data into Multiple Excel sheets in PHP

If you are working with large amount of data and mainly we have use Excel and CSV file format for export data in web development. So, in this post we have describe how to split huge amount of mysql datable data and export into multiple excel or csv file by using PHPExcel library in PHP. Suppose there are millions of data in your database, and that amount of data you cannot export into single excel file. At that time you have to divide your data into different files like what we have do in pagination. Like a pagination we have not load all data in single page, but we have divided same amount of data in different page. Same thing we have to do for export huge amount of data into multiple file using PHP script with PHPExcel library.

There are number of ways we can export database using PHP script can be done number of ways. But here we will export data into multiple excel file, and that file will be store under folder. By click on link, we can download multiple file one by one. So, here exporting data and downloading file has been divided, so chances of crash of our website will be very less.

The output of any web application is to get data in Excel, CSV or PDF format. If you want to make report from your web based application then data must be required in PDF format, but if you want to reuse data in other form, then data must be exported into Excel or CSV format. Because this two format data type is widely used for exchange between two web application. So, here in this post we have make this tutorial for export mysql data into multiple excel file using PHPExcel library in PHP script. Below you can find complete source code of this tutorial.

View Demo

Source Code

index.php

This is index.php file, in this file first we have make database connection, and fetch data from customer table and display on web page. After this we have make form with input select option for select how many records you want to export in single Excel file and below you can find submit button. Once click on submit button then in that block first it will include PHPExcel library. After this first it will calculate how many file want to make from mysql database. After this here object of PHPEcel library has been created and start exporting mysql data in to multiple excel file. Once all data has been exported into multiple files then that file has been stored under folder. These all file download link will be display on web page, and for download file, below you can fine download.php file.

<?php

$connect = new PDO("mysql:host=localhost;dbname=testing","root","");

$query = "SELECT * FROM tbl_customer ORDER BY CustomerID";

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

$total_rows = $statement->rowCount();

$download_filelink = '<ul class="list-unstyled">';

if(isset($_POST["export"]))
{
 require_once 'class/PHPExcel.php';
 $last_page = ceil($total_rows/$_POST["records_no"]);
 $start = 0;
 $file_number = 0;
 for($count = 0; $count < $last_page; $count++)
 {
  $file_number++;
  $object = new PHPExcel();
  $object->setActiveSheetIndex(0);
  $table_columns = array("Nos", "Customer Name", "Gender", "Address", "City", "Postal Code", "Country");
  $column = 0;
  foreach($table_columns as $field)
  {
   $object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
   $column++;
  }

  $query = "
  SELECT * FROM tbl_customer ORDER BY CustomerID LIMIT ".$start.", ".$_POST["records_no"]."
  ";
  $statement = $connect->prepare($query);
  $statement->execute();
  $excel_result = $statement->fetchAll();
  $excel_row = 2;
  foreach($excel_result as $sub_row)
  {
   $object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $excel_row-1);
   $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $sub_row["CustomerName"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $sub_row["Gender"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $sub_row["Address"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $sub_row["City"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $sub_row["PostalCode"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(6, $excel_row, $sub_row["Country"]);
   $excel_row++;
  }
  $start = $start + $_POST["records_no"];
  $object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
  $file_name = 'File-'.$file_number.'.xls';
  $object_writer->save($file_name);
  $download_filelink .= '<li><label><a href="download.php?filename='.$file_name.'" target="_blank">Download - '.$file_name.'</a></label></li>';
 }
 $download_filelink .= '</ul>';
}

?>
<html>
 <head>
  <title>Export Mysql Data into Multiple Excel File using PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 </head>
 <body>
  <div class="container box">
   <h3 align="center">Export Mysql Data into Multiple Excel File using PHP</h3>
   <br />
   <br />
   <form method="post">
    <div class="row">
     <div class="col-md-3" align="right"><label>No. of Records in Each File</label></div>
     <div class="col-md-2">
      <select name="records_no" class="form-control">
       <option value="5">5 per file</option>
       <option value="10">10 per file</option>
       <option value="15">15 per file</option>
      </select> 
     </div>
     <div class="col-md-2">
      <input type="submit" name="export" class="btn btn-success" value="Export to Excel" />
     </div>
     <div class="col-md-5">
      <?php echo $download_filelink; ?>
     </div>
    </div>
   </form>
   <br />
   <div class="table-responsive">
    <table id="customer_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>Customer Name</th>
       <th>Gender</th>
       <th>Address</th>
       <th>City</th>
       <th>Postal Code</th>
       <th>Country</th>
      </tr>
     </thead>
     <tbody>
     <?php
     foreach($result as $row)
     {
      echo '
      <tr>
       <td>'.$row["CustomerName"].'</td>
       <td>'.$row["Gender"].'</td>
       <td>'.$row["Address"].'</td>
       <td>'.$row["City"].'</td>
       <td>'.$row["PostalCode"].'</td>
       <td>'.$row["Country"].'</td>
      </tr>
      ';
     }
     ?>
     </tbody>
    </table>
   </div>
  </div>
  <br />
  <br />
 </body>
</html>

download.php

This file script is used for download exported excel file from folder. Once user click on excel download file then this file script has been execute and it will download particular excel file.

<?php

//download.php

if(isset($_GET["filename"]))
{
 if(file_exists($_GET["filename"]))
 {
  header("Content-Type: application/octet-stream");
  header("Content-Disposition: attachment; filename=" .  $_GET["filename"]);
  readfile($_GET["filename"]);
  unlink($_GET["filename"]);
 }
 else
 {
  echo 'No File Found';
 }
}

?>

Database

Run below SQL script, it will make customer table in your Database.

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_customer`
--

CREATE TABLE `tbl_customer` (
  `CustomerID` int(11) NOT NULL,
  `CustomerName` varchar(250) NOT NULL,
  `Gender` varchar(30) NOT NULL,
  `Address` text NOT NULL,
  `City` varchar(250) NOT NULL,
  `PostalCode` varchar(30) NOT NULL,
  `Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_customer`
--

INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Gender`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Female', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Female', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Male', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', 'Male', '120 Hanover Sq.', 'London', 'WA1 1DP', 'United Kingdom'),
(5, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'Male', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Male', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', 'Male', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'United States'),
(9, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'John Koskitalo', 'Male', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(39, 'Ann Devon', 'Female', '35 King George', 'London', 'WX3 6FW', 'United Kingdom'),
(38, 'Janine Labrune', 'Female', '67, rue des Cinquante Otages', 'Nantes', '44000', 'Finland'),
(37, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(36, 'Elizabeth Brown', 'Female', 'Berkeley Gardens 12 Brewery', 'London', 'WX1 6LT', 'United Kingdom'),
(30, 'Trina Davidson', 'Female', '1049 Lockhart Drive', 'Barrie', 'ON L4M 3B1', 'Canada'),
(31, 'Jeff Putnam', 'Male', 'Industrieweg 56', 'Bouvignies', '7803', 'Belgium'),
(32, 'Joyce Rosenberry', 'Female', 'Norra Esplanaden 56', 'HELSINKI', '380', 'Finland'),
(33, 'Ronald Bowne', 'Male', '2343 Shadowmar Drive', 'New Orleans', '70112', 'United States'),
(34, 'Justin Adams', 'Male', '45, rue de Lille', 'ARMENTIERES', '59280', 'France'),
(35, 'Pedro Afonso', 'Male', 'Av. dos Lusiadas, 23', 'Sao Paulo', '05432-043', 'Brazil'),
(100, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(101, 'Tonia Sayre', 'Female', '84 Haslemere Road', 'ECHT', 'AB32 2DY', 'United Kingdom'),
(102, 'Loretta Harris', 'Female', 'Avenida Boavista 71', 'SANTO AMARO', '4920-111', 'Portugal'),
(103, 'Sean Wong', 'Male', 'Rua Vito Bovino, 240', 'Sao Paulo-SP', '04677-002', 'Brazil'),
(104, 'Frederick Sears', 'Male', 'ul. Marysiuska 64', 'Warszawa', '04-617', 'Poland'),
(105, 'Tammy Cantrell', 'Female', 'Lukiokatu 34', 'HAMEENLINNA', '13250', 'Finland'),
(106, 'Megan Kennedy', 'Female', '1210 Post Farm Road', 'Norcross', '30071', 'United States'),
(107, 'Maria Whittaker', 'Female', 'Spresstrasse 62', 'Bielefeld Milse', '33729', 'Germany'),
(108, 'Dorothy Parker', 'Female', '32 Lairg Road', 'NEWCHURCH', 'HR5 5DR', 'United Kingdom'),
(109, 'Roger Rudolph', 'Male', 'Avenida Julio Saul Dias 78', 'PENAFIEL', '4560-470', 'Portugal'),
(110, 'Karen Metivier', 'Female', 'Rua Guimaraes Passos, 556', 'Sao Luis-MA', '65025-450', 'Brazil'),
(111, 'Charles Hoover', 'Male', 'Al. Tysiaclecia 98', 'Warszawa', '03-851', 'Poland'),
(112, 'Becky Moss', 'Female', 'Laivurinkatu 6', 'MIKKELI', '50120', 'Finland'),
(113, 'Frank Kidd', 'Male', '2491 Carson Street', 'Cincinnati', 'KY 45202', 'United States'),
(114, 'Donna Wilson', 'Female', 'Hallesches Ufer 69', 'Dettingen', '73265', 'Germany'),
(115, 'Lillian Roberson', 'Female', '36 Iolaire Road', 'NEW BARN', 'DA3 3FT', 'United Kingdom');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  ADD PRIMARY KEY (`CustomerID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;

Download