欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

上传Excel数据到mongo

程序员文章站 2022-06-11 14:58:46
...

Excel第一行为数据列的 key, 第二行为数据列的数据类型
Controller
@RequestMapping(value="/data/import", method=RequestMethod.POST)
public Message<String> importOpenStrategyData(
        @RequestParam(value="collectionName") String collectionName,
        @RequestParam(value="file", required = false) MultipartFile file) {

    try {
        if(file == null || file.isEmpty() || file.getBytes().length == 0 || file.getSize() == 0) {
            throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "导入文件为空");
        }
        if(!file.getOriginalFilename().endsWith(".xlsx")) {
            throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "请导入Excel(*.xlsx)文件");
        }
        File tempFile = SysService.createTempFile();//创建临时文件
        file.transferTo(tempFile);
        return Message.createSuccessMessage(openStrategyService.importOpenStrategyData(collectionName, tempFile));
    } catch (Exception e) {
        // TODO Auto-generated catch block
        throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "文件格式有问题,请检查");
    }
Service
public String importOpenStrategyData(String collectionName, File file) {
    
    InputStream is = null;
    XSSFWorkbook book = null;
    long total = 0L;// 统计导入数据条数
    long exceptinNums = 0L;// 异常数据条数
    try {

        is = new FileInputStream(file);
        book = new XSSFWorkbook(is);
        XSSFSheet sheet = book.getSheetAt(0);
        // 获取总共的行数
        int rowNums = sheet.getLastRowNum();
        if(rowNums <= 2) {
            throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "导入文件为空");
        }
        // 获取列名称及各列对应的数据类型
        Row titleRow = sheet.getRow(0);//第一行为td_name
        Row typeRow = sheet.getRow(1);//第二行为td_type
        if(titleRow == null || typeRow == null) {
            throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "数据列名称和数据类型不能为空");
        }
        int titleColNums = titleRow.getLastCellNum();
        int typeColNums = typeRow.getLastCellNum();
        if(titleColNums != typeColNums) {
            throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "数据列名称和数据类型不能完全匹配");
        }
        // colName,colType存放在数组
        List<String> colNames = new ArrayList<>();
        List<String> colTypes = new ArrayList<>();
        for(int i = 0; i<titleColNums; i++) {
            Cell td_name = titleRow.getCell(i);
            td_name.setCellType(CellType.STRING);
            String colName = td_name.getStringCellValue();
            if(StringUtils.isEmpty(colName)) {
                throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "数据列名称不能为空");
            }
            colNames.add(colName.trim());
            
            Cell td_type = typeRow.getCell(i);
            td_name.setCellType(CellType.STRING);
            String colType = td_type.getStringCellValue();
            if(StringUtils.isEmpty(colType)) {
                throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "数据类型不能为空");
            }
            colTypes.add(colType);
        }
        if(colNames.size() == 0 || colTypes.size() == 0) {
            throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "数据列名称和数据类型不能为空");
        }
        for (int i = 2; i < rowNums + 1; i++) {
            total++;
            Row valueRow = sheet.getRow(i);
            if(valueRow == null) {
                exceptinNums++;
                continue;
            }
            int cols = valueRow.getLastCellNum();// 获取数据有多少列
            if(titleColNums != cols) {
                exceptinNums++;
                continue;
            }
            Document doc = new Document();
            for (int j = 0; j < cols; j++) {
                String name = colNames.get(j);
                String dType = colTypes.get(j);
                Cell cell = valueRow.getCell(j);
                // 如果单元格为空,这一行跳过,记为数据异常
                if(cell == null) {
                    exceptinNums++;
                    break;
                }
                if(UploadDataTypeEnum.STRING.getId().equals(dType)) {
                    cell.setCellType(CellType.STRING);
                    doc.append(name, cell.getStringCellValue());
                }else if(UploadDataTypeEnum.NUMERIC.getId().equals(dType)) {
                    cell.setCellType(CellType.NUMERIC);
                    doc.append(name, cell.getNumericCellValue());
                }else if(UploadDataTypeEnum.DATE.getId().equals(dType)) {
                    cell.setCellType(CellType.STRING);
                    String cellValue = cell.getStringCellValue();
                    cellValue = cellValue.trim();
                    Date date = DateUtils.parse(cellValue, "yyyyMMdd");
                    if (date == null || cellValue.contains("-")) {
                        date = DateUtils.parse(cellValue, "yyyy-MM-dd");
                    }else if (date == null || cellValue.contains("/")) {
                        date = DateUtils.parse(cellValue, "yyyy/MM/dd");
                    }else {
                        throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "数据格式有问题,请检查!");
                    }
                    doc.append(name, date);
                }else if(UploadDataTypeEnum.BOOLEAN.getId().equals(dType)) {
                    cell.setCellType(CellType.BOOLEAN);
                    doc.append(name, cell.getBooleanCellValue());
                }
            }
            mongoTemplate.save(doc, collectionName);
        }
        
        return "导入成功,共导入了"+ (total - exceptinNums) + "条数据,有 " +exceptinNums+"条数据异常";
    }catch (Exception e) {
        // TODO: handle exception
        throw new HException(HErrorCode.ARGUMENT_ILLEGAL, "文件上传异常,请检查");
    } finally {
        try {
            if (book != null) {
                book.close();
            }
            if (is != null) {
                is.close();
            }
        } catch (Exception e) {
            throw new HException(HErrorCode.SYSTEM_EXCEPTION);
        }
    }
}