社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
php操作Excel其实就是用了一个PHPExcel类,这个类提供了很多方法,支持生成Excel文件,支持生成报表等等。
首先我们得去官网下载这个PHPExcel类。
百度PHPExcel,搜索结果最上面显示的那个就是PHPExcel的官网(http://phpexcel.codeplex.com/),点击进入,官网首页右侧有一个download按钮,点击下载即可,下载完后解压,找到里面的Classes目录,把这个目录重命名为PHPExcel并且复制放到服务器www根目录下,打开这个目录,里面会有PHPExcel.php以及PHPExcel文件夹,其中PHPExcel.php这个文件就是我们日后要调用的生成Excel的关键文件。注意里面的PHPExcel文件夹不能删,因为PHPExcel.php也依赖了其中的某些重要文件。
现在我们就可以开始使用php操作excel了。
一、入门
用过Excel的伙伴们都知道,要生成一个Excel文件,首先得新建一个excel表格,然后创建sheet(内置表),然后向sheet里填充数据,最后保存文件。那么同理,用php来生成一个excel也是相同的步骤。
首先得实例化一个PHPExcel类,这就相当于新建一个表格,然后使用该类的createSheet()方法创建sheet,用setActiveSheetIndex()方法设置某个sheet为活动状态,通过该sheet的下标设置。用getActiveSheet()方法获得当前处于活动状态的sheet,然后用该sheet的setCellValue()方法填充数据。最后使用PHPExcel_IOFactory类的save()方法保存文件。如下图:
具体使用:
在PHPExcel.php的同级目录下创建demo.php,
<?php
require './PHPExcel.php';
$PHPExcel=new PHPExcel(); //实例化一个PHPExcel类,相当于在桌面上创建一个表格
$sheet=$PHPExcel->getActiveSheet(); //获得当前获得sheet的操作对象
$sheet->setTitle('demo'); //设置名称
$sheet->setCellValue("A1","姓名")->setCellValue('B1',"分数"); //填充数据
$sheet->setCellValue("A2","张三")->setCellValue('B2',"90"); //填充数据
$sheet->setCellValue("A3","李四")->setCellValue('B3',"80"); //填充数据
$writer=PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007'); //按照指定格式生成Excel文件
$writer->save('./demo.xlsx'); // 保存到指定目录下
运行后,在当前目录下就会生成一个excel文件。
代码解释:使用sheet的setTitle()方法设置sheet的名称,使用setCellValue()方法填充数据。这里的A1就是对应于excel的第A列第1行,同理B2就是第B列第2行。如下图。
调用PHPExcel_IOFactory的静态方法createWriter生成excel文件,第一个参数是PHPExcel资源句柄,第二个的参数是excel的格式,返回一个文件写入句柄$writer,最后调用$writer的save方法保存到指定路径,参数是路径。
二、获取数据库中的数据并且导出到excel里
首先创建一个数据库,假定创建phpexcel数据库,再创建一张user表,表中有id,username,score,class,grade等5个字段,并且插入以下记录,
假定现在的业务需求是制作excel表,该表有3张sheet,每个sheet对应一个年级,里面放该年级学生的信息。
那么我们可以先在PHPExcel.php的同级目录下分别创建dbconfig.php(存储数据库的配置),DB.class.php(从数据获取数据),test.php(测试),
dbconfig.php:
<?php
return array(
'host'=>'127.0.0.1',
'user'=>'root',
'password'=>'',
'database'=>'phpexcel',
'charset'=>'utf8'
);
<?php
class DB{
private $conn=null;
/*连接数据库*/
public function __construct($config){
$this->conn=@mysql_connect($config['host'],$config['user'],$config['password']);
mysql_select_db($config['database'],$this->conn);
mysql_query("set names ".$config['charset'],$this->conn);
}
/*返回结果二维数组*/
public function getResult($sql)
{
$result=mysql_query($sql,$this->conn);
$res=array();
while ($row=mysql_fetch_assoc($result)){
$res[]=$row;
}
return $res;
}
/*根据年级查找学生信息*/
public function getDataByGrade($grade){
$sql="select username,score,class,grade from user where grade=".$grade." order by score desc";
$res=$this->getResult($sql);
return $res;
}
}
test.php:
<?php
require_once './DB.class.php';
require_once "./PHPExcel.php";
$config=include './dbconfig.php';
$DB=new DB($config); //实例化DB类,连接数据库
$excel=new PHPExcel();
for($i=1;$i<=3;$i++){ //三个年级,故需要3张sheet
if($i>1){ //因为有一个默认的内置表
$excel->createSheet(); //创建新的两张内置表
}
$excel->setActiveSheetIndex($i-1); //把新创建的sheet设置为当前活动sheet
$sheet=$excel->getActiveSheet(); //获得当前活动的sheet
$sheet->setTitle($i.'年级'); //设置标题
$data=$DB->getDataByGrade($i); //根据年级查找数据
$sheet->setCellValue("A1","姓名")->setCellValue("B1","分数")->setCellValue("C1","班级");//填充第一行的数据
for($j=0;$j<count($data);$j++){
$sheet->setCellValue("A".($j+2),$data[$j]['username'])->setCellValue("B".($j+2),$data[$j]['score'])->setCellValue("C".($j+2),$data[$j]['class']);//填充数据
}
}
$writer=PHPExcel_IOFactory::createWriter($excel,'Excel2007');
$writer->save('./demo.xlsx');
三、将excel输出到浏览器以便下载。
只需把test.php的最后两行换一下即可,如下:
<?php
require_once './DB.class.php';
require_once "./PHPExcel.php";
$config=include './dbconfig.php';
$DB=new DB($config); //实例化DB类,连接数据库
$excel=new PHPExcel();
for($i=1;$i<=3;$i++){ //三个年级,故需要3张sheet
if($i>1){ //因为有一个默认的内置表
$excel->createSheet(); //创建新的两张内置表
}
$excel->setActiveSheetIndex($i-1); //把新创建的sheet设置为当前活动sheet
$sheet=$excel->getActiveSheet(); //获得当前活动的sheet
$sheet->setTitle($i.'年级'); //设置标题
$data=$DB->getDataByGrade($i); //根据年级查找数据
$sheet->setCellValue("A1","姓名")->setCellValue("B1","分数")->setCellValue("C1","班级");//填充第一行的数据
for($j=0;$j<count($data);$j++){
$sheet->setCellValue("A".($j+2),$data[$j]['username'])->setCellValue("B".($j+2),$data[$j]['score'])->setCellValue("C".($j+2),$data[$j]['class']);//填充数据
}
}<pre name="code" class="php"> browser_export('Excel5','browser_excel03.xls');//输出到浏览器
$objWriter->save("php://output");
function browser_export($type,$filename){
if($type=="Excel5"){
header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
}else{
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
}
header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称
header('Cache-Control: max-age=0');//禁止缓存
}
四、为Excel设置样式
完善DB.class.php,
<?php
class DB{
public $conn=null;
public function __construct($config){//构造方法 实例化类时自动调用
$this->conn=mysql_connect($config['host'],$config['username'],$config['password']) or die(mysql_error());//连接数据库
mysql_select_db($config['database'],$this->conn) or die(mysql_error());//选择数据库
mysql_query("set names ".$config['charset']) or die(mysql_error());//设定mysql编码
}
/**
**根据传入sql语句 查询mysql结果集
**/
public function getResult($sql){
$resource=mysql_query($sql,$this->conn) or die(mysql_error());//查询sql语句
$res=array();
while(($row=mysql_fetch_assoc($resource))!=false){
$res[]=$row;
}
return $res;
}
/**
** 根据传入年级数 查询每个年级的学生数据
**/
public function getDataByGrade($grade){
$sql="select username,score,class from user where grade=".$grade." order by score desc";
$res=self::getResult($sql);
return $res;
}
/**
** 查询所有的年级
**/
public function getAllGrade(){
$sql="select distinct(grade) from user order by grade asc";
$res=$this->getResult($sql);
return $res;
}
/**
**根据年级数查询所有的班级
**/
public function getClassByGrade($grade){
$sql="select distinct(class) from user where grade=".$grade." order by class asc";
$res=$this->getResult($sql);
return $res;
}
/**
**根据年级数班级数查询学生信息
**/
public function getDataByClassGrade($class,$grade){
$sql="select username,score from user where class=".$class." and grade=".$grade." order by score desc";
$res=$this->getResult($sql);
return $res;
}
}
?>
然后test.php稍作修改即可。
<?php
$dir=dirname(__FILE__);//查找当前脚本所在路径
require $dir."/db.php";//引入mysql操作类文件
require './PHPExcel.php';//引入PHPExcel
$config=include './dbconfig.php';
$db=new DB($config);
$objPHPExcel=new PHPExcel();//实例化PHPExcel类, 等同于在桌面上新建一个excel
$objSheet=$objPHPExcel->getActiveSheet();//获得当前活动单元格
/*设置样式*/
$objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中
$objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微软雅黑");//设置默认字体大小和格式
$objSheet->getStyle("A2:Z2")->getFont()->setSize(20)->setBold(true);//设置第二行字体大小和加粗
$objSheet->getStyle("A3:Z3")->getFont()->setSize(16)->setBold(true);//设置第三行字体大小和加粗
$objSheet->getDefaultRowDimension()->setRowHeight(30);//设置默认行高
$objSheet->getRowDimension(2)->setRowHeight(50);//设置第二行行高
$objSheet->getRowDimension(3)->setRowHeight(40);//设置第三行行高
$gradeInfo=$db->getAllGrade();//查询所有的年级
$index=0;
foreach($gradeInfo as $g_k=>$g_v){
$gradeIndex=getCells($index*2);//获取年级信息所在列
$objSheet->setCellValue($gradeIndex."2","高".$g_v['grade']);
$classInfo=$db->getClassByGrade($g_v['grade']);//查询每个年级所有的班级
foreach($classInfo as $c_k=>$c_v){
$nameIndex=getCells($index*2);//获得每个班级学生姓名所在列位置
$scoreIndex=getCells($index*2+1);//获得每个班级学生分数所在列位置
$objSheet->mergeCells($nameIndex."3:".$scoreIndex."3");//合并每个班级的单元格
$objSheet->getStyle($nameIndex."3:".$scoreIndex."3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('6fc144');//填充班级背景颜色
$classBorder=getBorderStyle("445cc1");//获取班级边框样式代码
$objSheet->getStyle($nameIndex."3:".$scoreIndex."3")->applyFromArray($classBorder);//设置每个班级的边框
$info=$db->getDataByClassGrade($c_v['class'],$g_v['grade']);//查询每个班级的学生信息
$objSheet->setCellValue($nameIndex."3",$c_v['class']."班");//填充班级信息
$objSheet->getStyle($nameIndex)->getAlignment()->setWrapText(true);//设置文字自动换行
$objSheet->setCellValue($nameIndex."4","姓名")->setCellValue($scoreIndex."4","分数");
$objSheet->getStyle($scoreIndex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置某列单元格格式为文本格式
$j=5;
foreach($info as $key=>$val){
$objSheet->setCellValue($nameIndex.$j,$val['username'])->setCellValue($scoreIndex.$j,$val['score']);//填充学生信息
//$objSheet->setCellValue($nameIndex.$j,$val['username'])->setCellValueExplicit($scoreIndex.$j,$val['score']."12321321321321312",PHPExcel_Cell_DataType::TYPE_STRING);//填充学生信息
$j++;
}
$index++;
}
$endGradeIndex=getCells($index*2-1);//获得每个年级的终止单元格
$objSheet->mergeCells($gradeIndex."2:".$endGradeIndex."2");//合并每个年级的单元格
$objSheet->getStyle($gradeIndex."2:".$endGradeIndex."2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('c1b644');//填充年级背景颜色
$gradeBorder=getBorderStyle("c144b1");//获取年级边框样式代码
$objSheet->getStyle($gradeIndex."2:".$endGradeIndex."2")->applyFromArray($gradeBorder);//设置每个年级的边框
}
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');//生成excel文件
$objWriter->save($dir."/demo.xls");//保存文件
/*根据下标获得单元格所在列位置*/
function getCells($index){
$arr=range('A','Z');
//$arr=array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,....Z);
return $arr[$index];
}
/*获取边框样式代码*/
function getBorderStyle($color){
$styleArray = array(
'borders' => array(
'outline' => array(
'style' => PHPExcel_Style_Border::BORDER_THICK,
'color' => array('rgb' => $color),
),
),
);
return $styleArray;
}
?>
生成的excel效果如下:
解析如下:
1.设置单元格居中:
$objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
2.设置默认字体大小和格式:
$objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微软雅黑");
3.设置第二行字体大小和加粗
$objSheet->getStyle("A2:Z2")->getFont()->setSize(20)->setBold(true);
4.设置默认行高
$objSheet->getDefaultRowDimension()->setRowHeight(30);
5.设置第二行行高
$objSheet->getRowDimension(2)->setRowHeight(50);
6.合并单元格
$objSheet->mergeCells("A3:B3");//A3和B3合并
7.填充背景颜色
$objSheet->getStyle("A3:B3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('6fc144');//
8.设置边框
$gradeBorder=getBorderStyle("c144b1");//获取年级边框样式代码
$objSheet->getStyle("A3:B3")->applyFromArray($gradeBorder);
function getBorderStyle($color){
$styleArray = array(
'borders' => array(
'outline' => array(
'style' => PHPExcel_Style_Border::BORDER_THICK,
'color' => array('rgb' => $color),
),
),
);
return $styleArray;
}
9.超长数字显示,不显示科学计数
$objSheet->getStyle($scoreIndex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置某列单元格格式为文本格式
$objSheet->setCellValue($nameIndex.$j,$val['username'])->setCellValueExplicit($scoreIndex.$j,$val['score']."12321321321321312",PHPExcel_Cell_DataType::TYPE_STRING);//填充学生信息
四、php操作Excel生成图形报表
<?php
require "../PHPExcel/PHPExcel.php";//引入PHPExcel
$objPHPExcel=new PHPExcel();//实例化PHPExcel类, 等同于在桌面上新建一个excel
$objSheet=$objPHPExcel->getActiveSheet();//获得当前活动sheet
/**生成图形报表**/
$array=array(
array("","一班","二班","三班"),
array("不及格",20,30,40),
array("良好",30,50,55),
array("优秀",15,17,20)
);//准备数据
$objSheet->fromArray($array);//直接加载数组填充进单元格内
//开始图表代码编写
$labels=array(
new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$B$1',null,1),//一班
new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$C$1',null,1),//二班
new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$D$1',null,1),//三班
);//先取得绘制图表的标签
$xLabels=array(
new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$2:$A$4',null,3)//取得图表X轴的刻度
);
$datas=array(
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$B$2:$B$4',null,3),//取一班的数据
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$C$2:$C$4',null,3),//取二班的数据
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$D$2:$D$4',null,3)//取三班的数据
);//取得绘图所需的数据
$series=array(
new PHPExcel_Chart_DataSeries(
PHPExcel_Chart_DataSeries::TYPE_LINECHART,
PHPExcel_Chart_DataSeries::GROUPING_STANDARD,
range(0,count($labels)-1),
$labels,
$xLabels,
$datas
)
);//根据取得的东西做出一个图表的框架
$layout=new PHPExcel_Chart_Layout();
$layout->setShowVal(true);
$areas=new PHPExcel_Chart_PlotArea($layout,$series);
$legend=new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT,$layout,false);
$title=new PHPExcel_Chart_Title("高一学生成绩分布");
$ytitle=new PHPExcel_Chart_Title("value(人数)");
$chart=new PHPExcel_Chart(
'line_chart',
$title,
$legend,
$areas,
true,
false,
null,
$ytitle
);//生成一个图标
$chart->setTopLeftPosition("A7")->setBottomRightPosition("K25");//给定图表所在表格中的位置
$objSheet->addChart($chart);//将chart添加到表格中
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//生成excel文件
$objWriter->setIncludeCharts(true);
$objWriter->save("./chart.xls");//保存文件
//browser_export('Excel2007','browser_chart.xlsx');//输出到浏览器
//$objWriter->save("php://output");
function browser_export($type,$filename){
if($type=="Excel5"){
header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
}else{
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
}
header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称
header('Cache-Control: max-age=0');//禁止缓存
}
?>
首先得创建一张excel,名称为reader.xls,3张内置sheet,如下:
1.把所有的数据暴力地放到一个数组里面。
<?php
header("Content-Type:text/html;charset=utf-8");
require "../PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件
$filename="./reader.xls";
$objPHPExcel=PHPExcel_IOFactory::load($filename);//加载文件
$sheetCount=$objPHPExcel->getSheetCount();//获取excel文件里有多少个sheet
for($i=0;$i<$sheetCount;$i++){
$data=$objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据全部放入到数组中
print_r($data);
}
?>
2.逐行逐列读取数据
<?php
header("Content-Type:text/html;charset=utf-8");
require "../PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件
$filename="./reader.xls";
$objPHPExcel=PHPExcel_IOFactory::load($filename);//加载文件
foreach($objPHPExcel->getWorksheetIterator() as $sheet){ //循环取sheet
foreach($sheet->getRowIterator() as $row){//逐行处理
if($row->getRowIndex()<2) { continue; } //从第二行开始读取
foreach($row->getCellIterator() as $cell){//逐列读取
$data=$cell->getValue();//获取单元格数据
echo $data." ";
}
echo '<br/>';
}
echo '<br/>';
}
?>
2.部分加载
例如:只读取二年级和三年级的数据
<?php
header("Content-Type:text/html;charset=utf-8");
require "../PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件
$filename="./reader.xls";
$fileType=PHPExcel_IOFactory::identify($filename);//自动获取文件的类型提供给phpexcel用
$objReader=PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象
$sheetName=array("2年级","3年级");
$objReader->setLoadSheetsOnly($sheetName);//只加载指定的sheet
$objPHPExcel=$objReader->load($filename);//部分加载文件
foreach($objPHPExcel->getWorksheetIterator() as $sheet){//循环取sheet
foreach($sheet->getRowIterator() as $row){//逐行处理
if($row->getRowIndex()<2) { continue; }
foreach($row->getCellIterator() as $cell){//逐列读取
$data=$cell->getValue();//获取单元格数据
echo $data." ";
}
echo '<br/>';
}
echo '<br/>';
}
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!