How to solve the problem of numbers changing to scientific notation when exporting data to Excel in PHP_PHP Tutorial

When the data is exported to excel, the number format is incorrect. Generally, it is divided into the following two situations.

1. The excel cell setting length is not enough

Solution:

Copy code Code As follows:

//In the excel.php file
$objActSheet = $objPHPExcel->getActiveSheet();
// Set the column name
$objActSheet-> setCellValue("b1", "Card Number");
//Set the width of the column
$objActSheet->getColumnDimension('b')->setWidth(20);//Change the length set here Numeric value

2. Characters are understood as numbers by Excel. Generally, you set the field to text or find a way to add some spaces.
Solution:

Copy code The code is as follows:

//Add data, The main purpose is to use the chunk_split() function to process the data to be displayed as follows. You can view the specific usage of this function yourself
$objActSheet->setCellValue ( "b$i", chunk_split("123456789 ",4," ") ) ;//Of course, if you don’t want users to see spaces between numbers, then set the field value to be divided larger, for example, set 4 in the example to greater than or equal to 9.

The front part of the main code I exported to EXcel:

Copy the code The 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); //The file name exported by $filename
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">





}

The following is to output the data in the format of

After checking for a long time, I finally found it Some useful information is excerpted as follows:

"First, let’s understand the principle of excel exporting from the web page. When we send this data to the client, we want the client program (browser) to ) to read it in excel format, so set the mime type to: application/vnd.ms-excel. When excel reads the file, it will present the data in the format of each cell. If the cell does not have the specified format, excel will Present the cell data in the default format. This provides us with space for custom data formats. Of course, we must use the formats supported by excel. Here are some 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) Percentage: vnd.ms-excel.numberformat: #0.00%
You can also customize these formats, such as year You can define the month as: yy-mm, etc. So after knowing these formats, how to add these formats to the cell? It is very simple, we only need to add the style to the corresponding tag pair (that is, the closing tag). . For example, , add a style to the label pair as follows: 410522198402161833
Similarly, we can also add styles to

, and we can also add styles to , when we When adding styles to both the parent tag pair and the child tag pair, which style will the data be presented in? After testing, it will be presented in the style closest to the data.

http://www.bkjia.com/PHPjc/326480.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/326480.htmlTechArticleWhen the data is exported to excel, the number format is incorrect. Generally, it is divided into the following two situations. 1. The excel cell setting length is not enough. Solution: Copy the code. The code is as follows: //In the excel.php file...