PHP导出到Excel表格 两种方法 可将科学计数法改为文本格式
导出方法一 使用table输出导出
1 //导出代码 2 public function export(){ 3 set_time_limit(0); 4 ini_set('memory_limit','1024M');//设置导出最大内存 5 $ranking = self::getResult();//获得需要导出的数据 6 //输出的表头 7 $_pre = array( 8 "listing" => "ListingID", 9 "salesbefore" => "前十天销量", 10 "salesafter" => "后十天销量", 11 "saleschange" => "涨跌销量", 12 "percentchange" => "涨跌幅度(%)", 13 ); 14 $date = date("YmdHis");//日期作为输出文件后缀 15 $content = getXLSFromList($_pre,$ranking);//获得输出的表格内容 16 header("Content-type:application/vnd.ms-execl;charset=gb2312");//设置导出格式 17 header("Content-Disposition:attactment;filename=销量变化排行榜".$date.".xls");//设置导出文件名 18 header("Pragma: no-cache"); 19 header("Expires: 0"); 20 echo $content; 21 exit; 22 }
1 //此方法建议写入公共方法 通过数组遍历得出导出报表类型结构 2 function getXLSFromList($pres,$lists){ 3 // 内容太大建议搜索少量再导出 4 // if(count($lists)>=20000) 5 // { 6 // header("Content-Type:text/html;charset=utf-8"); 7 // echo "<br/><h1 style='color:red'>Export data is too large, please narrow your search!</h1><br/>"; 8 // exit; 9 // } 10 $keys=array_keys($pres);//获取表头的键名 11 $content=""; 12 $content.="<table border='1'><tr>"; 13 //输出表头键值 14 foreach($pres as $_pre){ 15 $content.="<td>$_pre</td>"; 16 } 17 $content.="</tr>"; 18 foreach($lists as $_list){ 19 $content.= "<tr>"; 20 foreach($keys as $key){ 21 $content.= "<td style='vnd.ms-excel.numberformat:@'>".$_list[$key]."</td>"; //style样式将导出的内容都设置为文本格式 输出对应键名的键值 即内容 22 } 23 $content.="</tr>"; 24 } 25 $content.="</table>"; 26 return $content; 27 }
导出方法二 使用PHPExcel导出
1 public function export(){ 2 set_time_limit(0); 3 ini_set('memory_limit','1024M'); 4 $ranking = self::getResult(); //获取需要导出的内容 5 include_once(EXTEND_PATH . 'Library/ORG/PHPExcel/PHPExcel.class.php'); //引入PHPExcel文件 6 $objPHPExcel = new PHPExcel(); 7 //设置文件的一些属性，在xls文件——>属性——>详细信息里可以看到这些值 8 $objPHPExcel->getProperties()->setCreator("ctos") 9 ->setLastModifiedBy("ctos") 10 ->setTitle("Office 2007 XLSX Test Document") 11 ->setSubject("Office 2007 XLSX Test Document") 12 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") 13 ->setKeywords("office 2007 openxml php") 14 ->setCategory("Test result file"); 15 //设置列宽 16 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15); 17 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); 18 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20); 19 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20); 20 $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); 21 //设置水平居中显示 22 $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 23 $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 24 $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 25 $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 26 $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 27 $objPHPExcel->setActiveSheetIndex(0) // set table header content 28 ->setCellValue('A1', 'Listing ID') 29 ->setCellValue('B1', '前十天销量') 30 ->setCellValue('C1', '后十天销量') 31 ->setCellValue('D1', '涨跌销量') 32 ->setCellValue('E1', '涨跌幅度(%)') 33 $a = 1; //设置默认值 34 foreach($ranking as $k){ 35 $a++; 36 $objPHPExcel->setActiveSheetIndex(0) 37 ->setCellValueExplicit('A'.$a, $k['listing'],PHPExcel_Cell_DataType::TYPE_STRING)//设置数字的科学计数法显示为文本 38 ->setCellValue('B'.$a, $k['salesbefore']) 39 ->setCellValue('C'.$a, $k['salesafter']) 40 ->setCellValue('D'.$a, $k['saleschange']) 41 ->setCellValue('E'.$a, $k['percentchange']) 42 //设置自动换行 43 $objPHPExcel->getActiveSheet()->getStyle("A$a")->getAlignment()->setWrapText(true); 44 $objPHPExcel->getActiveSheet()->getStyle("B$a")->getAlignment()->setWrapText(true); 45 } 46 $objPHPExcel->getActiveSheet()->setTitle('销量变化排行榜'); 47 $objPHPExcel->setActiveSheetIndex(0); 48 // excel头参数 49 header('Content-Type: application/vnd.ms-excel'); 50 header('Content-Disposition: attachment;filename="销量变化排行榜('.date('YmdHis').').xls"'); //日期为文件名后缀 51 header('Cache-Control: max-age=0'); 52 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式，excel2007为xlsx格式 53 $objWriter->save('php://output'); 54 }
