PHP导出SQL Server数据到Excel仅显示列名无数据问题修复

问题场景 #

原本使用PHP连接MySQL实现的Excel数据导出功能正常，切换为SQL Server数据库后，生成的Excel文件仅显示列名，无法读取数据库中的数据，且未生成错误日志。

原代码问题分析 #

• 错误在foreach循环内嵌套sqlsrv_fetch_array，导致结果集指针被提前耗尽；
• SQL Server查询结果无法直接通过foreach遍历，需用sqlsrv_fetch_array逐行获取。

修正后的代码 #

<?php
session_start();
date_default_timezone_set("Asia/Kolkata");
ini_set('display_errors',1);
ini_set('log_errors',1);
ini_set('error_log',dirname(__FILE__).'/errorlog.txt');
error_reporting(E_ALL);

$serverName = "192.168.22.68"; //serverName\instanceName
$connectionInfo = array( "Database"=>"Interopdb", "UID"=>"uatkrasqluser", "PWD"=>"CV!u@t2018");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if($conn) {
    
}else{
 echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
}

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

if(isset($_POST['export_excel_btn']))
{
    $file_ext_name = $_POST['export_file_type'];
    $fileName = "student-sheet";

    $student = "SELECT * FROM priyat";
    $query_run = sqlsrv_query($conn, $student, array(), array("Scrollable"=>"buffered"));
    
    if(sqlsrv_num_rows($query_run) > 0)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', 'Full Name');
        $sheet->setCellValue('C1', 'Email');
        $sheet->setCellValue('D1', 'Phone');
        $sheet->setCellValue('E1', 'Course');

        $rowCount = 2;
        // 使用sqlsrv_fetch_array逐行获取数据
        while($data = sqlsrv_fetch_array($query_run, SQLSRV_FETCH_ASSOC))
        {
            $sheet->setCellValue('A'.$rowCount, $data['id']);
            $sheet->setCellValue('B'.$rowCount, $data['fullname']);
            $sheet->setCellValue('C'.$rowCount, $data['email']);
            $sheet->setCellValue('D'.$rowCount, $data['phone']);
            $sheet->setCellValue('E'.$rowCount, $data['course']);
            $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';
        }

        // 修正Content-Disposition拼写错误
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="'.urlencode($final_filename).'"');
        $writer->save('php://output');

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

修正说明 #

• 替换foreach为sqlsrv_fetch_array循环，正确获取SQL Server查询结果；
• 移除嵌套的sqlsrv_fetch_array代码，避免结果集指针异常；
• 修正Content-Disposition头拼写错误（attactment改为attachment）。

内容的提问来源于stack exchange，提问作者PARINEETI GUPTA