Java导入excel文件

 

 

@RestController
@Slf4j
@RequestMapping("/oppor/book")
public class OptnContractBookProvider {
	
	@Autowired
	private ImportExcelOptnContractBookService importExcelOptnContractBookService;

   
    @PostMapping(value = "importOptnContractBookExcel", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    @ResponseBody
    public ResponseVo<Integer> importOptnContractBookExcel(
    		@RequestPart(value = "file") MultipartFile file,
            HttpServletRequest request,
            HttpServletResponse response) {
        ResponseVo<Integer> responseVo = new ResponseVo<Integer>();
        try {
            if (file.isEmpty()) {
                responseVo.setCode(-1);
                responseVo.setMsg("上传失败，请选择文件");
                return responseVo;
            }
            if (file.getInputStream() != null) {
                Integer i = importExcelOptnContractBookService.importOptnContractBookExcel(file.getInputStream()) ;
                log.info("OptnContractBookProvider--importOptnContractBookExcel() - i=" + i);
                if (i >= 1) {
                    responseVo.setCode(0);
                    responseVo.setData(i);
                    responseVo.setMsg("导入成功");
                    return responseVo;
                }
            }else {
                log.info("OptnContractBookProvider--InputStream=" + null);
            }
        } catch (IOException e) {
            log.warn("OptnContractBookProvider --> importOptnContractBookExcel() - ", e);
        }
        responseVo.setCode(-1);
        responseVo.setMsg("上传失败.");
        return responseVo;//根据需求返回，这里不明细写ResponseVo
    }


}

 

 

public Integer importOptnContractBookExcel(InputStream excelFile){
		log.info("ImportExcelOptnContractBookServiceImpl--importOptnContractBookExcel() - 开始执行" );
		Integer count = 0;
		List<OptnContractBookVo> optnContractVoList = readExcelOptnContractBook(excelFile);
		log.info("importOptnContractBookExcel() - optnContractVoList.size()=" + optnContractVoList.size());
		for (OptnContractBookVo optnContractBookVo : optnContractVoList) {
			OptnContractBook optnContractBook = new OptnContractBook();
			BeanUtils.copyProperties(optnContractBookVo,optnContractBook);
			String optnProjectCode = optnContractBookVo.getOptnProjectCode();
			OptnContractBook optnContractBookData = optnContractBookService.getOptnContractBookById(optnProjectCode);
			if (optnContractBookData == null) {//新增
				Integer insertOptnContractBook = optnContractBookService.insertOptnContractBook(optnContractBook);
				if (insertOptnContractBook > 0) {
					count++;
				}
			}else {//更新
				Integer modifyOptnContractBookById = optnContractBookService.modifyOptnContractBookById(optnContractBook);
				if (modifyOptnContractBookById > 0) {
					count++;
				}
			}
		}
		
		return count;
	}

	private List<OptnContractBookVo> readExcelOptnContractBook(InputStream inputStream) {
		List<OptnContractBookVo> resultList = new ArrayList<>();
		try {
			Workbook workbook = WorkbookFactory.create(inputStream);
			Sheet sheet = workbook.getSheetAt(0);
			int lastRow = sheet.getLastRowNum();
			int lastCell = sheet.getRow(1).getLastCellNum();//得到列数，默认数据从excel第2行开始
			for (int k = 1; k <= lastRow; k++) {
				Row row = sheet.getRow(k);
				if (row == null) {
					continue;
				}
				Boolean notSet = false;
				OptnContractBookVo vo = new OptnContractBookVo();
				for (int key = 0; key < lastCell; key++) {
					Cell cell = row.getCell(key);
					String value = ExcelUtilServiceImpl.getCellValueText(cell);
					if (value != null) {
						value = value.trim();
					}
					if (key == 2) {
						vo.setOptnProjectCode(value);
					}
					if (key == 3) {
						vo.setContractNo(value);
					}
					if (key == 4) {
						vo.setContractName(value);
					}
					if (key == 6) {
				
				SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
						if (value != null && !value.equals("")){
							Date contractDate = sdf.parse(value);
							Calendar cal=Calendar.getInstance();
							cal.set(Calendar.YEAR,2018);
							cal.set(Calendar.MONTH,10);
							cal.set(Calendar.DAY_OF_MONTH,1);
							Date month10 = cal.getTime();
							if(contractDate.before(month10)){
								notSet = true;
								break;
							}
							vo.setContractDate(contractDate);
						}
					}
					if (key == 21) {
						
						Integer subProjectStatus = 0;
						if (value.equals("未完结")){
							subProjectStatus = 0;
						}else if (value.equals("未完结")){
							subProjectStatus = 1;
						}
						vo.setSubProjectStatus(subProjectStatus);
					}
					if (key == 8) {
						vo.setMarketType(replaceMarketType(value));
					}
					if (key == 11) {
						vo.setChannelName(value);
					}
					
				}
				if(!notSet) {
					resultList.add(vo);
				}
			}
		} catch (Exception e) {
			log.warn("ImportExcelOptnContractBookServiceImpl --> readExcelOptnContractBook() - ", e);
			e.printStackTrace();
		}
		return resultList;
	}

	private Integer replaceMarketType(String marketTypeStr){
		Integer marketType = 0;
		if (marketTypeStr == null){
			return 3;
		}
		if (marketTypeStr.equals("省")){
			marketType = 1;
		}else if (marketTypeStr.equals("市")){
			marketType = 2;
		}else {
			marketType = 3;
		}
		return marketType;
	}

 

DAO

 

public interface OptnContractBookMapper extends BaseMapper<OptnContractBook> {
	
	
	public OptnContractBook getOptnContractBookById(@Param("optnProjectCode") String optnProjectCode);
	
	public Integer insertOptnContractBook(OptnContractBook optnContractBook);

	public Integer modifyOptnContractBookById(OptnContractBook optnContractBook);

}