Backend Development
PHP Tutorial
An article explains how to use PHP natively to export Excel and CSV files
An article explains how to use PHP natively to export Excel and CSV files
PHP native export to Excel
The principle of export is actually to set the Header header to tell the browser to use excel format to parse and read, and then generate an HTML table to output the content . Merging cells is actually the same as merging HTML tables. It supports style attributes and fields, and you can also operate td styles to control cells.
The exported pseudo code is as follows:
/**
* 导出excel文件
*
* @param string $excelFileName 导出的文件名
* @param array $title excel的标题列
* @param array $data 导出的数据
*/
public function exportExcel($excelFileName, $title, $data)
{
$str = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\nxmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\nxmlns=\"http://www.w3.org/TR/REC-html40\">\r\n<head>\r\n<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">\r\n</head>\r\n<body>";
$str .="<table border=1 align=center cellpadding=0 cellspacing=0>";
// 拼接标题行
$str .= '<tr style="height:25px;font-size:13px;font-weight: bold;">';
foreach ($title as $key => $val) {
$str .= '<td>'.$val.'</td>';
}
$str .= '</tr>';
// 拼接数据
foreach ($data as $key => $val) {
$str .= '<tr style="text-align: left;height:25px;font-size:13px;">';
foreach ($val as $v) {
if (is_numeric($v) && $v > 100000000) {
$str .= "<td style='vnd.ms-excel.numberformat:@'>".$v."</td>";
} elseif (is_numeric($v) && preg_match('/^[0-9]+(\.[0-9]{2})+$/', $v)) {
// 是两位小数的保留2位显示
$str .= "<td style='vnd.ms-excel.numberformat:0.00'>".$v."</td>";
} elseif (preg_match('/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1]) (0[0-9]|1[0-9]|2[0-4]):(0[0-9]|[1-5][0-9]):(0[0-9]|[1-5][0-9])$/', $v)) {
// 是日期
$str .= "<td style='vnd.ms-excel.numberformat:yyyy-mm-dd\ hh\:mm\:ss'>".$v."</td>";
} else {
$str .= "<td>".$v."</td>";
}
}
$str .= "</tr>\n";
}
$str .= "</table></body></html>";
// 实现文件下载
header("Content-Type: application/vnd.ms-excel; name='excel'");
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=" . $excelFileName);
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
header("Expires: 0");
exit($str);
}I simply wrote some styles and some regular checks, which can be deleted if they are not needed. Mainly talk about the formatting instructions of some cells.
The above export will have several common problems:
1. For ID numbers, mobile phone numbers and other large numbers, they will be displayed using scientific notation. If you don't mind string display, you can control it by setting the style of tdstyle='vnd.ms-excel.numberformat:@' This means displaying it in text format
2. For numbers, if you need to retain decimal places, you can set style='vnd.ms-excel.numberformat:0.00' If you need to retain several decimal places, just write a few 0s. Please note that if you If the value passed in has more decimal places than can be retained, the value will be rounded. For example, if the input is 87.98 and the decimal place setting is vnd.ms-excel.numberformat:0.0, then it will be displayed as 88.0
3. For the date format settingstyle= 'vnd.ms-excel.numberformat:yyyy-mm-dd\ hh\:mm\:ss' Spaces, colons, etc. need to be escaped. If you are using the space character table in HTML, it will be a little different. The above are commonly used format combinations, and you can do the conversion yourself. There are some other conversions style='vnd.ms-excel.numberformat:\"Short Date\"' This will be displayed as 2019/7/1; style ='vnd.ms-excel.numberformat:\"Medium Date\"' will be displayed as 1-Jul-19
4. Percentage display. To display a percentage, you can use the format style='vnd.ms-excel.numberformat:0%' One thing to note is that your value will be amplified 100 times when displayed. That is to say, if you want to display it as 2%, the value you pass in needs to be 0.02
5. Thousand separator. If you need to display numbers with thousands separators, you can set style='vnd.ms-excel.numberformat:#,'. For example, 123456 will be displayed as 123,456
The above are the most commonly used ones. There is also a commonly used list here, but the keyword he uses is mso-number-format I tried style Both mso-number-format and vnd.ms-excel.numberformat
PHP Export CSVCSV file is actually a general-purpose file, sometimes also called a text file. Therefore, the exported CSV datagram file cannot set the cell style, and the xls file, which is a binary file, needs to be opened using tools such as Excel. If there is no hard requirement to export this faster, I think some exports on Alibaba Cloud are in CSV format, but what we need here must be excel files. The pseudo code is as follows:
/**
* 导出CSV标准文件
*
* @param string $fileName 导出的文件名
* @param array $title 标题列
* @param array $data 导出的数据
*/
function exportCsv($fileName, $title, $data)
{
// 清空输出流，防止有别的信息
ob_end_clean();
// 打开一个输出流
ob_start();
// 设置header信息
header("Content-Type: text/csv");
header("Content-Disposition:filename=". $fileName);
// 打开文件流
$fileHandle = fopen('php://output', 'w');
//转码 防止乱码
fwrite($fileHandle, chr(0xEF).chr(0xBB).chr(0xBF));
// 先把标题写进去
fputcsv($fileHandle, $title);
$index = 0;
foreach ($data as $item) {
// 每2000条释放一次资源
if ($index == 2000) {
$index = 0;
ob_flush();
flush();
}
$index++;
fputcsv($fileHandle, $item);
}
// 释放资源
ob_flush();
flush();
ob_end_clean();
}Recommended learning: "PHP Video Tutorial"The above is the detailed content of An article explains how to use PHP natively to export Excel and CSV files. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
What are public, private, and protected in php
Aug 24, 2025 am 03:29 AM
Public members can be accessed at will; 2. Private members can only be accessed within the class; 3. Protected members can be accessed in classes and subclasses; 4. Rational use can improve code security and maintainability.
How to execute an UPDATE query in php
Aug 24, 2025 am 05:04 AM
Using MySQLi object-oriented method: establish a connection, preprocess UPDATE statements, bind parameters, execute and check the results, and finally close the resource. 2. Using MySQLi procedure method: connect to the database through functions, prepare statements, bind parameters, perform updates, and close the connection after processing errors. 3. Use PDO: Connect to the database through PDO, set exception mode, pre-process SQL, bind parameters, perform updates, use try-catch to handle exceptions, and finally release resources. Always use preprocessing statements to prevent SQL injection, verify user input, and close connections in time.
How to read a CSV file in PHP?
Aug 29, 2025 am 08:06 AM
ToreadaCSVfileinPHP,usefopen()toopenthefile,fgetcsv()inalooptoreadeachrowasanarray,andfclose()tocloseit;handleheaderswithaseparatefgetcsv()callandspecifydelimitersasneeded,ensuringproperfilepathsandUTF-8encodingforspecialcharacters.
How to use cURL in php
Aug 24, 2025 am 08:32 AM
cURLinPHPenablessendingHTTPrequests,fetchingAPIdata,anduploadingfiles.Initializewithcurl_init(),setoptionslikeCURLOPT_URLandCURLOPT_RETURNTRANSFER,useCURLOPT_POSTforPOSTrequests,sendJSONwithproperheaders,handleerrorsviacurl_errno()andHTTPcodeswithcur
How to use AJAX with php
Aug 29, 2025 am 08:58 AM
AJAXwithPHPenablesdynamicwebappsbysendingasynchronousrequestswithoutpagereloads.1.CreateHTMLwithJavaScriptusingfetch()tosenddata.2.BuildaPHPscripttoprocessPOSTdataandreturnresponses.3.UseJSONforcomplexdatahandling.4.Alwayssanitizeinputsanddebugviabro
What is the difference between isset and empty in php
Aug 27, 2025 am 08:38 AM
isset()checksifavariableexistsandisnotnull,returningtrueevenforzero,false,oremptystringvalues;2.empty()checksifavariableisnull,false,0,"0","",orundefined,returningtrueforthese"falsy"values;3.isset()returnsfalsefornon-exi
Edit bookmarks in chrome
Aug 27, 2025 am 12:03 AM
Chrome bookmark editing is simple and practical. Users can enter the bookmark manager through the shortcut keys Ctrl Shift O (Windows) or Cmd Shift O (Mac), or enter through the browser menu; 1. When editing a single bookmark, right-click to select "Edit", modify the title or URL and click "Finish" to save; 2. When organizing bookmarks in batches, you can hold Ctrl (or Cmd) to multiple-choice bookmarks in the bookmark manager, right-click to select "Move to" or "Copy to" the target folder; 3. When exporting and importing bookmarks, click the "Solve" button to select "Export Bookmark" to save as HTML file, and then restore it through the "Import Bookmark" function if necessary.
How to configure SMTP for sending mail in php
Aug 27, 2025 am 08:08 AM
Answer: Using the PHPMailer library to configure the SMTP server can enable sending mails through SMTP in PHP applications. PHPMailer needs to be installed, set up SMTP host, port, encryption method and authentication credentials of Gmail, write code to set sender, recipient, topic and content, enable 2FA and use application password to ensure that the server allows SMTP connection, and finally call the send method to send email.
