php ci 上传导入xls,ci框架读取上传的excel文件数据

此功能实现使用到PHPExcel类库,PHPExcel是一个PHP类库，用来帮助我们简单、高效实现从Excel读取Excel的数据和导出数据到Excel。也是我们日常开发中，经常会遇到的使用场景。

(一) PHPExcel下载

(二) PHPExcel引入到CI框架

1) 解压压缩包里的Classes文件夹中的内容到application\libraries\目录下，目录结构如下：

-- application\libraries\PHPExcel.php

-- application\libraries\PHPExcel (文件夹)

2)修改application\libraries\PHPExcel\IOFactory.php 文件

-- 将其类名从PHPExcel_IOFactory改为IOFactory，遵从CI类命名规则。

-- 将其构造函数改为public

(三) 具体代码实现

1) excel文件的接收及其验证

说明:detectUploadFileMIME()方法是判断上传文件是否为excel文件,此方法更为安全

if($_FILES['file']['error']){

switch($_FILES['file']['error']){

case 1:

$this->finish('文件大小超出了服务器的空间大小',1);

die;

break;

case 2:

$this->finish('要上传的文件大小超出浏览器限制',1);

die;

break;

case 3:

$this->finish('文件仅部分被上传',1);

die;

break;

case 4:

$this->finish('没有找到要上传的文件',1);

die;

break;

case 5:

$this->finish('服务器临时问价丢失',1);

die;

break;

case 6:

$this->finish('文件写入到临时文件夹出错',1);

die;

break;

default:

$this->finish('文件上传未知错误',1);

}

die;

}

//接收的excel信息

$info = pathinfo($_FILES['file']['name']);

$file = $_FILES['file'];

//验证excel文件类型

$res = $this->detectUploadFileMIME($file);

if($res){

$tmp_name = $info['filename'];

$new_name = $tmp_name . '-' . time() .'.'. $info['extension'];

$root_path = dirname(BASEPATH);

$new_dir = $root_path . '/data/excel/';

if (!file_exists($new_dir)) {

mkdir($new_dir, 0777, true);

}

$new_path = $new_dir . $new_name;

$new_path = str_replace('\\', DIRECTORY_SEPARATOR, $new_path);

$new_path = str_replace('/', DIRECTORY_SEPARATOR, $new_path);

$res = move_uploaded_file($_FILES['file']['tmp_name'], $new_path);

if (!$res) {

$this->finish('上传失败', 2);

die;

}

2) 读取excel文件数据

if (!file_exists($new_path)) {

exit("文件".$new_path."不存在");

}

/*加载PHPExcel*/

$this->load->library('PHPExcel.php');

$this->load->library('PHPExcel/IOFactory.php');

// $this->load->library('PHPExcel/Reader/Excel5.php');

$objPHPExcel = new PHPExcel();

$objProps = $objPHPExcel->getProperties();

//设置excel格式

if($info['extension'] =='xlsx' ){

$objReader = IOFactory::createReader('excel2007');

}else{

$objReader = IOFactory::createReader('Excel5');

}

//载入excel文件

$objPHPExcel = $objReader->load($new_path);

//读取第一张表

$sheet = $objPHPExcel->getSheet(0);

//获取总行数

$highestRow = $sheet->getHighestRow();

//获取总列数

$highestColumn = $sheet->getHighestColumn();

$excel_data = array();

for ($col='A';$col<=$highestColumn;$col++) {

for ($row=2;$row<=$highestRow;$row++) {

$excel_data[$row-2][]=$sheet->getCell($col.$row)->getValue();

}

}

print_r($excel_data);die;

我上传的excel文件内容为

打印出来的数据为

就这样完成la

补充detectUploadFileMIME()方法

/**

* 检测是否为excel文件程序

*

*

*/

public function detectUploadFileMIME($file) {

// 1.through the file extension judgement 03 or 07

$flag = 0;

$file_array = explode ( ".", $file ["name"] );

$file_extension = strtolower ( array_pop ( $file_array ) );

// 2.through the binary content to detect the file

switch ($file_extension) {

case "xls" :

// 2003 excel

$fh = fopen ( $file ["tmp_name"], "rb" );

$bin = fread ( $fh, 8 );

fclose ( $fh );

$strinfo = @unpack ( "C8chars", $bin );

$typecode = "";

foreach ( $strinfo as $num ) {

$typecode .= dechex ( $num );

}

if ($typecode == "d0cf11e0a1b11ae1") {

$flag = 1;

}

break;

case "xlsx" :

// 2007 excel

$fh = fopen ( $file ["tmp_name"], "rb" );

$bin = fread ( $fh, 4 );

fclose ( $fh );

$strinfo = @unpack ( "C4chars", $bin );

$typecode = "";

foreach ( $strinfo as $num ) {

$typecode .= dechex ( $num );

}

// echo $typecode;

if ($typecode == "504b34") {

$flag = 1;

}

break;

}

// 3.return the flag

return $flag;

}

ok,不谢(￣_,￣ )

更新一下,最近听说phpexcel已经停止维护了,所以可以用phpspreadsheet了