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.

PHP code – Write data in excel file

1. //Include PHPExcel
2. require_once("Classes/PHPExcel.php");
4. //Create a PHPExcel object
5. $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.

1. //Set document properties
2. $objPHPExcel->getProperties()->setCreator("Mitrajit Samanta")
3. ->setLastModifiedBy("Mitrajit Samanta")
4. ->setTitle("User's Information")
5. ->setSubject("User's Personal Data")
6. ->setDescription("Description of User's")
7. ->setKeywords("")
8. ->setCategory("");
10. //Set default font style and size
11. $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')
12. ->setSize(10);
14. //Set the first row as the header row
15. $objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID')
16. ->setCellValue('B1', 'NAME')
17. ->setCellValue('C1', 'MOBILE')
18. ->setCellValue('D1', 'COUNTRY');
20. //Rename the worksheet
21. $objPHPExcel->getActiveSheet()->setTitle('USER INFO');
23. //Set active worksheet index to the first sheet, so Excel opens this as the first sheet
24. $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

1. $sql = "select * from `user_details`";
2. $res = mysqli_query($con, $sql);
3. if(mysqli_num_rows($res)>0)
4. {
5. $i = 2;
6. while($row = mysqli_fetch_object($res)) {
7. $objPHPExcel->getActiveSheet()->setCellValue('A'.($i), $row->id)
8. ->setCellValue('B'.($i), $row->name)
9. ->setCellValue('C'.($i), $row->mobile)
10. ->setCellValue('D'.($i), $row->country);
11. $i++;
12. }
13. }

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

1. //Dynamic name, the combination of date and time
2. $filename = date('d-m-Y_H-i-s').".xlsx";
4. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
6. //if you want to save the file on the server instead of downloading,
7. //comment the last 3 lines and remove the comment from the next line
8. //$objWriter->save(str_replace('.php', '.xlsx', $filename));
9. header('Content-type: application/vnd.ms-excel');
10. header('Content-Disposition: attachment; filename='.$filename);
11. $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

1. CREATE TABLE IF NOT EXISTS `user_details` (
2. `id` int(11) NOT NULL AUTO_INCREMENT,
3. `name` varchar(50) NOT NULL,
4. `mobile` bigint(10) NOT NULL,
5. `country` varchar(50) NOT NULL,
6. PRIMARY KEY (`id`)
7. );
10. INSERT INTO `user_details` (`id`, `name`, `mobile`, `country`) VALUES
11. (1, 'Aritra Samanta', 9999999991, 'India'),
12. (2, 'Rina Saha', 9999999992, 'Australia'),
13. (3, 'Anil Das', 9999999993, 'London'),
14. (4, 'Akash Samanta', 9999999994, 'America'),
15. (5, 'Niharika Roy', 9999999995, 'Bulgeria');

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

1. <?php
2. define('HOSTNAME','localhost');
3. define('DB_USERNAME','database_username');
4. define('DB_PASSWORD','database_password');
5. define('DB_NAME', 'database_name');
7. $con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("error");
8. //Check connection
9. if(mysqli_connect_errno($con)) echo "Failed to connect MySQL: " .mysqli_connect_error();
10. ?>

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

