PHPExcel Class Does Not Display First Row from Database

Question

I wrote a class to export excel files with a worksheet for each query passed to it.

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.

If I DO NOT call the SetHeaderCells() function the first row is there, so I know it is something in my class.

SetHeaderCells() NOT called:

But if I do call the SetHeaderCells() then the first row is missing.

Calling this class with:

if(gfQSGetGetVar('Export')=='xls') {
    $ex2 = new ExportToExcel2('Somefile');
    $ex2->AddSheet('Sheet1', 'Select * from Division;');
    $ex2->AddSheet('Sheet2', 'Select * from Zone');
    $ex2->ExportMultiSheet();
}

Class:

require_once 'PhpExcel.php';

class ExportToExcel2 {

    public $AllSheetData = [];
    protected $SheetData = [];
    protected $PHPExcel  = '';
    protected $FileName  = '';

    function __construct($_filename) {

        $this->FileName = $_filename;
        $this->PHPExcel = new PHPExcel;

        //clean the output buffer before download
        ob_clean();
    }


    public function AddSheet($_WorkSheetName, $_Query) {
        $this->SheetData['Sheet_Name'] = $_WorkSheetName;
        $this->SheetData['Query'] = $_Query;
        $this->AllSheetData[] = $this->SheetData;
        unset($this->SheetData);
    }


    public function ExportMultiSheet($_ExportType='xls') {
        if(!empty($this->AllSheetData)) {
            $count=0;$Result='';
            $this->PHPExcel->setActiveSheetIndex(0);
            foreach($this->AllSheetData as $subarray) {

                if($count>0){
                    $this->PHPExcel->createSheet(null);
                    $this->PHPExcel->setActiveSheetIndex($count);
                }
                $count++; 
                foreach($subarray as $key => $value) {

                    if($key == 'Query') {
                        $Result = dbQuery($value);
                        $this->SetHeaderCells($Result);
                        $this->SetbodyCells($Result);

                    }
                    if($key =='Sheet_Name')  {
                        $this->PHPExcel->getActiveSheet()->setTitle($value);
                    }

                }
            }

            $this->ExportType($_ExportType);
        }
    }


    public function ExportSingleSheet($_Query, $_ExportType='xls') {
        $Result = dbQuery($_Query);
        $this->SetHeaderCells($Result);
        $this->SetBodyCells($Result);
        $this->SetProperties();
        $this->ExportType($_ExportType);
    }


    private function ExportType($_ExportType) {
        if($_ExportType=='xls') {
            $this->DownloadXLS();
        } 
        else if($_ExportType=='csv') {
            $this->DownloadCSV();
        }
    }


    private function SetProperties() {

        //set all columns to align left
        $this->PHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        //show gridlines?
        $this->PHPExcel->getActiveSheet()->setShowGridlines(true);

        //set columns a through z to auto width
        for($col = 'A'; $col !== 'Z'; $col++) {
            $this->PHPExcel->getActiveSheet()
                ->getColumnDimension($col)
                ->setAutoSize(true);
        }

        //set the first sheet to open first
        $this->PHPExcel->setActiveSheetIndex(0);
    }


    private function DownloadXLS() {
        $this->SetProperties();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$this->FileName.'-'.date("y.m.d").'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($this->PHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        exit;
    }


    private function DownloadCSV() {
        $this->SetProperties();
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment;filename="'.$this->FileName.'-'.date("y.m.d").'.csv"');
        header('Cache-Control: max-age=0');
        $objWriter = new PHPExcel_Writer_CSV($this->PHPExcel);
        $objWriter->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->PHPExcel->getActiveSheet()->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=>$value) {
                $this->PHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col2, $row2, $value);
                $col2++;
            }
            $row2++;
        }
    }
}

Solved!

The way I resolved this was to use the recommended Metadata function.

Changed the SetHeaderCells function to this:

private function SetHeaderCells($Result) {
    $row = 1; // 1-based index
    $col = 0;
    foreach( sqlsrv_field_metadata($Result) as $fieldMetadata ) {
        foreach( $fieldMetadata as $name => $value) {
            if($name=='Name') {
            $this->PHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
            $col++;                
            }
        }
    }
}

Answer 1

You're fetching the first record and using the $row_data keys to set headers; but then that record has already been fetched, so when you move on to the data you're immediately going into a fetch loop that will start with the second record in your resultset

I don't believe sqlsrv supports a rewind function, but can you perhaps use sqlsrv_field_metadata() to get the header information rather than fetching the first record.

Alternatively, something like:

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->PHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $key);
        $col++;
    }
    return $row_data;
}

private function SetBodyCells($Result, $row_data) {
    $row2 = 2;
    do {
        $col2 = 0;

        foreach($row_data as $key=>$value) {
            $this->PHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col2, $row2, $value);
            $col2++;
        }
        $row2++;
    } while($row_data = sqlsrv_fetch_array($Result, SQLSRV_FETCH_ASSOC));
}

$Result = dbQuery($_Query);
$firstRow = $this->SetHeaderCells($Result);
$this->SetBodyCells($Result, $firstRow);

but using the metadata for the headings would be a better approach