Write data in excel file from database using PHP

  •  
  •  
  •  
  •  
  •  
  •  

In this article, I will show you how to use PHPExcel library to write data in excel file. The data which will be written in the excel file will be fetched from the database. In this tutorial we will use MySQL database, you can choose another database if you want. You can also check my previous tutorial which lets you help to Read excel file and import data into MySQL database using PHP.

Write data in excel file from database using PHP

PHP code – Write data in excel file

//Include PHPExcel
require_once("Classes/PHPExcel.php");

//Create a PHPExcel object
$objPHPExcel = new PHPExcel();

Before starting anything you should include the PHPExcel.php page into your web application. Then create a new object of PHPExcel class.

//Set document properties
$objPHPExcel->getProperties()->setCreator("Mitrajit Samanta")
               ->setLastModifiedBy("Mitrajit Samanta")
               ->setTitle("User's Information")
               ->setSubject("User's Personal Data")
               ->setDescription("Description of User's")
               ->setKeywords("")
               ->setCategory("");

//Set default font style and size
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')
                                          ->setSize(10);

//Set the first row as the header row
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID')
                ->setCellValue('B1', 'NAME')
                ->setCellValue('C1', 'MOBILE')
                ->setCellValue('D1', 'COUNTRY');

//Rename the worksheet
$objPHPExcel->getActiveSheet()->setTitle('USER INFO');

//Set active worksheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

You can set different types of properties to the excel file such as creator’s name, who last modified, title, subject and much more. Also, you can manage the font family and size of the excel file. In the last line, the cells from A1 to D1 of the first row is set as the header row. setTitle(…) function rename the worksheet name. setActiveSheetIndex(0) function set the first worksheet as active sheet. Excel will open this as the first sheet.

Fetching data from database – Write data in excel file

$sql = "select * from `user_details`";
$res = mysqli_query($con, $sql);
if(mysqli_num_rows($res)>0)
{
   $i = 2;
   while($row = mysqli_fetch_object($res)) {
    $objPHPExcel->getActiveSheet()->setCellValue('A'.($i), $row->id)
                    ->setCellValue('B'.($i), $row->name)
                    ->setCellValue('C'.($i), $row->mobile)
                    ->setCellValue('D'.($i), $row->country);									  
      $i++;
   }
}

The above simple query returns all rows from the table. The initial value of the variable $i is 2. Because the first row of the excel is already occupied as the header row. So, we will start inserting the data from the second row. The cell range will be A2:D2. Next A3:D3. We will set the value of each cell by calling the function setCellValue(“cell number”,”value”).

Generate the excel file

//Dynamic name, the combination of date and time
$filename = date('d-m-Y_H-i-s').".xlsx";

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

//if you want to save the file on the server instead of downloading, 
//comment the last 3 lines and remove the comment from the next line
//$objWriter->save(str_replace('.php', '.xlsx', $filename));
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
$objWriter->save("php://output");

The file name of the excel is dynamic, the combination of date and time. At last, we forced to download the file as an excel in the browser. If you want to save file on the server instead of downloading comment the last three lines and remove the comment from the line //$objWriter->save(str_replace(‘.php’, ‘.xlsx’, $filename));.

Database – Write data into CSV file

