JAVA利用ajaxfileUpload.js和poi实现excel文件上传存取

2019独角兽企业重金招聘Python工程师标准>>>

jsp页面上传模块

<div style="padding:20px;" id="upload_layer">
    <input type="file" name= "myfile" id="file" value="选择文件"><br /><br />
    <button type="button" οnclick="uploadFile()" class="btn btn-default">开始导入</button>
    &nbsp;&nbsp;
    <a href="">下载模板</a>
</div>

 

点击开始导入的js代码

function uploadFile(){
            //showLoading();
            $.ajaxFileUpload({
                type: "POST",
                url: "url",
                //data: {file: $("#file")},//要传到后台的参数
                secureuri: false,//是否启用安全提交，默认为false
                fileElementId: 'file',//文件选择框的id属性
                dataType: 'json',//服务器返回的格式
                async: false,
                success: function (data) {
                    //closeLoading();
                    if (data.result == '0') {
                        //自己的逻辑
                    }
                },
                error: function (data, status, e) {
                        //自己的逻辑
                }
            });
        }

java后台存放上传的文件

public String importPicFile1(@RequestParam MultipartFile myfile, HttpServletRequest request) {
    Map<String, Object> map = new HashMap<String, Object>();
    if (myfile.isEmpty()) {
        map.put("result", "error");
        map.put("msg", "上传文件不能为空");
    } else {
        String fileName= myfile.getOriginalFilename();
        String fileBaseName = FilenameUtils.getBaseName(originalFilename);
        String floderName = fileBaseName + "_" + System.currentTimeMillis();
        try {

            String fileRootPath= request.getSession().getServletContext().getRealPath("/upload/" + floderName);
            //把上传的文件放到服务器的文件夹下
            FileUtils.copyInputStreamToFile(myfile.getInputStream(), new File(genePicPath, originalFilename));
            String result = importData(genePicPath, fileName);
            map.put("result", result);
        } catch (Exception e) {
            map.put("result", "error");
            map.put("msg", e.getMessage());

        }
    }
    String result = String.valueOf(JSONObject.fromObject(map));
    return result;
}

保存从Excel读取的信息

import java.util.List;
import java.util.Map;

public class UploadResult {
    
    //读取的正常数据
    private List<Map<String,Object>> resultInfo;

    //错误数据原因,所在行
    private List<Map<String,String>> errorInfo;

    //有效行数
    private Integer totalRow;

    //有效行对应Excel表的第几行
    private List<String> rows;

    public List<Map<String, Object>> getResultInfo() {
        return resultInfo;
    }

    public void setResultInfo(List<Map<String, Object>> resultInfo) {
        this.resultInfo = resultInfo;
    }

    public List<Map<String, String>> getErrorInfo() {
        return errorInfo;
    }

    public void setErrorInfo(List<Map<String, String>> errorInfo) {
        this.errorInfo = errorInfo;
    }

    public Integer getTotalRow() {
        return totalRow;
    }

    public void setTotalRow(Integer totalRow) {
        this.totalRow = totalRow;
    }

    public List<String> getRows() {
        return rows;
    }

    public void setRows(List<String> rows) {
        this.rows = rows;
    }
}

 

读取上传文件的内容

public static UploadResult readXlsSite(String genePicPath, String originalFilename) throws IOException {
        UploadResult uploadResult = new UploadResult();
        String file = genePicPath + File.separator + originalFilename;
        InputStream is = new FileInputStream(file);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

        // 存储读取的信息
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        // 存取出现错误的行和列
        List<Map<String, String>> errorInfos = new ArrayList<Map<String, String>>();

        // 存储总有效行数
        int totalRow = 0;

        List<String> rows = new ArrayList<>();

        // 只取第一张工作表Sheet
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
        if (hssfSheet != null) {
            // 循环行Row(Excel删除行内容后并没有真正删除行信息,会出现getLastRowNum大于实际看到行数的情况)
            int lastRowNum = hssfSheet.getLastRowNum();
            int cellNum = hssfSheet.getRow(0).getPhysicalNumberOfCells();
            for (int i = 2; i <= lastRowNum; i++) {
                boolean isUseFul = false;
                Map<String, String> errorInfo = new HashMap<>();
                Map<String, Object> stringObjectMap = new HashMap<String, Object>();
                HSSFRow hssfRow = hssfSheet.getRow(i);
                if (hssfRow == null) {
                    continue;
                }

                // 判断当前行是否为有效行(检验是否有列信息不为空)
                for (int j = 0; j < cellNum; j++) {
                    HSSFCell name = hssfRow.getCell(j);
                    if (name != null) {
                        if (getValue(name) != "") {
                            isUseFul = true;
                            break;
                        }
                    }
                }
                //如果为有效行,有效行+1,否则退出当前行判断
                if (isUseFul) {
                    totalRow++;
                } else {
                    continue;
                }

                //对列信息进行验证
                for (int j = 0; j < cellNum; j++) {
                    // 获取当前列对应的列名
                    HSSFCell name = hssfRow.getCell(j);
                    String info = getValue(hssfSheet.getRow(0).getCell(j));

                    //获取当前列是否为必填(Excel颜色为红)
                    HSSFFont font = hssfSheet.getRow(0).getCell(j).getCellStyle().getFont(hssfWorkbook);
                    short color = font.getColor();

                    if (IndexedColors.RED.index == color) {
                        String errorInfoStr = errorInfo.get("errorInfo");
                        if (name == null) {
                            errorInfo.put("errorRow", (i + 1) + "");
                            if (errorInfoStr == null) {
                                errorInfo.put("errorInfo", "第" + (j + 1) + " 列不能为空 ");
                            } else {
                                errorInfo.put("errorInfo", errorInfoStr + "第" + (j + 1) + "列不能为空 ");
                            }
                            break;
                        } else {
                            if (!judgeCellValueIsNotNull(name)) {
                                errorInfo.put("errorRow", (i + 1) + "");
                                if (errorInfoStr == null) {
                                    errorInfo.put("errorInfo", "第" + (j + 1) + " 列不能为空 ");
                                } else {
                                    errorInfo.put("errorInfo", errorInfoStr + "第" + (j + 1) + " 列不能为空 ");
                                }
                                break;
                            } else {
                                stringObjectMap.put(info, getValue(name));
                            }
                        }
                    } else {
                        // 如果当前列不为必填项,向map中封装有值的情况
                        if (name != null && judgeCellValueIsNotNull(name)) {
                            stringObjectMap.put(info, getValue(name));
                        }
                    }

                }
                if (!errorInfo.isEmpty()) {
                    errorInfos.add(errorInfo);
                }
                if (errorInfo.get("errorInfo") == null) {
                    list.add(stringObjectMap);
                    rows.add((i + 1) + "");
                }
            }
        }
        uploadResult.setResultInfo(list);
        uploadResult.setErrorInfo(errorInfos);
        uploadResult.setTotalRow(totalRow);
        uploadResult.setRows(rows);
        hssfWorkbook.close();
        is.close();
        //删除上传的文件
        clearFiles(genePicPath);
        return uploadResult;
    }

