在开发中，很多时候客户都要求要将excel表中的数据导入数据库，我在开发中也遇到过，在此记录一下。
思路
一、在项目中引入我们操作excel需要的jar包，（我是以apache的poi为例）。
二、在页面利用ajax请求将导入的excel表格传入后台。
三、后台接收数据，之后对表格中的数据进行校验，出去不合法的数据。
四、将校验合格的数据对实体类（要导入的数据库表对应的实体类）赋值
五、分批次将数据导入到数据库。数据太多，我们需要将数据分成多个list，每个list的数据不超过一千条。
代码：
一、导入依赖：
<!-- POI处理Excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>二、controller层
/**
* 人才信息导入数据
*
* @return
*/
@RequestMapping(value = "/importData",method = RequestMethod.POST)
@CatchErr
public ResultMsg<String> importData(MultipartFile personnelExcelData){
ResultMsg<String> resultMsg = new ResultMsg<>();
try {
resultMsg = personnelInfoManager.importData(personnelExcelData.getInputStream(), personnelExcelData.getOriginalFilename());
} catch (IOException e) {
e.printStackTrace();
}
return resultMsg;
}三、service层
/**
* 导入人才信息数据
* @param inputStream
* @param originalFilename
* @return
*/
ResultMsg<String> importData(InputStream inputStream, String originalFilename);四、serviceImpl层
①：实现层总代码：
/**
* 导入人才信息数据
*
* @param inputStream
* @param originalFilename
* @return
*/
@Override
@Transactional(rollbackFor = Exception.class)
public ResultMsg<String> importData(InputStream inputStream, String originalFilename) {
// 导入数据要分批次导入，每次导入量为1000条（防止数据量过大）
int count = 1000;
int lastIndex = count;
// 需要导入的结果集合
List<PersonnelInfo> resultList= new ArrayList<>();
// 插入数据库成功总数
Integer successCount = 0;
// 插入数据库失败总数
// Integer failCount = 0;
try {
List<List<Object>> dataByExcel = getDataByExcel(inputStream, originalFilename);
if(CollectionUtils.isEmpty(dataByExcel)){
return ResultMsg.ERROR("您的表中没有数据或者数据不符合校验规则，请检查!");
}
for (int i = 0; i < dataByExcel.size(); i++) {
System.out.println(dataByExcel.get(i));
PersonnelInfo personnel = new PersonnelInfo();
// 获取id值
personnel.setId(IdUtil.getSuid());
personnel.setCjsj(new Date());
personnel.setXm(String.valueOf(dataByExcel.get(i).get(0)));
personnel.setXb(String.valueOf(dataByExcel.get(i).get(1)));
personnel.setNl(Integer.valueOf(String.valueOf(dataByExcel.get(i).get(2))));
personnel.setLxdh(String.valueOf(dataByExcel.get(i).get(3)));
personnel.setByxx(String.valueOf(dataByExcel.get(i).get(4)));
personnel.setXl(String.valueOf(dataByExcel.get(i).get(5)));
personnel.setZy(String.valueOf(dataByExcel.get(i).get(6)));
personnel.setPxjg(String.valueOf(dataByExcel.get(i).get(7)));
personnel.setPxfx(String.valueOf(dataByExcel.get(i).get(8)));
personnel.setGzjy(String.valueOf(dataByExcel.get(i).get(9)));
personnel.setYxgw(String.valueOf(dataByExcel.get(i).get(10)));
personnel.setGzjyjs(String.valueOf(dataByExcel.get(i).get(11)));
personnel.setRcly(String.valueOf(dataByExcel.get(i).get(12)));
personnel.setZt(String.valueOf(dataByExcel.get(i).get(13)));
personnel.setBz(String.valueOf(dataByExcel.get(i).get(14)));
// 将结果装入我们的集合中
resultList.add(personnel);
}
// 将resultList中的数据拆分为多个list。
List<List<PersonnelInfo>> groupingList = getGroupingList(resultList, count);
if(!CollectionUtils.isEmpty(groupingList)){
for (int i = 0; i < groupingList.size(); i++) {
// 插入数据库
successCount = successCount + personnelInfoDao.insertImportData(groupingList.get(i));
}
}
// failCount = excelCount - successCount;
return ResultMsg.SUCCESS("本次导入数据库成功："+successCount +" 条");
} catch (Exception e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return ResultMsg.ERROR("导入数据失败，请重试！");
}
}②：下面的代码是①中的 getDataByExcel(InputStream in, String fileName) 方法，作用是利用poi对excel进行操作，除去不合法的数据，将合法数据返回（校验规则是根据具体的业务判断）：
/**
* 根据传入的excel流获取excel表格里面的内容
*
* @param in 输入流(excel表格)
* @param fileName 文件名称
* @return 获取的excel表格里面的数据
*/
private List<List<Object>> getDataByExcel(InputStream in, String fileName) throws Exception {
// 行集合
List<List<Object>> rowList = new ArrayList<List<Object>>();
// 创建一个excel工作薄
Workbook workbook = ExcelUtil.getWorkbook(in, fileName);
if (null == workbook) {
throw new Exception("创建excel失败！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
// 获取excel中所有的sheet
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheet = workbook.getSheetAt(i);
if (sheet == null) {
continue;
}
// 获取表格中每一行的数据(在我传入的excel中,第一行是标题,所以数据从第二行开始获取)
int rowNum = sheet.getFirstRowNum() + 1;
for (int j = rowNum; j <= sheet.getLastRowNum(); j++) {
// 每行中的列集合
List<Object> cellList = new ArrayList<>();
// 获取一行
row = sheet.getRow(j);
// 去除空行
if(row == null){
continue;
}
// 获取一行中所有的列
System.out.println(row.getLastCellNum());
for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
cell = row.getCell(k);
// 判断遍历列的时候是否有null值
Object cellValue = ExcelUtil.getCellValue(cell);
// 校验每列数据是否符合规则,只有全部符合的数据,才能被加入数据库。
// 本次excel表格中数据的规则是：每行的前十二列不能为空。
if(k < 12){
// 判断是否有空值，若有空值这不保存该行
if(cellValue == null || "".equals(cellValue)){
// 清空cellList，并跳出循环
cellList = null;
break;
}
// 判断是否有空值，若有空值这不保存该行
System.out.println(cellValue);
}
cellList.add(cellValue);
}
// 通过了验证，把该行数据插入集合
if(!CollectionUtils.isEmpty(cellList)){
rowList.add(cellList);
}
}
}
return rowList;
}③：这里是①中的 getGroupingList(List<PersonnelInfo> originalList,int count ) 方法，在获取到合法数据后，防止数据量过大，需要拆分结果集，防止数据大批量的插入数据库，导致数据库压力过大。拆分时，每个list的数据量不能超过1000条：
/**
* 将originalList中的数据拆分为多个list。
*
* @param originalList 数据源
* @param count 每个集合的总量
* @return 返回拆分后的集合集
*/
private List<List<PersonnelInfo>> getGroupingList(List<PersonnelInfo> originalList,int count ){
List<List<PersonnelInfo>> batchList = new ArrayList<>();
// 开始下标
int startIndex = 0;
// 结束下标
int lastIndex = 0;
if(originalList.size()<count){
batchList.add(originalList);
}else{
// 取商
int floor = (int)Math.floor(originalList.size() / count);
// 取模
int mod = originalList.size()%count;
for (int i = 0; i < floor; i++) {
lastIndex = lastIndex + count;
List<PersonnelInfo> cutList = originalList.subList(startIndex,lastIndex);
batchList.add(cutList);
startIndex = startIndex + count;
}
// 表示还有最后的一些数据
if(mod > 0){
List<PersonnelInfo> lastList = originalList.subList(lastIndex,originalList.size());
batchList.add(lastList);
}
}
return batchList;
}④：下面是插入数据库的sql语句，因为在开发中我们多喜欢用mybatis-plus等插件中的方法将数据插入到数据库，但是在批量查询的时候，我们还是应该别用它的批量插入，自己写一个如下的批量查询方法，效率要高一些。
五、Dao层接口
上面第四点中①小点中的 insertImportData(List<PersonnelInfo> personnelInfoList) 方法：
/**
* 导入人才信息数据
*
* @param personnelInfoList
* @return
*/
Integer insertImportData(@Param(value = "personnelInfoList") List<PersonnelInfo> personnelInfoList);xml中的sql语句：
<!--批量导入数据到数据库-->
<insert id="insertImportData" parameterType="xxx.xxx.xxx.xxx.xxx">
INSERT INTO rcxxb(id,xm,xl,rcly,xb,nl,lxdh,byxx,zy,gzjy,zt,yxgw,pxjg,bz,cjsj,pxfx,gzjyjs)
VALUES
<foreach collection="personnelInfoList" index="index" item="item" separator=",">
(
#{item.id},#{item.xm},#{item.xl},#{item.rcly},#{item.xb},#{item.nl},#{item.lxdh},#{item.byxx},#{item.zy},#{item.gzjy},#{item.zt},#{item.yxgw},#{item.pxjg},#{item.bz},#{item.cjsj},#{item.pxfx},#{item.gzjyjs}
)
</foreach>
</insert>六、HTML页面
①、上传组件：
<form id="importForm" method="post" enctype="multipart/form-data">
<div class="oneDivStyle">
<div class="twoDivStyle">
<span style="font-weight: 200;font-size: 16px">导入数据:</span>
</div>
<div class="threeDivStyle">
<input class="oneInputStyle" type="file" id="choiceExcel" name="personnelExcelData" style="outline-color: #fff;" />
<input class="twoInputStyle" type="button" onclick="importForms()" value="导入数据库"/>
</div>
</div>
</form>②、利用ajax请求后台接口：
<script type="text/javascript">
function importForms() {
var path = $("#choiceExcel").val()
if(path == ''){
$.Dialog.error ( "请选择上传文件!");
return;
}
var getSuffixOne = path.substring(path.length - 5 , path.length);
if(getSuffixOne != ".xlsx"){
$.Dialog.error ( "只能上传\".xlsx\" 文件");
return;
}
var uploadUrl = __ctx + "/personnelInfo/importData";
$ ( "#importForm" ).ajaxForm ({
type : "post",
url : uploadUrl ,
contentType : "application/json;charset=UTF-8" ,
success : function ( data ) {
var json = JSON.parse(data);
if(json.isOk == true){
$.Dialog.success(json.data);
// 刷新父级页面
try {
if(parent.reloadGrid){parent.reloadGrid()};
} catch (e) {
}
}else{
$.Dialog.error(json.data);
}
// 刷新页面
location.reload();
}
});
$("#importForm").submit();
}
</script>③、上传组件样式
<style>
.oneDivStyle {
width: 90%;
height: auto;
margin-left: 15%;
display: flex;
}
.twoDivStyle {
line-height: 32px;
}
.threeDivStyle {
display: flex;
}
.oneInputStyle {
width: 200px;
height: 30px;
position:relative;
left: 20px;
border-radius: 5px;
font-size: 15px;
padding-top: 3px;
line-height: 32px;
margin-right: 10px;
color: #00a2ff;
}
.oneInputStyle::before{
position: absolute;
top: 0;
left: 0;
content: '选择文件';
width: 80px;
height: 30px;
color: #ffffff;
background-color: #2C85E4;
border-radius: 5px;
font-size: 15px;
border: 1px solid #fff;
outline: none;
text-align: center;
z-index: 1;
}
.twoInputStyle {
width: 100px;
height: 30px;
position:relative;
left: 20px;
color: #ffffff;
background-color: #2C85E4;
border-radius: 5px;
font-size: 15px;
border: 1px solid #fff;
outline: none;
}
</style>④、样式效果
最后（很重要）
我们一定要注意，在上传文件时，from表单中一定要指明是 enctype ="multipart/form-data" ，method="post"，后台接口用 MultipartFile 类型接收，上传组件中的 name属性最好等于 后台接口中的参数名字，如 name="personnelExcelData" , 后台接口中的参数名字为 personnelExcelData，如 importData(MultipartFile personnelExcelData) 。 另外，在ajax请求中 我这里的url是自己拼接的，如下面代码中的 _ctx ,在使用的时候需要自己变化一下。
var uploadUrl = __ctx + "/personnelInfo/importData";
扫一扫
1845
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