CREATE TABLE IF NOT EXISTS `user_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `mobile` bigint(10) NOT NULL,
  `country` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);


INSERT INTO `user_details` (`id`, `name`, `mobile`, `country`) VALUES
(1, 'Aritra Samanta', 9999999991, 'India'),
(2, 'Rina Saha', 9999999992, 'Australia'),
(3, 'Anil Das', 9999999993, 'London'),
(4, 'Akash Samanta', 9999999994, 'America'),
(5, 'Niharika Roy', 9999999995, 'Bulgeria');

Create the above table into the database and insert the dummy data into that table.

<?php
define('HOSTNAME','localhost');
define('DB_USERNAME','database_username');
define('DB_PASSWORD','database_password');
define('DB_NAME', 'database_name');

$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
//Check connection
if(mysqli_connect_errno($con))	echo "Failed to connect MySQL: " .mysqli_connect_error();
?>

Create a PHP file “db.php” in the root folder and write the above database connection code into it. Change the database credentials according to yours.

Download the complete source code

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Write data in excel file from database using PHP || Mitrajit's Tech Blog</title>
<style>
body { font-family:Arial, Helvetica, sans-serif; font-size:14px; }
h1 { clear:both; margin-bottom:30px; font-size:17px; }
h1 a { font-weight:bold; color:#0099FF; }
span { clear:both; display:block; margin-bottom:30px; }
span a { font-weight:bold; color:#0099FF; }

#submitBtn {
  padding:5px 15px;
  background-color:#0099FF;
  border-radius:5px;
  margin-bottom:5px;
  border:2px solid #ccc;
  color:#fff;
  cursor:pointer;
  font-weight:bold;
}
#submitBtn:hover {
  background-color:#00CCFF;
}
table { border:1px solid #ccc; width:600px; color:#fff; }
table th {
  background-color:#0099FF;
  color:#fff;
}
td {
  background-color:#00CCFF;
  height:40px;
  text-align:center;
}
.div_ok, .div_fail {
  width:590px;
  background-color:#3399FF;
  padding:5px;
  margin-top:10px;
  color:#fff;
}
.div_fail {
  background-color:#FF0000;
}
</style>
</head>

<body>
  <h1>Read the full article -- <a href="http://www.mitrajit.com/2016/12/write-data-excel-file-database-using-php/" target="_blank">Write data in excel file from database using PHP</a> -- in <a href="http://www.mitrajit.com">Mitrajit's Tech Blog</a></h1>
  
<?php
include("db.php");
$sql = "select * from `user_details`";
$res = mysqli_query($con, $sql);
if(mysqli_num_rows($res) > 0) {
  ?>
  <form action="excel.php" method="post">
    <input type="submit" name="submitBtn" id="submitBtn" value="Generate Excel" />
    <table cellpadding="5" cellspacing="1">
      <tbody>
        <tr>
          <th>SL.</th>
          <th>NAME</th>
          <th>MOBILE</th>
          <th>COUNTRY</th>
        </tr>
    <?php
    while($row = mysqli_fetch_array($res)) {
      ?>
        <tr>
          <td align="center"><?php echo $row['id']; ?>.</td>
          <td><?php echo $row['name']; ?></td>
          <td align="center"><?php echo $row['mobile']; ?></td>
          <td><?php echo $row['country']; ?></td>
        </tr>
      <?php
    }
    ?>
      </tbody>
    </table>
  </form>
  <?php
}
?>
</body>
</html>

excel.php

<?php
include('db.php');
//Include PHPExcel.php
require_once("Classes/PHPExcel.php");

//Create a PHPExcel object
$objPHPExcel = new PHPExcel();

//Set document properties
$objPHPExcel->getProperties()->setCreator("Mitrajit Samanta")
               ->setLastModifiedBy("Mitrajit Samanta")
               ->setTitle("User's Information")
               ->setSubject("User's Personal Data")
               ->setDescription("Description of User's")
               ->setKeywords("")
               ->setCategory("");

// Set default font
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')
                                          ->setSize(10);

//Set the first row as the header row
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID')
                ->setCellValue('B1', 'NAME')
                ->setCellValue('C1', 'MOBILE')
                ->setCellValue('D1', 'COUNTRY');
                
//Rename the worksheet
$objPHPExcel->getActiveSheet()->setTitle('USER INFO');

//Set active worksheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);



/*************** Fetching data from database ***************/
$sql = "select * from `user_details`";
$res = mysqli_query($con, $sql);
if(mysqli_num_rows($res)>0)
{
   $i = 2;
   while($row = mysqli_fetch_object($res)) {
    $objPHPExcel->getActiveSheet()->setCellValue('A'.($i), $row->id)
                    ->setCellValue('B'.($i), $row->name)
                    ->setCellValue('C'.($i), $row->mobile)
                    ->setCellValue('D'.($i), $row->country);									  
      $i++;
   }
}
                
//Dynamic name, the combination of date and time
$filename = date('d-m-Y_H-i-s').".xlsx";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//if you want to save the file on the server instead of downloading, 
//comment the last 3 lines and remove the comment from the next line
//$objWriter->save(str_replace('.php', '.xlsx', $filename));
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
$objWriter->save("php://output");

Download the full source code from the download link below and please like and share the tutorial link to others.

About Mitrajit

2 comments on “Write data in excel file from database using PHP

  1. Thanks for the code (Mysql Export to Excel), Though I am relatively new in PHP programming, but I am battling with this programming challenge:
    I have set-up formulas and charts in Excel sheets 2 and sheet 3 respectively, but I want to export mysql query to the first sheet which will serve as input for sheets 2 and 3.
    The query is successfully exported but the formulas in sheets 2 and 3 are wiped off.
    Is this achievable? Please help.

Leave a Reply

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

Prove you are a human *