1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
2. <html xmlns="http://www.w3.org/1999/xhtml">
3. <head>
4. <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
5. <title>Write data in excel file from database using PHP || Mitrajit's Tech Blog</title>
6. <style>
7. body { font-family:Arial, Helvetica, sans-serif; font-size:14px; }
8. h1 { clear:both; margin-bottom:30px; font-size:17px; }
9. h1 a { font-weight:bold; color:#0099FF; }
10. span { clear:both; display:block; margin-bottom:30px; }
11. span a { font-weight:bold; color:#0099FF; }
13. #submitBtn {
14. padding:5px 15px;
15. background-color:#0099FF;
16. border-radius:5px;
17. margin-bottom:5px;
18. border:2px solid #ccc;
19. color:#fff;
20. cursor:pointer;
21. font-weight:bold;
22. }
23. #submitBtn:hover {
24. background-color:#00CCFF;
25. }
26. table { border:1px solid #ccc; width:600px; color:#fff; }
27. table th {
28. background-color:#0099FF;
29. color:#fff;
30. }
31. td {
32. background-color:#00CCFF;
33. height:40px;
34. text-align:center;
35. }
36. .div_ok, .div_fail {
37. width:590px;
38. background-color:#3399FF;
39. padding:5px;
40. margin-top:10px;
41. color:#fff;
42. }
43. .div_fail {
44. background-color:#FF0000;
45. }
46. </style>
47. </head>
49. <body>
50. <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>
52. <?php
53. include("db.php");
54. $sql = "select * from `user_details`";
55. $res = mysqli_query($con, $sql);
56. if(mysqli_num_rows($res) > 0) {
57. ?>
58. <form action="excel.php" method="post">
59. <input type="submit" name="submitBtn" id="submitBtn" value="Generate Excel" />
60. <table cellpadding="5" cellspacing="1">
61. <tbody>
62. <tr>
63. <th>SL.</th>
64. <th>NAME</th>
65. <th>MOBILE</th>
66. <th>COUNTRY</th>
67. </tr>
68. <?php
69. while($row = mysqli_fetch_array($res)) {
70. ?>
71. <tr>
72. <td align="center"><?php echo $row['id']; ?>.</td>
73. <td><?php echo $row['name']; ?></td>
74. <td align="center"><?php echo $row['mobile']; ?></td>
75. <td><?php echo $row['country']; ?></td>
76. </tr>
77. <?php
78. }
79. ?>
80. </tbody>
81. </table>
82. </form>
83. <?php
84. }
85. ?>
86. </body>
87. </html>

excel.php

1. <?php
2. include('db.php');
3. //Include PHPExcel.php
4. require_once("Classes/PHPExcel.php");
6. //Create a PHPExcel object
7. $objPHPExcel = new PHPExcel();
9. //Set document properties
10. $objPHPExcel->getProperties()->setCreator("Mitrajit Samanta")
11. ->setLastModifiedBy("Mitrajit Samanta")
12. ->setTitle("User's Information")
13. ->setSubject("User's Personal Data")
14. ->setDescription("Description of User's")
15. ->setKeywords("")
16. ->setCategory("");
18. // Set default font
19. $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')
20. ->setSize(10);
22. //Set the first row as the header row
23. $objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID')
24. ->setCellValue('B1', 'NAME')
25. ->setCellValue('C1', 'MOBILE')
26. ->setCellValue('D1', 'COUNTRY');
28. //Rename the worksheet
29. $objPHPExcel->getActiveSheet()->setTitle('USER INFO');
31. //Set active worksheet index to the first sheet, so Excel opens this as the first sheet
32. $objPHPExcel->setActiveSheetIndex(0);
36. /*************** Fetching data from database ***************/
37. $sql = "select * from `user_details`";
38. $res = mysqli_query($con, $sql);
39. if(mysqli_num_rows($res)>0)
40. {
41. $i = 2;
42. while($row = mysqli_fetch_object($res)) {
43. $objPHPExcel->getActiveSheet()->setCellValue('A'.($i), $row->id)
44. ->setCellValue('B'.($i), $row->name)
45. ->setCellValue('C'.($i), $row->mobile)
46. ->setCellValue('D'.($i), $row->country);
47. $i++;
48. }
49. }
51. //Dynamic name, the combination of date and time
52. $filename = date('d-m-Y_H-i-s').".xlsx";
53. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
54. //if you want to save the file on the server instead of downloading,
55. //comment the last 3 lines and remove the comment from the next line
56. //$objWriter->save(str_replace('.php', '.xlsx', $filename));
57. header('Content-type: application/vnd.ms-excel');
58. header('Content-Disposition: attachment; filename='.$filename);
59. $objWriter->save("php://output");

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