php两种导出excel的方法

原创
2011/08/08 15:31
阅读数 3.2W

所需要的:jquery库,phpexcel插件,页面导出excel效果测试文件explode.php,excel导出功能实现文件exp.php和explode_excel.php,文件相关内容在此文下面:

explode.php:测试页面内容:访问此页面后单击explode1与explode2能导出两种不同效果的excel,代码如下:

<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
<script src='jquery.js'></script>
<title></title>
<style>
td{
	text-align:center;
	font-size:12px;
	font-family:Arial, Helvetica, sans-serif;
	border:#1C7A80 1px solid;
	color:#152122;
	width:100px;
}
table,tr{
	border-style:none;
}
.title{
	background:#7DDCF0;
	color:#FFFFFF;
	font-weight:bold;
}
</style>
</head>
<body>
<script>
	$(document).ready(function(){
		$('#explode1').click(function(){
				window.location.href='exp.php';
		});

		$('#explode2').click(function(){
				window.location.href='explode_excel.php';
		});
	})
</script>
<table width="800" border="1">
  <tr>
    <td class='title'>Date</td>
    <td class='title' colspan="5" style='width:500px;text-align:center;'>CSAT Score</td>
    <td class='title'>Grand Total</td>
    <td class='title'>CSAT</td>
  </tr>
  <tr>
    <td>08/01/11</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0%</td>
  </tr>
  <tr>
    <td>08/01/11</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0%</td>
  </tr>
  <tr>
    <td>08/01/11</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0%</td>
  </tr>
</table>
<br />
<input type='button' id='explode1' value='Explode' style='margin-left:620px;background-color:#10899E;color:white;padding:3px;font-weight:bold;'><input type='button' id='explode2' value='Explode2' style='background-color:#10899E;color:white;padding:3px;font-weight:bold;margin-left:24px;'>
</body>
</html>

第一种,用phpexcel插件导出excel,即exp.php文件中的内容:

<?php
header("content-type:text/html;charset=utf-8");
/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */
include_once 'PHPExcel.php';

/** PHPExcel_Writer_Excel2003用于创建xls文件 */
include_once 'PHPExcel/Writer/Excel5.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("李汉团");
$objPHPExcel->getProperties()->setLastModifiedBy("李汉团");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

// Add some data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Date');
//合并单元格:
$objPHPExcel->getActiveSheet()->mergeCells('B1:F1');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'CSAT Score');
$objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Grand Total');
$objPHPExcel->getActiveSheet()->SetCellValue('H1', 'CSAT');
$objPHPExcel->getActiveSheet()->SetCellValue('A2', '08/01/11');
$objPHPExcel->getActiveSheet()->SetCellValue('B2', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('C2', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('D2', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('E2', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('F2', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('G2', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('H2', '0%');
$objPHPExcel->getActiveSheet()->SetCellValue('A3', '08/01/11');
$objPHPExcel->getActiveSheet()->SetCellValue('B3', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('C3', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('D3', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('E3', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('F3', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('G3', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('H3', '0%');
$objPHPExcel->getActiveSheet()->SetCellValue('A4', '08/01/11');
$objPHPExcel->getActiveSheet()->SetCellValue('B4', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('C4', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('D4', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('E4', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('F4', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('G4', '0');
$objPHPExcel->getActiveSheet()->SetCellValue('H4', '0%');

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Csat');

  
// Save Excel 2007 file
//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
 
 $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
 $objWriter->save(str_replace('.php', '.xls', __FILE__));
 header("Pragma: public");
 header("Expires: 0");
 header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
 header("Content-Type:application/force-download");
 header("Content-Type:application/vnd.ms-execl");
 header("Content-Type:application/octet-stream");
 header("Content-Type:application/download");
 header("Content-Disposition:attachment;filename=csat.xls");
 header("Content-Transfer-Encoding:binary");
 $objWriter->save("php://output");
?>

第二种,直接用header导出html,指定样式的excel,即explode_excel.php中的内容,如下:

<?php
header ( "Content-type:application/vnd.ms-excel" );
header ( "Content-Disposition:filename=csat.xls" );
	echo "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />
<title>无标题文档</title>
<style>
td{
	text-align:center;
	font-size:12px;
	font-family:Arial, Helvetica, sans-serif;
	border:#1C7A80 1px solid;
	color:#152122;
	width:100px;
}
table,tr{
	border-style:none;
}
.title{
	background:#7DDCF0;
	color:#FFFFFF;
	font-weight:bold;
}
</style>
</head>

<body>
<table width='800' border='1'>
  <tr>
    <td class='title'>Date</td>
    <td class='title' colspan='5' style='width:500px;text-align:center;'>CSAT Score</td>
    <td class='title'>Grand Total</td>
    <td class='title'>CSAT</td>
  </tr>
  <tr>
    <td>08/01/11</td>
    <td>0</td>
    <td>0</td>
    <td>测试内容!!</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0%</td>
  </tr>
  <tr>
    <td>08/01/11</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0%</td>
  </tr>
  <tr>
    <td>08/01/11</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0%</td>
  </tr>
</table>
</body>
</html>
";
?>

 

 

展开阅读全文
打赏
4
14 收藏
分享
加载中
table导入excel的时候如何设置图片的大小??
2016/01/20 16:39
回复
举报
说到底是只能指定url为生成excel文件的php文件了?
2014/12/18 16:01
回复
举报
更多评论
打赏
2 评论
14 收藏
4
分享
返回顶部
顶部