POI结合bootstrap-fileinput上传Excel内容到数据库

文章目录

• ​​一、准备工作​​

• ​​1、POI依赖​​
• ​​2、bootstrap-fileinput插件下载:​​

• ​​二、代码部分​​

• ​​1、前端代码:​​
• ​​2、javascript渲染部分:​​
• ​​3、Controller层:​​
• ​​4、Service:​​
• ​​5、Service实现类:​​
• ​​6、Dao层代码:​​

• ​​三、实现效果:​​

• ​​1、准备导入的excel数据：​​
• ​​2、在前端页面中导入该文件:​​
• ​​3、查看数据库是否更新了数据:​​

一、准备工作

1、POI依赖

如果是maven项目，在pom.xml中添加下面的依赖：

<!--POI 实现Excel的导入导出-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.15</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.15</version>
    </dependency>
     <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.3</version>
    </dependency>
     <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.7</version>
    </dependency>

2、bootstrap-fileinput插件下载:

github地址:​​https://github.com/kartik-v/bootstrap-fileinput​​Demo地址:​​https://plugins.krajee.com/file-input/demo​​中文文档:​​http://www.bootstrap-fileinput.com/​​API:​​https://plugins.krajee.com/file-input​​

二、代码部分

1、前端代码:

<div class="modal fade" id="batchAddOilDaily">
        <div class="modal-dialog" style="width:800px">
            <div class="modal-content">
                <div class="modal-header">
                    <button type="button" class="close" data-dismiss="modal" aria-hidden="true"
                            onclick="clear_batchAdd()" id="btn_close_batchAdd">
                        ×
                    </button>
                    <h3 class="modal-title" id="batchAddOilDailyLabel">文件上传</h3>
                </div>
                <div class="modal-body">
                    <div class="col-md-9 input-group">
                        <input id="excelUpload" type="file" name="file" class="form-control"/>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-success" data-dismiss="modal">关闭</button>
                    <button type="button" class="btn btn-success">提交更改</button>
                </div>
            </div>
        </div>
    </div>

2、javascript渲染部分:

//使用bootstrap-fileinput渲染
        $('#excelUpload').fileinput({
           language:'zh',   //设置语言
           uploadUrl:'/opms/oilDaily/uploadOilDaily',   //上传的地址
            allowedFileExtensions:['xls','xlsx'],   //接收的文件后缀
            theme:'fa',         //主题设置
            dropZoneTitle:'可以将文件拖放到这里',
            uploadAsync:true,            //默认异步上传
            showPreview:true,            //是否显示预览
            showUpload: true,//是否显示上传按钮
            showRemove: true, //显示移除按钮
            showCancel:true,   //是否显示文件上传取消按钮。默认为true。只有在AJAX上传过程中，才会启用和显示
            showCaption: true,//是否显示文件标题，默认为true
            browseClass: "btn btn-success", //文件选择器/浏览按钮的CSS类。默认为btn btn-primary
            dropZoneEnabled: true,//是否显示拖拽区域
            maxFileSize: 0,//最大上传文件数限制，单位为kb，如果为0表示不限制文件大小
            minFileCount: 1, //每次上传允许的最少文件数。如果设置为0，则表示文件数是可选的。默认为0
            maxFileCount: 1, //每次上传允许的最大文件数。如果设置为0，则表示允许的文件数是无限制的。默认为0
            previewFileIcon: "<i class='glyphicon glyphicon-king'></i>",//当检测到用于预览的不可读文件类型时，将在每个预览文件缩略图中显示的图标。默认为<i class="glyphicon glyphicon-file"></i>
            previewFileIconSettings: {
                'docx': '<i ass="fa fa-file-word-o text-primary"></i>',
                'xlsx': '<i class="fa fa-file-excel-o text-success"></i>',
                'xls': '<i class="fa fa-file-excel-o text-success"></i>',
                'pptx': '<i class="fa fa-file-powerpoint-o text-danger"></i>',
                'jpg': '<i class="fa fa-file-photo-o text-warning"></i>',
                'pdf': '<i class="fa fa-file-archive-o text-muted"></i>',
                'zip': '<i class="fa fa-file-archive-o text-muted"></i>',
            },
            msgFilesTooMany: "选择上传的文件数量({n}) 超过允许的最大数值{m}！",//字符串，当文件数超过设置的最大计数时显示的消息 maxFileCount。默认为：选择上传的文件数（{n}）超出了允许的最大限制{m}。请重试您的上传！
            elErrorContainer:'#kartik-file-errors'
        }).on('change',function () {
        }).on('fileuploaded',function (event,data,previewId,index) {//异步上传成功处理
            swal('系统提示',data.response.result,'success');
              //关闭文件上传的模态对话框
            $('#batchAddOilDaily').modal('hide');
            //重新刷新bootstrap-table数据
            $('#table').bootstrapTable('refresh');
            //清除文件输入 此方法清除所有未上传文件的预览，清除ajax文件堆栈，还清除本机文件输入
            $('#excelUpload').fileinput('clear');
            $('#excelUpload').fileinput('clear').fileinput('disable');
        }).on('fileerror',function (event,data,msg) {           //异步上传失败处理
            swal('系统提示','文件上传失败','error');
        });

