// 读取文件数据
private Map<String, Map> readExcel(MultipartFile file) throws IOException {
checkFile(file);
// 获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
// 创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
Map<String, Map> requset = new HashMap<>();
if (workbook != null) {
// 第一个sheet页为目录,不解析
for (int sheetNum = 1; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
// 获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
continue;
}
// 获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
// 获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
// 循环所有行
Map<String, List> rowMap = new HashMap<>();
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
// 获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
Cell cellKey = row.getCell(0);
String rowMapKey = null;
if (getCellValue(cellKey) != null) {
rowMapKey = getCellValue(cellKey).toString();
}
List<String> cellList = new ArrayList<>();
for (int cellNum = 1; cellNum <= 5; cellNum++) {
Cell cellValue = row.getCell(cellNum);
String rowMapValue = null;
if (getCellValue(cellValue) != null) {
rowMapValue = getCellValue(cellValue).toString();
}
cellList.add(rowMapValue);
}
if (rowMapKey != null && !"".equals(rowMapKey)) {
rowMap.put(rowMapKey, cellList);
}
}
requset.put(sheet.getSheetName(), rowMap);
}
workbook.close();
}
return requset;
}
// 校验文件是否合法
private static void checkFile(MultipartFile file) throws IOException {
// 判断文件是否存在
if (null == file) {
throw new FileNotFoundException("文件不存在!");
}
// 获得文件名
String fileName = file.getOriginalFilename();
// 判断文件是否是excel文件
if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
throw new IOException(fileName + "不是excel文件");
}
}
// 解析file文件
private static Workbook getWorkBook(MultipartFile file) throws IOException {
// 获得文件名
String fileName = file.getOriginalFilename();
// 创建Workbook工作薄对象,表示整个excel
Workbook workbook = null;
try {
// 获取excel文件的io流
InputStream is = file.getInputStream();
// 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith("xls")) {
// 2003
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx")) {
// 2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}
// 解析单元格数据
private static Object getCellValue(Cell cell) {
Object cellValue = null;
if (cell == null || "".equals(cell)) {
return cellValue;
}
// 获取单元格数据类型
CellType cellType = cell.getCellTypeEnum();
if (cellType == CellType.STRING) {
cellValue = cell.getStringCellValue();
} else if (cellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = cell.getDateCellValue();
} else {
cellValue = cell.getNumericCellValue();
}
} else if (cellType == CellType.BOOLEAN) {
cellValue = cell.getBooleanCellValue();
} else if (cellType == CellType.FORMULA) {
cellValue = cell.getCellFormula();
} else if (cellType == CellType.BLANK) {
cellValue = "";
}
return cellValue;
}
excel后台解析
最新推荐文章于 2024-04-17 19:11:45 发布