Skip to content

Commit bff059e

Browse files
ljcodeljcode
committed
Merge pull request #46 from lyywld/master
add demo_3.php
2 parents 49d4297 + 2d34f99 commit bff059e

2 files changed

Lines changed: 127 additions & 0 deletions

File tree

‎php/excel_demo/excel_export/demo_3.php‎

Lines changed: 126 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,126 @@
1+
<?php
2+
/*
3+
*用excel类导出excel。导出性能相比前两种方法：速度慢，可支持数据量小
4+
* @author:liyiyang
5+
* @date : 2016-06-06
6+
*/
7+
header("Content-Type:text/html;charset=utf-8");
8+
//载入PHPExcel类
9+
require_once "../excel_read/Classes/PHPExcel.php";
10+
//用于输出.xls的
11+
require_once '../excel_read/Classes/PHPExcel/Writer/Excel5.php';
12+
// 或者require_once 'Classes/PHPExcel/Writer/Excel2007.php';
13+
set_time_limit(0);//设置程序执行时间，0为没有时间上的限制
14+
ini_set("memory_limit", "-1");//取消内存限制
15+
16+
/*-----------------声明变量--------------------------*/
17+
$host = '127.0.0.1';//要链接的服务器
18+
$username = 'root';//数据库用户名
19+
$password = '123456';//数据库密码
20+
$dbname = 'db_zou';//数据库
21+
$charset = 'set names utf8';//设置字符集
22+
$filename = 'a.xls';//文件名
23+
$path = './'.$filename;//保存excel文件的位置
24+
25+
//创建一个excel对象实例
26+
$objPHPExcel = new PHPExcel();
27+
//缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0
28+
$objPHPExcel->setActiveSheetIndex(1);
29+
//设置当前活动sheet的名称
30+
$objPHPExcel->getActiveSheet()->setTitle('测试Sheet');
31+
32+
/*------设置excel的属性(非必要属性)-----------------*/
33+
//创建人
34+
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
35+
//最后修改人
36+
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
37+
//标题
38+
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
39+
//题目
40+
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
41+
//描述
42+
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
43+
//关键字
44+
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
45+
//种类
46+
$objPHPExcel->getProperties()->setCategory("Test result file");
47+
48+
49+
/*--------链接数据库查询--------------------------*/
50+
51+
//链接数据库
52+
$con = mysql_connect($host,$username,$password) or die ('database connect failed');
53+
//链接数据库
54+
mysql_select_db($dbname,$con);
55+
//设置字符集
56+
mysql_query($charset);
57+
$sql = "select * from excel_demo limit 1";
58+
$result = mysql_query($sql);
59+
//设置第一行的名称
60+
$objPHPExcel->getActiveSheet()->SetCellValue('A1', '字符串形式数字');
61+
$objPHPExcel->getActiveSheet()->SetCellValue('B1', '日期');
62+
$objPHPExcel->getActiveSheet()->SetCellValue('C1', '数字');
63+
$objPHPExcel->getActiveSheet()->SetCellValue('D1', '货币');
64+
$objPHPExcel->getActiveSheet()->SetCellValue('E1', '百分比');
65+
$n = 2;
66+
while($row = mysql_fetch_assoc($result))
67+
{
68+
//设置单元格的值
69+
$objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$n, $row['string'],PHPExcel_Cell_DataType::TYPE_STRING);//字符串(防止数值型字符串科学计数法)
70+
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$n,$row['date']);
71+
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$n,$row['number']);
72+
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$n,$row['money']);
73+
$objPHPExcel->getActiveSheet()->SetCellValue('E'.$n,$row['percent']);
74+
//设置单元格值的形式
75+
$objPHPExcel->getActiveSheet()->getStyle('D'.$n)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);//设置金额格式 11,333.00
76+
$objPHPExcel->getActiveSheet()->getStyle('E'.$n)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);//设置百分比格式 0.30 30.00%
77+
$n++;
78+
}
79+
/*------------------设置单元格属性(可以将某些属性写入上面的循环里)---------------------*/
80+
/*
81+
//合并单元格：
82+
$objPHPExcel->getActiveSheet()->mergeCells('A1:B1');
83+
//设置单元格宽度
84+
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
85+
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
86+
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
87+
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
88+
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
89+
//设置表头行高
90+
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);//第一行 : 字符串形式数字
91+
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(22);//第二行 : 日期
92+
//设置字体样式
93+
//'A1:E3' 表示从A1到E3所有单元格
94+
http://www.cnblogs.com/freespider/p/3284828.html
95+
$objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getFont()->setName('黑体');// 黑体/宋体
96+
$objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getFont()->setSize(20);
97+
$objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getFont()->setBold(true);//加粗
98+
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);//设置下划线
99+
$objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);//设置字体颜色:红色
100+
//设置居中
101+
$objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
102+
//所有垂直居中
103+
$objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
104+
//设置自动换行
105+
$objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getAlignment()->setWrapText(true);
106+
*/
107+
//输出文档
108+
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
109+
//或者$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);//保存excel—2007格式
110+
if(1 == 1)
111+
{
112+
//保存文件位置
113+
$objWriter->save($path);//保存文件 参数为文件目录及文件名
114+
}
115+
//直接输出到浏览器（输出时会把文件保存到一个默认的路径，用户无法选择路径）
116+
header("Pragma: public");
117+
header("Expires: 0");
118+
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
119+
header("Content-Type:application/force-download");
120+
header("Content-Type:application/vnd.ms-execl");
121+
header("Content-Type:application/octet-stream");
122+
header("Content-Type:application/download");
123+
header("Content-Disposition:attachment;filename=".$filename);
124+
header("Content-Transfer-Encoding:binary");
125+
$objWriter->save("php://output");
126+
?>

‎php/excel_demo/excel_export/readme.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
### excel导出
22
* demo_1.php 第一种导出excel的方法
33
* demo_2.php 第二种导出excel的方法
4+
* demo_3.php 第三种导出excel的方法（用excel类导出excel表格）
45
* demo_4.php 第二种方法的延伸,对于:文本、数字、日期、货币、百分比 的处理

0 commit comments

Comments
 (0)