php excel 导不出来了,TP框架下phpexcel 导入导出（解决获取不到AA列之后内容）

本人使用tp框架，单独写了导入导出公共文件

导入上传文件

html

$('#upExcel').click(function(){

$('#searchform').submit();

});

提交PHP页面

public $excel;

public function __construct()

{

parent::__construct();

$this->excel = new ExcelController();

}

$upload = new Upload();

$upload->maxSize = 3145728 ;// 设置附件上传大小

$upload->exts = array('xls', 'xlsx');// 设置附件上传类

$upload->savePath = '/hxrc/Public/Uploads/'; // 设置附件上传目录

// 上传文件

$info = $upload->uploadOne($_FILES['file']);

$filename = './Uploads/'.$info['savepath'].$info['savename'];

$exts = $info['ext'];

if(!$info) {// 上传错误提示错误信息

$this->error($upload->getError());

}else{// 上传成功

$this->excel->import_excel($filename, $exts);

}

导出下载文件

html

$('#downExcel').click(function(){

$('#searchform').submit();

});

PHP

//$data= M('User')->findAll(); //查出数据

$data = [['uid'=>1,'email'=>233,'password'=>123]];

$tim = date('Ymd',time());

$name='数据'.$tim; //生成的Excel文件文件名

$res=$this->excel->push($data,$name);

公共文件Excelcontroller

namespace Home\Controller;

use PHPExcel_IOFactory;

use Think\Controller;

class ExcelController extends Controller{

public function __construct()

{

/*将phpexcel文件放在org/util下*/

import("Org.Util.PHPExcel");

import("Org.Util.PHPExcel.Writer.Excel5");

import("Org.Util.PHPExcel.IOFactory");

}

/**

* 导入excel文件

* @param string $file excel文件路径

* @return array excel文件内容数组

*/

public function import_excel($filename, $exts='xls')

{

$PHPExcel=new \PHPExcel();

//如果excel文件后缀名为.xls，导入这个类

if($exts == 'xls'){

//vendor('PHPExcel.Reader.Excel5');

import("Org.Util.PHPExcel.Reader.Excel5");

$PHPReader=new \PHPExcel_Reader_Excel5();

}else if($exts == 'xlsx'){

//vendor('PHPExcel.Reader.Excel2007');

import("Org.Util.PHPExcel.Reader.Excel2007");

$PHPReader=new \PHPExcel_Reader_Excel2007();

}

//载入文件

$PHPExcel=$PHPReader->load($filename);

//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推

$currentSheet=$PHPExcel->getSheet(0);

//获取总列数

$allColumn=$currentSheet->getHighestColumn();

//获取总行数

$allRow=$currentSheet->getHighestRow();

++$allColumn;

//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始

$data = array();

for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){ //循环读取每个单元格的内容。注意行从1开始，列从A开始

for($colIndex='A';$colIndex!=$allColumn;$colIndex++){

$addr = $colIndex.$rowIndex;

$cell = $currentSheet->getCell($addr)->getValue();

if($cell instanceof PHPExcel_RichText){ //富文本转换字符串

$cell = $cell->__toString();

}

$data[$rowIndex][$colIndex] = $cell;

}

}

if(is_file($filename)){

unlink($filename);

}

//var_dump($data);

$this->save_import($data);

}

//保存数据到数据库

public function save_import($data)

{ //$data 是数组

// return $result_msg;

}

/* 导出excel函数*/

public function push($headArr,$data,$fileName='Excel', $width=20){

if (empty($headArr) && !is_array($headArr) && empty($data) && !is_array($data)) {

return false;

}

$date = date("YmdHis",time());

$fileName .= "_{$date}.xls";

$objPHPExcel = new \PHPExcel();

//设置表头

$tem_key = "A";

foreach($headArr as $v){

if (strlen($tem_key) > 1) {

$arr_key = str_split($tem_key);

$colum = '';

foreach ($arr_key as $ke=>$va) {

$colum .= chr(ord($va));

}

} else {

$key = ord($tem_key);

$colum = chr($key);

}

$objPHPExcel->getActiveSheet()->getColumnDimension($colum)->setWidth($width); // 列宽

$objPHPExcel->getActiveSheet()->getStyle($colum)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 垂直居中

$objPHPExcel->getActiveSheet()->getStyle($colum.'1')->getFont()->setBold(true); // 字体加粗

$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);

$tem_key++;

}

$objActSheet = $objPHPExcel->getActiveSheet();

$border_end = 'A1'; // 边框结束位置初始化

// 写入内容

$column = 2;

foreach($data as $key => $rows){ //获取一行数据

$tem_span = "A";

foreach($rows as $keyName=>$value){// 写入一行数据

if (strlen($tem_span) > 1) {

$arr_span = str_split($tem_span);

$j = '';

foreach ($arr_span as $ke=>$va) {

$j .= chr(ord($va));

}

} else {

$span = ord($tem_span);

$j = chr($span);

}

$objActSheet->setCellValue($j.$column, $value);

$border_end = $j.$column;

$tem_span++;

}

$column++;

}

$objActSheet->getStyle("A1:".$border_end)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); // 设置边框

$fileName = iconv("utf-8", "gb2312", $fileName);

//重命名表

//$objPHPExcel->getActiveSheet()->setTitle('test');

//设置活动单指数到第一个表

$objPHPExcel->setActiveSheetIndex(0);

ob_end_clean();//清除缓冲区,避免乱码

header('Content-Type: application/vnd.ms-excel');

header("Content-Disposition: attachment;filename=\"$fileName\"");

header('Cache-Control: max-age=0');

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output'); //文件通过浏览器下载

exit;

}

}