批量修改信息：Excel绑定

前端用到的包：jquery.uploadify.min.js、uploadify.css

Java用到的包：java.io、org.apache.poi

页面内容：

<tr style="height: 50px;">
    <td nowrap class="kv-label" style="text-align:right">选择文件&nbsp; &nbsp;</td>
    <td class="kv-content"><input type="file" name="uploadify" id="uploadify" /></td>
</tr>
<tr style="height: 50px;">
    <td class="kv-label">&nbsp;</td>
    <td  align="center" class="kv-content">
        <a href="javascript:com.fileUpload.doImportFile_device();"  class="easyui-linkbutton">上传</a>
    </td>
</tr>

<link href="<%=contextPath%>/plugins/uploadify/uploadify.css"  rel="stylesheet" type="text/css" />
<script  src="<%=contextPath%>/plugins/uploadify/jquery.uploadify.min.js"  type="text/javascript"></script>

js设置参数和后台接收地址

$(function () {
    com.fileUpload.importOnLoad_device();
});
if (typeof com == "undefined") {
    var com = {};
}
com.fileUpload= {}
com.fileUpload.importOnLoad_device = function(){
    $("#uploadify").uploadify({
        'swf': sy.contextPath+'/plugins/uploadify/uploadify.swf',
        'uploader': sy.contextPath+'/nbiot/batchFileUpload/importFileExcel_bindDevice_noSecurity?sessionId='+$("#sessionid").val(),
        'fileObjName': 'uploadify',
        'queueID': 'fileQueue',
        'fileTypeDesc' : "请选择Excel文档",
        'fileTypeExts' : '*.xls;*.xlsx;',
        'buttonText':'选择...',
        'auto': false,
        'successTimeout':1200,
        'formData':{'proId':''},
        onUploadStart:function(){
            var param={"savePath":"upload",'proId':$("#proName").combobox('getValue')};
            $("#uploadify").uploadify("settings","formData",param);
        },
        onUploadSuccess: function(file, data, response) {
            $.messager.progress('close');
            if(!response) {//response:true
                $.messager.alert("提示","导入失败",'error');
            } else{
                var dataList =JSON2.parse(data);
                if(dataList.total == -1) {
                    $.messager.alert("提示","导入失败",'error');
                    return;
                }else if(dataList.total == -2) {
                    $.messager.alert("提示","导入失败,导入模板文件错误，请选择正确模板导入",'error');
                    return;
                }else if (dataList.total==0) {
                    $.messager.alert("提示", "导入成功", 'info');
                    return;
                }else {
                    $.messager.alert("提示", "部分设备信息导入成功", 'info');
                    $("#maingrid").datagrid({
                        pagination: false,
                        rownumbers: true,
                        columns: [[
                            {field:'error',title: '失败原因',align: 'center',width: 200},
                            {field:'meterNo',title: '表号',align: 'center',width: 120},
                            {field:'deviceName',title: '设备名称',align: 'center',width: 120},
                            {field:'deviceCode',title: '设备标识码',align: 'center',width: 120}
                        ]],
                        data: dataList,
                        width: 'auto',
                        height:'200'
                    });
                }
            }
        }
    });
}
com.fileUpload.doImportFile_device = function(){
    if(com.fileUpload.checkFile()){
        var win = $.messager.progress({
            title:'档案导入',
            msg:'导入进行中，请稍候....'
        });
        //上传文件
        $("#uploadify").uploadify("upload","*","");
    }
};

jQuery uploadify文件参数详解：

https://www.cnblogs.com/Marydon20170307/p/9406120.html

后台Java代码：

@ResponseBody
@RequestMapping("/importFileExcel_bindDevice_noSecurity")
public EasyDataGrid importFileExcel_bindDevice(HttpSession session, @RequestParam("uploadify") MultipartFile uploadify,
                                               String proId, String savePath){
    SessionInfo info=(SessionInfo) session.getAttribute(ConfigUtil.getSessionInfoName());
    byte[] bytes;
    try {
        bytes = uploadify.getBytes();
        String uploadDir = session.getServletContext().getRealPath("/")+savePath;
        File dirPath = new File(uploadDir);
        if (!dirPath.exists()) {
            dirPath.mkdirs();
        }
        String sep = System.getProperty("file.separator");
        File uploadedFile = new File(uploadDir + sep + uploadify.getOriginalFilename());
        FileCopyUtils.copy(bytes, uploadedFile);
        return this.service.importFileExcel_bindDevice(info,uploadedFile,savePath,proId);
    } catch (Exception e) {
        e.printStackTrace();
        EasyDataGrid dataGrid = new EasyDataGrid();
        dataGrid.setTotal((long) -1);
        return dataGrid;
    }
}

