Why export when the selected date of data is not in excel. But if the date period is not selected data appears.
Index
function xlsExport() {
var data = { criteria: $("#select-filter-contact").val(), value: $("#string-input-contact").val(), period: $("#select-filter-contact-period").val(), start: $("#contact-date-start").val(), end: $("#contact-date-end").val() }; strXlsExportUrl = "{{ url('Contact/exportListContact') }}?" + $.param(data); window.location.assign(strXlsExportUrl); }
Controller
public function exportListContactAction(){ $criteria = $this->request->getQuery('criteria'); $value = $this->request->getQuery('value'); $period = $this->request->getQuery('period'); $start = $this->request->getQuery('start'); $end = $this->request->getQuery('end'); $map = [ "1" => "Name", "2" => "Email", "3" => "Phone", "4" => "Mobile", "5" => "MediaId", "6" => "TypeId", "7" => "OccupationId"]; $criteria = $map[$criteria]; $map2 = [ "1" => "CreateDate", "2" => "LastContact" ]; $period = $map2[$period]; $siteId = $this->getSite(); $options = $this->config->database; $dsn = "mysql:host={$options->host};dbname={$options->dbname};charset=utf8"; $opt = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false ]; $db = new PDO($dsn, $options->username, $options->password, $opt); $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); $db->query("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;"); $sql = " select c.*, r1.Description from Contact c left join Reference r1 on c.TypeId=r1.Id where c.SiteId=$siteId and c.ExpireDate='3000-01-01 00:00:00'"; if ($criteria) { $sql .= " and c.`$criteria` like '%$value%' "; } if ($period) { $sql .= " and c.`$period` between '$start' and '$end'"; } $sql .= " "; $query = $db->prepare($sql, array( \PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY )); $db->query("COMMIT ;"); $query->execute(); $fileName = 'Contact-'.Date('YmdGis').'.xls'; header("Content-type: application/vnd-ms-excel"); header("Content-Disposition: attachment; filename=".$fileName); echo '<table border="1" width="100%"> <thead> <tr> <th>No.</th> <th>Name</th> <th>Reference Id</th> <th>ShortName</th> <th>PrintName</th> <th>Phone</th> <th>Fax</th> <th>Email</th> <th>Url</th> <th>Mobile</th> <th>TwitterId</th> <th>FacebookId</th> <th>Type</th> <th>Remarks</th> <th>CreateDate</th> </tr> </thead>'; $no = 1; while ($row = $query->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) { ob_end_flush(); echo ' <tr> <td>'.$no.'</td> <td>'.$row['Name'].'</td> <td>'.$row['ReferenceId'].'</td> <td>'.$row['ShortName'].'</td> <td>'.$row['PrintName'].'</td> <td>'.$row['Phone'].'</td> <td>'.$row['Fax'].'</td> <td>'.$row['Email'].'</td> <td>'.$row['Url'].'</td> <td>'.$row['Mobile'].'</td> <td>'.$row['TwitterId'].'</td> <td>'.$row['FacebookId'].'</td> <td>'.$row['LineId'].'</td> <td>'.$row['Type'].'</td> <td>'.$row['Remarks'].'</td> <td>'.$row['CreateDate'].'</td> </tr> '; ob_start(); $no++; } echo '</table>'; $db->query("COMMIT ;");
}