使用excel现成模板导出mysql数据到浏览器

<?php 
	
	//echo 'sql='.$sql;
	require("fun.php");
	include('PHPExcel.php');
	$datenow=date("Y-m-d");
	$objPHPexcel = PHPExcel_IOFactory::load('template.xlsx');
	$objWorksheet = $objPHPexcel->getActiveSheet();
	$objWorksheet->getCell('C3')->setValue($datenow);
	
	mysql_query("set names utf8");//程序内控解决乱码问题
	$sql=file_get_contents("sql.txt");
	$res2 = mysql_query($sql,$connect_id);
	$num1=mysql_num_rows($res2);//取得结果集中行的数目
	$fields=mysql_num_fields($res2);//取得结果集中字段的数目
	
	$j=5;
	$a=$j-4;
	while($row=mysql_fetch_row($res2))//此句用数字做为数组索引
		//while($row = mysql_fetch_assoc($res2))//此句用字段名做为索引，感觉这个不容易对错行，喜欢用这个
	{
		for ($i=0;$i<$fields;$i++){
			if (empty($row[$i])||strlen($row[$i])==NULL||$row[$i]=='0000-00-00')
			{//当数组元素为空或者不存在时，$row[]="-"
				$row[$i]='-'; }
		}
		

				$objWorksheet->getCell('A'.$j)->setValue($a++);
				$objWorksheet->getCell('B'.$j)->setValue($row[1]);//这里的ABC对应表格中每个单元格所对应的字母
				$objWorksheet->getCell('C'.$j)->setValue($row[6]);
				$objWorksheet->getCell('D'.$j)->setValue($row[3]);
				$objWorksheet->getCell('E'.$j)->setValue($row[4]);
				$objWorksheet->getCell('F'.$j)->setValue($row[7]);
				$objWorksheet->getCell('G'.$j)->setValue($row[8]);
				$objWorksheet->getCell('H'.$j)->setValue($row[9]);
				$objWorksheet->getCell('I'.$j)->setValue($row[10]);
				$objWorksheet->getCell('J'.$j)->setValue($row[11]);
				$objWorksheet->getCell('K'.$j)->setValue($row[12]);
				$objWorksheet->getCell('L'.$j)->setValue($row[13]);
				$objWorksheet->getCell('M'.$j)->setValue($row[14]);
				$objWorksheet->getCell('N'.$j)->setValue($row[15]);
				$j++;
	}

<span style="white-space:pre">	</span>//***********************画出单元格边框*****************************
<span style="white-space:pre">		</span>$styleArray = array(
<span style="white-space:pre">			</span>'borders' => array(
<span style="white-space:pre">				</span>'allborders' => array(
<span style="white-space:pre">					</span>//'style' => PHPExcel_Style_Border::BORDER_THICK,
<span style="white-space:pre">					</span>'style' => PHPExcel_Style_Border::BORDER_THIN,
<span style="white-space:pre">					</span>//'color' => array('argb' => 'FFFF0000'),
<span style="white-space:pre">				</span>),
<span style="white-space:pre">			</span>),
<span style="white-space:pre">		</span>);
<span style="white-space:pre">		</span>$objWorksheet->getStyle('A5:N'.$n)->applyFromArray($styleArray);//这里的<span style="font-family: Arial, Helvetica, sans-serif;">('A5:N'.$n)对应表格中的单元格从A5到N单元格有数据写入</span>
<span style="white-space:pre">	</span>//***********************画出单元格边框结束*****************************

</pre><pre code_snippet_id="205581" snippet_file_name="blog_20140226_4_3006056" name="code" class="php">		
		mysql_free_result($res2);
				
				
				$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
				$filename=$datenow.'.xls';
				//$objWriter->save('write.xls');
				$objWriter->save($filename);//生成excel文件到本地服务器
				
				//echo '恭喜，导出成功！';
				// Here there will be some code where you create $objPHPExcel
				
				// redirect output to client browser
				header('Content-Type: application/vnd.ms-excel');//生成一个excel文件
				header('Content-Disposition: attachment;filename="'.$filename.'"');//提示下载$filename文件到浏览器
				header('Cache-Control: max-age=0');
				
				//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
				$objWriter->save('php://output');
				unlink ($filename);//删除当前文件夹中所建的表格 */

