PHP在输出EXCEL表格时,经常会碰到如下问题,如有些物料编号是数字0开头的,
产生EXCEL后,会发现数字0会丢失;还有类似"1-3"这种纯文本格式的导出后会自动转
化为日期型格式。诸如此类的,都是因为如果在导出的单元格没有设定想要的格式,那
么EXCEL会以默认的格式去呈现单元格的数据。
我们可以通过如下方式去规定数据保存的格式:
1)文本:vnd.ms-excel.numberformat:@
2)日期:vnd-ms-excel.numberformat:yyyy/mm/dd
3)数字:vnd-ms-excel.numberformat:#,##0.00
4)货币:vnd-ms-excel.numberformat:$#,##0.00
5)百分比:vnd-ms-excel.numberformat:#0.00%
参考如下范例:
header("Context-type:application/vnd.ms-excel");
header("Context-Disposition:attachement;filename=outExce.xls");
echo "<table border='1' width='100%'>";
echo "<tr><td style='vnd-ms-excel.numberformat:@'>00-35A-00</td></tr>";
echo "</table>";
//生成excel
private function downloadRecordAddressInfoToExcel($records){
$noAddressInfoCount=0;
$address=array();
if(is_array($records)){
$tem_address= "<table border='1' width='100%'>";
$table_td_s="<td>";
$table_td_number_s="<td style='vnd.ms-excel.numberformat:@'>";
$table_td_e="</td>";
$tem_address.= $table_td_s. "姓名".$table_td_e;
$tem_address.=$table_td_s."地址".$table_td_e;
$tem_address.=$table_td_s."电话".$table_td_e;
$tem_address.=$table_td_s. "订单号".$table_td_e;
$tem_address.=$table_td_s."时间".$table_td_e;
$step=0;
foreach($records as $k=>$record){
$address=json_decode($record['address'],true);
if(is_array($address)){
if($step<1){
header("Content-type:application/vnd.ms-excel;");
Header("Accept-Ranges:bytes");
header("Content-Disposition:filename=".$record['p_name'].".xls");
header("Pragma: no-cache");
header("Expires: 0");
$step++;
}
$tem_address.='<tr>';
$tem_address.=$table_td_s. $address['name'].$table_td_e.$table_td_s .$address['address'].$table_td_e.$table_td_number_s.$address['mobile'].$table_td_e;
$tem_address.=$table_td_number_s.$record['num'].$table_td_e.$table_td_s.date('Y-m-d H:i:s',$record['createtime']).$table_td_e;
$tem_address.='</tr>';
}else{
$noAddressInfoCount++;
$tem_address.="<tr><td></td><td></td><td></td><td></td><td></td></tr>";
}
}
}
if(!empty($tem_address) &&$step>0){
$tem_address.= "</table>";
echo $tem_address;
}else{
echo 'total:'.count($records).' <br/> Null data total:'.$noAddressInfoCount;
}
}