ThinkPHP和phpExcel实现异步多文件导入

ThinkPHP+phpExcel和jqueryFileUpload结合使用实现异步多文件导入
使用中发现一个小问题，上传的文件不知道跑到哪里去了，有知道的同学麻烦告知一下，⊙﹏⊙b汗
本程序基于ThinkPHP 3.1.3开发，支持Excel2003和Excel2007上传，有兴趣的朋友可以自行在高版本上进行研究
多文件使用方法：在选择文件的时候按住Ctrl键，用鼠标点击文件即可。
首先，分享Excel导入和输出的函数，在Common文件夹下的common.php文件中//excel输出
function exportExcel($expTitle,$expCellName,$expTableData){
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
$fileName = iconv('utf-8', 'gb2312', $expTitle).date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
$cellNum = count($expCellName);
$dataNum = count($expTableData);
import("Class.PHPExcel.PHPExcel",APP_PATH);
import("Class.PHPExcel.Writer.Excel5",APP_PATH);
import("Class.PHPExcel.Writer.Excel2007",APP_PATH);
$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.' 导出时间:'.date('Y-m-d H:i:s'));
for($i=0;$i $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
}
// Miscellaneous glyphs, UTF-8
for($i=0;$i for($j=0;$j $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
}
}

header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
//excel导入
function importExcel($file){
if(!file_exists($file)){
return array("error"=>0,'message'=>'file not found!');
}
//Vendor("PHPExcel.PHPExcel.IOFactory");
import("Class.PHPExcel.PHPExcel",APP_PATH);
import("Class.PHPExcel.Writer.Excel5",APP_PATH);
import("Class.PHPExcel.Writer.Excel2007",APP_PATH);

//$filePath = __ROOT__.'/Public/uploads/Excel/'.$savename;
//$filePath = $savePath.$savename;
$PHPExcel = new PHPExcel();
/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
$PHPReader = new PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead($file)){
$PHPReader = new PHPExcel_Reader_Excel5();
if(!$PHPReader->canRead($file)){
$this->error("Excel文件不存在！");
}
}

$PHPExcel = $PHPReader->load($file);
$allWorksheets = $PHPExcel->getAllSheets(); //读取所有sheets
$i = 0;

foreach($allWorksheets as $objWorksheet){
//print_r($objWorksheet);die;
$sheetname=$objWorksheet->getTitle();
//echo $objWorksheet->;die;
$allRow = $objWorksheet->getHighestRow();//how many rows
$highestColumn = $objWorksheet->getHighestColumn();//how many columns
$allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);
$array[$i]["Title"] = $sheetname;
$array[$i]["Cols"] = $allColumn;
$array[$i]["Rows"] = $allRow;
$arr = array();
$isMergeCell = array();
foreach ($objWorksheet->getMergeCells() as $cells) {//merge cells
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
$isMergeCell[$cellReference] = true;
}
}
for($currentRow = 1 ;$currentRow $row = array();
for($currentColumn=0;$currentColumn ;
$cell =$objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
$afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn+1);
$bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn-1);
$col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);
$address = $col.$currentRow;
$value = $objWorksheet->getCell($address)->getValue();
if(substr($value,0,1)=='='){
return array("error"=>0,'message'=>'can not use the formula!');
exit;
}
if($cell->getDataType()==PHPExcel_Cell_DataType::TYPE_NUMERIC){
$cellstyleformat=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
$formatcode=$cellstyleformat->getFormatCode();
if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {
$value=gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
}else{
$value=PHPExcel_Style_NumberFormat::toFormattedString($value,$formatcode);
}
}
if($isMergeCell[$col.$currentRow]&&$isMergeCell[$afCol.$currentRow]&&!empty($value)){
$temp = $value;
}elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$col.($currentRow-1)]&&empty($value)){
$value=$arr[$currentRow-1][$currentColumn];
}elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$bfCol.$currentRow]&&empty($value)){
$value=$temp;
}
$row[$currentColumn] = $value;
}
$arr[$currentRow] = $row;
}
$array[$i]["Content"] = $arr;
$i++;
}
spl_autoload_register(array('Think','autoload'));//must, resolve ThinkPHP and PHPExcel conflicts
unset($objWorksheet);
unset($PHPReader);
unset($PHPExcel);
unlink($file);
return array("error"=>1,"data"=>$array);
}使用ThinkPHP自带的上传功能进行处理//上传处理
public function up(){
import('ORG.Net.UploadFile');
$upload = new UploadFile();// 实例化上传类
$upload->maxSize = 3145728 ;// 设置附件上传大小
$upload->allowExts = array('xls', 'xlsx');// 设置附件上传类型
$savePath = $upload->savePath = './Upload/Excel/';// 设置附件上传目录
if(!$upload->upload()) {// 上传错误提示错误信息
$this->error($upload->getErrorMsg());
}else{// 上传成功 获取上传文件信息
$info = $upload->getUploadFileInfo();
}
$savename = $info[0]['savename'];
$data = importExcel($savePath.'/'.$savename);
//echo $savePath.'/'.$savename;die;
$this->ajaxReturn($data);
}模板的js代码$(function () {
'use strict';
// Change this to the location of your server-side upload handler:
var url = window.location.hostname === 'blueimp.github.io' ? '//jquery-file-upload.appspot.com/' : "{:U('Index/up')}";
$('#fileupload').fileupload({
url: url,
dataType: 'json',
done: function (e, data) {
$.each(data.result.data, function (index, file) {
$.each(file.Content,function(i,v){
var str = "";
$.each(v,function(a,b){
//b = b?b:'';
if(b==null){
b="";
}
str += ""+b+"";
})
$("").html(str).appendTo('#files');
});
//$('

').text(file.Cols).appendTo('#files');
});
},
progressall: function (e, data) {
var progress = parseInt(data.loaded / data.total * 100, 10);
$('#progress .progress-bar').css(
'width',
progress + '%'
);
}
}).prop('disabled', !$.support.fileInput)
.parent().addClass($.support.fileInput ? undefined : 'disabled');
});最后附上一张上传后的效果图


源代码已经分享到qq交流群，有需要的朋友可加群下载
QQ交流群：282882201

有的朋友会问：下面的图片是干嘛的，嘿嘿，我也不知道干嘛的，你看着办吧嘿嘿。如果觉得本文没用，忽略即可。

AD：真正免费，域名+虚机+企业邮箱=0元