I have been generating excel of database earlier I was using Mysqli database and php for the same and it was working properly but ever since I started using sql server for the same sqlsrv_num_rows() was not returning any row earlier for that i came up with solution which is that I used array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET after $student

$query_run = sqlsrv_query($conn, $student, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));

than excel was getting generated but While opening downloadable excel I got below written error message

your excel file format or file extension is not valid .verify that the file has not been corrupted and that the file extension matches the format of the file

and when I checked in my error log I got below written error message

foreach() argument must be of type array|object, resource given in C:\xampp\htdocs\ExcelConversionUsingSql\code.php on line 56

I am sharing my code too ..please help me in resolving this issue

$student = "SELECT * FROM PERSONS";
$query_run = sqlsrv_query($conn, $student, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));

if(sqlsrv_num_rows($query_run) > 0)
{
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    $sheet->setCellValue('A1', 'LastName');
    $sheet->setCellValue('B1', 'FirstName');
    $sheet->setCellValue('C1', 'Address');
    $sheet->setCellValue('D1', 'City');
    

    $rowCount = 2;
    foreach($query_run as $data)
    {
        $sheet->setCellValue('A'.$rowCount, $data['LastName']);
        $sheet->setCellValue('B'.$rowCount, $data['FirstName']);
        $sheet->setCellValue('C'.$rowCount, $data['Address']);
        $sheet->setCellValue('D'.$rowCount, $data['City']);

        $rowCount  ;
    }

    if($file_ext_name == 'xlsx')
    {
        $writer = new Xlsx($spreadsheet);
        $final_filename = $fileName.'.xlsx';
    }
    elseif($file_ext_name == 'xls')
    {
        $writer = new Xls($spreadsheet);
        $final_filename = $fileName.'.xls';
    }
    elseif($file_ext_name == 'csv')
    {
        $writer = new Csv($spreadsheet);
        $final_filename = $fileName.'.csv';
    }

    // $writer->save($final_filename);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attactment; filename="'.urlencode($final_filename).'"');
    $writer->save('php://output');

}
else
{
    $_SESSION['message'] = "No Record Found";
    header('Location: index.php');
    exit(0);
}
}

?>

CodePudding user response：

sqlsrv_query only returns the cursor to the statement. You can't itereate over the cursor. To itereate over the results you can use:

//Fetching Data by array
while($row = sqlsrv_fetch_array($query_run, SQLSRV_FETCH_ASSOC)) {
    var_dump($row);
}

Or

//Fetching Data by object
while($row = sqlsrv_fetch_object($query_run)) {
    var_dump($row);
}