Solution to the problem that numbers change to scientific notation when exporting excel with PHP-PHP Tutorial-php.cn

Solution to the problem that numbers change to scientific notation when exporting excel with PHP

PHP中文网

Release： 2023-02-28 20:22:01

Original

3964 people have browsed it

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

1. The length of the excel cell setting is not enough

Solution:

//在excel.php文件中 
$objActSheet = $objPHPExcel->getActiveSheet(); 
// 设置 栏目名称 
$objActSheet->setCellValue("b1", "卡号"); 
// 设置列的宽度 
$objActSheet->getColumnDimension(&#39;b&#39;)->setWidth(20);//改变此处设置的长度数值

Copy after login

2. The characters are interpreted as numbers by excel. Generally, you set the field to text or find a way to add some spaces.
Solution:

//添加数据处，主要是把要显示数据以chunk_split()函数处理以下，此函数的具体用法可以自己查看 
$objActSheet->setCellValue ( "b$i", chunk_split("123456789 "，4," ") );
//当然，如果不想让用户看到数字间有空格，那就把要分割的字段值设大一些，如例子中的4设为大于等于9的即可。

Copy after login

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

<? 
if(count($data)>40000){ 
$filename_type=&#39;csv&#39;; 
}else{ 
$filename_type=&#39;xls&#39;; 
} 
header("Content-Type: application/vnd.ms-excel"); 
Header("Accept-Ranges:bytes"); 
Header("Content-Disposition:attachment;filename=".$filename.".".$filename_type); //$filename导出的文件名 
header("Pragma: no-cache"); 
header("Expires: 0"); 
if($filename_type==&#39;xls&#39;){ 
echo &#39;<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>&#39;; 
}

Copy after login

The following is followed by

< /table> format output data

After searching for a long time, I finally found some useful information. Now it is excerpted as follows:

“First, let’s understand the principle of excel exporting from the web page. When we send this data to the customer When using the client, we want the client program (browser) to read it in excel format, so set the mime type to: application/vnd.ms-excel. When excel reads the file, it will be presented in the format of each cell. For data, if the cell does not have a specified format, Excel will present the data of the cell in the default format. This provides us with space to customize the data format. Of course, we must use the commonly used formats supported by Excel. Some 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 , or we can add styles to

; when we add styles to the parent When styles are added to both label pairs and sub-label pairs, which style will the data be presented in? After testing, it will be presented in the style closest to the data.

The above is the solution to convert numbers into scientific notation when exporting excel with PHP. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!