-- Yii
前台：
<form action="" method="post" enctype ="multipart/form-data" >
<div class="row" style="text-align: center;">
<?php echo CHtml::label('数据格式请使用excel2003统一模版编辑, 上传后将逐步显示数据修改信息', 'false'); ?>
</div>
<div class="row">
<?php echo CHtml::label('参赛数据上传Excel模版:', 'false'); ?>
<?php echo CHtml::link(CHtml::button('下载'), url('sysadmin/goods/getmodel')); ?>
</div>
<div class="row">
<?php echo CHtml::label('上传参赛数据Excel表格:', 'false'); ?>
<?php echo CHtml::fileField('myfile'); ?>
</div>
<div class="row" style="text-align: center;">
<?php echo CHtml::label('请保持上传文件小于2MB!', 'false', array('style'=>'color:red;text-align:center;')); ?>
</div>
<div class="row" style="text-align: center">
<?php echo CHtml::submitButton('确定', array('style'=>'width:70px;border:1px solid #2D5082;background-color:#4D6080;color:#fff'));?>
</div>
</form>
后台：
/**
* 上传参赛数据表格
*/
public function actionExcelin(){
if($_POST){
if($_FILES){
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';//引入读取Excel的类文件
/*$PHPExcel = new PHPExcel();*/
$file = CUploadedFile::getInstanceByName('myfile');//获取Excel文件
$fileName = 'excel/'.time().'.'.$file->getExtensionName();//Excel文件重命名
$file->saveAs($fileName);//保存Excel文件到服务器
/*$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read($fileName);*/
$objPHPExcel = PHPExcel_IOFactory::load($fileName);//加载Excel文件
//只加载指定sheet
$fileType = PHPExcel_IOFactory::identify($fileName);//自动获取文件的类型提供给phpexcel使用
$objReader = PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象
$sheetName = $objPHPExcel->getSheetNames();
$sheetName = $sheetName;
$objReader->setLoadSheetsOnly($sheetName);
//只加载指定sheet
/*$sheetCount = $objPHPExcel->getSheetCount();//获取Excel文件有多少个sheet
for($i=0;$i<$sheetCount;$i++){
$data = $objPHPExcel->getSheet($i)->toArray();//读取每个sheet中的数据 放入数组
echo "<pre>";
print_r($data);
}*/
foreach ($objPHPExcel->getWorksheetIterator() as $sheet){//循环取sheet
foreach ($sheet->getRowIterator() as $row){//逐行处理
if($row->getRowIndex() < 2){
continue;
}
$dataArr = array();
foreach ($row->getCellIterator() as $cell){//逐列处理
$data = $cell->getValue();//获取单元格数据
$dataArr[] = $data;
}
$Gender = $dataArr[13]=="男"?"1":"0";
$res = Yii::app()->db->createCommand()->insert('yqm_schoolsign', array(
"Content" => $dataArr[0],//参赛证卡号
"ActId" => $dataArr[1],//档案号
"RealName" => $dataArr[2],//姓名
"Group" => $dataArr[3],//组别
"Category" => $dataArr[4],//类别
"Title" => $dataArr[5],//参赛作品名称(内容)
"MatchTime" => $dataArr[6],//参赛时间
"LevelId" => $dataArr[7],//***
"MatchAddr" => $dataArr[8],//参赛地点
"FdTeacherName" => $dataArr[9],//辅导教师
"FdSchoolName" => $dataArr[10],//辅导学校
"FdTeacherTel" => $dataArr[11],//老师电话
"MatherTel" => $dataArr[12],//联系电话
"Gender" => $Gender,//性别
"HeadPic_Small" => $dataArr[14],//头像或照片
"IdCard" => $dataArr[15],//***号
"MatchLevel" => $dataArr[16],//赛季
"MatchRank" => $dataArr[17],//表演曲目级别
));
/* echo "<pre>";
var_dump($dataArr);*/
}
}
if($res){
echo "<h3>导入成功!</h3>";
}
Yii::app()->end();
}else{
SysShowMsg('请上传正确的excel文件', '返回批量修改', url('sysadmin/goods/batchedit'));
}
}
$this->render('excel');
}