?>

以上是用mysql,

以下是mysqli

<?php 
	
	//echo 'sql='.$sql;
	require("config.php");
	include('PHPExcel.php');
	$datenow=date("Y-m-d");
	$objPHPexcel = PHPExcel_IOFactory::load('template.xlsx');
	$objWorksheet = $objPHPexcel->getActiveSheet();
	$objWorksheet->getCell('C3')->setValue($datenow);
	
	mysqli_query($conn,"set names utf8");//程序内控解决乱码问题
	
	//$sql=file_get_contents("sql.txt");//从文件中读取字符串,php5以下不支持
//	$file = fopen("sql.txt","r");
//	$sql=fread($file ,filesize("sql.txt"));
//	fclose($file);

$sql=file_get_contents("sql.txt");
	
	
	
	$res2 = mysqli_query($conn,$sql);
	$num1=mysqli_num_rows($res2);//取得结果集中行的数目
	$fields=mysqli_num_fields($res2);//取得结果集中字段的数目
	
	$j=5;
	$a=$j-4;
	while($row=mysqli_fetch_row($res2))//此句用数字做为数组索引
		//while($row = mysql_fetch_assoc($res2))//此句用字段名做为索引，感觉这个不容易对错行，喜欢用这个
	{
		for ($i=0;$i<$fields;$i++){
			if (empty($row[$i])||strlen($row[$i])==NULL||$row[$i]=='0000-00-00')
			{//当数组元素为空或者不存在时，$row[]="-"
				$row[$i]='-'; }
		}
		

				$objWorksheet->getCell('A'.$j)->setValue($a++);//
				$objWorksheet->getCell('B'.$j)->setValue($row[0]);//商品编号
				$objWorksheet->getCell('C'.$j)->setValue($row[1]);//商品名称
				$objWorksheet->getCell('D'.$j)->setValue($row[2]);//供应商名称
				$objWorksheet->getCell('E'.$j)->setValue($row[3]);//供应商编号
				$objWorksheet->getCell('F'.$j)->setValue($row[4]);//商品类别
				$objWorksheet->getCell('G'.$j)->setValue($row[5]);//商品进价
				$objWorksheet->getCell('H'.$j)->setValue($row[6]);//商品卖价
				$objWorksheet->getCell('I'.$j)->setValue($row[7]);//进货日期
				$objWorksheet->getCell('J'.$j)->setValue($row[8]);//库存量
				$objWorksheet->getCell('K'.$j)->setValue($row[9]);//备注
				$n=$j++;
	}

	//***********************画出单元格边框*****************************
		$styleArray = array(
			'borders' => array(
				'allborders' => array(
					//'style' => PHPExcel_Style_Border::BORDER_THICK,
					'style' => PHPExcel_Style_Border::BORDER_THIN,
					//'color' => array('argb' => 'FFFF0000'),
				),
			),
		);
		$objWorksheet->getStyle('A5:K'.$n)->applyFromArray($styleArray);//这里只写到A5到K，所以把上面的N改成了K
	//***********************画出单元格边框结束*****************************
		mysqli_free_result($res2);
				
				
				$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
				$filename=$datenow.'.xls';
				//$objWriter->save('write.xls');
				$objWriter->save($filename);//生成excel文件到本地服务器
				
				//echo '恭喜，导出成功！';
				// Here there will be some code where you create $objPHPExcel
				
				// redirect output to client browser
				header('Content-Type: application/vnd.ms-excel');//生成一个excel文件
				header('Content-Disposition: attachment;filename="'.$filename.'"');//提示下载$filename文件到浏览器
				header('Cache-Control: max-age=0');
				
				//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
				$objWriter->save('php://output');
				unlink ($filename);//删除当前文件夹中所建的表格 */
?>