由上篇而来,直接上代码,聊以小记.
//导入函数
/*
* find(999) 999为数据库的主键id
* find(999) 用于获得表的字段
* $_POST['modelid']
*/
//$expTitle=>表的主题,$expCellName=>表头名(列名),$expTableData=>数据(以下采用数字索引数组)
public function importExcel()
{
/**
* Excel导入函数
*
*/
if (!empty($_FILES)) {
$config = array(
'maxSize' => 3145728,
'rootPath' => './Uploads/',
'savePath' => 'Excel/',
'saveName' => array('uniqid',''),
'exts' => array('xlsx','csv','xls'),
'autoSub' => true,
'subName' => array('date','Ymd'),
);
$upload = new \Think\Upload($config);
$info = $upload->upload();
if(!$info) {
$this->error($upload->getError());
}else{
foreach($info as $file) {
$file_name = $config['rootPath'].$file['savepath'].$file['savename'];
}
}
vendor("PHPExcel.PHPExcel");
$extension = substr(strrchr($file_name,"."),1);
if( $extension =='csv' )
{
$objReader = \PHPExcel_IOFactory::createReader('CSV');
}
else
{
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
}
$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 = $objReader->load($file_name,$encode='utf-8');
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$model = M('CompanyTaxation');
$new = array();
//获得表的字段
$list = $model->find(999);
$i=0;
foreach($list as $key => $val){//数字化数组的key 便于下面读取数据 [j][i];
$new[$i] = $key;
$i++;
unset($list[$key]);
}
//var_dump($new);die;
$ecxel = $objPHPExcel->getActiveSheet();
//第3行B列起 1行表标题 2行表头
for($i=3;$i<=$highestRow;$i++)
{
for($j=1;$j<sizeof($new);$j++){//去除第一列的NO
if($new[$j] == "tax_month"){
$data[$new[$j]]= totime($ecxel->getCell($cellName[$j].$i)->getValue());//数据
}elseif($new[$j] == "bid"){
//echo $ecxel->getCell($cellName[$j].$i)->getValue();die;
$data[$new[$j]]= get_id_by_name($ecxel->getCell($cellName[$j].$i)->getValue(),"Building");//数据
}elseif($new[$j] == "cid"){
$data[$new[$j]]= get_id_by_name($ecxel->getCell($cellName[$j].$i)->getValue(),"Company");//数据
}else{
$data[$new[$j]]= $ecxel->getCell($cellName[$j].$i)->getValue();//普通数据
}
}
$data['uid'] = UID;
$data['status'] = 1;
//echo "<pre>"; var_dump($data);die;
$model->add($data);
}
$this->success('导入成功!',U('lists?model='.$_POST['modelid']));
}else
{
$this->error("请选择上传的文件");
}
}
下面是html代码:
<!-- 导入界面 -->
<div id="import" style="position:relative;display: none; z-index:5;background:#ccc">
<div style=" width: 400px; height: 600px;vertical-align: middle;
text-align: center; margin-left: 240px;">
<h3>楼宇{$model['title']}导入</h3>
<form action="__URL__/importExcel" method="post" enctype="multipart/form-data">
<input type="file" name="import" style="width: 160px;margin: 150px" />
<input type="hidden" name="modelid" value="{$model['id']}"/>
<br />
<input type="submit" value="导入" style="width: 120px;" />
<input type="button" id='exit' value="退出" style="width: 120px;" />
</form>
</div>
</div>
<!-- 导入界面END -->
可以去这里下载PHPExcel http://download.csdn.net/detail/sct_t/9777365