Top Ad unit 728 × 90

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.
Exporting data to excel using php Reviewed by Ashok Sen on 20:49:00 Rating: 5

1 comment:

macduffradaker said...

What is the Jackpot City Jackpot? - Casino Roll
Jackpot 엠비 션 주소 City is one of 빡촌 후기 the biggest progressive online casino in the world. Jackpots are always big, often over 500+ times w88 login bigger, 바카라 룰 and they have the 토토커뮤니티 biggest jackpot

Website Design Company at Kolkata All Rights Reserved © 2014 - 2019
Developed by Asenwebmedia

Contact Form

Name

Email *

Message *

Powered by Blogger.