TP3.2中用phpexcel导入导出

好久不用都忘记了，找到以前的代码，整理一下：

存入ThinkPHP/Library/Vendor

命名为PHPExcel

导入：

后台：

public function addActivitydo(){
if (!empty($_FILES)){
$upload = new \Think\Upload(); // 实例化上传类
$upload->maxSize = 10485760 ; // 设置附件上传大小
$upload->exts = array('xls','xlsx'); // 设置附件上传类型
$upload->rootPath = './Upload/Excel';//上传根目录
$upload->autoSub = false; // 将自动生成以photo后面加时间的形式文件夹，关闭
// 上传文件
$info = $upload->upload(); 
$exts = $info['file']['ext'];
// 获取文件后缀
$filename = $upload->rootPath.$info['file']['savename']; // 生成文件路径名
if(!$info) { // 上传错误提示错误信息
$this->error($upload->getError()); 
}else{
vendor("PHPExcel.Classes.PHPExcel"); // 导入PHPExcel类库
$PHPExcel = new \PHPExcel(); // 创建PHPExcel对象，注意，不能少了\
if ($exts == 'xls') { // 如果excel文件后缀名为.xls，导入这个类
vendor("PHPExcel.Classes.PHPExcel.Reader.Excel5");
$PHPReader = new \PHPExcel_Reader_Excel5();
} else 

if ($exts == 'xlsx') {
vendor("PHPExcel.Classes.PHPExcel.Reader.Excel2007");
$PHPReader = new \PHPExcel_Reader_Excel2007();

}
$PHPExcel=$PHPReader->load($filename);
$currentSheet = $PHPExcel->getSheet(0); // 获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$allColumn = $currentSheet->getHighestColumn(); // 获取总列数
$allRow = $currentSheet->getHighestRow(); // 获取总行数
$data=array();
for($j=1;$j<=$allRow;$j++){
//从A列读取数据
for($k='A';$k<=$allColumn;$k++){
// 读取单元格
$data[$j][]=$PHPExcel->getActiveSheet()->getCell("$k$j")->getValue();
}
} 
for ($i = 2; $i <= $allRow; $i++) {
$data_p['表中字段'] =$PHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
$data_p['表中字段'] =$PHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
$data_p['表中字段'] =$PHPExcel->getActiveSheet()->getCell("C" .$i)->getValue();
$data_p['表中字段'] =$PHPExcel->getActiveSheet()->getCell("D" .$i)->getValue();
$data_p['表中字段'] =$PHPExcel->getActiveSheet()->getCell("E" .$i)->getValue();
$data_p['表中字段'] =$PHPExcel->getActiveSheet()->getCell("F" .$i)->getValue();
$data_p['表中时间'] = date("Y-m-d",time());
$ex=M('Test')->add($data_p);//表名
}
if($ex){ 
$this->success("导入成功");
}else{
$this->error("导入失败，原因可能是excel表中格式错误","5");// 提示错误
}
}
}else {
$this->display(); 
} 

}

 

前台：

<form action="{:U(addActivitydo)}" method="post" enctype="multipart/form-data" style="float:right;">
<input type="file" name="file" class="layui-btn">
<input type="submit" value="导入" class="layui-btn">
</form>

导出

CommonController
public function export($expTitle,$expCellName,$expTableData) {
vendor('PHPExcel.Classes.PHPExcel');
$cellNum = count($expCellName);
$dataNum = count($expTableData);
// 设置
$objPHPExcel = new \PHPExcel();
$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s')); 
for($i=0;$i<$cellNum;$i++){
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); 
} 
// Miscellaneous glyphs, UTF-8 
for($i=0;$i<$dataNum;$i++){
for($j=0;$j<$cellNum;$j++){
$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
} 
} 
$objPHPExcel
->getProperties()
->setCreator("可改")
->setLastModifiedBy("可改")
->setTitle("可改")
->setSubject("可改")
->setDescription("可改")
->setKeywords("excel")
->setCategory("result file");
$objPHPExcel->getActiveSheet()->setTitle('可改');
$objPHPExcel->setActiveSheetIndex(0);
$name='renyimingzi.xlsx';//任意名字
ob_end_clean();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename='.$name);
header('Cache-Control: max-age=0');
$ExcelWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$ExcelWriter->save('php://output');
exit; 
}
调用时，继承CommonController
if(I('get.export') == 'on'){
$xlsCell = array(
array('字段名','字段注释'),
array('字段名','字段注释'),
array('字段名','字段注释'),
);
$xlsData = M('Test')->select();
$xlsName = "可改";
$this->export($xlsName,$xlsCell,$xlsData);
}