title: PHPExcel Class Does Not Display First Row from Database
body: <p>I wrote a class to export excel files with a worksheet for each query passed to it.</p>
  
  <p>For some reason my worksheets are missing the first row of results. The headers show up properly, but the first row under the header is missing. </p>
  
  <p>If I DO NOT call the SetHeaderCells() function the first row is there, so I know it is something in my class.</p>
  
  <p>SetHeaderCells() NOT called:
  <a href="https://i.stack.imgur.com/omycy.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/omycy.jpg" alt="enter image description here"></a></p>
  
  <p>But if I do call the SetHeaderCells() then the first row is missing.
  <a href="https://i.stack.imgur.com/R1L1k.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/R1L1k.jpg" alt="enter image description here"></a></p>
  
  <p>Calling this class with:</p>
  
  <pre><code>if(gfQSGetGetVar('Export')=='xls') {
      $ex2 = new ExportToExcel2('Somefile');
      $ex2-&gt;AddSheet('Sheet1', 'Select * from Division;');
      $ex2-&gt;AddSheet('Sheet2', 'Select * from Zone');
      $ex2-&gt;ExportMultiSheet();
  }
  </code></pre>
  
  <p>Class:</p>
  
  <pre><code>require_once 'PhpExcel.php';
  
  class ExportToExcel2 {
  
      public $AllSheetData = [];
      protected $SheetData = [];
      protected $PHPExcel  = '';
      protected $FileName  = '';
  
      function __construct($_filename) {
  
          $this-&gt;FileName = $_filename;
          $this-&gt;PHPExcel = new PHPExcel;
  
          //clean the output buffer before download
          ob_clean();
      }
  
  
      public function AddSheet($_WorkSheetName, $_Query) {
          $this-&gt;SheetData['Sheet_Name'] = $_WorkSheetName;
          $this-&gt;SheetData['Query'] = $_Query;
          $this-&gt;AllSheetData[] = $this-&gt;SheetData;
          unset($this-&gt;SheetData);
      }
  
  
      public function ExportMultiSheet($_ExportType='xls') {
          if(!empty($this-&gt;AllSheetData)) {
              $count=0;$Result='';
              $this-&gt;PHPExcel-&gt;setActiveSheetIndex(0);
              foreach($this-&gt;AllSheetData as $subarray) {
  
                  if($count&gt;0){
                      $this-&gt;PHPExcel-&gt;createSheet(null);
                      $this-&gt;PHPExcel-&gt;setActiveSheetIndex($count);
                  }
                  $count++; 
                  foreach($subarray as $key =&gt; $value) {
  
                      if($key == 'Query') {
                          $Result = dbQuery($value);
                          $this-&gt;SetHeaderCells($Result);
                          $this-&gt;SetbodyCells($Result);
  
                      }
                      if($key =='Sheet_Name')  {
                          $this-&gt;PHPExcel-&gt;getActiveSheet()-&gt;setTitle($value);
                      }
  
                  }
              }
  
              $this-&gt;ExportType($_ExportType);
          }
      }
  
  
      public function ExportSingleSheet($_Query, $_ExportType='xls') {
          $Result = dbQuery($_Query);
          $this-&gt;SetHeaderCells($Result);
          $this-&gt;SetBodyCells($Result);
          $this-&gt;SetProperties();
          $this-&gt;ExportType($_ExportType);
      }
  
  
      private function ExportType($_ExportType) {
          if($_ExportType=='xls') {
              $this-&gt;DownloadXLS();
          } 
          else if($_ExportType=='csv') {
              $this-&gt;DownloadCSV();
          }
      }
  
  
      private function SetProperties() {
  
          //set all columns to align left
          $this-&gt;PHPExcel-&gt;getDefaultStyle()-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  
          //show gridlines?
          $this-&gt;PHPExcel-&gt;getActiveSheet()-&gt;setShowGridlines(true);
  
          //set columns a through z to auto width
          for($col = 'A'; $col !== 'Z'; $col++) {
              $this-&gt;PHPExcel-&gt;getActiveSheet()
                  -&gt;getColumnDimension($col)
                  -&gt;setAutoSize(true);
          }
  
          //set the first sheet to open first
          $this-&gt;PHPExcel-&gt;setActiveSheetIndex(0);
      }
  
  
      private function DownloadXLS() {
          $this-&gt;SetProperties();
          header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
          header('Content-Disposition: attachment;filename="'.$this-&gt;FileName.'-'.date("y.m.d").'.xls"');
          header('Cache-Control: max-age=0');
          $objWriter = PHPExcel_IOFactory::createWriter($this-&gt;PHPExcel, 'Excel2007');
          $objWriter-&gt;save('php://output');
  
          exit;
      }
  
  
      private function DownloadCSV() {
          $this-&gt;SetProperties();
          header('Content-Type: text/csv');
          header('Content-Disposition: attachment;filename="'.$this-&gt;FileName.'-'.date("y.m.d").'.csv"');
          header('Cache-Control: max-age=0');
          $objWriter = new PHPExcel_Writer_CSV($this-&gt;PHPExcel);
          $objWriter-&gt;save("php://output");
  
          exit;
      }
  
  
      private function SetHeaderCells($Result) {
          $row = 1; // 1-based index
          $row_data = sqlsrv_fetch_array($Result, SQLSRV_FETCH_ASSOC);
          $col = 0;
          foreach(array_keys($row_data) as $key) {
              $this-&gt;PHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($col, $row, $key);
              $col++;
          }
      }
  
  
      private function SetBodyCells($Result) {
          $row2 = 2;
          while($row_data = sqlsrv_fetch_array($Result, SQLSRV_FETCH_ASSOC)) {
              $col2 = 0;
  
              foreach($row_data as $key=&gt;$value) {
                  $this-&gt;PHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($col2, $row2, $value);
                  $col2++;
              }
              $row2++;
          }
      }
  }
  </code></pre>
  
  <p><strong>Solved!</strong></p>
  
  <p>The way I resolved this was to use the recommended Metadata function.</p>
  
  <p>Changed the SetHeaderCells function to this:</p>
  
  <pre><code>private function SetHeaderCells($Result) {
      $row = 1; // 1-based index
      $col = 0;
      foreach( sqlsrv_field_metadata($Result) as $fieldMetadata ) {
          foreach( $fieldMetadata as $name =&gt; $value) {
              if($name=='Name') {
              $this-&gt;PHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($col, $row, $value);
              $col++;                
              }
          }
      }
  }
  </code></pre>