spingboot+layui+poi实现excel导入功能，并批量插入数据库

北木桥溪

已于 2022-11-23 17:43:30 修改

阅读量2k

点赞数 1

分类专栏： Java实现文件上传文章标签： spring boot poi java ssm

于 2022-01-21 16:32:22 首次发布

本文链接：https://blog.csdn.net/weixin_54685622/article/details/122583418

版权

Java实现文件上传专栏收录该内容

6 篇文章 1 订阅

订阅专栏

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;

北木桥溪

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
spingboot+layui+poi实现excel导入功能，并批量插入数据库

1、maven的pom文件<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId>
复制链接

扫一扫