Exporting data to excel using php

Exporting data to excel using PHP and MySql or MSSQL is very difficult if don't know.  I would suggest you to use great PHPExcel library. It is really powerful, supports variety of formats, can do visual formatting and is easy to use.

You can find more about it at their webpage: http://phpexcel.codeplex.com/. ( PHPExcel has already moved at https://github.com/PHPOffice/PHPExcel ).

But I use very simple php script which is hard coded and you can easily do that. And for that no plugin is required.

1. Just create a php file "export_to_excel.php" which is given below as an example.
2. Within the file call the file of the connection string of table and database. (Here I have used php & Mssql.)
3. Then create no of column header you required using simple HTML table and store it in a variable.
4. Then create table data fetching the record from the database.
5. Finally add the Header for excel and print the variable. That's it.

<?php
    require_once("inc/my_connection.php");

    $asenHeadr = '<table>';
    $asenHeadr .= '<tr style="background-color:#003399;">';
    $asenHeadr .= '<th>SL No</th>';
    $asenHeadr .= '<th>UserName</th>';
    $asenHeadr .= '<th>Name</th>';
    $asenHeadr .= '<th>Father Name</th>';
    $asenHeadr .= '<th>Department</th>';
    $asenHeadr .= '<th>Designation</th>';
    $asenHeadr .= '<th>Date of Birth</th>';
    $asenHeadr .= '<th>Mobile No</th>';
    $asenHeadr .= '<th>Landline No</th>';
    $asenHeadr .= '<th>Gender</th>';
    $asenHeadr .= '<th>Date of Joining</th>';
    $asenHeadr .= '<th>E-Mail ID</th>';
    $asenHeadr .= '<th>Blood Group</th>';
    $asenHeadr .= '<th>Address</th>';
    $asenHeadr .= '</tr>';

    $sqltxt = "select User_Id, CONCAT(User_FNm,' ',User_MNm,' ',User_LNm) as FullName, FatherOrHus, Dept_Cd, Desig_Cd, CONVERT(varchar(25), DOB, 103) as DOB, User_Mob, LandPh, Gender, CONVERT(varchar(25), DOJ, 103) as DOJ, User_Email, BloodGrp, Address from Asen_User_Master where User_Status = 'Active'";

    $sql_res1 = sqlsrv_query($conn, $sqltxt) or die(print_r(sqlsrv_errors(), true));

    $asensl = 1;
    while ($row1= sqlsrv_fetch_array($sql_res1)){
        $Address = $row1['Address'];
        $Address = str_replace("''", "'", $Address);
        $Address = str_replace('""', '"', $Address);

        $asenData .= '<tr>';
        $asenData .= '<td>'.$asensl.'</td>';
        $asenData .= '<td>'.$row1['User_Id'].'</td>';
        $asenData .= '<td>'.$row1['FullName'].'</td>';
        $asenData .= '<td>'.$row1['FatherOrHus'].'</td>';
        $asenData .= '<td>'.$row1['Dept_Cd'].'</td>';
        $asenData .= '<td>'.$row1['Desig_Cd'].'</td>';
        $asenData .= '<td>'.$row1['DOB'].'</td>';
        $asenData .= '<td>'.$row1['User_Mob'].'</td>';
        $asenData .= '<td>'.$row1['LandPh'].'</td>';
        $asenData .= '<td>'.$row1['Gender'].'</td>';
        $asenData .= '<td>'.$row1['DOJ'].'</td>';
        $asenData .= '<td>'.$row1['User_Email'].'</td>';
        $asenData .= '<td>'.$row1['BloodGrp'].'</td>';
        $asenData .= '<td>'.$Address.'</td>';
        $asenData .= '</tr>';
        $asensl = $asensl + 1;
       
    } 
    $asenData .= '</table>';

    header("Content-Type: application/vnd.ms-excel");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("content-disposition: attachment;filename=User_Details.xls");
    $print_asen_doc = $asenHeadr . $asenData;
    print $print_asen_doc;

  ?>

Hope this is an informative for you and I like to thank you for reading this tutorial.