When the data is exported to excel, the numeric format is incorrect, which is generally divided into the following two situations.
1. Insufficient length for excel Cells
Solution:Copy codeThe Code is as follows: // in the excel. php file
$ ObjActSheet = $ objPHPExcel-> getActiveSheet ();
// Set the topic name
$ ObjActSheet-> setCellValue ("b1", "card number ");
// Set the column width
$ ObjActSheet-> getColumnDimension ('B')-> setWidth (20); // change the length value set here.
2. The character is understood as a number in excel. Generally, it is set to text or try to add spaces.
Solution:
Copy codeThe Code is as follows: // Add data, mainly to process the data to be displayed using the chunk_split () function. You can view the specific usage of this function on your own.
$ ObjActSheet-> setCellValue ("B $ I", chunk_split ("123456789", 4, ""); // Of course, if you do not want to see a space between digits, set the value of the field to be split to a greater value. For example, set 4 to a value greater than or equal to 9.
The first part of the main code for exporting EXcel is as follows: Copy codeThe Code is 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 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">
<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> ';
}
Output Data in <table> <tr> <td> </tr> </table> format.
I finally found some useful information for a long time. The excerpt is as follows:
"First, let's take a look at how excel exports from a Web page. When we send the data to the client, we want the Client Program (browser) to read it in excel format, so we set the mime type to application/vnd. ms-excel: When an excel file is read, data is displayed in the format of each cell. If the cell does not have a specified format, excel displays the data of the cell in the default format. This provides us with a space for custom data formats. Of course, we must use the formats supported by excel. Common formats are listed below:
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) 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. So how can I add these formats to cells when I know these formats? It is very simple. We only need to add the style to the corresponding tag pair (that is, to close the tag. For example, <td> </td>, add a style to the tag pair <td> </td> as follows: <td style = "vnd. ms-excel.numberformat: @ "> 410522198402161833 </td>
Similarly, you can add a style to <div> </div>, <tr> </tr>, and <table> </table>; when we add a style to both the parent and child tags, Which style will the data be presented in? After testing, it is displayed in the style closest to the data.