thinkphp 怎么通过excel表格批量导入数据库?
思路步骤:
(1)表单提交文件,通过thinkphp封装方法保存和获取文件信息 (import方法)
(2)使用PHPExcel库读取excel文件(data_import方法)
(3)提取的数据,循环插入数据库(insert_data方法)
1.静态页面
<!-- 表单 -->
<form id="form1" action="{:U('import')}" method="post" class="form-horizontal" enctype="multipart/form-data">
<!-- 基础文档模型 -->
<div id="tab2" class="tab-pane in tab1 tab_table" >
<input type="file" name="excelData" datatype="*4-50" />
</div>
<div class="form-item cf">
<button class="submit_btn" id="formbtn" type="submit" target-form="form-horizontal">保 存</button>
<button class="submit_btn" "javascript:window.history.back();">取 消</button>
</div>
</form>
</div>
</div>
2.服务端3个方法
public function import()
{
$upload = new \Think\Upload();// 实例化上传类
$upload->maxSize = 3145728;// 设置附件上传大小
$upload->exts = array('xls', 'xlsx');// 设置附件上传类型
$upload->rootPath = './Uploads/'; // 设置附件上传根目录
$upload->savePath = ''; // 设置附件上传(子)目录
// 上传文件
$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);
}
}
public function data_import($filename, $exts = 'xls', $or)
{
header("Content-type:text/html;charset=utf-8");
vendor('PHPExcel.PHPExcel');
$PHPExcel = new \PHPExcel();
if ($exts == 'xls') {
import("Org.Util.PHPExcel.Reader.Excel5");
$PHPReader = new \PHPExcel_Reader_Excel5();
} else if ($exts == 'xlsx') {
import("Org.Util.PHPExcel.Reader.Excel2007");
$PHPReader = new \PHPExcel_Reader_Excel2007();
}
$PHPExcel = $PHPReader->load($filename);
$currentSheet = $PHPExcel->getSheet(0);
$allColumn = $currentSheet->getHighestColumn();
$allRow = $currentSheet->getHighestRow();
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$address = $currentColumn . $currentRow;
$cell = $currentSheet->getCell($address)->getValue();
if ($cell instanceof PHPExcel_RichText) {
$cell = $cell->__toString();
}
$data[$currentRow - 1][$currentColumn] = $cell;
}
}
$this->insert_data($data);
}
public function insert_data($data){
$model=M('server','tab_');
foreach ($data as $k=>$v){
if($k!=0){
$info['game_id']=$v['A'];
$info['server_name']=$v['D'];
$model->add($info);
}
}
$this->success('批量新增成功',U('lists'));
}