We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Error export Excel!! Notify if there is a syntax error

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 ;");

}



43.9k

hi,

I would say,

  1. you're not returning a valid vnd-ms-excel file, but a html document with a table presentation.
  2. why don't you use all the full phalcon mvc features (shared database connection and configuration file, mvc paradigm, ...) ?
  3. I think you'll we be able to find some third party library to help you to build a valid ms-excel file