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

<?phpheader('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>';}