phpexcel常用功能总结和代码示例

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/>';
//}