When data is exported to Excel, the number format is not correct, generally divided into the following two kinds of situations.
1, the Excel cell set length is not enough
Workaround:
Copy Code code as follows:
In the excel.php file
$objActSheet = $objPHPExcel->getactivesheet ();
Set column name
$objActSheet->setcellvalue ("B1", "card number");
To set the width of a column
$objActSheet->getcolumndimension (' B ')->setwidth (20);//change the length value set here
2, the character is interpreted as a number of Excel, the general is to set the field to text or find ways to add some space class.
Workaround:
Copy Code code as follows:
Add data, mainly to display data in the Chunk_split () function to handle the following, the specific use of this function can see for themselves
$objActSheet->setcellvalue ("B$i", Chunk_split ("123456789", 4, ""))//Of course, if you do not want the user to see a space between the number, then the value of the field to be divided to set a larger number, If the 4 in the example is set to be greater than or equal to 9.
I export the previous section of Excel's main code:
Copy Code code as follows:
?
if (count ($data) >40000) {
$filename _type= ' csv ';
}else{
$filename _type= ' xls ';
}
Header ("Content-type:application/vnd.ms-excel");
Header ("Accept-ranges:bytes");
Header ("Content-disposition:attachment;filename=". $filename. "." $filename _type); $filename the exported file name
Header ("Pragma:no-cache");
Header ("expires:0");
if ($filename _type== ' xls ') {
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]-->
}
The following is the output of data in <table><tr><td></td></tr></table> format
After more than half a day, finally found a useful information, now excerpt as follows:
"First, let's take a look at how 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.
