|
功能实现：一个controller接口读取不同的excel,并导入到对应的数据库表中（一张excel表对应一张数据库表）。 方案：用POI工具，抽取导入公共类（ExcelReadUtil），不同的数据库导入，都继承这个公共类中的抽象类excelReadTemplate，实现抽象类中的packageObject（封装实体类）与insertObject（批量插入）方法。 具体实现：
jar包引入
org.apache.poi
poi
4.0.1
org.apache.poi
poi-ooxml
4.0.1
编写公共类 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
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 //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
return false;
}
}
return true;
}
}
编写某一张表的导入
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);//批量插入数据库的方法没写，
}
}
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;
}
}
ResponseVo 类是统一的返回实体类
public class ResponseVo 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 ResponseVo error(T data){
return ResponseVo(500,"失败",data);
}
//成功
private static ResponseVo success(){
return ResponseVo(200,"成功");
}
private static ResponseVo success(T data){
return ResponseVo(200,"成功",data);
}
}
|