1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Write To Excel Using PHP

Discussion in 'PHP' started by jasbir712, Jun 14, 2012.

  1. jasbir712

    jasbir712 New Member

    Joined:
    Jan 31, 2012
    Messages:
    5
    Likes Received:
    1
    Trophy Points:
    0
    PHPExcel project provides a set of classes, which allow us to Read, Write and Create Excel documents in PHP.

    File formats, supported by PHPExcel are .xls, .xlsx, CSV, .ods, Gnumeric, PDF and HTML.

    Requirements for PHPExcel:

    PHP version >= 5.2.0
    PHP extensions php_zip,php_xml,php_gd2 should be installed & enabled.

    In this article we'll be looking at how to install PHPExcel and how to create an Excel file of the data fetched from MySQL.

    Installating PHPExcel



    • Download the PHPExcel 1.7.7 - PEAR from phpexcel.codeplex.com.
    • Extract the downloaded file.
    • Create a subfolder called 'Classes' under /var/php_files/ (or as you wish) and copy the contents into the folder like /var/php_files/Classes/ or if you are running Windows c:\xampp\htdocs\Classes\
    Now we are ready to go...

    Write New Excel File



    Here I am using employee table with the following data.

    Code:
    mysql> select * from employee;
    +------+------------+-----------------+--------------+
    | id   | first_name | last_name       |    DOJ       |
    +------+------------+-----------------+--------------+
    |    1 | Ramesh     | Prasad          |  1996-09-17  |
    |    2 | Dilip      | Das             |  1987-07-30  |
    |    3 | Prakash    | Chandra         |  1982-10-24  |
    |    4 | Sourav     | Roy             |  1996-07-25  |
    |    5 | Vijay      | Kumar           |  1982-10-18  |
    |    6 | Arun       | Kumar           |  1982-10-11  |
    +------+------------+-----------------+--------------+
    The PHP code to read from the MySQL table and write out the data in a Excel file

    PHP:
    <?php
    /** Error reporting */
    error_reporting(E_ALL);

    /** Include path **/
    ini_set('include_path'ini_get('include_path').';../Classes/');

    /** PHPExcel */
    include 'PHPExcel.php';

    /** PHPExcel_Writer_Excel2007 */
    include 'PHPExcel/Writer/Excel2007.php';

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

    // Connect to MySQL
    $conn mysql_connect('localhost''root''');

    if (!
    $conn) {
        die(
    'Could not connect: ' mysql_error());
    }
    $db_selected mysql_select_db('mydb'$conn);
    if (!
    $db_selected) {
        die (
    'Can\'t use foo : ' mysql_error());
    }

    $result mysql_query("SELECT * from employee");

    // Setting Excel file properties
    // Change the properties detail as per your requirement
    $objPHPExcel->getProperties()->setCreator("Jasbir");
    $objPHPExcel->getProperties()->setLastModifiedBy("Pradeep");
    $objPHPExcel->getProperties()->setTitle("Employee Details");
    $objPHPExcel->getProperties()->setSubject("Employee DOJ Informaton");
    $objPHPExcel->getProperties()->setDescription("Excel 2007 file generated using PHP.");

    // Select current sheet
    $objPHPExcel->setActiveSheetIndex(0);
    // Add some data
    $objPHPExcel->getActiveSheet()->SetCellValue('A1''EMP_ID');
    $objPHPExcel->getActiveSheet()->SetCellValue('B1''First Name');
    $objPHPExcel->getActiveSheet()->SetCellValue('C1''Second Name');
    $objPHPExcel->getActiveSheet()->SetCellValue('D1''Date of Join');

    //column number, which we will be incrementing
    $colnum=1;

    while (
    $row mysql_fetch_assoc($result))
    {
        
    $colnum++;
        
    $objPHPExcel->getActiveSheet()->SetCellValue('A'."$colnum"$row["id"]);
        
    $objPHPExcel->getActiveSheet()->SetCellValue('B'."$colnum"$row["first_name"]);
        
    $objPHPExcel->getActiveSheet()->SetCellValue('C'."$colnum"$row["last_name"]);
        
    $objPHPExcel->getActiveSheet()->SetCellValue('D'."$colnum"$row["DOJ"]);
    }

    // Optionally, set the title of the Sheet
    $objPHPExcel->getActiveSheet()->setTitle('Simple');
     
    // Create a write object to save the the excel
    $objWriter PHPExcel_IOFactory::createWriter($objPHPExcel'Excel2007');

    // save to a file
    $objWriter->save('/home/jasbir/Documents/Employees.xls');

    // You may optionally output the data directly to a browser, here's how
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Employees.xls"');
    header('Cache-Control: max-age=0');
    $objWriter->save('php://output');

    ?>
    Please follow the PHPExcel Documentation for more information of all the functions used above or if you have trouble installing the class.
     

Share This Page