Saturday, 12 July 2014

Export CSV in cakephp

1. Download XLS sheet in CakePHP
In the controller function

function downloadXLS(){
        $this->set('contacttracker', $this->paginate());
$this->layout = false;
header ("Expires: Mon, 28 Oct 2008 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ("Content-type: application/vnd.ms-excel");
header ("Content-Disposition: attachment; filename=contacts_".date('d-m-Y').".xls");
header ("Content-Description: Generated Report" );
}

In the view section download_xls.ctp

<STYLE type="text/css">
.table{font-family:arial; font-size:12px;}
.tdheading{font-weight:bold;color:#fff;background-color:#2E2E2E;border-width: 0.5pt;border: solid;border-color:#fff; }
.tdcontent{border-width:0.5pt; border: solid;}
</STYLE>
<table class="table">
<tr>
<td colspan="17" style="font-size:20px;font-weight:bold;">Contacts Summary</td>
</tr>
<tr>
<td><b>Number of contacts:</b></td>
<td style="text-align:left" colspan="16"><?php echo count($contacttracker);?></td>
</tr>
<tr>
<td><b>Date:</b></td>
<td style="text-align:left" colspan="16"><?php echo date('d/m/Y'); ?></td>
</tr>
<tr>
<td colspan="10"></td>
</tr>
<tr VALIGN="TOP">
<td class="tdheading">Company Name</td>
<td class="tdheading">Title</td>
<td class="tdheading">First Name</td>
<td class="tdheading">Last Name</td>
<td class="tdheading">Email</td>
<td class="tdheading">Function</td>
<td class="tdheading">Designation</td>
<td class="tdheading">Country</td>
<td class="tdheading">State</td>
<td class="tdheading">City</td>
<td class="tdheading">Contact Type</td>
<td class="tdheading">Category</td>
<td class="tdheading">Level</td>
<td class="tdheading">Mobile</td>
<td class="tdheading">Desk Number</td>
<td class="tdheading">Main Number</td>

<td class="tdheading">Stage</td>

<td class="tdheading">Date of Birth</td>
<td class="tdheading">Alternate Email</td>
<td class="tdheading">SBU</td>
<td class="tdheading">Address</td>
<td class="tdheading">Postal Code</td>
<td class="tdheading">LinkedIn</td>
<td class="tdheading">Facebook</td>
<td class="tdheading">Twitter</td>
<td class="tdheading">IM id</td>
</tr>
<?php foreach($contacttracker as $clientprofile) {
$stage = $clientprofile['Contacttracker']['stage'];
if($clientprofile['Contacttracker']['active']==0) $stage = "Inactive";
if($clientprofile['Contacttracker']['stage'] == "") $stage = "No Contract";
if($clientprofile['Contacttracker']['stage'] == "Reqs Received") $stage = "Opening";

//$stage = ($clientprofile['Contacttracker']['active']==0)?'Inactive':(($clientprofile['Contacttracker']['stage']=='')?'Contract':$clientprofile['Contacttracker']['stage']);

?>
<tr>
<td class="tdcontent"><?php echo $clientprofile['Company']['name']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['address_title']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['firstname']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['lastname']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['email']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['Discipline']['name']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['title'];?></td>

<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['Country']['name']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['State']['name']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['City']['name']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['Companytype']['name']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['headquarter'];?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['Level']['name']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['mobcountry'].' '.$clientprofile['Clientcontact']['mobile']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['phonecountry'].' '.$clientprofile['Clientcontact']['phstd'].' '.$clientprofile['Clientcontact']['phone']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['bcountry'].' '.$clientprofile['Clientcontact']['boardstd'].' '.$clientprofile['Clientcontact']['board'].' '.$clientprofile['Clientcontact']['extn']; ?></td>
<td class="tdcontent"><?php echo $stage; ?></td>

<td class="tdcontent">
<?php
if(!empty($clientprofile['Clientcontact']['dob']) && $clientprofile['Clientcontact']['dob'] != "0000-00-00") {
$clientprofile['Clientcontact']['dob'] = str_replace("0000", date("Y"), $clientprofile['Clientcontact']['dob']);
$dob = date('d - M', strtotime($clientprofile['Clientcontact']['dob']));
echo $dob;
}

?>
</td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['email1']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['sbu'] ; ?></td>

<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['address']; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['pincode'] ; ?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['linkedin'];?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['facebook'];?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['twitter'];?></td>
<td class="tdcontent"><?php echo $clientprofile['Clientcontact']['im_id'];?></td>
</tr>
<?php } ?>
</table>

2. Download CSV sheet with PHP

           $con = mysqli_connect("localhost","root","","sudhir_demo") or die(mysqli_error());   
            ini_set('memory_limit','300M'); // mem
            ini_set('max_execution_time', 3000); // time
            header('Content-Type: text/csv; charset=utf-8');
            header('Content-Disposition: attachment; filename=data.csv');
            $output = fopen('php://output', 'w');
            fputcsv($output, array('ID', 'Company Name', 'Created Date'));
            $rows = mysqli_query($con,'SELECT id,company_name, company_createddate FROM company');
            while ($row = mysqli_fetch_array($rows,MYSQLI_ASSOC)) fputcsv($output, $row);
 

0 comments:

Post a Comment