php issues exporting data in excel format

2021-01-22 04:54:56
OfStack

Solve 2 problems:
1. The automatic conversion of textual data, such as identity cards, to scientific counting.
2. The problem of Chinese garbled code

Principle of excel exported from the web page. When we send this data to the client, we want the client program (browser) to read it in the excel format, so we set the mime type: application/vnd.ms-excel. When excel reads the file, it will render the data in each cell format. If cell does not specify a format, excel will render the cell data in the default format. This gives us room to customize the data format, but of course we must use the format supported by excel. Some common formats are listed below:

1) Text: vnd.ms-excel.numberformat:@
2) date: vnd. ms - excel. numberformat: yyyy/mm/dd
vnd.ms-excel.numberformat:#,##0.00
4) Currency: vnd.ms-excel.numberformat:￥#,##0.00
5) Percentage: vnd.ms-excel.numberformat: #0.00%

You can also customize these formats. For example, you can define the year and month as yy-mm, etc. Now that you know these formats, how do you add them to cell? Simply add the style to the corresponding tag pair (the closing tag). Such as < td > < /td > , give the label to the pair < td > < /td > Add styles as follows: < td style="vnd.ms-excel.numberformat:@" > 410522198402161833 < /td >
Similarly, we can also give < div > < /div > Add styles that can also be given < tr > < /tr > . < table > < /table > Add style; When we add styles to both parent and child tag pairs, which style will the data be rendered in? After testing, it will be rendered in the style closest to the data.

For example, the ID card column < td > Style:

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


$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 Export filename 
            header("Pragma: no-cache");
            header("Expires: 0");
echo '<html xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns="http://www.w3.org/TR/REC-html40">
 <head>
    <meta http-equiv="expires" content="Mon, 06 Jan 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]-->

</head>';
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"," The contact ")."</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>";