3、Controller层:

@RequestMapping(value = "/uploadOilDaily",method = {RequestMethod.GET,RequestMethod.POST},
            produces = "text/html;charset=UTF-8")
    @ResponseBody
    public String uploadOilDaily(@RequestParam(value = "file",required = false)
                                 MultipartFile[] file, HttpServletRequest request, ModelMap model){

        System.out.println("请求路径正确");
        //获取文件名
        String fileName= file[0].getOriginalFilename();
        System.out.println(fileName);
        int result=0;
        try{
            result=oilDailyService.batchAddOilDaily(file[0]);
        }catch (Exception e){
            System.out.println("文件处理异常");
            e.printStackTrace();
            return "{\"result\":\"excel文件数据导入失败!\"}";
        }
        if(result>0) {
            return "{\"result\":\"excel文件数据导入成功!\"}";
        }else{
            return "{\"result\":\"excel文件数据导入失败!\"}";
        }
    }

4、Service:

int batchAddOilDaily(MultipartFile file) throws IOException;

5、Service实现类:

@Override
    public int batchAddOilDaily(MultipartFile file) throws IOException {
        boolean flag=false;
        int result=0;
        //存放excel表中所有oilDaily数据
        List<OilDaily> oilDailyList=new ArrayList<>();

        //得到上传的文件名
        String fileName=file.getOriginalFilename();
        //截取文件名的后缀
        String suffix=fileName.substring(fileName.lastIndexOf(".")+1);
        //file.getInputStream()方法，返回InputStream对象，读取文件内容
        InputStream ins=file.getInputStream();
        Workbook wb=null;
        /*判断文件后缀
            XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
            HSSF － 提供读写Microsoft Excel XLS格式档案的功能。
            */
        if(suffix.equals("xlsx")){
            wb=new XSSFWorkbook(ins);
        }else{
            wb=new HSSFWorkbook(ins);
        }
        //获取excel表单的sheet对象
        Sheet sheet=wb.getSheetAt(0);
        //如果sheet不为空，就开始遍历表中的数据
        if(sheet!=null){
            //line=1:从表的第2行开始获取记录
            for(int line=1;line<=sheet.getLastRowNum();line++){
                //excel表单的sheet的行对象
                Row row=sheet.getRow(line);
                //如果某行为空，跳出本行
                if(row==null){
                    continue;
                }
                //获取第一个单元格的内容
                WellInfo wellInfo=new WellInfo();
                wellInfo.setWellId(row.getCell(0).getStringCellValue());
                //获取第二个单元格的内容
                Date oilTime=row.getCell(1).getDateCellValue();
                //第三个单元格:日产油量
                row.getCell(2).setCellType(CellType.NUMERIC);
                BigDecimal outOil=new BigDecimal(NumberToTextConverter.toText(row.getCell(2).getNumericCellValue()));
                //日产液量
                row.getCell(3).setCellType(CellType.NUMERIC);
                BigDecimal outFluid=new BigDecimal(NumberToTextConverter.toText(row.getCell(3).getNumericCellValue()));
                //日产水量
                row.getCell(4).setCellType(CellType.NUMERIC);
                BigDecimal outWater=new BigDecimal(NumberToTextConverter.toText(row.getCell(4).getNumericCellValue()));
                //日产气量
                row.getCell(5).setCellType(CellType.NUMERIC);
                BigDecimal outGas=new BigDecimal(NumberToTextConverter.toText(row.getCell(5).getNumericCellValue()));
                //含盐量
                row.getCell(6).setCellType(CellType.NUMERIC);
                BigDecimal oilSalinity=new BigDecimal(NumberToTextConverter.toText(row.getCell(6).getNumericCellValue()));
                //动液面
                row.getCell(7).setCellType(CellType.NUMERIC);
                BigDecimal oilDynamic=new BigDecimal(NumberToTextConverter.toText(row.getCell(7).getNumericCellValue()));
                //油压
                row.getCell(8).setCellType(CellType.NUMERIC);
                BigDecimal oilPress=new BigDecimal(NumberToTextConverter.toText(row.getCell(8).getNumericCellValue()));
                //套压
                row.getCell(9).setCellType(CellType.NUMERIC);
                BigDecimal casingPress=new BigDecimal(NumberToTextConverter.toText(row.getCell(9).getNumericCellValue()));
                //回压
                row.getCell(10).setCellType(CellType.NUMERIC);
                BigDecimal backPress=new BigDecimal(NumberToTextConverter.toText(row.getCell(10).getNumericCellValue()));
                //备注
                String oilRemark=null;
                if(row.getCell(11)!=null){
                     oilRemark=row.getCell(11).getStringCellValue();
                }
                OilDaily oilDaily=new OilDaily();
                oilDaily.setWellInfo(wellInfo);
                SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
                oilDaily.setOilTime(sdf.format(oilTime));
                oilDaily.setOutOil(outOil);
                oilDaily.setOutFluid(outFluid);
                oilDaily.setOutWater(outWater);
                oilDaily.setOutGas(outGas);
                oilDaily.setOilSalinity(oilSalinity);
                oilDaily.setOilDynamic(oilDynamic);
                oilDaily.setOilPress(oilPress);
                oilDaily.setCasingPress(casingPress);
                oilDaily.setBackPress(backPress);
                oilDaily.setOilRemark(oilRemark);

                //开始进行含水率和油气比的指标计算
                oilTargetUtil oilTargetUtil=new oilTargetUtil();
                oilTargetUtil.oilDailyTargetCalculation(oilDaily);

                //先获取当前最早的产油日期
                String earlyDate=oilDailyDao.findEarlyDate(oilDaily.getWellInfo().getWellId());
                //重新进行产量预警计算
                try {
                    oilTargetUtil.oilProductAlarm(oilDaily, earlyDate);
                }catch (Exception e){
                    System.out.println("批量导入时的产量预警报异常!");
                    e.printStackTrace();
                }
               oilDailyList.add(oilDaily);
            }//end for循环

            for(OilDaily oilDaily:oilDailyList){
//                System.out.println(oilDaily);
                //先判断数据库中是否存在当日数据，若存在，则更新，若不存在，则插入
                List<OilDaily> oilDailyList1=oilDailyDao.findOilDaily(oilDaily);
                if(oilDailyList1.size()>0){
                    oilDaily.setOilId(oilDailyList1.get(0).getOilId());
                    result=oilDailyDao.updateOilDaily(oilDaily);
                }else{
                    result=oilDailyDao.addOilDaily(oilDaily);
                }

            }

        }//end sheet!=null
        return result;
    }

