java POI读取excel,并导入数据库

功能实现：一个controller接口读取不同的excel,并导入到对应的数据库表中（一张excel表对应一张数据库表）。
方案：用POI工具，抽取导入公共类（ExcelReadUtil），不同的数据库导入，都继承这个公共类中的抽象类excelReadTemplate，实现抽象类中的packageObject（封装实体类）与insertObject（批量插入）方法。
具体实现：

1. jar包引入

<dependency>
	<groupId>org.apache.poi</groupId>
	<arrtifactId>poi</artifactId>
	<vesion>4.0.1</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.1</version>
</dependency>

2. 编写公共类 ExcelReadUtil

public class ExcelReadUtil {
	public abstract static class ExcelReadTemplate{
		/**
		* 处理每一行数据，封装为实体类
		* beginCell：从当前行的第beginCell列开始读取
		* row：行对象
		**/
		protected abstract ResponseVo PackageObject(int beginCell,Row row);
		/**
		*将对象集合插入到数据库中
		**/
		protected abstract boolean insertObject(List list);
	}
		/**
		*beginRow：开始读取行数
		*beginCell：开始读取列数
		**/	
		public final ResponseVo execute(MultipartFile file,int beginRow,int beginCell){
			InputStream fileInputStream = null;
			try{
				fileInputStream = file.getInputStream();//文件流
				//这种方式2003/2007/2010版本都是可以处理的
				Workbook workbook = WorkbookFactory.create(fileInputStream);
				int sheetCount = worbook.getNumberOfSheets();//sheet页的数量
				for(int i = 0; i < sheetCount; i++){
					Bookean isShow = workbook.isSheetHidden(i);//判断当前页是否是隐藏页
					if(isShow){
						continue;
					}
					Sheet sheet = workbook.getSheetAt(i);
					// sheet.setForceFormlaRecalculation(true);
					int rowCount = sheet.getPhysicalNumberOfRows();//获取当前sheet页的总行数
					if(rowCount==0）{
						continue;
					}
					//遍历每一行
					List list = new ArrayList();
					for(int j = beginRow; j < rowCount; j++){
						Row row = sheet.getRow(j); // 当前行对象
						if(isRowEmpty(row)){  //isRowEmpty()方法是判断当前行是否为空行
							continue;
						}
						//int cellCount = row.getPhysicalNumberOfCells();//获取当前行的总行数
						ResponseVo responseVo = pacekgetObject(beginCell, row);
						list.add(responseVo.getData());
					}
					insertObject(list);//批量插入
				}
			}catch(Exception e){
				return ResponseVo.error("插入数据库失败”);
			}finally{
				if(fileInputStream != null){
					try{
						fileInputStream .close();
					}catch(IOException e){
						e.printStackTrace();
					}
				}
			}
			return ResponseVo.success();
		}

		/**
		* excel 文件单元格格式转换
		 */
		public static String formatConversion(Cell cell){
			SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
			String cellValue = "";
			if(ObjectUtils.isEmpty(cell)){
				return null;
			}
			CellType celltype = cell.getCellTypeEnum();
			//把数据当成String来读，避免出现1读成1.0的情况
			if(cellType == Celltype.NUMERIC){
				cell.setCellType(CellType.STRING);
			}
			cellType = cell.getCellTypeEnum();
			//判断数据的类型
			switch (cellType){
				case NUMERIC : //数字/日期
					if(DateUtil.isCellDateFormatted(cell){
						cellValue = fmt.format(cell.getDateCellValue())
					}else{
						cellValue = String.valueOf(cell.getStringCellVuale());
						if(cellValue .contains("E")){//是否包含字符串E
							cellValue = String.valueOf(new Double(cell.getNumericCellvalue()).longValue());
						}
					}
					break;
				case STRING: //字符串
					cellValue = String.valueOf(cell.getStringCellValue());
					break;
				case BOOLEAN: //boolean
					cellValue = String.valueOf(cell.getBooleanCellValue());
					break;
				case FORMULA: //公式
					try{
						cellValue = cell.getNumericCellValue()+"";
					}catch(IllegalStateException e){
						cellValue = cell.getStringCellValue();
					}
					break;
				case BLANK: //空值
					cellValue = cell.getStringValue();
					break;
				case ERROR : //故障
					cellValue = "非法字符";
					break;
				default:
					cellValue = "未知类型";
					break;
			}
			return cellValue ;
		}
		
		/**
		*判断是否是空行
		*/
		public static boolean isRowEmpty(Row row){
			for(int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++){
				Cell cell = row.getCell(c);
				if(cell!=null && cell.getCellType()!=Cell.CELL_TYPE_BLANK){
					return false;
				}
			}
			return true;
		}
}

3. 编写某一张表的导入

	public class TestClassExcelRead extends ExcelReadTemplate{
		@Autowired
		TestClassMapper testClassMapper;
		@Override
		protected ResponseVo packageObject(int beginCell, Row row){
			TestClass testClass = new TestClass();
			testClass .setName(formatConversion(row.getCell(beginCell)));
			testClass .setage(formatConversion(row.getCell(++beginCell)));//取下一个单元格的值；
			return RespinseVo.success(testClass);
 		}
		
		@Override
		protected boolean insertObject(List list){
			return testClassMapper.insertList(list);//批量插入数据库的方法没写，
		}
	}

4. controller层

public class ExcleReadController{
	@Autowired
	TestClassExcelRead testClassExcelRead ;
	
	@PostMapping("/excleRead/v1")
	public ResponseVo fileUpload(@RequestParam MultipartFile file){
		ResponseVo responseVo =	testClassExcelRead.execute(file,3,1);//从第4行第2列开始读取
		return responseVo;
	}
}

5. ResponseVo 类是统一的返回实体类

public class ResponseVo <T> implements Serializable{
	private String final long serialVersionUID = -5454646446459978;
	private Integer retCode = 200;
	private String retMsg = "成功";
	private T data;
	// get set 方法
	private ResponseVo {}
	private ResponseVo (Integer retCode,String retMsg){
		this.retCode = retCode;
		this.retMsg = retMsg;
	}
	private ResponseVo (Integer retCode,String retMsg,T data){
		this.retCode = retCode;
		this.retMsg = retMsg;
		this.data = data;
	}
	//失败
	private static<T> ResponseVo<T> error(T data){
		return ResponseVo<T>(500,"失败",data);
	}
	//成功
	private static<T> ResponseVo<T> success(){
		return ResponseVo<T>(200,"成功");
	}
	private static<T> ResponseVo<T> success(T data){
		return ResponseVo<T>(200,"成功",data);
	}
}