PHP Classes

PHP Export Table to Excel: Generate Excel file from database table SQL query

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 167 All time: 8,889 This week: 71Up
Version License PHP version Categories
phpdbtabletoexcel 1.0.0Custom (specified...5PHP 5, Databases, Files and Folders
Description 

Author

shamsh parvez

This package can generate Excel file from database table SQL query.

It can connect to a database using PDO and executes a given SQL database table query.

The class executes the query and then outputs the results to a file in HTML table based format that is served for download and can be read and imported correctly by Microsoft Excel.

Picture of shamsh parvez
  Performance   Level  
Name: shamsh parvez <contact>
Classes: 2 packages by
Country: India India
Age: ???
All time rank: 4179288 in India India
Week rank: 195 Up19 in India India Up

Level 1 Star

Example

<?php

require_once '../class/Excel.php';

use agoussec\class\Export;
$export = new Export();



/********************************************************************************************************|
 * WAY 1 FOR CREATING DATABASE CONNECTIONS |
 *_______________________________________________________________________________________________________|
 *
 *
 * $exportObject = new Export('localhost', 'dbuser', 'password', 'database');
 *
 *
 * EXPLAIN - PASS DATABASE CREDENTIALSE AS PARAMETER WHEN CREATING OBJECT OF CLASS
 *
 */


/********************************************************************************************************|
 * WAY 2 FOR CREATING DATABASE CONNECTIONS |
 *_______________________________________________________________________________________________________|
 *
 *
 * try {
 * $Conn = new PDO('mysql:host=localhost;dbname=dbname', 'dbuser', "dbpassword");
 * $Conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 * } catch(PDOException $e) {
 * return $e->getMessage();
 * }
 *
 *
 * $export->setConnection($Conn);
 *
 *
 * EXPLAIN - CREATE PDO CONNECTION AND PASS PDO CONNECTION OBJECT TO setConnection($CONNECTION) LIKE ABOVE
 *
 */


        try {
            $Conn = new PDO('mysql:host=localhost;dbname=dbname', 'dbuser', "dbpassword");
            $Conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            return $e->getMessage();
        }
        $export->setConnection($Conn);

if (isset($_REQUEST["export"])) {

    $sql = "SELECT
                table2.column1 as `Column 1 `,
                table3.column2 as `Column 2`,
                table1.column3 as `Column 3`,
                table1.column4 as `Column 4`,
                table1.column5 as `Column 5`,
                table1.column6 as `Column 6`,
                table1.column7 as `Column 7`,
                table1.column8 as `Column 8`,
                table1.column9 as `Column 9`
            FROM `table1`
            left JOIN table2 ON table2.id = table1.table2id
            left JOIN table3 ON table3.id = table1.table3id
            WHERE table1.status = 1";
          
                   
    $export->setQuery($sql);

    /********************************************************************************************************|
    * EXPLANATION - DATA [OPTIONAL] |
    *________________________________________________________________________________________________________|
    *
    * $export->setData($ARRAY); // OPTIONAL
    *
    * DEF - ROWS OF DATABASE TABLE DATA IN ARRAY
    *
    * EXPLAIN - YOU CAN DIRECTLY PASS DATABASE TABLE ROWS AND THEN NO NEED TO CREATE DATABASE CONNECTION AND PASSING MYSQL QUERIES
    *
    * PARAM - $ROWARRA
    *
    * EXMAPLE -
    * $export->setData('dsdada'); // ERROR - Data should be Array!
    * $export->setData(); // ERROR - Data can not be empty!
    */
   



   

    /********************************************************************************************************|
    * EXPLANATION - FILENAME [OPTIONAL] |
    *________________________________________________________________________________________________________|
    *
    * $export->setFilename('setFilename'); // OPTIONAL
    *
    * DEF - FILENAME OF GENERATED EXCEL FILE (CURRENT TIMESTAMP WILL BE USER WHEN NOT INITIALISING)
    *
    * EXPLAIN - THIS METHOD WILL ACCEPT STRING DATA TYPE AS PARAMETER
    *
    * PARAM - 'FILENAME'
    *
    */
    $export->setFilename('report-excel');

       
    /*******************************************************************************************************|
    * EXPLANATION - HEADERROW [OPTIONAL] |
    *_______________________________________________________________________________________________________|
    *
    * $export->setHeaderRow(true); // OPTIONAL
    *
    * DEF - HEADERROW IS COLUMNS NAME OF EXCEL SHEET ORIGINATED FROM DATABASE TABLE COLUMN NAME
    *
    * EXPLAIN - THIS METHOD WILL ACCEPT BOOLEAN DATA TYPE AS PARAMETER
    *
    * PARAM - [TRUE, FALSE]
    *
    * TRUE - FOR ADD DATABASE TABLE COLUMN NAME IN EXCEL SHEET (YOU CAN REDEFINE COULUMN NAME USING ALIASE NAME SELECT SQL QUERY )
    * FALSE - FOR NOT USING COULUMN NAME IN EXCEL SHEET
    *
    */

    $export->setHeaderRow(true);


    /*******************************************************************************************************|
    * EXPLANATION - TIMESTAMP [OPTIONAL] |
    *_______________________________________________________________________________________________________|
    *
    * $export->setTimestamp(true); // OPTIONAL
    *
    * DEF - CURRENT TIMESTAMP WILL BE ADDED IN EXCEL FILENAME
    *
    * EXPLAIN - THIS METHOD WILL ACCEPT BOOLEAN DATA TYPE AS PARAMETER
    *
    * PARAM - [TRUE, FALSE]
    *
    * TRUE - FOR USING TIMESTAMP IN FILENAME // report-excel-1616836723.xls
    * FALSE - FOR NOT USING TIMESTAMP IN FILENAME // report-excel.xls
    *
    */
    $export->setTimestamp(true);
    $export->getFile();
}

Details

PHPDBTabletoExcel

Create Excel sheet directly from database table rows


  Files folder image Files (6)  
File Role Description
Files folder imagesrc (2 directories)
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file composer.lock Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files (6)  /  src  
File Role Description
Files folder imageclass (1 file)
Files folder imagedemo (1 file)

  Files folder image Files (6)  /  src  /  class  
File Role Description
  Plain text file Excel.php Class Class source

  Files folder image Files (6)  /  src  /  demo  
File Role Description
  Accessible without login Plain text file index.php Example Example script

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
Downloadphpdbtabletoexcel-2021-04-01.zip 23KB
Downloadphpdbtabletoexcel-2021-04-01.tar.gz 23KB
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:167
This week:0
All time:8,889
This week:71Up

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.