6、Dao层代码:

//插入数据
@Insert("insert into oilDaily(wellId,oilTime,outOil,outFluid,outWater,outGas,waterContent,oilGasRatio," +
            "oilSalinity,oilDynamic,alarmId,oilPress,casingPress,backPress,oilRemark)" +
            "values(#{wellInfo.wellId},#{oilTime},#{outOil},#{outFluid},#{outWater},#{outGas}," +
            "#{waterContent},#{oilGasRatio},#{oilSalinity},#{oilDynamic},#{alarmInfo.alarmId},#{oilPress}," +
            "#{casingPress},#{backPress},#{oilRemark})")
    Integer addOilDaily(OilDaily oilDaily);
//更新数据
@Update("update oilDaily set oilTime=#{oilTime},outOil=#{outOil},outFluid=#{outFluid},outWater=#{outWater}," +
            "outGas=#{outGas},waterContent=#{waterContent},oilGasRatio=#{oilGasRatio},oilSalinity=#{oilSalinity}," +
            "oilDynamic=#{oilDynamic},alarmId=#{alarmInfo.alarmId},oilPress=#{oilPress},casingPress=#{casingPress}," +
            "backPress=#{backPress},oilRemark=#{oilRemark} " +
            "where oilId=#{oilId} and wellId=#{wellInfo.wellId}")
    Integer updateOilDaily(OilDaily oilDaily);

三、实现效果:

1、准备导入的excel数据：

2、在前端页面中导入该文件:

3、查看数据库是否更新了数据: