Layui导入Excel数据到数据库

慕白Lee

已于 2022-02-28 21:58:33 修改

阅读量1.4w

点赞数 11

分类专栏：前端 # Layui 文章标签： layui big data html

于 2019-09-10 10:00:52 首次发布

本文链接：https://blog.csdn.net/libusi001/article/details/100689952

版权

前端同时被 2 个专栏收录

38 篇文章 6 订阅

订阅专栏

Layui

24 篇文章 4 订阅

订阅专栏

Layui常用总结

Layui常用总结50篇（VIP典藏版）

一、前端页面

二、后台代码

三、代码优化：mapper.xml批量插入

四、注意事项

一、前端页面

1、内容主题区域

<script type="text/html" id="toolbarDemo">
            <div class="layui-btn-container">
                <div class="layui-btn-container">
                    <button class="layui-btn layui-btn-sm" lay-event="add">新增</button>
                    <button class="layui-btn layui-btn-sm" lay-event="uploadExcel">Excel上传</button>
                </div>
            </div>
</script>
<table class="layui-hide" id="test" lay-filter="test"></table>

2、头工具栏事件

//头工具栏事件
        table.on('toolbar(test)', function (obj) {
            var status= table.status(obj.config.id);
            switch (obj.event) {
                case 'uploadExcel':
                    var uploadUrl = ctxPath + 'xxx/uploadHtml';
                    layer.open({
                        type: 2,
                        title: 'Excel上传',
                        shadeClose: true,
                        shade: 0.8,
                        area: ['500px', '50%'],
                        content: uploadUrl,
                        btn: ['确定', '取消'],
                        yes: function (index) {
                            var formSubmit = layer.getChildFrame('form', index);
                            var submited = formSubmit.find('button')[0];
                            submited.click();
                            $('.layui-laypage-btn').click();
                        }
                    });
                };
            });

3、upload.htmlye页面代码

<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>

</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: ctxPath + 'xx/upload',
            size: '800',
            accept: "file",
            exts: 'xls|xlsx',
            before: function () {
                layer.load();
            },
            done: function (result) {
                if (result.code === 0) {
                    layer.msg(result.message, {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.message, {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>

二、后台代码

    @ResponseBody
    @PostMapping(value = "upload")
    public Result upload(@RequestParam MultipartFile file) {
        log.info("Excel批量上传信息");
        try {
            InputStream inputStream = file.getInputStream();
            List<Map<String, Object>> list = GetMapByExcel.readExcelcc(inputStream, 6);
            List<Entity> sbList = new ArrayList<>();
            for (Map<String, Object> map : list) {
                Set<String> keySet = map.keySet();
                Entity entity = new Entity();
                for (String string : keySet) {
                    String stt = (String) map.get(string);
                    String str = stt.trim();
                    ....
                }
                entity.setDeleted(1);
                sbList.add(entity);
            }
            //批量插入
            int i = sbService.insertBatch(sbList);
            if (i > 0) {
                log.info("数据导入成功");
                return Result.success("数据导入成功", null);
            } else {
                log.error("数据导入失败!");
                return Result.error("数据导入失败!");
            }
        } catch (Exception e) {
            log.error("数据导入出现异常：{}", e.getMessage());
            return Result.error("数据导入失败，请检查模板数据格式!");
        }
    }

三、代码优化：mapper.xml批量插入

<insert id="insertBatch">
        INSERT INTO xx
        (id,...,deleted)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{xx.Id},
            ...
            #{xx.status}
            )
        </foreach>
    </insert>

四、注意事项

1、数据格式类型匹配

2、前端上传文件大小限定，最好1M以内，也可更改配置的

3、其他

4、自己封装的读取excel工具类（有些同学需要就贴出来了）：

package com.sgcc.censor.common.util;


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
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;

/**
 * @author libusi
 */
@Slf4j
public class ReadDataByExcel {

    /**
     * 读取excel指定列,包含空
     */
    public static List<Map<String, Object>> readExcel(InputStream inputStream, Integer cel) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            XSSFWorkbook wb0 = new XSSFWorkbook(inputStream);
            //循环Sheets,默认取1
            for (int i = 0; i < 1; i++) {
                Sheet sheet = wb0.getSheetAt(0);
                int lastRowNum = sheet.getLastRowNum();
                //获取表头数据行
                Row rowHead = sheet.getRow(0);
                for (int j = 0; j < lastRowNum; j++) {
                    Row row = sheet.getRow(j + 1);
                    if (row != null) {
                        Map<String, Object> map = new LinkedHashMap<>();
                        for (int k = 0; k < cel; k++) {
                            //获取表头和数据
                            Cell cellHead = rowHead.getCell(k);
                            Cell cell = row.getCell(k);
                            String cellValue = null;
                            //处理各种带空数据
                            String headValue = cellHead.toString().trim();

                            if (cell != null && !StringUtils.isEmpty(cell.toString().trim())) {
                                cellValue = cell.toString().trim();
                            } 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;
    }

}

有用请点赞，养成良好习惯！

疑问、交流、鼓励请留言！