PHPExcel 使用简单说明

PHPExcel 下载地址

  • 解压后把Classes文件夹复制到项目目录并重新命名为PHPExcel。
  • 新建一个excel表格 ------------实例化PHPExcel
  • 创建sheet(内置表) -----------createSheet()方法 获取当前活动对象 getActiveSheet方法
  • 填充数据 ------------setCellValue方法
  • 保存文件 -------------PHPExcel_IOFactory::createWriter()方法 save()方法

首先看一下目录结构:

QQ截图20190829114642.png


创建index.php

<?php
/**
 * Created by ZhengNiu.
 * User: admin
 * Date: 2019/8/28
 * Time: 14:59
 */
$dir = dirname(__FILE__);
require_once $dir . '/PHPExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();//实例化PHPExcel类 等同于在桌面上新建一个excel。
$objSheet = $objPHPExcel->getActiveSheet();//获取当前活动sheet的操作对象
$objSheet->setTitle('demo');//给当前活动sheet设置名称
$objSheet->setCellValue("A1", "姓名")->setCellValue("B1", "分数");//给当前活动sheet填充数据
$objSheet->setCellValue("A2", "张三")->setCellValue("B2", 50);
/*$arr = [
    ['姓名','分数'],
    ['李四','60'],
    ['王五','70']
];
$objSheet->fromArray($arr);//直接加载数据块添加数据*/

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//按照指定格式生成excel文件
$objWriter->save($dir . '/demo.xlsx');

效果:

4.png

结合数据库查询写一个简单的小例子:场景有这样一个订单表如图,我们把1-20号订单,21-40号订单,41-60号订单分开放在不同的sheet里面。

1.png

dbconfig.php--数据库配置文件

  <?php
    /**
     * Created by ZhengNiu.
     * User: admin
     * Date: 2019/8/28
     * Time: 15:37
     */
    $dbconfig = [
        'host' => '127.0.0.1',
        'username' => 'root',
        'password' => '123456',
        'database' => 'test',
        'charset'  => 'utf8'
    ];

db.php----数据库操作类

<?php
/**
 * Created by ZhengNiu.
 * User: admin
 * Date: 2019/8/28
 * Time: 15:39
 */
require_once dirname(__FILE__) . '/dbconfig.php';

class db
{
    public $conn = null;
    static $_instance;
    /**
     * db constructor.
     * @param $config
     */
    private function __construct($config)
    {
        $this->conn = mysqli_connect($config['host'], $config['username'], $config['password'], $config['database']);
        if ($this->conn->connect_errno) {
            printf("Connect failed: %s\n", $this->conn->connect_error);
            exit();
        }
        mysqli_query($this->conn, "set names " . $config['charset']) or die(mysqli_error($this->conn));
    }

    /**
     * @param $sql
     * @return array
     */
    public function getResult($sql)
    {
        $resource = mysqli_query($this->conn, $sql) or die(mysqli_error($this->conn));
        $res = [];
        while (($row = mysqli_fetch_assoc($resource)) != false) {
            $res[] = $row;
        }
        return $res;
    }
    private function __clone(){}

    /**
     * @param $config
     * @return db
     */
    public static function getInstance($config){
        if(FALSE == (self::$_instance instanceof self)){
            self::$_instance = new self($config);
        }
        return self::$_instance;
    }

    /**
     * @param $sql
     * @return bool|mysqli_result
     */
    public function query($sql)
    {
        return $this->conn->query($sql);
    }

    /**
     ** 查询所有的年级
     **/
    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;
    }
}

helpers.php---调试输出文件

<?php
/**
 * Created by ZhengNiu.
 * User: admin
 * Date: 2019/8/29
 * Time: 10:15
 */
/**
 * @param $val
 * @param bool $dump
 * @param bool $exit
 */
function debug($val, $dump = false, $exit = true)
{
    if ($dump) {
        $func = 'var_dump';
    } else {
        $func = (is_array($val) || is_object($val)) ? 'print_r' : 'printf';
    }
    header("Content-type:text/html;charset=utf-8");
    echo '<pre>debug output:<hr />';
    $func($val);
    echo "</pre>";
    if ($exit) exit;
}

export.php----逻辑代码文件

<?php
/**
 * Created by ZhengNiu.
 * User: admin
 * Date: 2019/8/28
 * Time: 15:59
 */
