When exporting data to excel in php Numbers become the solution of scientific counting
- 2020-05-27 04:36:20
- OfStack
When data is exported to excel, the number format is not correct, and 1 can be divided into the following two cases.
1. Insufficient length of excel cell Settings
Solutions:
2. Characters are understood as Numbers by excel, and 1 is usually used to set that field to text or to find a way to add 1 space 1.
Solutions:
I export the first part of EXcel's main code:
And then the following is < table > < tr > < td > < /td > < /tr > < /table > Format output data
After searching for half a day, I finally found 1 point of useful information. Now I extract it as follows:
"First, let's take a look at how excel is exported from the web page. When we send this 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 excel reads the file, it will render the data in each cell format. If cell does not specify the format, excel will render the cell data in the default format. This gives us the space to customize the data format, but of course we have to use the format that excel supports. Here are some common 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 date of year you can define: yy-mm and so on. So once you know these formats, how do you add them to cell? Quite simply, we need to add the style to the corresponding tag pair (that is, the closed tag). Such as < td > < /td > Give the label right < td > < /td > Add the style as follows: < td style="vnd.ms-excel.numberformat:@" > 410522198402161833 < /td >
In the same way, we can give < div > < /div > Add style, also can give < tr > < /tr > . < table > < /table > Add style; When we add styles to both parent and child tag pairs, which style will the data be rendered in? After testing, it is rendered in the style closest to the data.
1. Insufficient length of excel cell Settings
Solutions:
// in excel.php In the file
$objActSheet = $objPHPExcel->getActiveSheet();
// Set up the The column name
$objActSheet->setCellValue("b1", " The card number ");
// Set the width of the column
$objActSheet->getColumnDimension('b')->setWidth(20);// Change the length value set here
2. Characters are understood as Numbers by excel, and 1 is usually used to set that field to text or to find a way to add 1 space 1.
Solutions:
// Add data to the place where you want to display the data chunk_split() The function handles the following, and you can see how it is used
$objActSheet->setCellValue ( "b$i", chunk_split("123456789 " . 4," ") );// Of course, if you don't want the user to see Spaces between the Numbers, make the value of the field to be split large 1 Some, as in the example 4 Let's say greater than or equal to 9 The can.
I export the first part of EXcel's main code:
<?
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 '<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>';
}
And then the following is < table > < tr > < td > < /td > < /tr > < /table > Format output data
After searching for half a day, I finally found 1 point of useful information. Now I extract it as follows:
"First, let's take a look at how excel is exported from the web page. When we send this 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 excel reads the file, it will render the data in each cell format. If cell does not specify the format, excel will render the cell data in the default format. This gives us the space to customize the data format, but of course we have to use the format that excel supports. Here are some common 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 date of year you can define: yy-mm and so on. So once you know these formats, how do you add them to cell? Quite simply, we need to add the style to the corresponding tag pair (that is, the closed tag). Such as < td > < /td > Give the label right < td > < /td > Add the style as follows: < td style="vnd.ms-excel.numberformat:@" > 410522198402161833 < /td >
In the same way, we can give < div > < /div > Add style, also can give < tr > < /tr > . < table > < /table > Add style; When we add styles to both parent and child tag pairs, which style will the data be rendered in? After testing, it is rendered in the style closest to the data.