PHP export Excel Format data problem _php tips

Solve 2 problems:
1. The issue of automatic conversion of text data such as identity cards to scientific counting methods .
2. The problem of garbled Chinese

The principle that Excel derives from a Web page. When we send this data to the client, we want the client program (browser) to read it in Excel format, so the MIME type is set to: application/ Vnd.ms-excel, when Excel reads a file, it renders the data in the format of each cell, and if the cell does not have a specified format, Excel renders the cell's data in the default format. This gives us the space to customize the data format, and of course we have to use the format that Excel supports. Here's a list of some of the most commonly used formats:

1) Text: vnd.ms-excel.numberformat:@
2) Date: VND.MS-EXCEL.NUMBERFORMAT:YYYY/MM/DD
3) Number: vnd.ms-excel.numberformat:#,# #0.00
4) Currency: vnd.ms-excel.numberformat:￥#,# #0.00
5) Percent: Vnd.ms-excel.numberformat: #0 0%

These formats you can also customize, such as years you can define as: yy-mm and so on. So how do you add these formats to the cell when you know these formats? Quite simply, we just need to add the style to the corresponding label pair (that is, the closing tag). such as <td></td>, add a style to the label <td></td>, as follows: <td style= "vnd.ms-excel.numberformat:@" > 410522198402161833</td>
Also, we can add styles to <div></div>, or add styles to <tr></tr>,<table></table>; When we add a style to both the parent tag pair and the child label pairs, which style does the data render? After testing, it renders in the style closest to the data.

For example, the <td> style of the ID card column:

echo "<td style= ' vnd.ms-excel.numberformat:@ ' >". $printable. " </td>\n ";

Copy Code code as follows:

$filename =iconv ("UTF-8", "Gb2312//ignore", "member name. xls");//date (' y-m-d-h-i-s '). XLS ";
Header ("Content-type:application/vnd.ms-excel");
Header ("Accept-ranges:bytes");
Header ("Content-disposition:attachment;filename=". $filename); $filename the exported file name
Header ("Pragma:no-cache");
Header ("expires:0");

Echo ' xmlns:x= "Urn:schemas-microsoft-com:office:excel"
xmlns= "HTTP://WWW.W3.ORG/TR/REC-HTML40" >
<meta http-equiv= "Expires" content= "Mon, modified 1999 00:00:01 GMT" >
<meta http-equiv=content-type content= "text/html; charset=gb2312 ">
<!--[if GTE MSO 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name></x:Name>
<x:WorksheetOptions>
<x:DisplayGridlines/>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><! [endif]-->

echo "<table><tr>
<th> ". Iconv (" UTF-8 "," Gb2312//ignore "," member name ")." </th>
<th> ". Iconv (" UTF-8 "," Gb2312//ignore "," Account ")." </th>
<th> ". Iconv (" UTF-8 "," Gb2312//ignore "," Contacts ")." </th>
</tr> ";
foreach ($list as $v)
{
echo "<tr>";
echo "<td>". Iconv ("UTF-8", "Gb2312//ignore", $v ["user_name"]). " </td> ";
echo "<td style= ' vnd.ms-excel.numberformat:@ ' >". $v ["account_id"]. " </td> ";
echo "<td>". Iconv ("UTF-8", "Gb2312//ignore", $v ["Contact_Name"]). " </td> ";
echo "</tr>";
}
echo "</table>";