转:PHP导出excel文件的几种方式
PHP导出excel文件的几种方式
文章来源:http://www.cnblogs.com/fredshare/archive/2012/10/29/2744243.html
先说说动态生成的内容当作文件来下载的方法:
1、通过把Content-Type设置为application/octet-stream,可以把动态生成的内容当作文件来下载;content-type内容设置可以参看:
http://www.ostools.net/commons
2、用Content-Disposition设置下载的文件名;
3、 基本上,下载程序都是这么写的:
<?php $filename = "document.txt"; header('Content-Type: application/octet-stream'); header('Content-Disposition: attachment; filename=' . $filename); print "Hello!"; ?>
4、当然要考虑到浏览器兼容性和对如果文件名是中文的话出现异常bug,我们这么写:
<?php $ua = $_SERVER["HTTP_USER_AGENT"]; $filename = "文件名.txt"; $encoded_filename = urlencode($filename); $encoded_filename = str_replace("+", "%20", $encoded_filename); header('Content-Type: application/octet-stream'); if (preg_match("/MSIE/", $ua)) { header('Content-Disposition: attachment; filename="' . $encoded_filename . '"'); } else if (preg_match("/Firefox/", $ua)) { header('Content-Disposition: attachment; filename*="utf8\'\'' . $filename . '"'); } else { header('Content-Disposition: attachment; filename="' . $filename . '"'); } print 'ABC'; ?>
明白了这些之后,生成excel就很简单了,处理下下载文档格式,解析数据,balabala就好了,看看怎么生成一篇excel的文档吧:
1、原始方式:发送header,用附件的表头发送到用户浏览器表示是要下载的,然后读出数据库中的数据,一条一条的解析,写入excel格式的文件中。
<?php $DB_Server = "localhost"; $DB_Username = "root"; $DB_Password = ""; $DB_DBName = "DBName"; $DB_TBLName = "DB_TBLName"; $savename = date("YmjHis"); $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect."); mysql_query("Set Names 'gbk'"); $file_type = "vnd.ms-excel"; $file_ending = "xls"; header("Content-Type: application/$file_type;charset=gbk"); header("Content-Disposition: attachment; filename=".$savename.".$file_ending"); header("Pragma: no-cache"); $now_date = date("Y-m-j H:i:s"); $title = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date"; $sql = "Select * from $DB_TBLName"; $ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database"); $result = @mysql_query($sql,$Connect) or die(mysql_error()); echo("$title\n"); $sep = "\t"; for ($i = 0;$i < mysql_num_fields($result);$i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); $i = 0; while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; $i++; } return (true); ?>
2、用PHPExcel库
下面是用PHPExcel实现的与上面功能相同的excel的方法。getCol为递归实现的函数,用于根据数字返回对应的列号编码。因为导出的过程中需要指出行号,列号。行号为简单的数字,而列号则为“A-Z”的组合。为了方便二维数组的导入,根据列数自动得到列号编码。如“5”->“E”、“27”->“AZ”。
//设置PHPExcel类库的include path $dirToLib1='./resource/PHPExcel1.7.0/Classes'; ini_set('include_path', ini_get('include_path').PATH_SEPARATOR.$dirToLib1); function getCol($num){//递归方式实现根据列数返回列的字母标识 $arr=array(0=>'Z',1=>'A',2=>'B',3=>'C',4=>'D',5=>'E',6=>'F',7=>'G',8=>'H',9=>'I',10=>'J',11=>'K',12=>'L',13=>'M',14=>'N',15=>'O',16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',26=>'Z'); if($num==0) return ''; return $this->getCol((int)(($num-1)/26)).$arr[$num%26]; } function exportToExcel($header,$data,$fileName){ //利用PHPExcel工具进行导出,功能强大,格式规范,可导出2007 $fileName=$this->UTF8ToGBK($fileName); $objExcel = new PHPExcel(); $objWriter = new PHPExcel_Writer_Excel5($objExcel); $objExcel->setActiveSheetIndex(0); $objActSheet = $objExcel->getActiveSheet(); //设置当前活动sheet的名称 //$objActSheet->setTitle('测试Sheet'); //************************************* //设置单元格内容 $i=1; foreach ($header as $key => $value){ $objActSheet->setCellValueExplicit($this->getCol($i).'1', $value,PHPExcel_Cell_DataType::TYPE_STRING); //************************************* //设置单元格样式 $objStyle = $objActSheet->getStyle($this->getCol($i++).'1'); //设置边框 $objBorder = $objStyle->getBorders(); $objBorder->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getTop()->getColor()->setARGB('FFDDDDDD'); $objBorder->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getBottom()->getColor()->setARGB('FFDDDDDD'); $objBorder->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getLeft()->getColor()->setARGB('FFDDDDDD'); // color $objBorder->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objBorder->getRight()->getColor()->setARGB('FFDDDDDD'); // color //设置填充颜色 $objFill = $objStyle->getFill(); $objFill->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objFill->getStartColor()->setARGB('FF00EEEE'); } $i=1; foreach ($data as $row){ $i++; $j=1; foreach ($header as $key => $value){ $objActSheet->setCellValueExplicit($this->getCol($j++).$i, $row[$key],PHPExcel_Cell_DataType::TYPE_STRING); } } //************************************* //输出内容 //到浏览器 header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="'.$fileName.'.xls"'); header("Content-Transfer-Encoding: binary"); header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $objWriter->save('php://output'); }
3、用PHPExcel等库,确实比较容易实现,但对大量数据的支持很不好,很容易到达PHP内存使用上限。这里的方法是利用fputcsv写CSV(这是一种逗号分隔值文件格式,见http://baike.baidu.com/view/468993.htm)文件的方法,直接向浏览器输出Excel文件:
<?php // 输出Excel文件头,可把user.csv换成你要的文件名 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="user.csv"'); header('Cache-Control: max-age=0'); // 从数据库中获取数据,为了节省内存,不要把数据一次性读到内存,从句柄中一行一行读即可 $sql = 'select * from tbl where ……'; $stmt = $db->query($sql); // 打开PHP文件句柄,php://output 表示直接输出到浏览器 $fp = fopen('php://output', 'a'); // 输出Excel列名信息 $head = array('姓名', '性别', '年龄', 'Email', '电话', '……'); foreach ($head as $i => $v) { // CSV的Excel支持GBK编码,一定要转换,否则乱码 $head[$i] = iconv('utf-8', 'gbk', $v); } // 将数据通过fputcsv写到文件句柄 fputcsv($fp, $head); // 计数器 $cnt = 0; // 每隔$limit行,刷新一下输出buffer,不要太大,也不要太小 $limit = 100000; // 逐行取出数据,不浪费内存 while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) { $cnt ++; if ($limit == $cnt) { //刷新一下输出buffer,防止由于数据过多造成问题 ob_flush(); flush(); $cnt = 0; } foreach ($row as $i => $v) { $row[$i] = iconv('utf-8', 'gbk', $v); } fputcsv($fp, $row); }