php基础 --- PHPExcel的使用总结

PHPExcel是使用php语言操作excel的一个类库

使用前

去官网下载phpexcel插件源码,提取代码包根目录下Classes文件夹,放在自己的项目目录下(重命名为PHPExcel)
官网地址:https://github.com/PHPOffice/PHPExcel

PHPExcel导出文件

1、导出步骤

(1)新建一个excel表格:实例化PHPExcel类
(2)创建sheet(内置表):creatSheet(), setActiveSheetIndex(), getActiveSheet()
(3)填充数据:setCellValue()
(4)保存数据:PHPExcel_IOFactory::createWriter(),save()

2、导出代码示例

(1)实例化对象

1
2
3
4
<?php
$dir = dirname(__FILE__);//找到当前脚本所在路径
require $dir."/PHPExcel/PHPExcel.php";//引入文件
$objPHPExcel = new PHPExcel();//实例化phpexcel类

(2)获取当前活动sheet操作对象

1
$objSheet = $objPHPExcel->getActiveSheet();

(3)给当前活动sheet填充数据

1
2
$objSheet->setCellValue("A1","name")->setCellValue("B1","age");
$objSheet->setCellValue("A2","小张")->setCellValue("B2","20");


1
2
3
4
5
6
$data = [
['name','age'],
['小明','21'],
['小丽','30']
];
$objSheet->fromArray($data);

(4)将文件保存到服务器

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

(5)通知浏览器下载

Excel5

1
2
3
4
5
6
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=emp.xls');
header('Cache-Control: max-age=0');//禁止缓存

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel5");
$objWriter->save("php://output");

Excel2007

1
2
3
4
5
6
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=emp.xlsx');
header('Cache-Control: max-age=0');//禁止缓存

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");
$objWriter->save("php://output");

##PHPExcel导入文件

1、导出步骤
(1)实例化excel读取对象
(2)加载excel文件
(3)读取excel文件

2、代码示例
(1)实例化excel读取对象

1
2
$fileType=PHPExcel_IOFactory::identify($filename);
$objReader=PHPExcel_IOFactory::createReader($fileType);

1
2
3
4
5
6
7
8
$objReader = new \PHPExcel_Reader_Excel2007();

if (!$objReader->canRead($file)) {
$objReader = new \PHPExcel_Reader_Excel5();
if (!$objReader->canRead($file)) {
echo '不能读取文件';die();
}
}

(2)加载excel文件

加载指定sheet

1
2
$objReader->setLoadSheetsOnly('Sheet1');//只加载指定的sheet
$objPHPExcel=$objReader->load('emp.xlsx');//加载文件

加载全部sheet,获取指定sheet数据

1
2
$phpExcel = $objReader->load($file);
$curSheet = $phpExcel->getSheet(0);

(3)读取excel文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$rowCount = $curSheet->getHighestRow(); //最大行数
$colCount = $curSheet->getHighestColumn(); //最大列数
$flag = ['0'=>'name','1'=>'age'];

for ($row = 2; $row <= $rowCount; $row++) {
$value = [];

for ($col = 'A'; $col <= $colCount; $col++) {
$tmp = $col . $row; //excel 坐标
$k = ord($col) - 65; //列对应关系

$value[$flag[$k]] = trim($curSheet->getCell($tmp)->getValue());
}
$data[] = $value;
}

print_r($data);