ThinkPHP和phpExcel实现异步多文件导入
- WBOY原創
- 2016-06-07 11:34:491350瀏覽
ThinkPHP+phpExcel和jqueryFileUpload结合使用实现异步多文件导入
使用中发现一个小问题，上传的文件不知道跑到哪里去了，有知道的同学麻烦告知一下，⊙﹏⊙b汗
本程序基于ThinkPHP 3.1.3开发，支持Excel2003和Excel2007上传，有兴趣的朋友可以自行在高版本上进行研究
多文件使用方法：在选择文件的时候按住Ctrl键，用鼠标点击文件即可。
首先，分享Excel导入和输出的函数，在Common文件夹下的common.php文件中//excel输出<br>
function exportExcel($expTitle,$expCellName,$expTableData){<br>
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称<br>
$fileName = iconv('utf-8', 'gb2312', $expTitle).date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定<br>
$cellNum = count($expCellName);<br>
$dataNum = count($expTableData);<br>
import("Class.PHPExcel.PHPExcel",APP_PATH);<br>
import("Class.PHPExcel.Writer.Excel5",APP_PATH);<br>
import("Class.PHPExcel.Writer.Excel2007",APP_PATH);<br>
$objPHPExcel = new PHPExcel();<br>
$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');<br>
<br>
$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格<br>
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' 导出时间:'.date('Y-m-d H:i:s'));<br>
for($i=0;$i
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);<br>
}<br>
// Miscellaneous glyphs, UTF-8<br>
for($i=0;$i
for($j=0;$j
$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);<br>
}<br>
}<br>
<br>
header('pragma:public');<br>
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');<br>
header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印<br>
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');<br>
$objWriter->save('php://output');<br>
exit;<br>
}<br>
//excel导入<br>
function importExcel($file){<br>
if(!file_exists($file)){<br>
return array("error"=>0,'message'=>'file not found!');<br>
}<br>
//Vendor("PHPExcel.PHPExcel.IOFactory");<br>
import("Class.PHPExcel.PHPExcel",APP_PATH);<br>
import("Class.PHPExcel.Writer.Excel5",APP_PATH);<br>
import("Class.PHPExcel.Writer.Excel2007",APP_PATH);<br>
<br>
//$filePath = __ROOT__.'/Public/uploads/Excel/'.$savename;<br>
//$filePath = $savePath.$savename;<br>
$PHPExcel = new PHPExcel();<br>
/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/<br>
$PHPReader = new PHPExcel_Reader_Excel2007();<br>
if(!$PHPReader->canRead($file)){<br>
$PHPReader = new PHPExcel_Reader_Excel5();<br>
if(!$PHPReader->canRead($file)){<br>
$this->error("Excel文件不存在！");<br>
}<br>
}<br>
<br>
$PHPExcel = $PHPReader->load($file);<br>
$allWorksheets = $PHPExcel->getAllSheets(); //读取所有sheets<br>
$i = 0;<br>
<br>
foreach($allWorksheets as $objWorksheet){<br>
//print_r($objWorksheet);die;<br>
$sheetname=$objWorksheet->getTitle();<br>
//echo $objWorksheet->;die;<br>
$allRow = $objWorksheet->getHighestRow();//how many rows<br>
$highestColumn = $objWorksheet->getHighestColumn();//how many columns<br>
$allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);<br>
$array[$i]["Title"] = $sheetname;<br>
$array[$i]["Cols"] = $allColumn;<br>
$array[$i]["Rows"] = $allRow;<br>
$arr = array();<br>
$isMergeCell = array();<br>
foreach ($objWorksheet->getMergeCells() as $cells) {//merge cells<br>
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {<br>
$isMergeCell[$cellReference] = true;<br>
}<br>
}<br>
for($currentRow = 1 ;$currentRow
$row = array();<br>
for($currentColumn=0;$currentColumn
;<br>
$cell =$objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);<br>
$afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn+1);<br>
$bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn-1);<br>
$col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);<br>
$address = $col.$currentRow;<br>
$value = $objWorksheet->getCell($address)->getValue();<br>
if(substr($value,0,1)=='='){<br>
return array("error"=>0,'message'=>'can not use the formula!');<br>
exit;<br>
}<br>
if($cell->getDataType()==PHPExcel_Cell_DataType::TYPE_NUMERIC){<br>
$cellstyleformat=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();<br>
$formatcode=$cellstyleformat->getFormatCode();<br>
if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {<br>
$value=gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));<br>
}else{<br>
$value=PHPExcel_Style_NumberFormat::toFormattedString($value,$formatcode);<br>
}<br>
}<br>
if($isMergeCell[$col.$currentRow]&&$isMergeCell[$afCol.$currentRow]&&!empty($value)){<br>
$temp = $value;<br>
}elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$col.($currentRow-1)]&&empty($value)){<br>
$value=$arr[$currentRow-1][$currentColumn];<br>
}elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$bfCol.$currentRow]&&empty($value)){<br>
$value=$temp;<br>
}<br>
$row[$currentColumn] = $value;<br>
}<br>
$arr[$currentRow] = $row;<br>
}<br>
$array[$i]["Content"] = $arr;<br>
$i++;<br>
}<br>
spl_autoload_register(array('Think','autoload'));//must, resolve ThinkPHP and PHPExcel conflicts<br>
unset($objWorksheet);<br>
unset($PHPReader);<br>
unset($PHPExcel);<br>
unlink($file);<br>
return array("error"=>1,"data"=>$array);<br>
}使用ThinkPHP自带的上传功能进行处理//上传处理<br>
public function up(){<br>
import('ORG.Net.UploadFile');<br>
$upload = new UploadFile();// 实例化上传类<br>
$upload->maxSize = 3145728 ;// 设置附件上传大小<br>
$upload->allowExts = array('xls', 'xlsx');// 设置附件上传类型<br>
$savePath = $upload->savePath = './Upload/Excel/';// 设置附件上传目录<br>
if(!$upload->upload()) {// 上传错误提示错误信息<br>
$this->error($upload->getErrorMsg());<br>
}else{// 上传成功 获取上传文件信息<br>
$info = $upload->getUploadFileInfo();<br>
}<br>
$savename = $info[0]['savename'];<br>
$data = importExcel($savePath.'/'.$savename);<br>
//echo $savePath.'/'.$savename;die;<br>
$this->ajaxReturn($data);<br>
}模板的js代码$(function () {<br>
'use strict';<br>
// Change this to the location of your server-side upload handler:<br>
var url = window.location.hostname === 'blueimp.github.io' ? '//jquery-file-upload.appspot.com/' : "{:U('Index/up')}";<br>
$('#fileupload').fileupload({<br>
url: url,<br>
dataType: 'json',<br>
done: function (e, data) {<br>
$.each(data.result.data, function (index, file) {<br>
$.each(file.Content,function(i,v){<br>
var str = "";<br>
$.each(v,function(a,b){<br>
//b = b?b:'';<br>
if(b==null){<br>
b="";<br>
}<br>
str += "<td>"+b+"</td>";<br>
})<br>
$("<tr></tr>").html(str).appendTo('#files'); <br>
});<br>
//$('<p></p>').text(file.Cols).appendTo('#files');<br>
});<br>
},<br>
progressall: function (e, data) {<br>
var progress = parseInt(data.loaded / data.total * 100, 10);<br>
$('#progress .progress-bar').css(<br>
'width',<br>
progress + '%'<br>
);<br>
}<br>
}).prop('disabled', !$.support.fileInput)<br>
.parent().addClass($.support.fileInput ? undefined : 'disabled');<br>
});最后附上一张上传后的效果图
源代码已经分享到qq交流群，有需要的朋友可加群下载
QQ交流群：282882201
有的朋友会问：下面的图片是干嘛的，嘿嘿，我也不知道干嘛的，你看着办吧嘿嘿。如果觉得本文没用，忽略即可。
AD：真正免费，域名+虚机+企业邮箱=0元