A solution to the scientific counting of numbers when PHP exports Excel

When data is exported to Excel, the number format is not correct, generally divided into the following two cases.

1. Excel cell set length is not enough

Workaround:

In the excel.php file $objActSheet = $objPHPExcel->getactivesheet (); Set column name $objActSheet->setcellvalue ("B1", "card number"); Set the width of the column $objActSheet->getcolumndimension (' B ')->setwidth (20);//change the length value set here

2, the character is understood by Excel as a number, the general is to set that field to text or to find ways to add some of the space class.
Workaround:

To add data, the main point is to display the data in the Chunk_split () function to handle the following, the specific use of this function can be viewed by itself $objActSheet->setcellvalue ("B$i", Chunk_split ("123456789 ", 4," "));//Of course, if you do not want users to see a space between the numbers, then the value of the field to be divided into a larger number, such as the example of 4 is set to be greater than or equal to 9.

I exported the previous section of the main code for Excel:

<? 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 ' 
 Output data in <table><tr><td></td></tr></table> format 
 
 After most of the day, I found a little useful information, Now excerpt from the following: 
 
 "First, let's look at how Excel exports from a Web page. When we send this data to the client, we want to have the client program (the browser) read it in Excel format, so we set the MIME type 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 must use the format supported by Excel. Here is a list of some common formats: 
 1) Text: vnd.ms-excel.numberformat:@ 
 2) Date: VND.MS-EXCEL.NUMBERFORMAT:YYYY/MM/DD 
 3) Numbers: vnd.ms-excel.numberformat:#,# #0. 
 4) Currency: vnd.ms-excel.numberformat:￥#,# #0.5 
) Percentage: Vnd.ms-excel.numberformat: #0.00% 
 These formats you can also customize, such as the year you can be defined as: yy-mm and so on. So what do you know about these formats and how do you add them to the cell? Very simply, we just need to add the style to the corresponding label pair (that is, the closing tag). For example, add a style to the label <td></td> <td></td>, as follows: <td style= "vnd.ms-excel.numberformat:@" > 410522198402161833</td> 
 Also, we can add styles to <p></p>, and we can also give <tr></tr>,<table></ table> add style; When we add a style to both the parent tag pair and the child label pair, which style will the data be rendered? After testing, it is rendered in a style that is closest to the data. 

The above is the content of the solution that the number turns to scientific count when PHP exports Excel, more related content please concern topic.alibabacloud.com (www.php.cn)!

