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

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

导入上传文件

html

<input type="file" name="file"/>
<input type="button" id="upExcel" value="导入Excel" />
$('#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

<input type="button" id="downExcel" value="下载用户信息"/>
$('#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

<?php
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;
}
}

土豆