1、maven的pom文件添加下面jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.4.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
2、前端页面定义layui按钮，弹出导入页面
//定义按钮
<button class="layui-btn layui-btn-sm" lay-event="uploadExcel">Excel上传</button>
//弹出层位置
<table class="layui-table" id="currentTableId" lay-filter="currentTableFilter"></table>
//toolbar监听事件
table.on('toolbar(currentTableFilter)', function (obj) {
if(obj.event === 'uploadExcel'){
layer.open({
type: 2,
title: 'Excel上传',
shadeClose: true,
shade: 0.8,
area: ['500px', '50%'],
content: 'uploadHtml',
btn: ['确定', '取消'],
yes: function (index) {
var formSubmit = layer.getChildFrame('form', index);
var submited = formSubmit.find('button')[0];
submited.click();
$('.layui-laypage-btn').click();
}
});
}
});
3、弹出框页面代码
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="utf-8">
<title>导入excel数据</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="/static/layui/lib/layui-v2.6.3/css/layui.css" media="all">
<script src="/static/layui/lib/layui-v2.6.3/layui.js" charset="utf-8"></script>
<script src="static/assets/js/jquery.min.js"></script>
</head>
<body class="layui-layout-body">
<div class="layui-upload" align="center">
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;" align="center">
<legend>请选择您要上传的Excel文件</legend>
</fieldset>
<div style="margin-top: 30px" align="center">
<button id="importData" class="layui-btn layui-btn-normal">导入</button>
</div>
</div>
<script src="../../layui/layui.js" th:src="@{/layui/layui.js}"></script>
<script th:src="@{/js/jquery-3.4.0.min.js}"></script>
<script th:inline="javascript">
ctxPath = /*[[@{/}]]*/ '';
layui.use(["element", "layer", "upload"], function () {
var layer = layui.layer;
layui.upload.render({
elem: "#importData",
url: '/upload',
size: '800',
accept: "file",
exts: 'xls|xlsx',
before: function () {
layer.load();
},
done: function (result) {
if (result.code === 0) {
layer.msg(result.msg, {icon: 1, time: 1000}, function () {
var index = parent.layer.getFrameIndex(window.name);
parent.layer.close(index);
parent.$('.layui-laypage-btn').click();
});
} else if (result.code === -1) {
layer.msg(result.msg, {icon: 5, time: 2500}, function () {
var index = parent.layer.getFrameIndex(window.name);
parent.layer.close(index);
parent.$('.layui-laypage-btn').click();
});
}
layer.closeAll('loading');
}
});
});
</script>
</body>
</html>
4、后端controller
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.xxx.bean.Unit;
import com.xxx.common.ReadDataByExcel;
import com.xxx.service.UnitService;
import com.xxx.util.JsonResult;
import com.xxx.util.UtilTool;
import lombok.extern.slf4j.Slf4j;
@Controller
@Slf4j
public class UploadController {
@Autowired
private UnitService unitService;
//弹出页面
@RequestMapping("/uploadHtml")
public String uploadHtml(String mode,Model model) {
return "upload/upload";
}
//批量数据添加
@Transactional
@ResponseBody
@PostMapping(value = "upload")
public JsonResult upload(@RequestParam MultipartFile file) {
System.out.println(file.getOriginalFilename());
log.info("Excel批量上传信息");
try {
InputStream inputStream = file.getInputStream();
List<Map<String, Object>> list = ReadDataByExcel.readExcel(inputStream);//自定义读取excel
List<Unit> unitsList = new ArrayList<>();
for (Map<String, Object> map : list) {
Set<String> keySet = map.keySet();
Unit unit = new Unit();
for (String string : keySet) {
String stt = (String) map.get(string);
String str = stt.trim();
if (string.equals("单位")) {
unit.setUnit(str);
}else if (string.equals("地区")) {
unit.setRegin(str);
}else if (string.equals("地域")) {
unit.setReach(str);
}else if (string.equals("情况说明")) {
unit.setRemark(str);
}
}
unit.setRegin(UtilTool.getRegin(unit.getRegin()));
unit.setReach(UtilTool.getReach(unit.getRegin(), unit.getReach()));
unitsList.add(unit);
}
//批量插入
boolean i = unitService.saveBatch(unitsList);
if (i) {
log.info("数据导入成功");
return JsonResult.success("数据导入成功");
} else {
log.error("数据导入失败!");
return JsonResult.error(-1,"数据导入失败!");
}
} catch (Exception e) {
log.error("数据导入出现异常：{}", e.getMessage());
return JsonResult.error(-1,"数据导入失败，请检查模板数据格式!");
}
}
}
5、自定义读取excel 里面数据 （poi）
import lombok.extern.slf4j.Slf4j;
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.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class ReadDataByExcel {
public static List<Map<String, Object>> readExcel(InputStream inputStream) {
List<Map<String, Object>> list = new ArrayList<>();
try {
XSSFWorkbook wb0 = new XSSFWorkbook(inputStream);
// 循环Sheets,默认取0，第一个sheet
// 获得Excel表的sheet的数目
int numberOfSheets = wb0.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
//获取sheet工作表
Sheet sheet = wb0.getSheetAt(i);
//获取sheet中第一行行号，表头行 0
int firstRowNum = sheet.getFirstRowNum();
//获取最后一行行号 ，从0开始，20行 ==0---19
int lastRowNum = sheet.getLastRowNum();
// 获取表头数据行
Row rowHead = sheet.getRow(firstRowNum);
for (int j = firstRowNum; j < lastRowNum; j++) { //表头不用参与遍历，所有循环少一次
Row row = sheet.getRow(j + 1);//获取第二行
if (rowHead!=null && !isRowEmpty(row)) {
Map<String, Object> map = new LinkedHashMap<>();
//rowHead.getLastCellNum()获取最后一个不为空的列是第几个 从0开始，比实际大一 如：10列==0--10
//rowHead.getPhysicalNumberOfCells()获取不为空的列的个数
for (int k = 0; k < rowHead.getLastCellNum(); k++) {
// 获取表头和数据
Cell cellHead = rowHead.getCell(k);
Cell cell = row.getCell(k);
String cellValue = null;
String headValue = null;
// 处理各种带空数据
if(cellHead!=null) {
headValue = cellHead.toString().trim().replaceAll(" ", "");
}else {
headValue="";
}
if (cell != null && !cell.toString().trim().isEmpty()) {
cellValue = cell.toString().trim().replaceAll(" ", "");
} else {
cellValue = "";
}
// map封装
map.put(headValue, cellValue);
}
// list封装
list.add(map);
}
}
}
try {
inputStream.close();
} catch (Exception e) {
log.error("exception:", e);
}
} catch (Exception e) {
log.error("exception:", e);
}
return list;
}
//判断表行是否为空
public static boolean isRowEmpty(Row row) {
for(int i = row.getFirstCellNum();i<row.getLastCellNum();i++) {
Cell cell =row.getCell(i);
if(cell!=null&&cell.getCellType()!=Cell.CELL_TYPE_BLANK) {
return false;
}
}
return true;
}
}
如果在读取excel数据的时候关于一大片的合并区域，poi只会返回左上角第一个cell的数据，其余的全都返回空
注意：
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；
XSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；
SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；
当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。
判断excel版本除了后缀判断，也可以使用一下方法判断
/**
* 优化excel类型判断
* 注意可能会获取报错 getFileMagic() only operates on streams which support mark(int)
* 原因InputStream中markSupported方法返回值为false造成的，BufferedInputStream中返回值是true，所以改为InputStream is = new BufferedInputStream(multipartFile.getInputStream())
*/
//InputStream is = new BufferedInputStream(multipartFile.getInputStream());
FileMagic fileMagic = FileMagic.valueOf(inputStream);
if (Objects.equals(fileMagic, FileMagic.OLE2)) {
return EXCEL2003;
} else if (Objects.equals(fileMagic, FileMagic.OOXML)) {
return EXCEL2007;
