excel批量导入,从第二行拿取字段类型
servimport org.apache.dolphinscheduler.api.service.BaseService;
import org.apache.dolphinscheduler.api.service.FileService; import org.apache.dolphinscheduler.common.enums.Status; import org.apache.dolphinscheduler.common.model.Result; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.*; @Service public class ScanExcelService{ private static final Logger logger = LoggerFactory.getLogger(FileService.class); private static final String OFFICE_EXCEL_XLS = ".xls"; private static final String OFFICE_EXCEL_XLSX = ".xlsx"; private static final String EXCEL_NAME = "excelName"; private static final String EXCEL_TYPE = "excelType"; private static String ERRORVALUETYPE = "非法字符"; private static String UNKNOWVALUETYPE = "未知类型"; public List<Map<String,String>> scanValue(MultipartFile file) throws Exception { //检验excel文件 checkFile(file); //读取字段 Workbook workbook = null; try { InputStream input = file.getInputStream(); workbook = WorkbookFactory.create(input); } catch (Exception e) { logger.error("io.lrd.datax.utils.ExcelReaderUtil analysis error", e); throw new Exception("Error parsing Excel format"); } Sheet sheet = workbook.getSheetAt(0); if (sheet == null) { throw new Exception("Error parsing Excel format"); } List<Map<String, String>> list = new ArrayList<>(); //遍历行 for (int rownum = 0; rownum < sheet.getLastRowNum(); rownum++) { //拿到行 Row row = sheet.getRow(rownum); if (row == null){ throw new Exception("current row data cannot be empty"); } //遍历列 for (int cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) { Map<String, String> excelMap = new HashMap<>(); if (rownum == 0) { //拿到单元格数据内容 String cellValue = getCellValue(row.getCell(cellnum)); excelMap.put(EXCEL_NAME, cellValue); excelMap.put(EXCEL_TYPE, ""); list.add(excelMap); } if (rownum == 1) { Map<String, String> currentExcelMap = list.get(cellnum); String cellType = getCellType(row.getCell(cellnum)); currentExcelMap.put(EXCEL_TYPE, cellType); } } }return list; } /** * 得到类型 * * @param cell * @return */ private String getCellType(Cell cell) { String type = ""; if (cell == null) { return null; } else if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) { if (HSSFDateUtil.isCellDateFormatted(cell)) { type = "Date"; } else { type = "int"; } } else if (cell.getCellTypeEnum().equals(CellType.STRING)) { type = "String"; } else if (cell.getCellTypeEnum().equals(CellType.BOOLEAN)) { type = "Boolean"; } else if (cell.getCellTypeEnum().equals(CellType.FORMULA)) { type = "String"; } else if (cell.getCellTypeEnum().equals(CellType.BLANK)) { type = "String"; } return type.trim(); } /** * 得到数据 * * @param cell * @return */ private String getCellValue(Cell cell) { String value = ""; if (cell != null) { CellType cellType = cell.getCellTypeEnum(); if (cellType.equals(CellType.NUMERIC)) { if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 SimpleDateFormat sdf = null; Date d = cell.getDateCellValue(); sdf = new SimpleDateFormat("yyyy/MM/dd"); value = sdf.format(d); // 验证short值 } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式 cell.setCellType(CellType.STRING); value = String.valueOf(cell.getRichStringCellValue().getString()); } else { value = String.valueOf(cell.getNumericCellValue()); } } else if (cellType.equals(CellType.STRING)) { value = cell.getStringCellValue(); } else if (cellType.equals(CellType.BOOLEAN)) { value = cell.getBooleanCellValue() + ""; } else if (cellType.equals(CellType.FORMULA)) { value = cell.getCellFormula() + ""; } else if (cellType.equals(CellType.BLANK)) { value = ""; } else if (cellType.equals(CellType.ERROR)) { value = ERRORVALUETYPE; } else { value = UNKNOWVALUETYPE; } } return value.trim(); } /** * 检验excel文件 * * @param file * @throws Exception */ private static void checkFile(MultipartFile file) throws Exception { if (file == null || file.isEmpty()) { throw new Exception("Error file cannot be empty"); } if (!file.getOriginalFilename().endsWith(OFFICE_EXCEL_XLS) && !file.getOriginalFilename().endsWith(OFFICE_EXCEL_XLSX)) { throw new Exception("Error parsing Excel format"); } } }