导入功能实现

1、引入bootstrap-fileinput

<link rel="stylesheet" href="bootstrap-fileinput/css/fileinput.css">
<link rel="stylesheet" href="bootstrap-fileinput/css/fileinput-rtl.css">

<script src="bootstrap-fileinput/js/fileinput.js"></script>
<script src="bootstrap-fileinput/js/zh.js"></script>

2、导入按钮

<div id="toolbar" class="toolbar">
    <button type="button" class="btn btn-primary" onclick="add();">
        <span class="glyphicon glyphicon-plus" aria-hidden="true"></span> 添加
    </button>
    <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#exampleModal">
        <span class="glyphicon glyphicon-import" aria-hidden="true"></span> 导入
    </button>
    <button type="button" class="btn btn-primary" onclick="exportData();">
        <span class="glyphicon glyphicon-export" aria-hidden="true"></span> 导出
    </button>
</div>

3、模态框

<!-- Modal -->
<div class="modal fade" id="exampleModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
    <div class="modal-dialog modal-lg" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="exampleModalLabel">Modal title</h5>
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
            </div>
            <div class="modal-body">
                <div class="file-loading">
                    <input id="input-b9" name="file" multiple type="file">
                </div>
                <div id="kartik-file-errors"></div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                <button type="button" class="btn btn-primary" title="Your custom upload logic">Save</button>
            </div>
        </div>
    </div>
</div>

4、js代码

<script>
    $(document).ready(function() {
        $("#input-b9").fileinput({
            showPreview: false,
            showUpload: true,
            elErrorContainer: '#kartik-file-errors',
            allowedFileExtensions: ["xls", "xlsx"],
            uploadUrl: 'import'
        });
    });
</script>

5、java代码

    @PostMapping("import")
    @ResponseBody
    public ActionResult upload(@PathVariable("file") MultipartFile file) throws Exception {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(file.getInputStream());
            List<StudentVO> getData = readOldExcel(hssfWorkbook);
            if (getData == null) {
                return new ActionResult(-1,"解析文件失败",null);
            }
            file.getInputStream().close();

        Connection conn = null;
        PreparedStatement stmt = null;
        //
        try {
            conn = dbUtil.getConnection();
            for(StudentVO stu:getData){
                stmt = conn.prepareStatement("INSERT INTO STUDENT(SNAME,GENDER,BIRTH) VALUES(?,?,?)");
                stmt.setString(1, stu.getSname());
                stmt.setString(2, stu.getGender());
                stmt.setString(3, stu.getBirth());
                stmt.executeUpdate();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                conn.close();//关闭数据库的连接
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return ActionResult.ok(getData);
    }

    //处理2007之前的excel
    private List<StudentVO> readOldExcel(HSSFWorkbook hssfWorkbook) {
        List<StudentVO> students = new ArrayList<StudentVO>();
        HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
        HSSFCell cell = null;
        HSSFRow row = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = sheetAt.getFirstRowNum()+1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            Object[] objects = new Object[row.getLastCellNum()];
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                switch (cell.getCellTypeEnum()) {
                    case STRING:
                        objects[j] = cell.getStringCellValue();
                        System.out.println(cell.getStringCellValue());
                        break;
                    case _NONE:
                        objects[j] = "";
                        break;
                    case BOOLEAN:
                        objects[j] = cell.getBooleanCellValue();
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case NUMERIC:
                        //处理double类型的  1.0===》1
                        DecimalFormat df = new DecimalFormat("0");
                        String s = df.format(cell.getNumericCellValue());
                        objects[j] = s;
                        System.out.println(s);
                        break;
                    default:
                        objects[j] = cell.toString();
                }
            }
            //处理数据
            if (objects != null) {
                StudentVO stu = new StudentVO();
                stu.setSname((String) objects[1]);
                stu.setGender((String)objects[2]);
                stu.setBirth(sdf.format(row.getCell(3).getDateCellValue()));
                students.add(stu);
            }
        }
        return students;
    }

6、github地址

https://github.com/yangzc23/yangzc

7、参考资料

[01] Basic Usage Demo
[02] Bootstrap FileInput 组件使用
[03] 文件上传控件bootstrap-fileinput的使用
[04] bootstrap-fileinput + poi 导入Excel完整示例（包含前端后端和失败处理）
[05] poi实现导入和导出功能
[06] bootstrap-fileinput配置项
[07] poi读取Excel时日期为数字 的解决方法
[08] poi解析excel读取日期为数字的问题

微信扫一扫关注公众号

点击链接加入群聊

https://jq.qq.com/?_wv=1027&k=5eVEhfN
软件测试学习交流QQ群号：511619105