html
<div>fdafda
<form enctype="multipart/form-data" id="batchUpload" action="/visitor/import" method="post" class="form-horizontal">
<button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;" type="button">选择文件</button>
<input type="file" name="file" style="width:0px;height:0px;" id="uploadEventFile" />
<input id="uploadEventPath" disabled="disabled" type="text" placeholder="请选择excel表" style="border: 1px solid #e6e6e6; height: 26px;width: 200px;" />
</form>
<button type="button" class="btn btn-success btn-sm" onclick="excelImport.uploadBtn()">上传</button>
</div>
js
/**
* excel 导入
*/
var ExcelImport = function() {
this.init = function() {
//模拟上传excel
$("#uploadEventBtn").unbind("click").bind("click", function() {
$("#uploadEventFile").click();
});
$("#uploadEventFile").bind("change", function() {
$("#uploadEventPath").attr("value", $("#uploadEventFile").val());
});
};
//点击上传钮
this.uploadBtn = function() {
var uploadEventFile = $("#uploadEventFile").val();
if (uploadEventFile == '') {
alert("请择excel,再上传");
} else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excel
alert("只能上传Excel文件");
} else {
var url = "/visitor/import";
var formData = new FormData($('form')[0]);
excelImport.sendAjaxRequest(url, "POST", formData);
}
};
this.sendAjaxRequest = function(url, type, data) {
$.ajax({
url : url,
type : type,
data : data,
dataType : "json",
success : function(result) {
alert(result.message);
Visitor.table.refresh();
},
error : function(result) {
alert(result.message);
},
cache : false,
contentType : false,
processData : false
});
};
};
var excelImport;
$(function() {
excelImport = new ExcelImport();
excelImport.init();
});controller
package com.stylefeng.guns.modular.server.controller;
import com.baomidou.mybatisplus.plugins.Page;
import com.stylefeng.guns.core.base.controller.BaseController;
import com.stylefeng.guns.core.common.annotion.Permission;
import com.stylefeng.guns.core.common.constant.factory.PageFactory;
import com.stylefeng.guns.modular.server.model.Park;
import com.stylefeng.guns.modular.server.service.IParkService;
import com.stylefeng.guns.modular.server.warpper.VisitorWarpper;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.ui.Model;
import org.springframework.beans.factory.annotation.Autowired;
import com.stylefeng.guns.core.log.LogObjectHolder;
import com.stylefeng.guns.modular.server.model.Visitor;
import com.stylefeng.guns.modular.server.service.IVisitorService;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 访客管理控制器
*
* @author
* @Date 2018-09-27 17:30:18
*/
@Controller
@RequestMapping("/visitor")
public class VisitorController extends BaseController {
private String PREFIX = "/server/visitor/";
@Autowired
private IVisitorService visitorService;
@Autowired
private IParkService parkService;
/**
* 跳转到列表页面
*/
@Permission
@RequestMapping("")
public String index() {
return PREFIX + "visitor.html";
}
/**
* 分页列表
*/
@RequestMapping(value = "/list")
@ResponseBody
public Object list(@RequestParam(required = false) String visitorName) {
Page<Map<String, Object>> page = new PageFactory<Map<String, Object>>().defaultPage();
List<Map<String, Object>> result = visitorService.selectVisitorList(page, visitorName, page.getOrderByField(), page.isAsc());
page.setRecords((List<Map<String, Object>>) new VisitorWarpper(result).warp());
return super.packForBT(page);
}
/**
* 跳转到新增页面
*/
@RequestMapping("/visitor_add")
public String visitorAdd(Model model) {
List<Park> parkList = parkService.selectList(null);
model.addAttribute("park_list",parkList);
return PREFIX + "visitor_add.html";
}
/**
* 新增
*/
@Permission
@RequestMapping(value = "/add")
@ResponseBody
public Object add(Visitor visitor) {
visitor.setCreatetime(new Date());
visitorService.insert(visitor);
return SUCCESS_TIP;
}
/**
* 跳转到修改页面
*/
@RequestMapping("/visitor_update/{visitorId}")
public String visitorUpdate(@PathVariable Integer visitorId, Model model) {
Visitor visitor = visitorService.selectById(visitorId);
model.addAttribute("item",visitor);
Park park = parkService.selectById(visitor.getParkId());
model.addAttribute("park",park);
LogObjectHolder.me().set(visitor);
return PREFIX + "visitor_edit.html";
}
/**
* 修改
*/
@Permission
@RequestMapping(value = "/update")
@ResponseBody
public Object update(Visitor visitor) {
visitorService.updateById(visitor);
return SUCCESS_TIP;
}
/**
* 删除
*/
@Permission
@RequestMapping(value = "/delete")
@ResponseBody
public Object delete(@RequestParam Integer visitorId) {
visitorService.deleteById(visitorId);
return SUCCESS_TIP;
}
/**
* excel 导入
*/
@RequestMapping(value = "/import", method= RequestMethod.POST)
@ResponseBody
public Map<String, Object> importExcel(@RequestParam(value="file",required = false) MultipartFile file, HttpServletRequest request, HttpServletResponse response){
Map<String, Object> map = new HashMap<String, Object>();
String result = visitorService.readExcelFile(file);
map.put("message", result);
return map;
}
}
service
package com.stylefeng.guns.modular.server.service;
import com.baomidou.mybatisplus.plugins.Page;
import com.stylefeng.guns.modular.server.model.Visitor;
import com.baomidou.mybatisplus.service.IService;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
import java.util.Map;
/**
* 访客管理的服务类
*
* @author
* @date 2018-09-27 17:30:19
*/
public interface IVisitorService extends IService<Visitor> {
/**
* 分页查询列表
*/
List<Map<String,Object>> selectVisitorList(Page<Map<String, Object>> page, String visitorName, String orderByField, boolean asc);
/**
* 读取excel中的数据,生成list
*/
String readExcelFile(MultipartFile file);
}
serviceImp
package com.stylefeng.guns.modular.server.service.impl;
import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.stylefeng.guns.modular.server.dao.VisitorMapper;
import com.stylefeng.guns.modular.server.model.Visitor;
import com.stylefeng.guns.modular.server.service.IVisitorService;
import com.stylefeng.guns.modular.server.service.util.ReadExcel;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* 访客管理 的服务实现类
*
* @author
* @date 2018-09-27 17:30:19
*/
@Service
public class VisitorServiceImpl extends ServiceImpl<VisitorMapper, Visitor> implements IVisitorService {
@Override
public List<Map<String, Object>> selectVisitorList(Page<Map<String, Object>> page, String visitorName, String orderByField, boolean asc) {
return this.baseMapper.selectVisitorList(page, visitorName, orderByField, asc);
}
Visitor visitor = new Visitor();
@Override
public String readExcelFile(MultipartFile file) {
String result = "";
//创建处理EXCEL的类
ReadExcel readExcel = new ReadExcel();
//解析excel，获取上传的事件单
List<Map<String, Object>> excelList = readExcel.getExcelInfo(file);
//至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
for(Map<String, Object> row:excelList){
visitor.setParkId(Integer.parseInt( row.get("parkId").toString() ));
visitor.setVisitorName(row.get("visitorName").toString());
visitor.setVisitorMobile(row.get("visitorMobile").toString());
visitor.setCreatetime(new Date());
visitor.setAppointTime(new Date());
visitor.setFollowNum(1);
visitor.setInviter("aaa");
visitor.setInviterCompany("aaaaa");
visitor.setVisitingTime(new Date());
visitor.setStatus(1);
boolean ret = insert(visitor);
if(ret == false){
result = "插入数据库失败";
}
}
if(excelList != null && !excelList.isEmpty()){
result = "上传成功";
}else{
result = "上传失败";
}
return result;
}
}
util
package com.stylefeng.guns.modular.server.service.util;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
/**
*
* @author
*
*/
public class ReadExcel {
// 总行数
private int totalRows = 0;
// 总条数
private int totalCells = 0;
// 错误信息接收器
private String errorMsg;
// 构造方法
public ReadExcel() {
}
// 获取总行数
public int getTotalRows() {
return totalRows;
}
// 获取总列数
public int getTotalCells() {
return totalCells;
}
// 获取错误信息
public String getErrorInfo() {
return errorMsg;
}
/**
* 读EXCEL文件，获取信息集合
*
* @param mFile
* @return
*/
public List<Map<String, Object>> getExcelInfo(MultipartFile mFile) {
String fileName = mFile.getOriginalFilename();// 获取文件名
// List<Map<String, Object>> userList = new LinkedList<Map<String, Object>>();
try {
if (!validateExcel(fileName)) {// 验证文件名是否合格
return null;
}
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
return createExcel(mFile.getInputStream(), isExcel2003);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 根据excel里面的内容读取客户信息
*
* @param is 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003) {
try {
Workbook wb = null;
if (isExcel2003) {// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
return readExcelValue(wb);// 读取Excel里面客户的信息
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 读取Excel里面客户的信息
*
* @param wb
* @return
*/
private List<Map<String, Object>> readExcelValue(Workbook wb) {
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
this.totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
if (totalRows > 1 && sheet.getRow(0) != null) {
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();
// 循环Excel行数
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
// 循环Excel的列
Map<String, Object> map = new HashMap<String, Object>();
for (int c = 0; c < this.totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) {
// 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String parkId = String.valueOf(cell.getNumericCellValue());
map.put("parkId", parkId.substring(0, parkId.length() - 2 > 0 ? parkId.length() - 2 : 1));// 园区id
} else {
map.put("parkId", cell.getStringCellValue());// 园区id
}
} else if (c == 1) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String visitorName = String.valueOf(cell.getNumericCellValue());
map.put("visitorName",visitorName.substring(0, visitorName.length() - 2 > 0 ? visitorName.length() - 2 : 1));// 来访姓名
} else {
map.put("visitorName",cell.getStringCellValue());// 来访姓名
}
}else if (c == 2) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String visitorMobile = String.valueOf(cell.getNumericCellValue());
map.put("visitorMobile",visitorMobile.substring(0, visitorMobile.length() - 2 > 0 ? visitorMobile.length() - 2 : 1));// 访客手机
} else {
map.put("visitorMobile",cell.getStringCellValue());// 访客手机
}
}
}
}
// 添加到list
userList.add(map);
}
return userList;
}
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
public boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
// @描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}maven添加配置
<!--excel 导入excel 需要的jar包 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>参考网站 : https://blog.csdn.net/he140622hwt/article/details/78478960
716
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
