18
07月
2016
if (IS_POST) {
$download_config = C('DOWNLOAD_UPLOAD');
$file_driver = C('DOWNLOAD_UPLOAD_DRIVER');
$dirver_config = C("UPLOAD_{$file_driver}_CONFIG") ? C("UPLOAD_{$file_driver}_CONFIG") : array(
'mimes' => array(
'application/vnd.ms-excel',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'application/xml',
'text/plain',
'application/vnd.ms-office',//在不同的版本不同的平台,finfo扩展返回的文件mimetype会有差异。
),
'exts' => 'xls,xlsx,xml,cvs',
'rootPath' => './Uploads/',
'savePath' => 'manager/excel/',
'callback' => true
);
$download_config['mimes'] = $dirver_config['mimes'];
$download_config['exts'] = $dirver_config['exts'];
$download_config['rootPath'] = $dirver_config['rootPath'];
$download_config['savePath'] = $dirver_config['savePath'];
$File = D('File');
$info = $File->upload($_FILES, $download_config, $file_driver, $dirver_config);
if (!$info) {
$this->error(L('UPLOADFILEFORMATORSIZEERROR'));
}
$filename = realpath('.' . $info['file']['path']);//文件的绝对路径
if (!file_exists($filename)) {
$this->error(L('FILEDOESNOTEXIST'));
}
Vendor("PHPExcel.IOFactory");
$objExcel = \PHPExcel_IOFactory::load($filename);
//获取第一个sheet
$sheet = $objExcel->getSheet('0');
//$highestRow = $sheet->getHighestRow();
//最大列数获取不到,就不能使用这种方法来循环了
//$highestColumn = $sheet->getHighestColumn();
//贫困户的字段名
$regcogniser_params = array(
'4' => 'address',
'5' => 'name',
'7' => 'cardid',
'9' => 'relationship',
'20' => 'status',
'21' => 'property',
'22' => 'reason',
'23' => 'income',
'24' => 'mobile',
);
//贫困户家庭成员的字段名
$family_params = array(
'5' => 'name',
'7' => 'cardid',
'9' => 'relationship',
);
$villager_data = array();
$currentRow = 0;
foreach ($sheet->getRowIterator() as $row) {
if ($currentRow < 2) {
$currentRow++;
continue;
}
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$villager = array();
$celllist = array();
$currentColumn = 0;
$is_recogniser = false;//用来标识是识别户还是家庭成员
//遍历当前行的单元格
foreach ($cellIterator as $cell) {
$column = $cell->getCalculatedValue();
$column = trim($column);//有多余空白字符时会影响部分代码
if ($currentColumn == 9 && $column == '户主') {
$is_recogniser = true;
$villager_data_length = count($villager_data);
$currentRecog = $villager_data_length > 0 ? $villager_data_length : 0;
}
if (array_key_exists($currentColumn, $regcogniser_params)) {
if ($regcogniser_params[$currentColumn] == 'status') {
if (!empty($column)) {
switch ($column) {
case '未脱贫':
$villager[$regcogniser_params[$currentColumn]] = '1';
break;
case '已脱贫':
$villager[$regcogniser_params[$currentColumn]] = '2';
break;
}
}
} else {
$villager[$regcogniser_params[$currentColumn]] = $column;
}
}
$currentColumn++;
}
if ($is_recogniser) {
$village_name = mb_substr($villager['address'], 0,3,"utf-8");
$village_code = M('Village')->where(array('village_name'=>array('like',$village_name."%")))->getField('code');
$villager['village_code'] = $village_code;
$villager_data[] = $villager;
} else {
if (isset($villager_data[$currentRecog]) && !empty($villager)) {
unset($villager['address']);
unset($villager['property']);
unset($villager['reason']);
unset($villager['income']);
unset($villager['mobile']);
$villager_data[$currentRecog]['family'][] = $villager;
}
}
$currentRow++;
}
$recogniserM = M('表名');
$familyM = M('表名');
$recogniserM->startTrans();
try {
foreach ($villager_data as $villager) {
if (isset($villager['family'])) {
$family = $villager['family'];
unset($villager['family']);
} else {
$family = array();
}
$recogniser_id = $recogniserM->where(array('name' => $villager['name'], 'cardid' => $villager['cardid'], 'status' => array('neq', '0')))->getField('id');
if (!empty($recogniser_id)) {
$recogniserM->where(array('id' => $recogniser_id))->data($villager)->save();
} else {
$villager['addtime'] = $_SERVER['REQUEST_TIME'];
$recogniser_id = $recogniserM->data($villager)->add();
}
if (!empty($family) && $recogniser_id) {
foreach ($family as $value) {
$value['recogniser_id'] = $recogniser_id;
$family_id = $familyM->where(array('name' => $value['name'], 'cardid' => $value['cardid'], 'status' => array('neq', '0')))->getField('id');
if (!empty($family_id)) {
$familyM->where(array('id' => $family_id))->data($value)->save();
} else {
$value['addtime'] = $_SERVER['REQUEST_TIME'];
$familyM->data($value)->add();
}
}
}
}
$recogniserM->commit();
/* //缓存筛选分类
$typeResult = M('recogniser')
->group('help_type')
->select();
$typeData[] = array(
'type_id' => -1,
'type_name' => '全部'
);
if ($typeResult) {
$i = 1;
foreach ($typeResult as $v) {
$typeData[] = array(
'type_id' => $i,
'type_name' => $v['help_type']
);
$i++;
}
}
S('recogniser_help_type',null);
S('recogniser_help_type',$typeData);
*/
$this->success(L('SAVESUCCESS'), U('index'));
} catch (\Exception $e) {
$recogniserM->rollback();
$this->error(L('SAVEFAILED'));
}
} else {
$this->error(L('PARAMETERERROR'));
}
特殊说明,本文版权归 ning个人博客 所有带原创标签请勿转载,转载请注明出处.
本文标题: php导入excel到数据库