读取excel每单元格的数据

private static String getValue(HSSFCell hssfCell) {
    if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
        // 返回布尔类型的值
        return String.valueOf(hssfCell.getBooleanCellValue());
    } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
        // 返回数值类型的值
        return String.valueOf(hssfCell.getNumericCellValue());
    } else {
        // 返回字符串类型的值
        return String.valueOf(hssfCell.getStringCellValue());
    }
}

删除上传的临时文件(如果不删除的话,上传的文件过多会导致项目臃肿)

//删除文件和目录
private static void clearFiles(String fileRootPath){
    File file = new File(fileRootPath);
    if(file.exists()){
        deleteFile(file);
    }
}
private static void deleteFile(File file){
    if(file.isDirectory()){
        File[] files = file.listFiles();
        for(int i=0; i<files.length; i++){
            deleteFile(files[i]);
        }
    }
    file.delete();
}

处理获取的数据

public String importData(String genePicPath, String originalFilename) throws IOException {
    String file = genePicPath + File.separator + originalFilename;
    InputStream is = new FileInputStream(file);
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    // 插入数据条数(有效数据可能在DB已存在)
    int count = 0;
    //有效数据条数
    int totalCount = 0;
    UploadResult UploadResult = new UploadResult();
    List<Map<String, String>> errorInfos = null;
    //再次处理数据(主要判断该数据是否已存在,不存在才存)
    List<Map<String, Object>> correctInfos= new ArrayList<>();
    List<String> rows = null;
    try {
        UploadResult = HandleExcel.readXlsSite(genePicPath, originalFilename);
        totalCount = UploadResult.getResultInfo().size();
        errorInfos = UploadResult.getErrorInfo();
        rows = UploadResult.getRows();
        if (totalCount == 0) {
            return "上传文件数据异常";
        }
        for (int i = 0; i < totalCount; i++) {
            // 获取读取数据中的唯一性标识数据
            String siteId = UploadResult.getResultInfo().get(i).get("??").toString();
            // 检查该数据是否已存在
            Document doc = MongoUtils.findOne(Consts.COLLECTION_SITE, Filters.eq("??", siteId));
            Map<String, String> existRow = new HashMap<>();
            if (doc == null) {
                Map<String, Object> correctInfo = UploadResult.getResultInfo().get(i);
                correctInfos.add(correctInfo);
                count++;
                continue;
            }
            existRow.put("existRow", rows.get(i));
            existRow.put("errorRow", rows.get(i));
            errorInfos.add(existRow);
        }
        if (count > 0) {
            MongoUtils.saveAll(correctInfos, Consts.COLLECTION_SITE);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    hssfWorkbook.close();
    is.close();
    // 封装返回信息
    String result = "有效数据" + UploadResult.getTotalRow() + "条(成功" + count + "条,失败" + (UploadResult.getTotalRow() - count) + "条)。<br/>";
    int size = errorInfos.size();
    if (size > 0) {
        result = result + "错误说明 :<br/>";
        for (int i = 0; i < size; i++) {
            Map<String, String> errorinfo = errorInfos.get(i);
            if (errorinfo.get("errorRow") != null) {
                if (errorinfo.get("errorInfo") != null) {
                    result = result + "第" + errorinfo.get("errorRow") + "行:";
                    result = result + errorinfo.get("errorInfo");
                }
                if (errorinfo.get("existRow") != null) {
                    result = result + "第" + errorinfo.get("existRow") + "行已存在";
                }
                result = result + "; <br/>";
            }
        }
    }
    return result;
}

springMVC配置

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/>

转载于:https://my.oschina.net/scymore/blog/886944