PHP无需外部库生成XLSX？解决XLS打开安全提示问题

Hey there! Let's break down your two questions one by one with practical solutions:

1. How to eliminate the warning for your current ".xls" file #

First, let's clarify: the file you're generating isn't a real BIFF-format XLS file—it's just an HTML table renamed with a .xls extension. Excel detects the content doesn't match the file type, hence the warning. Here are two easy fixes:

Option 1: Improve the HTML output for better Excel compatibility #

Wrap your table in a complete HTML structure and use the correct MIME type. Also, use <td> instead of <th> for data rows (since <th> is meant for headers only, which confuses Excel):

$output = '<!DOCTYPE html><html><body>'; // Add full HTML wrapper
if(mysqli_num_rows($result)>0) {
    $output .= ' <table class="table" border="1">
    <tr>
    <th>ID</th>
    <th>Name</th>
    </tr> ';
    while($row = mysqli_fetch_array($result)) {
        $output .= ' <tr>
        <td>'.$row["ID"].'</td> <!-- Use <td> for data cells -->
        <td>'.$row["Name"].'</td>
        </tr> ';
    }
    $output .= '</table>';
}
$output .= '</body></html>'; // Close HTML wrapper

$fileName = "ExcelFile".date('Y_m_d').".xls";
header("Content-Type: application/vnd.ms-excel"); // Standard XLS MIME type
header("Content-Disposition: attachment; filename=$fileName");
echo $output;

This makes Excel recognize the content as valid HTML, and the warning should disappear for most Excel versions.

Option 2: Switch to CSV format (no warnings at all) #

If you don't need complex formatting, CSV is a simpler, fully-supported alternative that Excel won't flag:

$fileName = "ExcelFile".date('Y_m_d').".csv";
header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=$fileName");

$output = fopen('php://output', 'w');
// Write header row
fputcsv($output, ['ID', 'Name']);

if(mysqli_num_rows($result)>0) {
    while($row = mysqli_fetch_array($result)) {
        fputcsv($output, [$row["ID"], $row["Name"]]);
    }
}
fclose($output);

2. Generate real XLSX without external libraries #

XLSX files are essentially ZIP archives containing a specific set of XML files. You can use PHP's built-in ZipArchive class (enabled by default in most environments) to create a valid XLSX file without any external libraries.

Here's a minimal working example tailored to your data:

$fileName = "ExcelFile".date('Y_m_d').".xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.$fileName.'"');
header('Cache-Control: max-age=0');

// Initialize Zip archive
$zip = new ZipArchive();
$zip->open('php://output', ZipArchive::CREATE | ZipArchive::OVERWRITE);

// 1. Add core content types file
$contentTypes = '<?xml version="1.0" encoding="UTF-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
    <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
    <Default Extension="xml" ContentType="application/xml"/>
    <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
    <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
</Types>';
$zip->addFromString('[Content_Types].xml', $contentTypes);

// 2. Add package relationships file
$rels = '<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>';
$zip->addFromString('_rels/.rels', $rels);

// 3. Add workbook relationships file
$workbookRels = '<?xml version="1.0" encoding="UTF-8"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
</Relationships>';
$zip->addFromString('xl/_rels/workbook.xml.rels', $workbookRels);

// 4. Add workbook definition
$workbook = '<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <sheets>
        <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
    </sheets>
</workbook>';
$zip->addFromString('xl/workbook.xml', $workbook);

// 5. Add worksheet with your data
$sheetContent = '<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <sheetData>
        <row>
            <c><v>ID</v></c>
            <c><v>Name</v></c>
        </row>';

if(mysqli_num_rows($result)>0) {
    while($row = mysqli_fetch_array($result)) {
        // Escape special characters to avoid breaking XML
        $name = htmlspecialchars($row["Name"]);
        $sheetContent .= '<row>
            <c><v>'.$row["ID"].'</v></c>
            <c><v>'.$name.'</v></c>
        </row>';
    }
}

$sheetContent .= '</sheetData></worksheet>';
$zip->addFromString('xl/worksheets/sheet1.xml', $sheetContent);

// Finalize and output the ZIP
$zip->close();
exit;

This creates a valid XLSX file with your data that opens in Excel without any warnings. You can extend it with styling or additional sheets by expanding the XML structure if needed.

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