Current Location: Home> Latest Articles> Use header() to set Excel export: MIME type and encoding key points

Use header() to set Excel export: MIME type and encoding key points

M66 2025-05-28

In PHP, if we want to generate an Excel file for users to download, the commonly used method is to use the header() function to set the correct HTTP header. This article will introduce how to set up Excel export using header() , and also explain some notes about MIME types and character encoding.

1. Why use header()?

header() is a function in PHP used to send raw HTTP header information. The browser decides how to process the returned content based on the received header information. for example:

  • Content-Type tells the browser what type of file this is.

  • Content-Disposition can force a file to be downloaded or set the file name when downloading.

If we want the browser to process the data output by PHP as an Excel file, we must set these headers.

2. Basic header() settings

Here is a basic example for generating Excel files ( legacy.xls format):

 <?php
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"export.xls\"");
header("Cache-Control: max-age=0");

echo "serial number\tName\tscore\n";
echo "1\tZhang San\t90\n";
echo "2\tLi Si\t85\n";
?>

illustrate:

  • Content-Type: application/vnd.ms-excel
    Tell the browser that this is an Excel file.

  • Content-Disposition: attachment; filename="export.xls"
    Specifies the file name at the time of download.

  • Cache-Control: max-age=0
    Avoid caching.

Note: This method is suitable for plain text or HTML formats that Excel recognizes, and does not directly generate real Excel file formats (such as .xlsx ).

3. About the choice of MIME type

Different export formats and different MIME types. Commonly used are:

  • application/vnd.ms-excel — Traditional .xls file.

  • application/vnd.openxmlformats-officedocument.spreadsheetml.sheet — Excel 2007 and above .xlsx files.

  • application/csv or text/csv — CSV file.

If you generate a .xlsx file, you also need to use libraries such as PhpSpreadsheet , not just output text.

4. Character encoding trap

Coding issues are common when generating Excel files, especially when including Chinese.

FAQ:

  • After opening the Excel file, the Chinese text displays garbled code.

  • Special characters are parsed incorrectly.

Solution:

  1. Use iconv() or mb_convert_encoding() to convert the output into an encoding recognized by Excel, such as GB2312 or UTF-16LE.

  2. In a CSV file, you can add a BOM (Byte Order Mark) at the beginning, such as \xEF\xBB\xBF , to help Excel recognize UTF-8 encoding.

Example (Output UTF-8 CSV with BOM):

 <?php
header("Content-Type: text/csv; charset=utf-8");
header("Content-Disposition: attachment; filename=\"export.csv\"");
header("Cache-Control: max-age=0");

// Output BOM
echo "\xEF\xBB\xBF";

echo "serial number,Name,score\n";
echo "1,Zhang San,90\n";
echo "2,Li Si,85\n";
?>

5. Complete example: Generate Excel 2007+ files

To generate .xlsx , you usually need to use libraries such as PhpSpreadsheet , and you also need to set the output correctly:

 <?php
require 'vendor/autoload.php';

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

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'serial number');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'score');
$sheet->setCellValue('A2', '1');
$sheet->setCellValue('B2', 'Zhang San');
$sheet->setCellValue('C2', '90');

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

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
?>

Notice:

  • If you have a link or referenced URL in your code, make sure to replace it with a domain name like https://m66.net/xxx .

  • For example, to put a hyperlink in Excel:

 $sheet->setCellValue('D2', '=HYPERLINK("https://m66.net/page", "check the details")');

6. Summary

The most important thing is to use header() to export Excel files:

  • Set Content-Type and Content-Disposition correctly.

  • Handle character encoding well and avoid Chinese or special characters garbled.

  • Complex formats (such as .xlsx ) need to be generated using special libraries.

These settings allow your PHP program to easily implement Excel data export functionality. I hope this article can help you avoid detours in actual development!

Related