Home > Article > Backend Development > Solution to the problem that numbers change to scientific notation when exporting excel with PHP
Solution to the problem that numbers change to scientific notation when exporting excel with PHP
- PHP中文网Original
- 2017-03-30 17:48:563900browse
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('b')->setWidth(20);//改变此处设置的长度数值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的即可。The front part of the main code I exported to 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导出的文件名
header("Pragma: no-cache");
header("Expires: 0");
if($filename_type=='xls'){
echo '<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>';
}The following is followed by f5d188ed2c074f8b944552db028f98a1a34de1251f0d9fe1e645927f19a896e8b6c5a531a458a2e790c1fd6421739d1cb90dd5946f0946207856a8a37f441edffd273fcf5bcad3dfdad3c41bd81ad3e5069c56f0760ff4c133c755e97b4f8540 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, b6c5a531a458a2e790c1fd6421739d1cb90dd5946f0946207856a8a37f441edf, add a style to the label pair b6c5a531a458a2e790c1fd6421739d1cb90dd5946f0946207856a8a37f441edf as follows: 856ffc0d6270c9280a30389c3e38e43541052219840216183390cc1e42bbf9b97c5947038eac7f874e
Similarly, we can also add styles to e388a4556c0f65e1904146cc1a846bee94b3e26ee717c64999d7867364b1b4a3, or we can add styles to a34de1251f0d9fe1e645927f19a896e8fd273fcf5bcad3dfdad3c41bd81ad3e5, f5d188ed2c074f8b944552db028f98a1f16b1740fad44fb09bfe928bcc527e08; 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)!