PHPExcel导入excell写入数据库；
1：下载PHPExcel了扩展http://phpexcel.codeplex.com/
2：写一个导入按钮
<input type="button" class="btn btn-xs btn-success" data-toggle="modal" data-target="#myModal"
id="import" value="导入excell" />
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<form class="form-horizontal ajaxForm2" id='formadd' method="post" action="{:U('imports')}">
<div class="modal-dialog" >
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"
aria-hidden="true">×
</button>
<h4 class="modal-title" id="myModalLabel">
导入Excell
</h4>
</div>
<div class="modal-body">
<div class="row">
<div class="col-xs-12">
<div class="form-group">
<!-- <label class="col-sm-3 control-label no-padding-right" for="form-field-1"> 所属商户： </label> -->
<div class="col-sm-9">
<input type="file" name="excelData" datatype="*4-50" />
<span class="Validform_checktip"></span>
</div>
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="submit" id='formbtn' class="btn btn-primary">
提交保存
</button>
<button type="button" class="btn btn-default" data-dismiss="modal">
关闭
</button>
</div>
</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</form>
</div><!-- /.modal -->
3：PHP后台处理
/**
* Created by PhpStorm.
* function: data_import
* Description:导入数据
* User: Xiaoxie
* @param $filename
* @param string $exts
* @param $or
*
*/
public function data_import($filename, $exts = 'xls',$or)
{
//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
vendor('PHPExcel.PHPExcel');
//创建PHPExcel对象，注意，不能少了\
$PHPExcel = new \PHPExcel();
//如果excel文件后缀名为.xls，导入这个类
if ($exts == 'xls') {
Vendor('PHPExcel.PHPExcel.Reader.Excel5');
$PHPReader = new \PHPExcel_Reader_Excel5();
} else if ($exts == 'xlsx') {
Vendor('PHPExcel.PHPExcel.Reader.Excel2007');
$PHPReader = new \PHPExcel_Reader_Excel2007();
}
//载入文件
$PHPExcel = $PHPReader->load($filename);
//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$currentSheet = $PHPExcel->getSheet(0);
//获取总列数
$allColumn = $currentSheet->getHighestColumn();
//获取总行数
$allRow = $currentSheet->getHighestRow();
//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
//从哪列开始，A表示第一列
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
//数据坐标
$address = $currentColumn . $currentRow;
//读取到的数据，保存到数组$data中
$cell = $currentSheet->getCell($address)->getValue();
if ($cell instanceof PHPExcel_RichText) {
$cell = $cell->__toString();
}
$data[$currentRow - 1][$currentColumn] = $cell;
// print_r($cell);
}
}
// 写入数据库操作
$this->insert_data($data);
}
/**
* Created by PhpStorm.
* function: insert_data
* Description:写入数据库操作
* User: Xiaoxie
* @param $data
*
*/
public function insert_data($data)
{
$created_time = date('Y-m-d H:i:s');
$apinfo = A('apinfo');
foreach ($data as $k => $v) {
if ($k != 0) {
//shop信息
$info['shop_name'] = $v['C'];
$info['address']=$v['D'];
$info['contact_name'] = $v['I'];
$info['contact_phone'] = $v['J'];
$info['lng'] = $v['G'];
$info['lat'] = $v['H'];
$info['shop_code'] = time().$k;
$type_explain = $v['K'];
$where['type_explain'] = array('like',"%$type_explain%");
$info['type_code'] = 5;
$info['wa_area'] = $v['L'];
$id = M('shop')->add($info);//shop_id
$info['insert_time'] = date('Y-m-d H:i:s');
//开始添加device信息
$infos['dev_no'] = $info['shop_code'];
$infos['dev_code'] = $v['B'];
$infos['dev_mac'] = strtolower(str_replace('-', '', $v['B'])) ;
$infos['device_name'] = $v['C'];
$infos['device_ip'] = $v['F'];
$infos['location_id'] = '3397';
$infos['area_code'] = $v['L'];
$infos['address'] = $v['D'];
$infos['device_address'] = $v['D'];
$infos['agent_id'] = 1;
$infos['customer_id'] = 1;
$infos['shop_id'] = $id;
$infos['lng'] = $v['G'];
$infos['lat'] = $v['H'];
$infos['pss'] = $v['M'];
$infos['site_code'] = $apinfo->setWanganCode($v['L'],3,$info['type_code'],$id);
$result = M('device')->add($infos);
$apinfo->insertdevice($info,$infos,$id);
$apinfo->apinfo_defaultoption($infos['dev_mac']);
}
}
$this->success('设备添加成功',U('apinfo/apinfo_list'),1);
}
/**
* Created by PhpStorm.
* function: imports
* Description:导入excell
* User: Xiaoxie
*
*/
public function imports()
{
header("Content-Type:text/html;charset = utf-8");
$upload = new \Think\Upload();// 实例化上传类
$upload->maxSize = 3145728;// 设置附件上传大小
$upload->exts = array('xls', 'xlsx');// 设置附件上传类
$upload->rootPath = './public/Uploads/'; // 设置附件上传目录
// 上传文件
$info = $upload->uploadOne($_FILES['excelData']);
$filename = $upload->rootPath . $info['savepath'] . $info['savename'];
$exts = $info['ext'];
if (!$info) {// 上传错误提示错误信息
$this->error($upload->getError());
} else {// 上传成功
$this->data_import($filename, $exts,3);
}
}