1 先导入配置文件
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</dependency>
2 编写Chontroller
/**
* 上传excel文件
* @author SHF
* @version 创建时间:2018年12月14日 上午9:28:14
* @param excelInput
* @param type 1:大客户经理 2内部人员'
* @return
* @throws Exception
*/
@RequestMapping("/uploadExcel")
@ResponseBody
public Object uploadExcel(@RequestParam("excelInput") MultipartFile excelInput,Integer type) throws Exception{
// 检查文件类型
String fileName = checkFile(excelInput);
if("1".equals(fileName) || "2".equals(fileName)) {
return new SuccessTip("请上传Excel格式的文件");
}
Workbook workbook = null;
InputStream inputStream = excelInput.getInputStream();
List<String[]> list = new ArrayList<String[]>();
try {
workbook = WorkbookFactory.create(inputStream);
int numberOfSheets = workbook.getNumberOfSheets();
if (numberOfSheets > 0) {
Sheet sheet = workbook.getSheetAt(0);
if (null != sheet) {
// 获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
// 获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
// 获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
// 获得当前行的列数
int lastCellNum = row.getLastCellNum();
if(lastCellNum != 3) {
//必须要有三列数据
return new SuccessTip("上传的数据不能有为空值!");
}
String[] cells = new String[row.getLastCellNum()];
// 循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
}
//插入数据
if (!list.isEmpty()) {
for (String[] strs : list) {
Integer userId = ShiroKit.getUser().getId();
Date date = new Date();
String name = strs[0];
String dept_userType = strs[1];
String phone = strs[2];
/*if(StringUtils.isBlank(phone)) {
//throw new Exception("手机号码不能为空");
return new SuccessTip("手机号码不能为空");
}*/
ProjectInnerUser tProjectInnerUser = tProjectInnerUserService.selectOne(new EntityWrapper<ProjectInnerUser>().eq("phone", phone).eq("is_enable", 1).eq("is_deleted", 0));
if(tProjectInnerUser == null) {
tProjectInnerUser = new ProjectInnerUser();
tProjectInnerUser.setName(name);
tProjectInnerUser.setPhone(phone);
if(type == 1) {
tProjectInnerUser.setDept(dept_userType);
}else {
tProjectInnerUser.setUserType(dept_userType);
}
tProjectInnerUser.setType(type);
tProjectInnerUser.setRemark("excel导入的数据");
tProjectInnerUser.setIsDeleted(0);
tProjectInnerUser.setIsEnable(1);
tProjectInnerUser.setCreator(userId);
tProjectInnerUser.setCreated(new Date());
tProjectInnerUser.setModifier(userId);
tProjectInnerUser.setModified(date);
tProjectInnerUserService.insert(tProjectInnerUser);
}else {
tProjectInnerUser.setName(name);
if(type == 1) {
tProjectInnerUser.setDept(dept_userType);
}else {
tProjectInnerUser.setUserType(dept_userType);
}
tProjectInnerUser.setType(type);
tProjectInnerUser.setRemark("excel导入的数据");
tProjectInnerUser.setModifier(userId);
tProjectInnerUser.setModified(date);
tProjectInnerUserService.updateById(tProjectInnerUser);
}
}
}
} catch (EncryptedDocumentException e1) {
e1.printStackTrace();
} catch (InvalidFormatException e1) {
e1.printStackTrace();
} finally {
if (null != workbook) {
workbook.close();
}
if (null != inputStream) {
inputStream.close();
}
}
return SUCCESS_TIP;
}
public static String checkFile(MultipartFile file) throws IOException {
// 判断文件是否存在
if (null == file) {
//throw new GunsException(BizExceptionEnum.FILE_NOT_FOUND);
return "1";//文件不存在
}
// 获得文件名
String fileName = file.getOriginalFilename();
// 判断文件是否是excel文件
if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
//throw new GunsException(BizExceptionEnum.UPLOAD_NOT_EXCEL_ERROR);
return "2";//上传的不是excel文件
}
return fileName;
}
/**
* 根据不同类型获取值
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
cellValue = stringDateProcess(cell);
break;
case STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
* 时间格式转换
*
* @param cell
* @return
*/
public static String stringDateProcess(Cell cell) {
String result = new String();
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm ");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
return result;
}
3 页面我们使用Jquery的form表单上传 jquery.form.js 地址:https://github.com/jquery-form/form
@layout("/common/_container.html"){
<script src="${ctxPath}/static/js/jquery.form.js"></script>
<div class="ibox float-e-margins">
<div class="ibox-content">
<div class="form-horizontal">
<form id="form1" method="post" enctype="multipart/form-data">
<input type="hidden" value="${type!}" id="type" name="type" accept="xlsx">
<input type="file" class="form-control" name="excelInput" id="excelInput" >
<p>长传文件的格式:</p>
<div style="font-size:16px;">
<p style="float:left;width:100px;">姓名</p>
<p style="float:left;width:100px;">部门</p>
<p style="float:left;width:100px;">手机号码 </p>
</div>
<div class="row">
<div class="flexca">
<input type="reset" class="btn btn-warning" onClick="closePag()" value="取消">
<input type="submit" id="tj" class="btn btn-primary" value="提交" style="margin-left:65px;">
</div>
</div>
</form>
</div>
</div>
</div>
<script type="text/javascript">
var type = null;
$(function(){
type = $("#type").val();//全局配置文件的类型 1:表示从大客户经理页面跳转过来的 2:表示从内部员工页面跳转过来的
/** 验证文件是否导入成功 */
var options = {
target:'#form1',
url:Feng.ctxPath + "/bigUser/uploadExcel",
success:function(data) {
if(data.code == 200){
Feng.success("上传成功!");
}else{
Feng.error("上传失败!"+data.message);
}
closePag();
}
};
$('#form1').ajaxForm(options);
});
//关闭弹框
function closePag(){
if(type == 1){
parent.layer.close(window.parent.TBigUser.layerIndex);
window.parent.TBigUser.table.refresh();
}else{
parent.layer.close(window.parent.InsideUser.layerIndex);
window.parent.InsideUser.table.refresh();
}
}
//-->
</script>
@}
或者使用ajax的方式提交表单(这样可以减少兼容性)
$(function(){
type = $("#type").val();//全局配置文件的类型 1:表示从大客户经理页面跳转过来的 2:表示从内部员工页面跳转过来的
$("#tj").click(function () {
var formData = new FormData($('#form1')[0]);
$.ajax({
type: 'post',
url: Feng.ctxPath + "/bigUser/uploadExcel",
data: formData,
cache: false,
processData: false,
contentType: false,
}).success(function (data) {
if(data.code == 200){
Feng.success("上传成功!");
}else{
Feng.error("上传失败!"+data.message);
}
closePag();
}).error(function () {
alert("上传失败");
});
});
});