本篇是导入相关模板,入口参数为被导入文件的地址。此demo支持一个sheet的导入,若需要多个sheet需要调整下参数,请大家自行验证。
此篇demo需引入POI相关依赖,依赖为poi-ooxml的4.1.2版本。
在方法getWorkbook中,会涉及到一些枚举类的使用。大家不必过于纠结,这个枚举类实际上就是对03和07版本Excel后缀的封装。
下面是本导入demo的模板,如果大家需要POI的导出模板,在文末有相关连接,大家自取。
/**
* @Description : 导入excel - poi-ooxml V4.1.2
* @Author : Future Buddha
* @Date: 2022-04-14 10:21
*/
@Slf4j
public class ImportExcelUtil {
/**
* 导入方法入口
* @param filePath 导入文件路径地址
* @return
* @throws BizException
*/
public static List<List<String>> importExcel(String filePath) throws BizException {
Workbook wookbook = getWorkbook(filePath);
if (Objects.isNull(wookbook)) {
return Lists.newArrayList();
}
List<List<String>> list = Lists.newArrayList();
int numberOfSheets = wookbook.getNumberOfSheets();
//遍历sheet - 此例中只有一个sheet
for (int i = 0; i < numberOfSheets; i++) {
if (i > 0) {
break;
}
//获取第一个sheet
Sheet sheet = wookbook.getSheetAt(i);
if (Objects.isNull(sheet)) {
continue;
}
//获取第一行和最后一行
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
//遍历row并获取改行个单元格的值
for (int j = firstRowNum; j < lastRowNum; j++) {
Row row = sheet.getRow(j);
if (Objects.isNull(row)) {
continue;
}
List<String> objs = Lists.newArrayList();
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
for (int m = firstCellNum; m < lastCellNum; m++) {
Cell cell = row.getCell(m);
String obj = getCellVal(cell);
objs.add(obj);
}
list.add(objs);
}
}
return list;
}
/**
* 获取该单元格的值
* @param cell
* @return
*/
private static String getCellVal(Cell cell) {
CellType cellTypeEnum = cell.getCellTypeEnum();
String value;
switch (cellTypeEnum) {
case STRING:
value = cell.getStringCellValue();
break;
case NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
value = TimeFormatUtil.dataToString(DateUtil.getJavaDate(cell.getNumericCellValue()));
}else{
value = String.valueOf(cell.getNumericCellValue());
}
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
value = cell.getCellFormula();
break;
case _NONE:
case BLANK:
case ERROR:
default:
value = "";
}
return value;
}
/**
* 创建基于路径下Excel的Workbook对象
* @param filePath
* @return
*/
private static Workbook getWorkbook(String filePath) {
String fileType = filePath.substring(filePath.lastIndexOf(".") + 1);
Workbook workbook;
try {
if (Objects.equals(ExcelSuffixEnum.XLS.getSuffix(), fileType)) {
workbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
} else if (Objects.equals(ExcelSuffixEnum.XLSX.getSuffix(), fileType)) {
workbook = new XSSFWorkbook(new FileInputStream(new File(filePath)));
} else {
throw new BizException(USER_REQUIRE_FILE_FORM_NON_MATCH_EXCEPTION.detailMsg(filePath));
}
} catch (FileNotFoundException e) {
log.error(USER_REQUIRE_FILE_NON_EXIST_EXCEPTION.getReason(), e);
throw new BizException(USER_REQUIRE_FILE_NON_EXIST_EXCEPTION);
} catch (IOException e) {
log.error(SYSTEM_IO_EXCEPTION.getReason(), e);
throw new BizException(SYSTEM_IO_EXCEPTION);
}
return workbook;
}
}
导出demo相关https://blog.csdn.net/u011528738/article/details/123157236?spm=1001.2014.3001.5502