java 导出文件上传模板，上传Excel文件批量导入数据

后端代码

controller层 

@RequestMapping(value = "/importExcel", method = {RequestMethod.POST, RequestMethod.GET})
@ResponseBody
public ResponseBean importExcel(HttpServletRequest request) {
    SUser sUser = JwtService.getConnUser(request);

    MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
    List<MultipartFile> file = multipartRequest.getFiles("file");

    String fileName = file.get(0).getOriginalFilename();
    try {
        return importService.importExcel(fileName, file.get(0), sUser);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return new ResponseBean(false, 0, "解析xls文件失败", null);
}

service层

@Transactional(readOnly = false,rollbackFor = Exception.class)
    @Override
    public ResponseBean importExcel(String fileName, MultipartFile file) throws Exception {
        List<XXX> buses = new ArrayList<>();
        List<XXX> isCoveredList = new ArrayList<>();
        List<String> isCoveredIds = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$")) {
            return new ResponseBean(false, 0, "上传文件格式不正确", null);
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        wb = new HSSFWorkbook(is);

        Sheet sheet = wb.getSheetAt(0);
        Bus bus;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
             //r = 2 表示从第三行开始循环 如果你的第三行开始是数据
             Row row = sheet.getRow(r);
             //通过sheet表单对象得到 行对象
             if (row == null){
                 continue;
             }
     
            bus = new Bus();
//                if( row.getCell(0).getCellType() != 1){
//                    // 循环时，得到每一行的单元格进行判断
//                     throw new MyException("导入失败(第"+(r+1)+"行,用户名请设为文本格式)");
//                }

            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//设置读取转String类型
            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//设置读取转String类型

            //得到每一行的第一个单元格的值 
            String vehicleNo = row.getCell(0).getStringCellValue();
            if(vehicleNo == null || vehicleNo.isEmpty()){
                //判断是否为空
                return new ResponseBean(false, 0, "导入失败(第"+(r+1)+"行,未填写)", null);
            }

            //得到每一行的第二个单元格的值 日期
            String exp = null;

            if (!StringUtils.isEmpty(row.getCell(1))) {
                if (row.getCell(1).getCellType() == 1){//string类型
                    exp = row.getCell(1).getStringCellValue();
                } else {//日期类型
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    try {
                        exp = sdf.format(row.getCell(1).getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }
            }else {
                return new ResponseBean(false, 0, "导入失败(第"+(r+1)+"行,时间未填写)", null);
            }

            String plateColorCodeName = row.getCell(2).getStringCellValue();
            Integer plateColorCode;
            if (!StringUtils.isEmpty(plateColorCodeName)) {
                if ("蓝色".equals(plateColorCodeName)){
                    plateColorCode = 1;
                } else if ("黄色".equals(plateColorCodeName)){
                    plateColorCode = 2;
                } else {
                    plateColorCode = 0;
                }
            } else {
                return new ResponseBean(false, 0, "导入失败(第"+(r+1)+"行,未填写)", null);
            }

            //对车牌号进行查重操作，若有重复的则覆盖，并将被覆盖的存到一个list里
            Bus isNeedCover = checkIsNeedCover(vehicleNo,plateColorCode);
            if (!StringUtils.isEmpty(isNeedCover.getId())) {
                isCoveredList.add(isNeedCover);
                isCoveredIds.add(isNeedCover.getId());
            }

            String company = null;
            if (!StringUtils.isEmpty(row.getCell(3))) {
                row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);//设置读取转String类型
                company = row.getCell(3).getStringCellValue();
            }


            //完整的循环一次 就组成了一个对象
            bus.setVehicleNo(vehicleNo);
            bus.setExp(exp);
            bus.setPlateColorCode(Integer.valueOf(plateColorCode));
            if (!StringUtils.isEmpty(company)){
                bus.setCompany(company);
            }

            buses.add(bus);
        }
        this.insertBatch(buses);
        //删除被覆盖的list
        this.deleteBatchIds(isCoveredIds);
        return new ResponseBean(true, 0, "解析xls文件成功", isCoveredList);
    }

 

html页面

<button class="layui-btn btn-export margintop-16" id="btn_export" style="float: left;"
        onclick="exportFormwork()">模板导出
</button>

<div class="file-upload" id="handleXls" style="float: left;line-height: 51.4px;">
    <input type="file" name="multipartFile" id="xlsFile" style="opacity: 0;width: 0px; height: 1px;"/>
    <label for="multipartFile" class='layui-btn layui-btn-warm layui-btn layui-btn-sm layui-btn-fluid' onclick="inputTrigger()" style="width: 120px;">选择xls文件</label>
</div>

js

/**
 * 导入模板导出
 */
function exportFormwork() {
    location.href = url.exportFormwork;
}
/**
* 导入
*/
function inputTrigger() {
$('#xlsFile').trigger('click')
}

var xlsFile = [];
/**
 * 上传文件
 */
$(document).on('change','#xlsFile', function () {
    for (var i = 0; i < $(this)[0].files.length; i++) {
        xlsFile.push($(this)[0].files[i]);
    }

    var formData = new FormData();
    for (var i = 0; i < xlsFile.length; i++) {
        formData.append('file', xlsFile[i]);
    }

    $.mask_element('#test_mask', 'xls文件解析中'); //打开加载层
    $.ajax({
        url: url.importShuttleBus,
        method: "post",
        data: formData,
        dataType: 'JSON',
        cache: false, // 不缓存
        processData: false, // jQuery不要去处理发送的数据
        contentType: false, // jQuery不要去设置Content-Type请求头
        success: function (data) {
            $.mask_close('#test_mask');//关闭加载层
            if (data.isSuccess) {
                //刷新table
                searchSubmitInfo();
                layer.msg(data.responseMsg);
            } else {
                layer.msg(data.responseMsg, {icon: 5})
            }
        },
        error: function (e) {
            layer.msg('上传文件过大[总上传大小不得超过50M]', {icon: 5});
            $.mask_close('#test_mask');//关闭加载层
        }
    })
    xlsFile = [];
    event.target.value="";
})