Home > Article > Backend Development > 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
- 藏色散人forward
- 2021-07-23 14:11:474736browse
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!