不解释,直接上代码,懒得整理,破败之刃给你,自己去砍
public static List<FofAdviseStrategyPoolLocalVo> readExcel2List(String name, MultipartFile batfile) throws Exception {
InputStream is = batfile.getInputStream();
//校验文件格式及生成对应文档
Workbook workbook = getWorkbook(is, name);
List<FofAdviseStrategyPoolLocalVo> excelEntityList = new ArrayList<FofAdviseStrategyPoolLocalVo>();
//只取第一页sheet
Sheet sheet = workbook.getSheetAt(0);
Row row0 = sheet.getRow(0);
int minColIx = row0.getFirstCellNum();
int maxColIx = row0.getLastCellNum();
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (isRowEmpty(row)) {
continue;
}
List<String> rowList = new ArrayList<String>();
FofAdviseStrategyPoolLocalVo excelEntity = new FofAdviseStrategyPoolLocalVo();
//遍历最小行开始
String cell0 = getCellValue(row.getCell(0));
if (StringUtils.isBlank(cell0)) {
//第一列行号为空,即为走到最后一行
logger.error("文件行号不能为空,错误数据所在行数:" + rowNum);
CoreCommonUtils.raiseBizException(BizCode.Unknown);
}
for (int colIx = minColIx; colIx < maxColIx; colIx++) {
// Cell cell = row.getCell(colIx, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
String cell = getCellValue(row.getCell(colIx));
if (StringUtils.isBlank(cell)) {
//等于空的时候,返回空字符串
rowList.add("");
continue;
}
rowList.add(cell.toString());
}
excelEntity.setSeqNo(StringUtils.isEmpty(rowList.get(0)) ? null : Integer.valueOf(rowList.get(0)));
excelEntity.setFundId(rowList.get(1));
excelEntity.setFundName(rowList.get(2));
excelEntityList.add(excelEntity);
}
return excelEntityList;
}
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把数字当成String来读,避免出现1读成1.0的情况
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
public static boolean isRowEmpty(Row row) {
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
return false;
} else {
break;
}
}
return true;
}
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (excel2003L.equals(fileType)) {
wb = new HSSFWorkbook(inStr); // 2003-
} else if (excel2007U.equals(fileType)) {
wb = new XSSFWorkbook(inStr); // 2007+
} else {
throw new Exception("解析的文件格式有误!");
}
return wb;
}