$dir = dirname(__FILE__);
require_once $dir . '/db.php';
require_once $dir . '/PHPExcel/PHPExcel.php';
$db = db::getInstance($dbconfig);
$objPHPExcel = new PHPExcel();//实例化PHPExcel类 等同于在桌面上新建一个excel。
for ($i = 1; $i <= 3; $i++) {
    if ($i > 1) {
        $objPHPExcel->createSheet();//创建sheet
    }
    $objPHPExcel->setActiveSheetIndex($i - 1);//把新创建的sheet设定为当前sheet
    $objSheet = $objPHPExcel->getActiveSheet();//获取当前活动sheet
    $objSheet->setTitle('订单号:' . (($i - 1) * 20 + 1) . '~' . $i * 20);
    $where = " id>=".(($i-1)*20 + 1).' AND id<='.($i*20);
    $data = $db->getResult("SELECT * FROM `order` WHERE {$where}");//查询每个年级的学生数据
    $objSheet->setCellValue('A1', '订单ID')
        ->setCellValue('B1', '是否删除')
        ->setCellValue("C1", '发货时间')
        ->setCellValue("D1", '是否取消')
        ->setCellValue("E1", '是否发货')
        ->setCellValue("F1", '是否验证')
        ->setCellValue("G1", '验证时间');
    //从第二行开始写入
    $j = 2;
    foreach ($data as $k => $v) {
        $objSheet->setCellValue("A" . $j, $v['id'])
            ->setCellValue("B" . $j, $v['is_del'])
            ->setCellValue("c" . $j, $v['send_time'])
            ->setCellValue("D" . $j, $v['is_cancel'])
            ->setCellValue("E" . $j, $v['is_send'])
            ->setCellValue("F" . $j, $v['is_token'])
            ->setCellValue("G" . $j, $v['token_time']);
        $j++;
    }
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//按照指定格式生成excel文件
//保存在服务器
//$objWriter->save($dir . '/export.xlsx');
//输出到浏览器
browser_export('Excel5','browser_export03.xls');
$objWriter->save('php://output');
exit;

/**
 * 输出到浏览器
 *
 * @param $type
 * @param $filename
 */
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');//禁止缓存
}

效果:

5.png

下面我们以年级、班级、学生成绩为例介绍一些常用的样式(设置字体、设置边框、合并单元格、设置、设置背景色、以及单元格内换行、单元格不以科学记数法显示等)

首先我们建一个数据表并插入一些数据如图:

2.png

insert.php----插入测试数据文件

<?php
/**
 * Created by ZhengNiu.
 * User: admin
 * Date: 2019/8/29
 * Time: 9:23
 */
$dir = dirname(__FILE__);
require_once $dir . '/db.php';
require_once $dir.'/helpers.php';
$conn = db::getInstance($dbconfig);
//$conn->query("truncate table `user`");die;

$sqlTpl = "INSERT INTO `user` (`id`,`class`,`grade`,`score`,`username`) VALUES ";
$val = "";
for ($i = 1; $i <= 1000; $i++) {
    $username = getUsername();
    $value = [$i, rand(1, 4), rand(1, 3), rand(20, 100), "'{$username}'"];
    $val .= " ( " . implode(',', $value) . " ) ,";
}
$insertStr = $sqlTpl.rtrim($val, ',');


$conn->query($insertStr);


/**
 * @return string
 */
function getUsername()
{
    $name = ["张飞", "李奎", "赵三", "赵云", "王昭君", "孙策", "孙尚香", "哪吒", "刘婵", "后裔", "妲己"];
    $randge = range("A", "Z");
    $randgeNum = range(0, 1000);
    return $name[rand(0, count($name) - 1)] . $randge[rand(0, count($randge) - 1)] . $randgeNum[rand(0, count($randgeNum) - 1)];
}

style.php-----逻辑代码

<?php
/**
 * Created by ZhengNiu.
 * User: admin
 * Date: 2019/8/28
 * Time: 15:59
 */
$dir = dirname(__FILE__);
require_once $dir . '/db.php';
require_once $dir . '/PHPExcel/PHPExcel.php';
require_once $dir.'/helpers.php';
$db = db::getInstance($dbconfig);
$objPHPExcel = new PHPExcel();//实例化PHPExcel类 等同于在桌面上新建一个excel。
$objSheet = $objPHPExcel->getActiveSheet();//获得当前活动单元格
$objSheet->setTitle("学生成绩统计");
$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", "姓名\n换行")->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']);//填充学生信息
            $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);//设置每个年级的边框
}



//die;
//debug($index);



$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//按照指定格式生成excel文件
//保存在服务器
//$objWriter->save($dir . '/export.xlsx');
//输出到浏览器
browser_export('Excel5','browser_export03.xls');
$objWriter->save('php://output');
exit;

/**
 * 输出到浏览器
 *
 * @param $type
 * @param $filename
 */
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');//禁止缓存
}

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

演示结果:

3.png

插入图片、添加丰富的文字块、添加超链接、添加批注等

