excel表格数据导入java封装类

excel表格数据导入java封装类

需要导入的pom文件依赖

<dependency>
	    <groupId>org.apache.xmlbeans</groupId>
	    <artifactId>xmlbeans</artifactId>
	    <version>2.6.0</version>
	</dependency>
	 <!-- poi -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.9</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>3.9</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml-schemas</artifactId>
	    <version>3.9</version>
	</dependency>

	<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-collections4 -->
	<dependency>
	    <groupId>org.apache.commons</groupId>
	    <artifactId>commons-collections4</artifactId>
	    <version>4.1</version>
	</dependency>

后台代码

/**
	传入两个值：
		1，需要导入的execl表格
		2，需要封装数据的封装类
*/
@Operation(name="添加到账导入信息")
@RequestMapping("upload")
@ResponseBody
public Map<String, Object> addPayment(ReceiveThePayment receiveThePayment,@RequestParam MultipartFile file) throws Exception{
    Map<String, Object> map = new HashMap<String,Object>(16);	
	XSSFWorkbook workbook =null;
	
	//把MultipartFile转化为File
	CommonsMultipartFile cmf= (CommonsMultipartFile)file;
	DiskFileItem dfi=(DiskFileItem) cmf.getFileItem();
	File fo=dfi.getStoreLocation();
 
	//创建Excel，读取文件内容
	workbook = new XSSFWorkbook(FileUtils.openInputStream(fo));
 
	//获取第一个工作表
	XSSFSheet sheet = workbook.getSheetAt(0);
	
	//获取sheet中第一行行号
	int firstRowNum = sheet.getFirstRowNum();
	//获取sheet中最后一行行号
	int lastRowNum = sheet.getLastRowNum();
	
	try {
		//循环插入数据（根据表格来插入数据）
		for(int i=firstRowNum+1;i<=lastRowNum;i++){
			XSSFRow row = sheet.getRow(i);
			
            XSSFCell date = row.getCell(0);//日期
			if(date!=null){
				date.setCellType(Cell.CELL_TYPE_STRING);
				String stringCellValue = date.getStringCellValue();
				Date parse = new SimpleDateFormat("yyyyMMdd").parse(stringCellValue);
				Instant instant = parse.toInstant();
		        ZoneId zoneId = ZoneId.systemDefault();
		        LocalDateTime localDateTime = instant.atZone(zoneId).toLocalDateTime();
				receiveThePayment.setDate((localDateTime));   //将数据封装到pojo类中
			}
            
			XSSFCell consignor = row.getCell(1);//到款方
			if(consignor!=null){
				consignor.setCellType(Cell.CELL_TYPE_STRING);
				receiveThePayment.setConsignor((consignor.getStringCellValue()));    //将数据封装到pojo类中
			}
			
			XSSFCell arrivalAmount = row.getCell(2);//到款金额
			if(arrivalAmount!=null){
				arrivalAmount.setCellType(Cell.CELL_TYPE_STRING);
				String stringCellValue = arrivalAmount.getStringCellValue();
				Double result = Double.parseDouble("".equals(stringCellValue)?"0.00":stringCellValue);
				receiveThePayment.setArrivalAmount(result);   //将数据封装到pojo类中
			}

			//插入数据if(receiveThePaymentService.insertReceiveThePaymentSelective(receiveThePayment)) {
				map.put("status",true);
				map.put("msg", "操作成功");
			}else {
				map.put("status",false);
				map.put("msg", "操作失败");
				return map;
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
    return map;
}