读取的问价格式如下：

直接上代码：

controller层

@ApiOperation(value = "全自动导入资源和编目")@PostMapping("/autoExcelToSql")public Response readExcelToList(@RequestPart("file") MultipartFile file) throws IOException, BizException {String filename = file.getOriginalFilename();Assert.isNull(filename, "无法获取文件信息");Assert.isTrue(!filename.contains(".xls"), "上传文件类型错误");// 获取文件输入流InputStream inputStream = file.getInputStream();// 获取excel工作簿对象List<AutoExcelToSqlDTO> tableInfos = new ArrayList<>();String substring = filename.substring(filename.lastIndexOf("."), filename.length());if (".xls".equals(substring)) {HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());tableInfos = rdResourceDirService.excelToList(workbook);} else if (".xlsx".equals(substring)) {XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());//tableInfos = excelxToList(workbook);}// 关闭输入流log.info("tableInfos=" + tableInfos);inputStream.close();return Response.success(tableInfos);}

service层

/*** 文件是.xls** @param workbook* @return*/@Overridepublic List<AutoExcelToSqlDTO> excelToList(HSSFWorkbook workbook) throws BizException {// 得到excel工作表对象HSSFSheet sheetAt = workbook.getSheetAt(0);List<AutoExcelToSqlDTO> autoExcelToSqlDTOS = new ArrayList<>();// 负载行数for (int i = 0; i < sheetAt.getLastRowNum() + 1; i++) {// 获取行数Row row = sheetAt.getRow(i);//首行（即表头）不读取if (row != null) {if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2) {continue;}int ii = i + 1;//读取当前行中单元格数据，索引从0开始// 序号double number = 0;if (row.getCell(0) != null) {row.getCell(0).setCellType(NUMERIC);number = row.getCell(0).getNumericCellValue();}// 资源名称String resName = null;if (row.getCell(1) != null) {row.getCell(1).setCellType(STRING);resName = row.getCell(1).getStringCellValue();}Assert.isTrue(StringUtil.isBlank(resName), "序号为" + ii + "行的数据缺失资源名称参数!");// 归集状态String collectionSituation = null;int collectionSituationInt;if (row.getCell(2) != null) {row.getCell(2).setCellType(STRING);collectionSituation = row.getCell(2).getStringCellValue();}Assert.isTrue(StringUtil.isBlank(collectionSituation), "序号为" + ii + "行的数据缺失归集状态参数!");Assert.isTrue(!collectionSituation.equals("未归集") && !collectionSituation.equals("已归集"), "序号为" + ii + "行的数据归集状态参数格式错误!");if (collectionSituation.equals("未归集")) {collectionSituationInt = 1;} else {collectionSituationInt = 2;}// 更新周期String updateFrequency = null;Integer updateFrequencyInt = null;if (row.getCell(3) != null) {row.getCell(3).setCellType(STRING);updateFrequency = row.getCell(3).getStringCellValue();}if (StringUtil.isNotBlank(updateFrequency)) {switch (updateFrequency) {case "实时":updateFrequencyInt = 1;break;case "每小时":updateFrequencyInt = 2;break;case "每天":updateFrequencyInt = 3;break;case "每月":updateFrequencyInt = 4;break;case "每季度":updateFrequencyInt = 5;break;case "每半年":updateFrequencyInt = 6;break;case "每年":updateFrequencyInt = 7;break;case "不更新":updateFrequencyInt = 8;break;case "不定时":updateFrequencyInt = 9;break;}}// 是否发布(0-未发布 1-已发布)String isRelease = null;int isReleaseInt = 0;if (row.getCell(4) != null) {row.getCell(4).setCellType(STRING);isRelease = row.getCell(4).getStringCellValue();}Assert.isTrue(StringUtil.isBlank(isRelease), "序号为" + ii + "行的数据缺失发布状态参数!");Assert.isTrue(!isRelease.equals("未发布") && !isRelease.equals("已发布"), "序号为" + ii + "行的数据发布状态参数格式错误!");if (isRelease.equals("已发布")) {isReleaseInt = 1;}// 共享方式String resPower = null;int resPowerInt;if (row.getCell(5) != null) {row.getCell(5).setCellType(STRING);resPower = row.getCell(5).getStringCellValue();}Assert.isTrue(StringUtil.isBlank(resPower), "序号为" + ii + "行的数据缺失共享方式参数!");Assert.isTrue(!resPower.equals("公开") && !resPower.equals("受限"), "序号为" + ii + "行的数据共享方式参数格式错误!");if (resPower.equals("公开")) {resPowerInt = 1;} else {resPowerInt = 2;}// 资源来源(部门名称)String resGetSource = null;if (row.getCell(6) != null) {row.getCell(6).setCellType(STRING);resGetSource = row.getCell(6).getStringCellValue();}// 资源应用范围String resLevel = null;Integer resLevelInt = null;if (row.getCell(7) != null) {row.getCell(7).setCellType(STRING);resLevel = row.getCell(7).getStringCellValue();}if (StringUtil.isNotBlank(resLevel)) {switch (resLevel) {case "省级":resLevelInt = 1;break;case "市级":resLevelInt = 2;break;case "区级":resLevelInt = 3;break;}}// 提供部门（id）double resSourceDeptId = 0;if (row.getCell(9) != null) {row.getCell(9).setCellType(NUMERIC);resSourceDeptId = row.getCell(9).getNumericCellValue();}Assert.isTrue(resSourceDeptId == 0, "序号为" + ii + "行的数据缺失部门id参数!");// 资源说明String rdResourceDesc = null;if (row.getCell(10) != null) {row.getCell(10).setCellType(STRING);rdResourceDesc = row.getCell(10).getStringCellValue();}// 处理基本信息AutoExcelToSqlDTO autoExcelToSqlDTO = new AutoExcelToSqlDTO().setResName(resName).setCollectionSituation(collectionSituationInt).setUpdateFrequency(updateFrequencyInt).setIsRelease(isReleaseInt).setResPower(resPowerInt).setResGetSource(resGetSource).setResLevel(resLevelInt).setResSourceDeptId((long) resSourceDeptId).setRdResourceDesc(rdResourceDesc);// 判断是不是合并行int mergerCellRegionRow = getMergerCellRegionRow(sheetAt, row.getRowNum(), 0);i = i + mergerCellRegionRow - 1;// 读取编目信息List<AutoResDataItemDTO> autoResDataItemDTOList = new ArrayList<>();for (int j = row.getRowNum(); j <= i; j++) {Row rowItem = sheetAt.getRow(j);String colName = null;if (rowItem.getCell(11) != null) {colName = rowItem.getCell(11).getStringCellValue();}Assert.isTrue(StringUtil.isBlank(colName), "第"+ j + "行字段中文名不可为空!");String colDesc = null;if (rowItem.getCell(12) != null) {colDesc = rowItem.getCell(12).getStringCellValue();}Assert.isTrue(StringUtil.isBlank(colDesc), "第"+ j + "行字段英文名不可为空!");Assert.isNull(rowItem.getCell(13), "字段类型不能为空");String colType = null;String substring = null;if (rowItem.getCell(13) != null) {colType = rowItem.getCell(13).getStringCellValue();substring = colType.contains("(") ? colType.substring(0, colType.lastIndexOf("(")) : colType;}double colLength = 0.0;if (rowItem.getCell(14) != null) {colLength = rowItem.getCell(14).getNumericCellValue();}double colPrecesion = 0.0;if (rowItem.getCell(15) != null) {colPrecesion = rowItem.getCell(15).getNumericCellValue();}String ifKey = null;int ifKeyInt;if (rowItem.getCell(16) != null) {ifKey = rowItem.getCell(16).getStringCellValue();}if ("yes".equals(ifKey)) {ifKeyInt = 1;} else {ifKeyInt = 2;}AutoResDataItemDTO autoResDataItemDTO = new AutoResDataItemDTO().setChineseName(colDesc).setEnglishName(colName).setFieldType(substring).setFieldLength(colLength + "").setFieldAccuracy(colPrecesion + "").setKeywords(ifKeyInt);autoResDataItemDTOList.add(autoResDataItemDTO);}// 整合参数autoExcelToSqlDTO.setAutoResDataItemDTOList(autoResDataItemDTOList);autoExcelToSqlDTOS.add(autoExcelToSqlDTO);}}return autoExcelToSqlDTOS;}

/*** 判断单元格是否是合并的单格，如果是，获取其合并的行数。** @param sheet 表单* @param cellRow 被判断的单元格的行号* @param cellCol 被判断的单元格的列号* @return* @throws IOException*/private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow, int cellCol) {int retVal = 0;int sheetMergerCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergerCount; i++) {CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列int lastRow = cra.getLastRow(); // 合并单元格CELL结束行int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列if (cellRow >= firstRow && cellRow <= lastRow) {// 判断该单元格是否是在合并单元格中if (cellCol >= firstCol && cellCol <= lastCol) {retVal = lastRow - firstRow + 1; // 得到合并的行数break;}}}return retVal;}

******优化字段获取写法

/*** 文件是.xlsx** @param workbook* @return*/@Overridepublic List<AutoExcelToSqlDTO> excelxToList(XSSFWorkbook workbook) throws BizException {// 得到excel工作表对象XSSFSheet sheetAt = workbook.getSheetAt(0);List<AutoExcelToSqlDTO> autoExcelToSqlDTOS = new ArrayList<>();// 负载行数for (int i = 0; i < sheetAt.getLastRowNum() + 1; i++) {// 获取行数Row row = sheetAt.getRow(i);//首行（即表头）不读取if (row != null) {if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2) {continue;}int ii = i + 1;//读取当前行中单元格数据，索引从0开始// 序号//double number = getDoubleCellValue(row.getCell(0));//if (row.getCell(0) != null) {//row.getCell(0).setCellType(NUMERIC);//number = row.getCell(0).getNumericCellValue();//}// 资源名称String resName = getStringCellValue(row.getCell(1));Assert.isBlank(resName, "序号为" + ii + "行的数据缺失资源名称参数!");// 归集状态String collectionSituation = getStringCellValue(row.getCell(2));int collectionSituationInt;Assert.isBlank(collectionSituation, "序号为" + ii + "行的数据缺失归集状态参数!");Assert.isTrue(!collectionSituation.equals("未归集") && !collectionSituation.equals("已归集"), "序号为" + ii + "行的数据归集状态参数格式错误!");if (collectionSituation.equals("已归集")) {collectionSituationInt = 1;} else {collectionSituationInt = 2;}// 更新周期String updateFrequency = getStringCellValue(row.getCell(3));Integer updateFrequencyInt = null;if (StringUtil.isNotBlank(updateFrequency)) {switch (updateFrequency) {case "实时":updateFrequencyInt = 1;break;case "每小时":updateFrequencyInt = 2;break;case "每天":updateFrequencyInt = 3;break;case "每月":updateFrequencyInt = 4;break;case "每季度":updateFrequencyInt = 5;break;case "每半年":updateFrequencyInt = 6;break;case "每年":updateFrequencyInt = 7;break;case "不更新":updateFrequencyInt = 8;break;case "不定时":updateFrequencyInt = 9;break;default:updateFrequencyInt = null;}}// 是否发布(0-未发布 1-已发布)String isRelease = getStringCellValue(row.getCell(4));int isReleaseInt = 0;Assert.isBlank(isRelease, "序号为" + ii + "行的数据缺失资源状态参数!");Assert.isTrue(!isRelease.equals("未发布") && !isRelease.equals("已发布"), "序号为" + ii + "行的数据发布状态参数格式错误!");if (isRelease.equals("已发布")) {isReleaseInt = 1;}// 共享方式String resPower = getStringCellValue(row.getCell(5));int resPowerInt;Assert.isBlank(resPower, "序号为" + ii + "行的数据缺失共享方式参数!");Assert.isTrue(!resPower.equals("公开") && !resPower.equals("受限"), "序号为" + ii + "行的数据共享方式参数格式错误!");if (resPower.equals("公开")) {resPowerInt = 1;} else {resPowerInt = 2;}// 资源来源(部门名称)String resGetSource = getStringCellValue(row.getCell(6));// 资源应用范围String resLevel = getStringCellValue(row.getCell(7));Integer resLevelInt = null;if (StringUtil.isNotBlank(resLevel)) {switch (resLevel) {case "省级":resLevelInt = 1;break;case "市级":resLevelInt = 2;break;case "区级":resLevelInt = 3;break;default:resLevelInt = null;}}// 提供部门（id）double resSourceDeptId = getDoubleCellValue(row.getCell(9));Assert.isTrue((int) resSourceDeptId == 0, "序号为" + ii + "行的数据缺失部门id参数!");// 资源说明String rdResourceDesc = getStringCellValue(row.getCell(10));// 处理基本信息AutoExcelToSqlDTO autoExcelToSqlDTO = new AutoExcelToSqlDTO().setResName(resName).setCollectionSituation(collectionSituationInt).setUpdateFrequency(updateFrequencyInt).setIsRelease(isReleaseInt).setResPower(resPowerInt).setResGetSource(resGetSource).setResLevel(resLevelInt).setResSourceDeptId((long) resSourceDeptId).setRdResourceDesc(rdResourceDesc);// 判断是不是合并行int mergerCellRegionRow = getMergerCellRegionRowx(sheetAt, row.getRowNum(), 0);// 如果是合并行if (mergerCellRegionRow > 0) {i = i + mergerCellRegionRow - 1;}// 读取编目信息List<AutoResDataItemDTO> autoResDataItemDTOList = new ArrayList<>();for (int j = row.getRowNum(); j <= i; j++) {Row rowItem = sheetAt.getRow(j);// 英文名称String colDesc = getStringCellValue(rowItem.getCell(12));// 如果英文字段不是空，则说明这行有编目信息需要导入if (StringUtil.isNotBlank(colDesc)) {String colName = getStringCellValue(rowItem.getCell(11));// 字段类型String colType = getStringCellValue(rowItem.getCell(13));Assert.isBlank(colType, "字段类型不能为空，行" + (j + 1));String substring = colType.contains("(") ? colType.substring(0, colType.lastIndexOf("(")) : colType;// 字段长度double colLength = getDoubleCellValue(rowItem.getCell(14));// 字段精度double colPrecesion = getDoubleCellValue(rowItem.getCell(15));String ifKey = getStringCellValue(rowItem.getCell(16));int ifKeyInt;if ("yes".equals(ifKey)) {ifKeyInt = 1;} else {ifKeyInt = 2;}AutoResDataItemDTO autoResDataItemDTO = new AutoResDataItemDTO().setChineseName(colName).setEnglishName(colDesc).setFieldType(substring).setFieldLength(colLength + "").setFieldAccuracy(colPrecesion + "").setKeywords(ifKeyInt);autoResDataItemDTOList.add(autoResDataItemDTO);// 整合参数autoExcelToSqlDTO.setAutoResDataItemDTOList(autoResDataItemDTOList);}}autoExcelToSqlDTOS.add(autoExcelToSqlDTO);}}return autoExcelToSqlDTOS;}