<?php 
    $dir=dirname(__FILE__);//查找当前脚本所在路径
    require $dir."/db.php";//引入mysql操作类文件
    require $dir."./../PHPExcel/PHPExcel.php";//引入PHPExcel
    $objPHPExcel=new PHPExcel();//实例化PHPExcel类, 等同于在桌面上新建一个excel
    $objSheet=$objPHPExcel->getActiveSheet();//获得当前活动sheet
    /**插入图片代码开始**/
    $objDrawing=new PHPExcel_WorkSheet_Drawing();//获得一个图片的操作对象
    $objDrawing->setPath($dir."./../1.png");//加载图片路径
    $objDrawing->setCoordinates("F6");//设置图片插入位置的左上角坐标
    $objDrawing->setWidth(500);//设置插入图片的大小 等比
    //$objDrawing->setHeight(100);//会覆盖上面的setWidth
    //$objDrawing->setOffsetX(20)->setOffsetY(40);//设定单元格内偏移量
    $objDrawing->setWorkSheet($objSheet);//将图片插入到sheet
    /**代码结束**/
    /**添加丰富的文字块**/
    $objRichText=new PHPExcel_RichText();//获得一个文字块操作对象
    $objRichText->createText("我的测试");//添加普通的文字 不能操作样式
    $objStyleFont=$objRichText->createTextRun("我的测试");//生成可以添加样式的文字块
    $objStyleFont->getFont()->setSize(16)->setBold(True)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_GREEN));//加一些样式
    $objRichText->createText(",我的测试");
    $objSheet->getCell("F4")->setValue($objRichText);//将文字块插入sheet里
    /**代码结束**/
    /**添加批注代码开始**/
    $objSheet->mergeCells("F4:N4");//合并单元格
    $objSheet->getComment("F4")->getText()->createTextRun("Van:\r\n我的测试\n\n时尚时尚最时尚");//添加批注
    /**代码结束**/

    /**超链接代码开始**/
    $objSheet->setCellValue("I3","我的测试");//添加文字
    $objSheet->getStyle("I3")->getFont()->setSize(16)->setBold(true)->setUnderline(true)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLUE));//添加样式
    $objSheet->getCell("I3")->getHyperlink()->setUrl("http://www.baidu.com");//给文字加上链接地址
    /**代码结束**/

    $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//生成excel文件
    //$objWriter->save($dir."/export_1.xls");//保存文件
    browser_export('Excel2007','browser_excel03.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');//禁止缓存
    }
?>

演示:
6.png

图表绘制

<?php 
    $dir=dirname(__FILE__);//查找当前脚本所在路径
    require $dir."/db.php";//引入mysql操作类文件
    require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel
    $db = db::getInstance($dbconfig);//实例化db类 连接数据库
    $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($dir."/export_1.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');//禁止缓存
    }
?>

演示:
7.png
读取excel:

<?php
header("Content-Type:text/html;charset=utf-8");
$dir = dirname(__FILE__);//找到当前脚本所在路径
require $dir . "/PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件
$filename = $dir . "/demo.xlsx";
$fileType = PHPExcel_IOFactory::identify($filename);//自动获取文件的类型提供给phpexcel用
$objReader = PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象
$sheetName = array("demo");
$objReader->setLoadSheetsOnly($sheetName);//只加载指定的sheet
$objPHPExcel = $objReader->load($filename);//加载文件
//$sheetCount=$objPHPExcel->getSheetCount();//获取excel文件里有多少个sheet
//  for($i=0;$i<$sheetCount;$i++){
//  $data=$objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据 全部放入到数组中
//  print_r($data);
//}
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/>';
}
exit;
?>

**拓展:

PHP启用缓存

  • 默认 -----------按cell占用PHP内存
  • cache_ in memory serialized ------------ 序列化存放PHP内存
  • cache in memory gzip ----------进一 步压缩
  • cache_ in memory_ disclSAM ------------ 存放于硬盘
  • cache_ in memory .phpTemp -----------存放于临时文件夹
  • cache in memory_ memcache ----------存放于memcache里
以memcache为例子简单的说明一下、具体使用请参考文档
<?php
    $dir = dirname(__FILE__);
    require_once $dir . '/PHPExcel/PHPExcel.php';
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;
    $cacheSettings = array(
            'memcacheServer' => 'localhost',
            'memcachePort'  => 11211,
            'cache Time'  => 600
    );
    PHPExcel_Settings:setCacheStorageMethod($cacheMethod, $cacheSettings); 
?>

PHPExcel相关文章、大文件导出数据相关文章


关注友儿不迷路

Last modification:September 19th, 2019 at 09:05 pm
如果觉得我的文章对你有用,请随意赞赏