1.下载地址 https://github.com/PHPOffice/PHPExcel
2.dbconfig.php配置文件
$phpexcel = [ 'host' => '127.0.0.1', 'username' => 'root', 'password' => '', 'database' => 'localhost_test', 'charset' => 'utf8', ];
3.db.php 功能性文件
<?php
/**
* Created by PhpStorm.
* User: 、
* Date: 2019/12/17
* Time: 14:20
*/
$dir = dirname(__FILE__);
require $dir . '/dbconfig.php';
/**
* 输出到浏览器的方法
* @param $type 文件类型
* @param $filename 文件名称
*/
function browserExport($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');//禁止缓存
}
//根据年级取出数据
function getDataByGrade($dbh,$grade){
$stmt = $dbh->query('SELECT * from phpexcel WHERE grade ='.$grade);
$rows = $stmt->fetchAll();
return $rows;
}
/**
* 查出所有年级
* @param $dbh
* @return mixed
*/
function getAllGrade($dbh){
$stmt = $dbh->query('SELECT DISTINCT(grade) from phpexcel');
$rows = $stmt->fetchAll();
return $rows;
}
/**
* 根据年级查询所有班级
* @param $dbh
* @param $grade
* @return mixed
*/
function getAllClassByGrade($dbh,$grade){
$stmt = $dbh->query('SELECT DISTINCT(class) from phpexcel WHERE grade = '.$grade.' order by class');
$rows = $stmt->fetchAll();
return $rows;
}
/**
* 根据年级和班级查询学生信息
* @param $dbh
* @param $class
* @param $grade
* @return mixed
*/
function getDataByClassGrade($dbh,$class,$grade){
$stmt = $dbh->query('SELECT username,score from phpexcel WHERE grade = '.$grade.' and class = '.$class);
$rows = $stmt->fetchAll();
return $rows;
// $dbh->errorInfo();
// return $dbh->errorInfo();
}
/**
* 根据下标获取单元格所在的列
* @param $index
* @return mixed
*/
function getCells($index){
return range('A','Z')[$index];
}
/**
* 获取外边框样式
* @param $color
* @return array
*/
function getBorderStyle($color){
$styleArray = [
'borders' => [
'outline' => [
'style' => PHPExcel_Style_Border::BORDER_THICK,
'color' => ['rgb' => $color]
]
]
];
return $styleArray;
}
4.demo.php 常用功能
<?php header("Content-type:text/html;charset=utf-8"); $dir = dirname(__FILE__); require $dir . '/phpExcel/PHPExcel.php'; require $dir . '/dbconfig.php'; require $dir . '/db.php'; /** 牛刀小试 $obj = new PHPExcel(); $objSheet = $obj->getActiveSheet();//获取当前活动的sheet对象 $objSheet->setTitle('demo'); $objSheet->setCellValue('A1', '姓名')->setCellValue('B1', '分数'); $objSheet->setCellValue('A2', '张三')->setCellValue('B2', '30'); $array = [ [], ['', '姓名', '分数'], ['', '李四', '60'], ['', '王五', '70'], ]; $objSheet->fromArray($array);//直接从数据块中来填充数据 $objWriter = PHPExcel_IOFactory::createWriter($obj, "Excel2007"); $objWriter->save($dir . "/demo_2.xlsx");*/ //数据操作,连接数据库读取数据 $dbh = new PDO('mysql:host=localhost;dbname=localhost_test', $phpexcel['username'], $phpexcel['password']); $dbh->query('set names utf8;'); $obj = new PHPExcel(); /* for ($i = 1; $i <= 3; $i++) { $rows = getDataByGrade($dbh,$i);//根据年级取出数据 if($i>1){//默认有一个了,更多的才需要新建 $obj->createSheet(); } $obj->setActiveSheetIndex($i-1);//把新创建的sheet表设置为当前活动sheet $objSheet = $obj->getActiveSheet();//获取当前活动的sheet对象 $objSheet->setTitle($i.'年级');//设置sheet表的名称 $objSheet->setCellValue('A1', '姓名') ->setCellValue('B1', '成绩') ->setCellValue('C1', '班级') ->setCellValue('D1', '年级'); $j = 2; foreach ($rows as $k => $v) { $objSheet->setCellValue('A' . $j, $v['username']) ->setCellValue('B' . $j, $v['score']) ->setCellValue('C' . $j, $v['class']) ->setCellValue('D' . $j, $v['grade']); $j++; } }*/ //print_r(getCells(3));die; $objSheet = $obj ->getActiveSheet();//获取当前的sheet表 $objSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置水平居中 $objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置垂直居中 $objSheet->getDefaultStyle()->getFont()->setName('微软雅黑')->setSize('14');//设置默认的字体和颜色 $objSheet->getStyle('A2:Z2')->getFont()->setSize('20')->setBold(true);//设置年级行的字体 $objSheet->getStyle('A3:Z3')->getFont()->setSize('16')->setBold(true);//设置班级行的字体 $gradeInfo = getAllGrade($dbh);//查询所有年级 $index = 0; foreach ($gradeInfo as $gk => $gv){ $classInfo = getAllClassByGrade($dbh,$gv['grade']);//查询每个年级的所有班级 $gradeIndex = getCells($index * 2 ); //每个年级开始列 $objSheet->setCellValue($gradeIndex.'2','高'.$gv['grade'].'年级');//第二行写年级名称 foreach ($classInfo as $ck => $cv){ $nameIndex = getCells($index * 2);//获取班级姓名列的位置 $scoreIndex = getCells($index * 2 +1);//获取班级成绩列的位置 $info = getDataByClassGrade($dbh,$cv['class'],$gv['grade']);//查询每个班级的学生信息 $objSheet->setCellValue($nameIndex.'3',$cv['class'].'班');//第三行写班级名称 $objSheet->mergeCells($nameIndex.'3:'.$scoreIndex.'3');//合并每个班级的名称的单元格 $objSheet->getStyle($nameIndex.'3:'.$scoreIndex.'3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('42E61A');//填充班级行背景颜色 $objSheet->getStyle($nameIndex.'3:'.$scoreIndex.'3')->applyFromArray(getBorderStyle('e3df51'));//设置边框样式和颜色 $objSheet->getStyle($nameIndex)->getAlignment()->setWrapText(true);//设置自动换行 $objSheet->setCellValue($nameIndex.'4',"姓\n名")->setCellValue($scoreIndex.'4','分数');//第四行写姓名分数(换行的字符串外边必须是双引号) // $objSheet->getStyle($nameIndex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置数字超长时不转为科学计数显示 $j = 5;//学生信息从第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'],PHPExcel_Cell_DataType::TYPE_STRING);//设置数字超长时不转为科学计数显示 $j++; } $index++; } $gradeEndIndex = getCells($index * 2 - 1 );//每个年级结束列 $objSheet->mergeCells($gradeIndex.'2:'.$gradeEndIndex.'2');//合并每个年级的名称的单元格 $objSheet->getStyle($gradeIndex.'2:'.$gradeEndIndex.'2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('e36951');//填充年级行背景颜色 $objSheet->getStyle($gradeIndex.'2:'.$gradeEndIndex.'2')->applyFromArray(getBorderStyle('e3df51'));//设置边框样式和颜色 } //print_r($gradeInfo);die; $objWriter = PHPExcel_IOFactory::createWriter($obj, "Excel2007");//生成文件 //$objWriter->save($dir . "/demo_2.xlsx");//保存文件到服务器 //将文件输出到浏览器 browserExport('Excel5','browser.xls'); $objWriter->save("php://output");
5.demo2.php 不常用功能
<?php header("Content-type:text/html;charset=utf-8"); $dir = dirname(__FILE__); require $dir . '/phpExcel/PHPExcel.php'; require $dir . '/db.php'; $obj = new PHPExcel(); $objSheet = $obj ->getActiveSheet();//获取当前的sheet表 ///**插入图片开始**/ //$objImage = new PHPExcel_Worksheet_Drawing();//获取一个图片操作对象 //$objImage ->setPath($dir.'/0.jpeg');//图片路径(网络url地址不中) //$objImage ->setCoordinates('F6');//设置图片插入路径 //$objImage ->setWidth(300);//设置图片大小(等比例变化) //$objImage ->setOffsetX(10)->setOffsetY(10);//设置图片在一个单元格的偏移量(不能超过单元格大小,否则失效) //$objImage ->setWorksheet($objSheet);//图片插入表 ///**插入图片结束**/ // ///**丰富文字块开始**/ //$objRichText = new PHPExcel_RichText();//获取文字块操作对象 //$objRichText->createText('普通文字');//添加普通文字 //$objStyleFont = $objRichText->createTextRun('有样式的文字块');//生成有样式文字 //$objStyleFont->getFont()->setSize(16)->setBold(True)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLUE));//加样式 //$objRichText->createText('普通文字');//添加普通文字 //$objSheet->getCell("G4")->setValue($objRichText);//写入sheet表 ///**丰富文字块结束**/ // 添加批注 //$objSheet->getComment("F4")->getText()->createTextRun('这是我测试写的批注'); // 添加超链接 //$objSheet->setCellValue('C5','点击跳转')->getCell("C5")->getHyperlink()->setUrl("https://www.baidu.com"); /**生成报表开始**/ //测试数据 $array = [ ['', '一班', '二班', '三班',], ['不及格', 20, 10, 40], ['良好', 50, 40, 70], ['优秀', 10, 5, 4], ]; $objSheet->fromArray($array);//填充数据 //标签 $labels = [ 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),//三班 ]; //表的x轴 $xLabels = [ new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$2:$A$4',null,3), ]; //数据 $datas = [ 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 = [ 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('人数'); $chart = new PHPExcel_Chart( 'line_chart', $title, $legend, $areas, true, false, null, $ytitle ); $chart->setTopLeftPosition('A7')->setBottomRightPosition('K25'); $objSheet->addChart($chart); /**生成报表结束**/ $objWriter = PHPExcel_IOFactory::createWriter($obj, "Excel2007");//生成文件 $objWriter->setIncludeCharts(true);//报表需要的 browserExport('Excel5','demo.xls');//将文件输出到浏览器 $objWriter->save("php://output");
6.read.php 读取文件
<?php header("Content-type:text/html;charset=utf-8"); $dir = dirname(__FILE__); require $dir . '/PHPExcel/PHPExcel/IOFactory.php'; $filename = $dir . '/demo_2.xlsx'; /**部分加载开始**/ $fileType = PHPExcel_IOFactory::identify($filename);//文件类型 $objReader = PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象 $sheetName = ['1年级','3年级']; $objReader->setLoadSheetsOnly($sheetName); $obj = $objReader->load($filename);//加载文件 /**部分加载结束**/ //$obj = PHPExcel_IOFactory::load($filename);//加载文件生成对象 //将所有的数据一次全部拿出 $sheetCount = $obj->getSheetCount();//求一共有多少sheet $data = []; for ($i = 0; $i < $sheetCount; $i++) {//遍历将数据写入$data $data[$i] = $obj->getSheet($i)->toArray(); } print_r($data); //循环单个取 //foreach ($obj->getWorksheetIterator() as $sheet){//获取sheet // foreach ($sheet->getRowIterator() as $row){//获取每一行 // foreach ($row->getCellIterator() as $cell){//获取每一个单元格 // $data = $cell->getValue(); // echo $data.' '; // } // echo '<br/>'; // } // echo '<br/>'; //}