public EasyDataGrid importFileExcel_bindDevice(SessionInfo info,File file, String savePath, String proId) {
    EasyDataGrid dataGrid = new EasyDataGrid();
//存放执行失败的记录，用于在前台显示
    List<Map> errorList = new ArrayList<Map>();
    InputStream inStream  = null;
    try {
        inStream  = new FileInputStream(file);
        Workbook workBook = WorkbookFactory.create(inStream );
        Sheet sheet = workBook.getSheetAt(0);

        // 通过文件流创建Excel文档对象
        String meterNo = "";//表号
        String deviceName = "";// 设备名称
        String deviceCode = "";// 设备标识码

        int colNum = 0;//Excel列数
        int rowNum = 0;//Excel行数
        for (Row row : sheet) {
            rowNum = rowNum + 1;
            if (rowNum == 1) continue;// 排除第一行
            if (row == null) continue;// 排除空行
            if (row.getCell(0) == null || StringUtil.isNull(row.getCell(0))) continue;// 排除空行

            colNum = row.getLastCellNum();
            if(colNum<3){
                dataGrid.setTotal((long) -2);
                return dataGrid;
            }else if(colNum>3){
                if(StringUtil.isNotNull(row.getCell(6)) && StringUtil.isNotNull(row.getCell(6).toString())){
                    dataGrid.setTotal((long) -2);
                    return dataGrid;
                }
            }
//接收excel内容
            if (row.getCell(0) != null) {
                if (row.getCell(0).getCellType() == 0) {
                    meterNo = row.getCell(0).getNumericCellValue() + "";
                    if (StringUtil.isNotNull(meterNo)) {
                        meterNo = (long) Double.parseDouble(meterNo) + "";
                    }
                } else {
                    meterNo = row.getCell(0).getStringCellValue().replace(" ", "");
                    meterNo = StringUtil.trimStr(meterNo);
                }
            } else meterNo = "";
            if (row.getCell(1) != null) {
                if (row.getCell(1).getCellType() == 0) {
                    deviceName = row.getCell(1).getNumericCellValue() + "";
                    if (StringUtil.isNotNull(deviceName)) {
                        deviceName = (long) Double.parseDouble(deviceName) + "";
                    }
                } else {
                    deviceName = row.getCell(1).getStringCellValue().replace(" ", "");
                    deviceName = StringUtil.trimStr(deviceName);
                }
            } else deviceName = "";
            if (row.getCell(2) != null) {
                if (row.getCell(2).getCellType() == 0) {
                    deviceCode = row.getCell(2).getNumericCellValue() + "";
                    if (StringUtil.isNotNull(deviceCode)) {
                        deviceCode = (long) Double.parseDouble(deviceCode) + "";
                    }
                } else {
                    deviceCode = row.getCell(2).getStringCellValue().replace(" ", "");
                    deviceCode = StringUtil.trimStr(deviceCode);
                }
            } else deviceCode = "";
//将符合要求的信息存入数据库，并返回错误代码
            int code = updateMeterParam(info, proId, meterNo, deviceName, deviceCode);
            if (code == -1) {
                Map map = new HashMap<String,Object>();
                map.put("meterNo", meterNo);
                map.put("deviceName", deviceName);
                map.put("deviceCode", deviceCode);
                map.put("error", "该设备未在系统中建档");
                errorList.add(map);
            }
            if (code == -2) {
                Map map = new HashMap<String,Object>();
                map.put("meterNo", meterNo);
                map.put("deviceName", deviceName);
                map.put("deviceCode", deviceCode);
                map.put("error", "设备标识码已存在");
                errorList.add(map);
            }
            if (code == -3) {
                Map map = new HashMap<String,Object>();
                map.put("meterNo", meterNo);
                map.put("deviceName", deviceName);
                map.put("deviceCode", deviceCode);
                map.put("error", "设备已注册，不能修改");
                errorList.add(map);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        dataGrid.setTotal((long) -1);
        dataGrid.setRows(errorList);
        return dataGrid;
    }
    dataGrid.setTotal((long) errorList.size());
    dataGrid.setRows(errorList);
    return dataGrid;
}