PHPExcel类库的使用

首先下载PHPEXCEL 下载地址：https://github.com/PHPOffice/PHPExcel

一、生成Excel

    <?php
    require "PHPExcel-1.8.0/Classes/PHPExcel.php";//引入PHPExcel加载文件

    $obj_PHPExcel = new PHPExcel();//实例化PHPExcel类 等同于新建一个Excel表格
    $obj_Sheet = $obj_PHPExcel->getActiveSheet(); //获得当前活动sheet的活动对象

    //$obj_PHPExcel->createSheet();//可以循环创建多个sheet,在建立多个sheet时使用
    //$obj_PHPExcel->setActiveSheetIndex(0);//设置为活动sheet 从0开始
    //$obj_Sheet = $obj_PHPExcel->getActiveSheet(); //获得当前活动sheet的活动对象

    $obj_Sheet->setTitle('s1');//设置当前活动Sheet名称

    //1.逐个单元格进行填充
    //$obj_Sheet->setCellValue("A1", "姓名")->setCellValue("B1", "分数");//填充数据
    //$obj_Sheet->setCellValue("A2", "张三")->setCellValue("B2", "50");//填充数据

    //$obj_PHPExcel->getActiveSheet()->mergeCells('A1:F1');//合并单元格A1:F1(起始坐标，结束坐标)
    //$obj_PHPExcel->getActiveSheet()->unmergeCells('A1:F1');// 拆分单元格

    $obj_Sheet->getDefaultStyle()->getAlignment() //设置居中显示
        ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)//垂直居中
        ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中

    //2.传入数组的方式填充
    //$obj_Sheet->fromArray([
    //    ['标题1','标题2'], //第一个数组为第一行，数组的第一个元素为第一列
    //    ['1','2'],
    //    ['3','4'],
    //    ['5','6'],
    //    ['7','8'],
    //    ['9','0'],
    //]);

    $result = [
        ['id' => 'ID号',                'name' => '用户',    'pwd' => '密码',   'addr' => '地址'],
        ['id' => '2w5s2525dw88ee8wq87', 'name' => 'test01', 'pwd' => '123456', 'addr' => '广州'],
        ['id' => 'd15as6ds1d6as11das6', 'name' => 'test02', 'pwd' => '123456', 'addr' => '广州'],
        ['id' => 'eq4wqw564e56wq46e4w', 'name' => 'test03', 'pwd' => '123456', 'addr' => '广州'],
        ['id' => 'dasx4zx56c4x564c56a', 'name' => 'test04', 'pwd' => '123456', 'addr' => '广州'],
        ['id' => 'we6qw456eq4w56e4q56', 'name' => 'test05', 'pwd' => '123456', 'addr' => '广州'],
        ['id' => 'd4as56d1456q4545454', 'name' => 'test06', 'pwd' => '123456', 'addr' => '广州'],
        ['id' => 'e4qw64e6qw46eq46447', 'name' => 'test07', 'pwd' => '123456', 'addr' => '广州'],
        ['id' => '6e4q6we8qw7e89wq78e', 'name' => 'test08', 'pwd' => '123456', 'addr' => '广州'],
        ['id' => 'e897e9qw87e98qw798e', 'name' => 'test09', 'pwd' => '123456', 'addr' => '广州'],
    ];

    $obj_Sheet->fromArray($result);//填充数组

    $obj_Writer = PHPExcel_IOFactory::createWriter($obj_PHPExcel, 'Excel2007');//创建工厂对象

    //操作1 保存文件
    //$obj_Writer->save('demo.xlsx');//执行保存文件

    //操作2 输出浏览器
    browser_export('demo', false);
    $obj_Writer->save('php://output');


    //输出到浏览器 判断文件类型
    function browser_export($filename, $type = 'Excel5'){
        // Redirect output to a client’s web browser (Excel5)
        if ($type == 'Excel5') {
            $ext = '.xls';
            header('Content-Type: application/vnd.ms-excel');//输出excel03文件
        }
        else {
            $ext = '.xlsx';
            header('Content-Type: application/vnd.openxmlformats-   officedocument.spreadsheetml.sheet');//输出excel07文件

        }
        header('Content-Disposition: attachment;filename="'.$filename.$ext.'"');//输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存

    }

二、导入Excel

<?php
header('Content-type:text/html;charset=UTF-8');
require "PHPExcel-1.8.0/Classes/PHPExcel/IOFactory.php";//引入读取excel类文件

$filename = __DIR__.'/demo.xlsx';//需要导入的excel路径

//1.部分加载
$file_type = PHPExcel_IOFactory::identify($filename);//读取文件类型
$obj_reader = PHPExcel_IOFactory::createReader($file_type);//获取文件操作对象
$obj_reader->setLoadSheetsOnly('s1');//只读取sheet的名称 多个可以用数组array(sheet1,sheet2...)
$obj_PHPExcel = $obj_reader->load($filename);//加载文件

//2.全部加载
//$obj_PHPExcel = PHPExcel_IOFactory::load($filename); //加载文件
//$sheet_count = $obj_PHPExcel->getSheetCount(); //获取文件sheet数量

/*1.全部读取
for ($i=0; $i < $sheet_count; $i++) {
    $data = $obj_PHPExcel->getSheet($i)->toArray();//读取每个sheet的数据放入数组中
    var_dump($data);
}
*/

/*2.逐行读取*/
foreach ($obj_PHPExcel->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>";
}

三、前端下载excel

1.生成excel文件 使用html5 download属性
<a href="http://localhost/PHPExcel/demo.xlsx" download="demo.xlsx">Download file</a>

2.执行PHP脚本 直接输出到浏览器
<a href="http://localhost/PHPExcel/import.php" >Download file</a>

PHP直接生成excel文件

 header("Content-Type: application/vnd.ms-excel");
 header("Content-Disposition:filename={$file_name}.xls");
 header("Pragma: no-cache");
 header("Expires: 0");

 //此种方法直接输出html表格 因为Excel的兼容性可以打开，但不是真正的excel表格
 echo '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
 echo '<meta http-equiv="Content-type" content="text/html;charset=utf-8" /> ';
 echo $excel_content;//直接输出html的表格代码
 echo '</html>';
}